Row size error (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

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

  1. Via mysql-client: set global innodb_fast_shutdown = 1;
  2. Sluit de server af
  3. Backup de huidige redo-logbestanden. Bv. mv ib_logfile0 ib_logfile0-bk-org en mv ib_logfile1 ib_logfile1-bk-org
  4. Specificeer in my.cnf de nieuwe loggroote. Bv.: innodb_log_file_size = 1G
  5. 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
Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Row_size_error_(MySQL)&oldid=27812"