Door
Dennis WhoCares
op 16-06-2017 08:06
gewijzigd op 16-06-2017 10:03
7.707 views
Hi all,
ik ben weer eens bezig om mijn imports te versnellen, almede door advies om gehele csv bestanden te importeren naar een tijdelijke tabel.
Dit werkt idd een heeeel stuk sneller ;-)
Nou heb ik 1 tabel : dns_tickets
en nog een tabel : csvTickets
normaliter deed ik per regel uit de csv een insert of update, op basis van de key.
Dat begint na 10.000-20.000 regels beetje langzaam te gaan ;-)
Dus vervolgens doe ik nou :
...
$pdo = new PDO("mysql:host=$host;dbname=$database",
$username, $password,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
...
$stmt = $pdo->prepare("TRUNCATE csvTickets");
$stmt->execute();
$affectedRows = $pdo->exec("LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE csvTickets
FIELDS TERMINATED BY ".$pdo->quote(';')."
LINES TERMINATED BY ".$pdo->quote("\n"));
echo "Imported $affectedRows records";
$pdo->exec('DELETE FROM csvTickets WHERE TicketCode = "TicketCode"');
Dit werkt geweldig binnen een seconde is die al klaar met ALLE tickets te importeren.
Nou wou ik dus mijn dns_tickets tabel updaten:
UPDATE dns_tickets dns
INNER JOIN csvTickets csv
ON dns.ticket_code = csv.TicketCode
SET dns.ticket_watchers = csv.Watchers
WHERE dns.ticket_source = 2
Dit heb ik uitgevoerd in phpmyadmin om te kijken of het werkt.
Helaas loopt alles vast en is alles unresponsive.
Heeft iemand enig idee wat hier aan de hand is?
System time van Mysql sprong 90% en na 5 minuten gewacht te hebben heb ik via ssh de server een force reboot gegeven.
Ik hoopte dat ik zo nog sneller 20.000 records kon updaten van de al bestaande 170.000
Dit is allemaal wel erg weinig.
Begin eens met de key_buffer_size te verhogen naar 128MB. Stel ook een innodb_buffer_pool_size in, bijvoorbeeld ook 128MB. Je hebt ruimte zat, dus je kan dit allemaal nog veel hoger maken. Misschien heeft een wat grotere query_cache_size ook nut voor je leessnelheid, bijvoorbeeld ook 128MB.
Dit zijn puur eerste getallen, je kan dit vrij fors verhogen indien gewenst. innodb_buffer_pool_size geeft je in de meeste gevallen de grootste winst, en deze wordt vaak enkele gigabytes groot gemaakt. 5-6GB op een 8GB server is niet ongewoon.
ik heb ze naar 6GB gezet, ook innodb, maar.. het blijft net zo traag -.-
(ook de full processlist, laat alleen mijn query zien, dus zijn geen andere queries gaande)
Je kan niet alles klakkeloos zo hoog zetten, dan fiets je door je geheugen heen en ga je swap eten, en dat is ook weer traag. Je zult gericht waarden moeten tunen (en uiteraard ook tussentijds restarten).
Ook noem je het feit dat je op een VPS zit. Weet je de backing voor de storage hier ook? Als hier geen behoorlijke (SSD) cache laag tussen zit zal er altijd traagheid blijven, gewoon omdat de gemiddelde VPS storage environment beperkt is in het aantal iops dat je redelijkerwijs kunt doen. Je bent immers 1 van de velen die op dezelfde storage zitten te hameren.
maar de query duurt nog steeds 58 seconden.
Behalve als de 'doel tabel' leeg is, dan is het 0.8 seconden genoeg om 22000 records te importeren.
Het updaten van bestaande tickets vanuit deze csv tabel duurt ook slechts een 0.7 seconden gemiddeld.
Dus ik denk dat er iets is met de query of de tabellen..
INSERT INTO csvInsertTest (ticket_code,ticket_summary,ticket_uniquecode,ticket_source)
SELECT csv.ticket_code, csv.Summary,CONCAT('dimble_',csv.TicketCode)
FROM csvImport csv
LEFT JOIN csvInsertTest t ON t.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE t.ticket_uniquecode IS NULL
Maar ik weet niet goed hoe ik dit het beste kan debuggen en fixen..
SELECT csv.ticket_code, csv.Summary,CONCAT('dimble_',csv.TicketCode)
FROM csvImport csv
LEFT JOIN csvInsertTest t ON t.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE t.ticket_uniquecode IS NULL
voeg anders eens een kolom toe aan de tabel csvInsert.
laten we zeggen "gedaan" en maak dat een tinyint, default 0
je load-data query veranderen we in
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE csvTickets
(ticket_code,ticket_summary,ticket_uniquecode,ticket_source)
FIELDS TERMINATED BY ".$pdo->quote(';')."
LINES TERMINATED BY ".$pdo->quote("\n"))
op regel 2 noem je, in dezelfde volgorde als in de csv-file de kolommen. En de kolom Gedaan, laten we achterwege
de update query wordt:
UPDATE dns_tickets dns
INNER JOIN csvTickets csv
ON dns.ticket_code = csv.TicketCode
SET dns.ticket_watchers = csv.Watchers
csv.gedaan = 1
WHERE dns.ticket_source = 2
Alle records die in csvTickets staan met de waarde 0 bij Gedaan, zijn dus niet ge-update.
Die wil je dan mi. alsnog inserten:
INSERT INTO csvImport (ticket_code,ticket_summary,ticket_uniquecode,ticket_source)
SELECT csv.TicketCode, csv.Summary,CONCAT('dimble_',csv.TicketCode)
FROM csvImport csv
WHERE gedaan = 0;
nb: i mis een kolom in deze laatste query: er wordt in 4 kolommen geinsert, maar de select heeft er maar 4. Maar ik kopieer die van jouw query hierboven
zoiets vroeg ik idd in een paar comments terug, of er met de update ook de andere tabel kunnen updaten :D
Ik ga dit morgen gelijk ff toepassen.
over de NB
ja dat kan idd kloppen
kopieren planken en doen, dr zijb uiteraard meer velden, maar die doen er niet zo toe.
Ik zal ze morgen ff nalopen :)
ik neem ook aan dat t csv.gedaan moet worden :)
Nogmaals bedankt!
Morgenochtend gelijk testen en toepassen
Als de kolomnamen voorkomen in meerdere tabellen _moet_ je de tabelnaam noemen.
Zo niet, dan is de tabelnaam optioneel.
Maar het is wel aan te raden, want over 17 maanden als je de code voer iets omoet aanpassen, dan weet je echt niet meer welke kolom bij welke tabel hoorde
ik wil hierbij even iedereen bedanken :)
Het werkt geheel als gewenst.
de extra kolom 'gedaan' erbij en vervolgens de records waar 'gedaan' 0 is gewoon importeren.
Dit werkt ook weer 0,8 seconden voor een extra 1000 records op het moment
Het enige rare :
als ik de csv file importeer en ik benoem de namen van de kolommen op volgorde zoals ze in de csv staan.
krijg ik niet het aantal imports terug als ik gebruik maak van return $pdo->exec($loadDataQuery)
Ik heb het geprobeerd om de kolommen gelijk na tabelnaam te noemen, en helemaal aan het einde na de settings (field en line seperation, en eventueel skip lines)
Dus ik heb deze even achterwege gelaten. Ik heb wel voor de zekerheid een paar 'dummy' kolommen gemaakt voor het geval dat ;-)
De opmaak van de csv zou in principe niet meer moeten/mogen veranderen.
Maar ik ben zeer tevreden en snelheid is van minuten naar enkele seconden gegaan, geweldig
