[DB] Insert traag naarmate data meer wordt

[.Johnny]

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:

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.

tabel	laadtijd blok1	laadtijd blok2	laadtijd blok3
item	48.89s	3m 6.07s	6m 42.78s
item_actor	2m 51.70s	6h 21m 49.17s	9h 36m 32.62s
item_referentie	14m 40.05s	34h 51m 50.45s	18+ 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...

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]

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;

[lier]

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.

[.Johnny]

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:

/*!40000

is dat een soort conditional comments voor MySQL?

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

[Hydra]

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.

[.Johnny]

Hier de nieuwe loadtimes; het scheelt enorm:

tabel	laadtijd blok1	laadtijd blok2	laadtijd blok3
item	19.53s	24.86s	38.35s
item_actor	2m 19.28s	7m 35.53s	9m 52.67s
item_referentie	13m 49.96s	29m 30.75s	30m 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:

tabel	laadtijd blok1	laadtijd blok2	laadtijd blok3
item	48.89s	3m 6.07s	6m 42.78s
item_actor	2m 51.70s	6h 21m 49.17s	9h 36m 32.62s
item_referentie	14m 40.05s	34h 51m 50.45s	18+ 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]

.Johnny schreef op maandag 11 oktober 2010 @ 13:38:
-edit:
wat betekent trouwens dit:

code:

/*!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.

[.Johnny]

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.