Door
Dennis WhoCares
op 16-06-2017 08:06
gewijzigd op 16-06-2017 10:03
7.709 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
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 ON ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE csvInsertTest.ticket_uniquecode IS NULL
Alternatief voeg je een kolom toe aan de temp tabel en breid je de UPDATE query uit de openingspost uit met een kolom "gedaan" die je de waarde 1 geeft.
Wel moet je dan in de LOAD DATA query alle kolommen gaan noemen, omdat deze niet in de csv file staat.
Sowieso is dat wel verstandig, want mocht om een of andere reden de volgorde van de kolommen veranderen, gaat het heel erg mis.
dag allemaal, mijn excuses voor de late reactie, ondertussen ben ik van servers aan het migreren...
Ik kom hier later op terug, ik had het nog geprobeerd maar alsnog liep de cpu gebruik van sql in de 100 (4 keer)
csvInsertTest bevat 64000 records, csvImport 22000, waarvan 19722 al bestaan.
Tot m'n verbazing had ik meerdere 'index' op ticketcode in de csvInsertTest... Ook na dat opgelost te hebben, liep het nog steeds vast.
Ik moet even alles migreren en dan kom ik er weer op terug.
@Ivo, wat bedoel je met kolommen noemen ?
De LOAD DATA weet toch niet wat de kolom naam is van de file die die import.
Ik las iets over '@dummy' gebruiken enz... maar begreep het allemaal zo snel niet.
Ik zal t nog eens verder uitvogelen voordat ik meer vragen ga stellen hierover :)
stel je tabel heet mytabel en heeft 3 kolommen kolA, kolB en kolC
En laten we zeggen dan in voorbeeld.csv de data staat voor kolC, kolA, kolB (andere volgorde dan de volgorde - zo een tabel al kolommen op een zekere volgorde heeft-.
LOAD DATA INFILE 'voorbeeld.csv'
INTO TABLE mytabel
(kolC, kolA, kolB)
IGNORE 1 LINES;
Wanneer je last hebt van hoge cpu usage hoeft dat niet direct door de gebruikte query te komen, maar kan configuratie zelf ook een issue zijn. Wanneer een index bijvoorbeeld niet in geconfigureerde buffers passen krijg je een hoop geschuif met gegevens. Wat zijn bij de uitvoer de resultaten van bijvoorbeeld SHOW FULL PROCESSLIST, wat zeggen de logs (en schakel ook het slow query log even in) etc. Ook zegt 100% cpu niet zoveel, het zegt alleen maar dat je *een core* hard aanslingert (en dat is wat een server hoort te doen). Uiteraard is ook een EXPLAIN van de query handig. Toegegeven, explain in mysql is ruk, maar het is beter dan niets.
zou je mij wat meer 'data' kunnen geven over "Wanneer een index bijvoorbeeld niet in geconfigureerde buffers passen krijg je een hoop geschuif met gegevens."
De SHOW FULL PROCESSLIST laat de INSERT query zien, als zijnde: SEND DATA
Dit duurt gewoon ... minuten lang, onbegrijpelijk, terwijl het updaten van de data 9-12 seconden werk is.
Ik heb zojuist ff de query aangepast, naar voorbeeld van Ivo met LEFT JOIN ... WHERE ... IS NULL
Het duurde zojuist 69 seconden om 780 tickets van de 22000 toe te voegen... is dit begrijpelijk gedrag ?
Op de csvTabel staat een index op ItemCode
Op de tickets tabel staat een index op ID, source_id, uniquecode, en een fulltext op summary
>> Het duurde zojuist 69 seconden om 780 tickets van de 22000 toe te voegen... is dit begrijpelijk gedrag ?
>> Op de tickets tabel staat een index op ID, source_id, uniquecode, en een fulltext op summary
Nee, normaliter moet je 22000 tickets in enkele seconden kunnen toevoegen, zo niet sneller. Heb je te maken met beschadigde indexes of iets dergelijks? Dit gebeurt in MyISAM erg snel. Probeer eens een REPAIR.
aan de hand van repair kom ik erachter dat de storage engine van de tabel met csv data, innodb is/was
Het duurde alsnog 67 seconden
eplain:
+ Options
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 INSERT csvImportTest NULL ALL NULL NULL NULL NULL NULL NULL NULL
1 SIMPLE csv NULL ALL NULL NULL NULL NULL 20268 100.00 Using temporary
1 SIMPLE t NULL index NULL ticket_uniquecode 66 NULL 21551 10.00 Using where; Not exists; Using index; Using join b...
[size=xsmall]Toevoeging op 24/06/2017 21:35:04:[/size]
p.s. dit draait nu op een nieuwe VPS server, 8 CPU 16GB RAM, ubuntu met laatste software versies. Dus het is nu niet meer zo'n drama, als voorheen, maar.. er is wel betere performance gewenst :(
Gebruikte Query:
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
[size=xsmall]Toevoeging op 24/06/2017 22:11:56:[/size]
Ik denk dat t m zit in de LEFT JOIN WHERE ... IS NULL
gewoonweg de tabel te kopieren en alle data overzetten duurt slechts een seconde.
Is het mogelijk om tijdens deze update:
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
in de csv ook een veld te updaten ? (maar alleen als er in de tickets geupdate is ?)
>> p.s. dit draait nu op een nieuwe VPS server, 8 CPU 16GB RAM
En heb je MySQL ook ingesteld dat hij dit geheugen kan gebruiken? Anders gaat dat nog geen kant op.
