2006 - MySQL server has gone away
Ik heb een tabel van 127 miljoen records. 5 kolommen, 4 maal -> INT(9) en 1 maal -> VARCHAR(255)
Ze zijn allemaal geindexeerd. Maar als ik bijv een query als dit uitvoer
Code (php)
1
SELECT * FROM `spec_product` where TRIM(waarde) = '&;' group by waarde limit 10
de INDEXen
Quote:
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Edit Drop Drop PRIMARY BTREE Yes No id 126157109 A No
Edit Edit Drop Drop child_id BTREE Yes No child_id 151631 A No
waarde 12615710 A No
product_id 126157109 A No
Edit Edit Drop Drop ind BTREE No No product_id 21026184 A No
waarde 126157109 A No
header_id 126157109 A No
child_id 126157109 A No
Edit Edit Drop Drop waarde BTREE No No waarde 14017456 A No
Edit Edit Drop Drop product_id BTREE No No product_id 18022444 A No
Edit Edit Drop Drop header_id BTREE No No header_id 55723 A No
Edit Edit Drop Drop PRIMARY BTREE Yes No id 126157109 A No
Edit Edit Drop Drop child_id BTREE Yes No child_id 151631 A No
waarde 12615710 A No
product_id 126157109 A No
Edit Edit Drop Drop ind BTREE No No product_id 21026184 A No
waarde 126157109 A No
header_id 126157109 A No
child_id 126157109 A No
Edit Edit Drop Drop waarde BTREE No No waarde 14017456 A No
Edit Edit Drop Drop product_id BTREE No No product_id 18022444 A No
Edit Edit Drop Drop header_id BTREE No No header_id 55723 A No
Dan krijgt hij de foutmelding in de titel.
my.cnf is dit:
Quote:
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
innodb_lock_wait_timeout = 100
key_buffer_size = 3G
max_allowed_packet = 128M
innodb_buffer_pool_size=6G
wait_timeout=28800
interactive_timeout = 28800
innodb_log_file_size = 228M
datadir = /var/lib/mysql
tmpdir = /tmpmysql
read_buffer_size=2M
tmp_table_size=1G
max_heap_table_size=1G
#sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_buffer_pool_size=6G
innodb_log_file_size=512M
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
max_allowed_packet=100M
open_files_limit=40000
default-storage-engine=InnoDB
innodb_file_per_table=1
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
innodb_lock_wait_timeout = 100
key_buffer_size = 3G
max_allowed_packet = 128M
innodb_buffer_pool_size=6G
wait_timeout=28800
interactive_timeout = 28800
innodb_log_file_size = 228M
datadir = /var/lib/mysql
tmpdir = /tmpmysql
read_buffer_size=2M
tmp_table_size=1G
max_heap_table_size=1G
#sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_buffer_pool_size=6G
innodb_log_file_size=512M
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
max_allowed_packet=100M
open_files_limit=40000
default-storage-engine=InnoDB
innodb_file_per_table=1
RAM van mijn vps is is 8GB
Weet iemand, hoe ik dit stabiel kan maken?
Gewijzigd op 08/06/2021 01:39:29 door Daniel van Seggelen
Je kan ook tijdens het draaien van die query eens in de gaten houden wat je geheugengebruik doet (`htop` is daar erg handig voor).
Code (php)
1
2
3
4
2
3
4
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
group by waarde
limit 10
where TRIM(waarde) = '&;'
group by waarde
limit 10
Group-by gebruik je in combinatie met aggregatie functies, zoals sum(), min(), count().
Bijvoorbeeld om te achterhalen dat er 10 auto's in je tabel staan en 4 fietsen.
Mysql accepteert bij bepaalde settings, dat je toch group-by in een query duwt, en probeert er dan iets van te maken, waarbij soms aanwijsbare onzin het resultaat is (ik zoek zo nog eens naar een tutorial die ik lang geleden eens maakte).
Soms is het effect van group-by in deze setup in mysql dat er een soort van uniek wordt afgedwongen. Wat je ook met DISTINCT had bereikt.
Maar aangezien je al in de query de waarde van de kolom Waarde op &; zet (even vanuit gaande dat TRIM() niets toevoegt), lijkt dat ook niet logisch.
kortom:
Wat doet je query met
Code (php)
1
2
3
2
3
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
limit 10
where TRIM(waarde) = '&;'
limit 10
En beter nog: zijn er rijen in je tabel waar ook echt spaties voor of achter je Waarde staan?
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &; overbleef.
En zit hier nog een index op?
Quote:
If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns.
https://dev.mysql.com/doc/refman/5.7/en/select.html
Quote:
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
limit 10
where TRIM(waarde) = '&;'
limit 10
Dit heb ik gedaan. Hetzelfde probleem. #2006 - MySQL server has gone away.
Quote:
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &;
Ja ik hoopte dat dit nog steeds mogelijk was, maar dit duurt idd te lang.
Quote:
En beter nog: zijn er rijen in je tabel waar ook echt spaties voor of achter je Waarde staan?
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &; overbleef.
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &; overbleef.
Ja, die zijn er echt wandaar de TRIM. Maar zonder trim, doet hij het wel snel. Maar hij vind ze dan niet allemaa;
Code (php)
1
2
3
2
3
SELECT * FROM `spec_product`
where waarde like '%&;%'
limit 10
where waarde like '%&;%'
limit 10
Dit duurt ook te lang maar hij stopt er niet mee 86 seconde.
Bij vele rijen zitten al vele spaties, links en rechts van een waarde.
Als ik deze spaties allemaal wil trimmen, gaat mysql ook weg. De database lijkt te groot.
Desalniettemin. Ik weet dat vele waardes hetzelfde zijn. daarom wil ik die waarde in een andere tabel zetten voor normalizatie. en zo de tabelgrootte te beperken.
Gewijzigd op 08/06/2021 19:19:56 door Daniel van Seggelen
Over je query: LIKE '%naald%' duurt lang omdat er geen index gebruikt kan worden, dat kan alleen met LIKE 'naald%', dus zonder dat het begint met een procentteken.
Als het iets eenmaligs is, is performance niet zo'n probleem - als ie het maar afmaakt. Dus dan moet je de timeout(s) (flink) oprekken.
UPDATE tabel SET waarde = TRIM(waarde);
eventueel in moten opgedeeld met WHERE waarde LIKE ' %' of '% '
en eventueel op id eindigt op 1, 2 , 3 etc
Maar via een niet-php oplossing (command line, HeidiSQL etc) moet je ook langdurende query's uit kunnen voeren.
Hiermee schoon je dan eenmalig je data op, en daarna zou je er wat aan hebben.
Kijk ook naar de invoerkant, want kennelijk is er een proces dat extra spaties aanlevert.