Row size error (MySQL)
Foutmeldingen
Dec. 2015
In december 2015 stuitte ik op deze foutmelding toen ik met een tabel werkte met zo'n 300 kleine velden:
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.
Zomer 2018
In de zomer van 2018 had ik iets vergelijkbaars. Het betrof een tabel met 23 velden, waaronder veel flinke varchar-velden:
Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
Feb. 2021
Feb. 2021: Op een MySQL-server zonder aanpassingen qua tabellen of database-engine. Het betrof een tabeldefinitie van 60 velden, allemaal van het type varchar
:
Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
In negen kolommen heb ik varchar(1023)
vervangen door text(1023)<.code>. Toen was het probleem verdwenen.
Row size?
- Er zijn 65.535 bytes beschikbaar voor het defineren van een rij
- De definitie van een text-kolom vergt 43 bytes
- De definitie van een varchar-kolom vergt 8.192 bytes (dit klopt niet, want dan kan een tabel uit max. 8 varchar-kolommen kunnen bestaan). Die ruimte wordt gebruikt voor een pointer, plus dat het eerste aantal bytes daar direct wordt opgeslagen
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
en mv 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: 301 kleine velden (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.
Casus: Te grote velden (juli 2018)
- Nu de tegenhanger van het probleem hiervoor: Slechts 23 velden, maar wel grote varchar-velden
- Oplossing: Al deze velden verandert naar TEXT - Probleem opgelost.
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