import csv -> update other table
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 :
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
...
$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"');
$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:
Code (php)
1
2
3
4
5
2
3
4
5
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
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
Gewijzigd op 16/06/2017 10:03:13 door Dennis WhoCares
---
Als je hele tabellen leeg gooit, is TRUNCATE mytable doorgaans sneller dan DELETE FROM mytable
TRUNCATE gooit de tabel weg en maakt hem opnieuw, leeg, aan. En hij hoeft dus niet stuk voor stuk de records te verwijderen en meta-data als autoincrement tellers, index etc bij te werken.
bedankt voor de tip om truncate te gebruiken.
Ehm, nee in principe zit er geen index op ticket_code en TicketCode
Ik kan csv.TicketCode wel een unique kunnen geven,
maar ticket_code zou heeeeel misschien niet uniek kunnen zijn.
Vergat trouwens de WHERE op te geven in m'n bericht.
Het kan zijn de ticket_code zelfde kan zijn, maar andere source.
een primary key dwingt uniek af, maar een key alleen zou dat niet doen.
ik begrijp uit het verhaal dat zowel TicketCode als ticket_code niet per se uniek voorkomen in de tabellen?
ik zou dan in elk geval een index zetten op die beide kolommen.
En eentje op ticket_source zou ook kunnen.
met die laatste zou het gemakkelijker moeten worden om uit de 170.000 records de betreffende te vinden.
En de key op de *code kolommen zou het gemakkelijker moeten maken om de beide tabellen tegen elkaar te leggen, zonder steeds 20.000*20.000 records te moeten langs lopen.
ik was net m'n comment aan het aanpassen :D
dns_tickets heeft wel een primary key op ticket_id
ik had daarnet csv.TicketCode een unique index gegeven en de update lijkt er per direct doorheen te gaan.
Is er nog een truukje om de eerst regel te laten vervallen ? zonder dat php de file hoeft te openen?
Of zou ik dat gewoon met shell_exec() moeten doen?
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
Toevoeging op 16/06/2017 11:11:34:
https://dev.mysql.com/doc/refman/5.7/en/load-data.html
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
Gewijzigd op 16/06/2017 11:12:26 door Ivo P
Ik had idd de manual page eerst ff moeten kijken voordat ik ging vragen :)
Dit is alleen om m'n vraag uit te breiden en of ik dit wel goed doe, in de trend van ... performance.
Ik weet aan de hand van hierboven:
- hoeveel tickets ik geimporteerd heb
- hoeveel tickets ik geupdate heb
- hoeveel tickets er 'nieuw' zijn (welke nog niet in productie tabel aanwezig zijn)
Deze missende tickets wil ik dus toevoegen:
Code (php)
1
2
3
2
3
INSERT INTO csvInsertTest (ticket_code,ticket_summary,ticket_uniquecode,ticket_source) SELECT csv.TicketCode, csv.Summary,CONCAT('dimble_',csv.TicketCode) FROM csvImport csv WHERE NOT EXISTS (
SELECT test.ticket_id FROM csvInsertTest test WHERE test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
)
SELECT test.ticket_id FROM csvInsertTest test WHERE test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
)
Gewijzigd op 18/06/2017 22:26:11 door Ben van Velzen
zou je mij een voorbeeld kunnen geven hoe ik dat het beste kan toepassen ?
Het veld ticket_uniquecode is inderdaad Unique en een Index.
Code (php)
1
2
3
4
2
3
4
INSERT INTO csvInsertTest (ticket_code,ticket_summary,ticket_uniquecode,ticket_source)
SELECT csv.TicketCode, csv.Summary, CONCAT('dimble_',csv.TicketCode) FROM csvImport csv
LEFT JOIN csvInsertTest test ON test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE test.ticket_uniquecode IS NULL
SELECT csv.TicketCode, csv.Summary, CONCAT('dimble_',csv.TicketCode) FROM csvImport csv
LEFT JOIN csvInsertTest test ON test.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE test.ticket_uniquecode IS NULL
Code (php)
1
2
3
4
5
2
3
4
5
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
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.
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 :)
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-.
Code (php)
1
2
3
4
2
3
4
LOAD DATA INFILE 'voorbeeld.csv'
INTO TABLE mytabel
(kolC, kolA, kolB)
IGNORE 1 LINES;
INTO TABLE mytabel
(kolC, kolA, kolB)
IGNORE 1 LINES;
bedankt voor je reactie ik dacht al bijna da LOAD DATA de kolomnamen kon detecteren :D
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
Gewijzigd op 24/06/2017 21:11:24 door Dennis WhoCares
>> 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.
Wat zegt EXPLAIN ervan?
Het duurde alsnog 67 seconden
eplain:
Code (php)
1
2
3
4
5
2
3
4
5
+ 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...
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...
Toevoeging op 24/06/2017 21:35:04:
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:
Code (php)
1
2
3
4
5
2
3
4
5
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
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
Toevoeging op 24/06/2017 22:11:56:
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:
Code (php)
1
2
3
4
5
2
3
4
5
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
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 ?)
Gewijzigd op 24/06/2017 21:39:17 door Dennis WhoCares
En heb je MySQL ook ingesteld dat hij dit geheugen kan gebruiken? Anders gaat dat nog geen kant op.
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M