import csv -> update other table
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.
Gewijzigd op 24/06/2017 22:46:15 door Ben van Velzen
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)
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.
Gewijzigd op 25/06/2017 00:36:08 door Ben van Velzen
Ik heb geen idee wat voor SSD enz erin zit
Ik heb met een optimizer en wat proberen en doen, de config aangepast:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 50
myisam-recover-options = FORCE,BACKUP
max_connections = 500
query_cache_limit = 128M
query_cache_size = 128M
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 50
myisam-recover-options = FORCE,BACKUP
max_connections = 500
query_cache_limit = 128M
query_cache_size = 128M
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 8G
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..
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
Maar ik weet niet goed hoe ik dit het beste kan debuggen en fixen..
Code (php)
1
2
3
4
2
3
4
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
FROM csvImport csv
LEFT JOIN csvInsertTest t ON t.ticket_uniquecode = CONCAT('dimble_',csv.TicketCode)
WHERE t.ticket_uniquecode IS NULL
Deze query alleen al werkt 57 seconden..
Gewijzigd op 25/06/2017 21:38:12 door Dennis WhoCares
laten we zeggen "gedaan" en maak dat een tinyint, default 0
je load-data query veranderen we in
Code (php)
1
2
3
4
2
3
4
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"))
(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:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
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
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:
Code (php)
1
2
3
4
2
3
4
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;
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
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