Row size error (MySQL)
Ik werk met een tabel met 301 velden. Om de haverklap krijg ik deze foutmelding:
Error Code: 1118. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help
Na een aantal optimalisaties werd dit de foutmelding:
Error Code: 1118. Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
Row size?
- Er zijn 8126 bytes beschikbaar om de kolommen te defineren
- Onder normale omstandigheden vergt de definitie van een text-kolom in MySQL 5.5.32 43 bytes. Vanaf 187 velden krijg je dus deze foutmelding
Vervang varchar-velden door text-velden
Een varchar-veld vergt (ik geloof) 8192 bytes voor een pointer, omdat de eerste x-aantal bytes met de pointer worden opgeslagen. Tekst-velden hebben dat probleem niet. Daarom kan het helpen om tekstvelden te gebruiken ipv. varchar-velden
innodb_file_per_table
De instelling innodb_file_per_table
komt snel om de hoek kijken bij het optimaliseren van databases voor veel rijen:
- Het maakt dynamic row format mogelijk
- Het maakt gebruik van het Barracuda-bestandsformaat mogelijk.
Je stelt dit in door het volgende statement toe te voegen aan my.cnf:
innodb_file_per_table
Barracuda-bestandsformaat
- Barracuda is het bestandsformaat waarin een tabel op disk wordt weggeschreven. Het standaard-bestandsformaat is Antilope
- Barracuda kan overweg met dynamic row format en ik geloof dat pointers naar velden minder data innemen.
Je stelt 't als volgt in, in my.cnf:
innodb_file_format = Barracuda
innodb_log_buffer_size
De parameter innodb_log_buffer_size
bepaalt de buffer voordat een logfile naar disk wordt geschreven. Dit heeft vermoedelijk betrekking op performance, maar speelt geen rol bij het kunnen verwerken van tabellen met veel velden.
innodb_buffer_pool_size
De parameter innodb_buffer_pool_size
bepaalt in hoeverre een innoDB-database in-memory werkt. Relevant voor tuning, maar vermoedelijk niet voor het kunnen verwerken van tabellen met veel velden.
innodb_fast_shutdown
- De variabele
innodb_fast_shutdown
configureert de afsluitprocedure. Bij waarde=1, worden alle buffers geleegd bij het afsluiten. Dat kan lang duren - Voorafgaand aan het aanpassen van het redo-logbestand, handig om via de client het commando
set global innodb_fast_shutdown = 1;
te geven, zodat buffers doorgespoeld worden
Redo-logfile aanpassen
- Via mysql-client:
set global innodb_fast_shutdown = 1;
- Sluit de server af
- Backup de huidige redo-logbestanden. Bv.
mv ib_logfile0 ib_logfile0-bk-org
enmv ib_logfile1 ib_logfile1-bk-org
- Specificeer in my.cnf de nieuwe loggroote. Bv.:
innodb_log_file_size = 1G
- Herstart mysqld.
Redo-logfile-foutmelding
Van de regen in de drop: De logfile kan nogal eens de oorzaak zijn van het row size-probleem, maar nu heb ik die op een onhandige manier verwijderd ofzo (locatie: /var/lib/mysql
), en wil mysqld niet meer starten. Zie Ib logfile-foutmelding (MySQL) voor details.
Casus dec. 2015
Iteratie 01
- Tabel met 301 velden. De data in deze velden is heel beperkt. Er zijn geen BLOB's.
- Alle varchar-velden vervangen door text-velden
- Instellingen my.cnf:
innodb_file_per_table innodb_file_format = Barracuda
- Instellingen definitie betreffende tabel mbt. rijlengte:
ENGINE=InnoDB ROW_FORMAT=dynamic KEY_BLOCK_SIZE=8;
Foutmelding - Het script heeft dus ruim 52 seconde gedraait:
Error Code: 1118. Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. 52.721 sec
Iteratie 02
ROW_FORMAT=dynamic
verandert in ROW_FORMAT=compressed
. Het script draait nu voor 50 seconde. Da's geen verbetering.
Iteratie 03 - Redo-log aanpassen
Het redo-log vergroot van 512MB naar 1GB. Het script draaide nu 50 seconde. Dus geen verbetering.
Iteratie 04 - Waar ligt de limiet?
Deze tabel bevalt uitsluitend tekst-velden, en de foutmelding ontstaat bij iets meer dan 171 velden.
Conclusie
- Optimalisatie lijkt geen verschil uit te maken
- Normalisatie is te veel werk
- Lang niet alle velden zijn nodig → Onnodige velden verwijderen.
Zie ook
Bronnen
- http://dba.stackexchange.com/questions/90096/row-size-too-large-8126
- http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql/75333#75333
- http://dba.stackexchange.com/questions/45837/mysql-row-size-too-large-8126/45862#45862
- http://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html
- https://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-file-formats.html
- http://www.mysqlab.net/knowledge/kb/detail/topic/innodb/id/6553
- https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html
- http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html
- http://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large - Goede achtergrondinformatie