Toon posts:

[DB] Insert traag naarmate data meer wordt

Pagina: 1
Acties:

Onderwerpen


  • .Johnny
  • Registratie: september 2002
  • Laatst online: 07-09 13:35
Ik ben op dit moment bezig een grote hoeveelheid data te laden in een test;

het gaat om 3 blokken, van ongeveer 2M items. Daarnaast heb ik data die per item uit de 2M items, referenties naar andere items bevat. Dit zijn ongeveer 25 referenties per item (totaal dus 50M koppelingen per blok). Daarnaast zijn er per item actoren, ongeveer 5 per item (dus 10M per blok). In werkelijkheid is het iets complexer dan ik hier beschrijf, maar om het simpel te houden heb ik 3 tabellen aangemaakt;


SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE IF NOT EXISTS `item` (
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `item_actor` (
  `item_id` bigint(20) NOT NULL,
  `actor_id` bigint(20) NOT NULL,
  PRIMARY KEY (`item_id`,`actor_id`),
  KEY `actor_id` (`actor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `item_referenties` (
  `item_id` bigint(20) NOT NULL,
  `referentie_item_id` bigint(20) NOT NULL,
  PRIMARY KEY (`item_id`,`referentie_item_id`),
  KEY `referentie_item_id` (`referentie_item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

De data komt ergens anders vandaan, en ik wil graag wel de mogelijkheid hebben om terug te kunnen koppelen naar de bron; daarom moet ik de IDs aanhouden uit de bron, en dat past niet in een INT, vandaar de BIGINT.

De data laad ik uit flat files die ik heb gegenereerd door middel van een LOAD DATA LOCAL INFILE IGNORE statement. Per tabel is er 1 flatfile die in principe direct de database in kan, dus 2M regels in items.txt, 50M regels in item_referentie.txt etc.

Voor het eerste blok (draait in een loopje in PHP, na elkaar) gaat het nog redelijk; dit zijn de loadtimes voor blok1 vs blok2 vs blok3; note dat hij voor blok1 met een schone DB begint, de andere blokken komen bovenop de data die daarvoor is geladen.

tabellaadtijd blok1laadtijd blok2laadtijd blok3
item48.89s3m 6.07s6m 42.78s
item_actor2m 51.70s6h 21m 49.17s9h 36m 32.62s
item_referentie14m 40.05s34h 51m 50.45s18+ hours and counting*
*deze LOAD DATA draait nog op dit moment.

Zoals je kunt zien is de load time in het 2e en 3e blok gigantisch ontploft. Ik heb het vermoeden dat ik tegen de redelijkheid van een index size aanloop. Moet ik deze data dan maar op gaan splitsen in meerdere tabellen per blok? want dit zijn voor een test 3 blokken, ik heb er straks 14... }:O

Zoals je ook kunt zien gebruik ik MyISAM; ik begon met InnoDB, maar dat was met de items tabel al traag. InnoDB deed dezelfde query zonder index 2x zo langzaam als MyISAM met een PK. Met unique key was InnoDB zelfs 20x trager over dezelfde insert in MyISAM. Maar misschien blijft het bij InnoDB constanter ipv de exponentiele groei in laadtijd met MyISAM? of vinden jullie dat ik uberhaupt van MySQL bij zoiets moet afstappen? want kan ik straks over die index nog wel een redelijke query draaien (bv top uitrekenen van het aantal referenties per actor in een deelverzameling van items)?

  • Creepy
  • Registratie: juni 2001
  • Laatst online: 00:27

Creepy

Moderator Devschuur®

Tactical Espionage Splatterer

En de tabel locken en het updaten van de index uitschakelen tijdens het inserten? De mysqldump commandtool genereert bijv. het volgende:
LOCK TABLES `MyTable` WRITE;
/*!40000 ALTER TABLE `MyTable` DISABLE KEYS */;

.... inserts hier ...;

/*!40000 ALTER TABLE `MyTable` ENABLE KEYS */;
UNLOCK TABLES;

"I had a problem, I solved it with regular expressions. Now I have two problems". That's shows a lack of appreciation for regular expressions: "I know have star problems" --Kevlin Henney


  • lier
  • Registratie: januari 2004
  • Laatst online: 17:08

lier

MikroTik nerd

In de tijd dat ik veel met dataconversies deed, zorgden we altijd voorafgaand aan de conversie dat alle vormen van constraints en indexen "uit" stonden om de performance zo optimaal mogelijk te hebben. Dit betrof echter conversies naar MSSQL, je zou zelf moeten kijken wat de mogelijkheden met houw DBMS zijn.

Verder is het misschien handig om een connectie te openen en deze pas weer te sluiten als je proces klaar is.

Eerst het probleem, dan de oplossing


  • .Johnny
  • Registratie: september 2002
  • Laatst online: 07-09 13:35
Zal dat met die LOCK eens uitproberen. Ik heb in elk geval ook DELAY_KEY_WRITE aangezet voor de tabellen, maar dat maakt (in elk geval voor blok1) niets uit. Ook vermoed ik dat de connectie niet uitmaakt aangezien het 1 query is die de hele insert doet (LOAD DATA LOCAL INFILE) voor een text file in de dump. De overhead van nog een connectie voor de volgende file is dan te verwaarlozen. Per rij uit de file komt er voor zover ik weet geen nieuwe connectie. Dat zie ik ook terug in de query time van de laatste file van blok3 die nu nog draait; dat zijn geen individuele queries per rij, maar gewoon 1 statement die nu al ~6700+ seconden aan het draaien is.

-edit:
wat betekent trouwens dit:
code:
1
/*!40000

is dat een soort conditional comments voor MySQL?

[Voor 7% gewijzigd door .Johnny op 11-10-2010 13:58]


  • Hydra
  • Registratie: september 2000
  • Laatst online: 16-09 20:08
In vrijwel alle gevallen is het 'goedkoper' om een index na het inserten van alle data op te bouwen dan het na idere insert te doen. Indices zijn een optimalisatie waarbij je insert-speed inruilt voor select-speed.

https://niels.nu


  • .Johnny
  • Registratie: september 2002
  • Laatst online: 07-09 13:35
Hier de nieuwe loadtimes; het scheelt enorm:

tabellaadtijd blok1laadtijd blok2laadtijd blok3
item19.53s24.86s38.35s
item_actor2m 19.28s7m 35.53s9m 52.67s
item_referentie13m 49.96s29m 30.75s30m 2.80s


Je blijft wel duidelijk een toename in de laadtijd zien, maar die is nu een stuk minder dramatisch!
Ik had ook verwacht dat het unlocken en flushen daarna veel tijd zou kosten (moest de key toch instellen?) maar dat bleek niet zo te zijn.

Oude loadtimes:
tabellaadtijd blok1laadtijd blok2laadtijd blok3
item48.89s3m 6.07s6m 42.78s
item_actor2m 51.70s6h 21m 49.17s9h 36m 32.62s
item_referentie14m 40.05s34h 51m 50.45s18+ hours and counting*
*deze LOAD DATA draait nog op dit moment.


Mijn vraag die ik nu over houd; zou het dan toch de moeite zijn om het met InnoDB te doen? Of is dat niet verstandig met zulke grote (BIGINT) keys? Gezien de totale laadtijd ga ik denk ik vanmiddag eens een testje doen.

- edit:
okay; net een test begonnen met InnoDB maar dat is direct al zo traag dat ik het weer gestopt heb. Is dit dan echt niet aan InnoDB besteed?

[Voor 4% gewijzigd door .Johnny op 12-10-2010 11:25]


  • GlowMouse
  • Registratie: november 2002
  • Niet online

GlowMouse

wees solidair

.Johnny schreef op maandag 11 oktober 2010 @ 13:38:
-edit:
wat betekent trouwens dit:
code:
1
/*!40000

is dat een soort conditional comments voor MySQL?
Ja, conditioneel op versienummer (hier voeren MySQL 4.0 en hoger de code tussen de comments wel uit).
.Johnny schreef op dinsdag 12 oktober 2010 @ 09:50:
Mijn vraag die ik nu over houd; zou het dan toch de moeite zijn om het met InnoDB te doen? Of is dat niet verstandig met zulke grote (BIGINT) keys? Gezien de totale laadtijd ga ik denk ik vanmiddag eens een testje doen.
Let even op dat InnoDB tweaking vereist en anders te langzaam zal zijn. Op http://www.mysqlperforman...ance-optimization-basics/ staan de settings die het belangrijkst zijn. Afhankelijk van je hardware zal parallel restore sneller zijn, de tool daarvoor is mk-parallel-restore, en dat kan een factor 2 schelen.

geeft geen inhoudelijke reacties meer


  • .Johnny
  • Registratie: september 2002
  • Laatst online: 07-09 13:35
Nog even een hele late toevoeging mbt het disablen van Keys op MyISAM tabellen: voor de primary keys heeft dat totaal geen effect. Als je data clean is kun je die dus beter achteraf toevoegen, en dan alle keys tegelijk per tabel in 1 ALTER TABLE statement.
Pagina: 1


Nintendo Switch (OLED model) Apple iPhone 13 LG G1 Google Pixel 6 Call of Duty: Vanguard Samsung Galaxy S21 5G Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True

Tweakers maakt gebruik van cookies

Bij het bezoeken van het forum plaatst Tweakers alleen functionele en analytische cookies voor optimalisatie en analyse om de website-ervaring te verbeteren. Op het forum worden geen trackingcookies geplaatst. Voor het bekijken van video's en grafieken van derden vragen we je toestemming, we gebruiken daarvoor externe tooling die mogelijk cookies kunnen plaatsen.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Forum cookie-instellingen

Bekijk de onderstaande instellingen en maak je keuze. Meer informatie vind je in ons cookiebeleid.

Functionele en analytische cookies

Deze cookies helpen de website zijn functies uit te voeren en zijn verplicht. Meer details

janee

    Cookies van derden

    Deze cookies kunnen geplaatst worden door derde partijen via ingesloten content en om de gebruikerservaring van de website te verbeteren. Meer details

    janee