MySQL ERROR 1114 "The table is full": cēloņi un risinājumi

MySQL ERROR 1114 "The table is full" izklausās pēc stingra vienas tabulas limita, bet reti tāds ir. Tas pats ziņojums attiecas uz pilnu disku, InnoDB tablespace, kam nav atļauts augt, pagaidu tabulu, ko aizkulisēs izveidoja smags vaicājums, un pāris dzinēju limitiem. Risinājums pilnībā atkarīgs no tā, kurš no tiem ir jūsu gadījums, un pirmās desmit minūtes vislabāk veltīt tam, lai to noskaidrotu.

Pati kļūda izskatās šādi:

ERROR 1114 (HY000): The table 'order_items' is full

Servera kļūdu sarakstā tā ir kļūda 1114, simbols ER_RECORD_FILE_FULL, SQLSTATE HY000, ar ziņojumu "The table '%s' is full". Rokasgrāmatas problēmu novēršanas ieraksts ir divus teikumus garš: disks var būt pilns, vai tabula var būt sasniegusi maksimālo izmēru. Lapa par tabulu izmēra limitiem to izvērš zemāk redzamajā sarakstā.

Vispirms paskatieties uz tabulas nosaukumu

Nosaukums ziņojumā ir noderīgākais pavediens, ko saņemat.

• Kāda no jūsu īstajām tabulām (augstāk order_items): problēma ir tur, kur šī tabula glabājas, diskā, tablespace vai dzinēja limitā.
• Ģenerēts nosaukums, parasti sākas ar #sql, ko jūs nekad neesat izveidojuši: tā ir iekšēja pagaidu tabula, ko MySQL uzbūvēja vaicājuma izpildes laikā: UNION, atvasinātai tabulai vai CTE, dažām GROUP BY un ORDER BY kombinācijām, DISTINCT ar ORDER BY, COUNT(DISTINCT), logu funkcijām un līdzīgos gadījumos. Problēma nav tabulā, bet vaicājumā un pagaidu krātuvē.

Tad uzreiz savāciet faktus par krātuvi:

-- Which engine and which tablespace does the table live in?
SELECT table_schema, table_name, engine, create_options
FROM information_schema.TABLES
WHERE table_name = 'order_items';

SELECT name, space_type, file_size / 1024 / 1024 AS size_mb
FROM information_schema.INNODB_TABLESPACES
WHERE name LIKE '%order_items%' OR space_type = 'System';

SELECT @@innodb_file_per_table, @@innodb_data_file_path,
       @@innodb_temp_data_file_path, @@datadir, @@tmpdir;

df -h /var/lib/mysql /tmp
df -i /var/lib/mysql             # inodi var beigties ātrāk nekā baiti
du -sh /var/lib/mysql/'#innodb_temp' /var/lib/mysql/ibtmp1

1. cēlonis: disks tiešām ir pilns

Joprojām biežākais gadījums un pirmais, ko min dokumentācija. Pārbaudiet visus iesaistītos sējumus: datu direktoriju, innodb_data_file_path failu atrašanās vietu, ja tie ir citur, tmpdir un direktoriju ar sesiju pagaidu tablespace (pēc noklusējuma #innodb_temp datu direktorijā). Paskatieties arī uz inodiem.

Ierastie vietas ēdāji ir binārie žurnāli, slow un general žurnāli, veci dump, ko kāds atstājis datu direktorijā, un ibtmp1, kas izauga viena milzīga vaicājuma laikā. Ja vainīgi binlog, tīriet tos pareizi, nevis ar rm; mūsu ceļvedis par PURGE BINARY LOGS un diska vietas atbrīvošanu to droši izskaidro.

2. cēlonis: InnoDB tablespace nav atļauts augt

Ar ieslēgtu innodb_file_per_table (noklusējums) katrai tabulai ir savs .ibd fails, kas aug pēc vajadzības, līdz disks vai failu sistēma pasaka "nē". Mākslīgos griestus var sasniegt tabulas sistēmas tablespace: tabulas, kas izveidotas, kamēr innodb_file_per_table bija izslēgts, vecas instalācijas, kas daudzus gadus atjauninātas, vai tabulas, kas tur novietotas apzināti.

Kļūdu saraksts šo gadījumu apraksta tieši: InnoDB ziņo kļūdu 1114, "kad sistēmas tablespace beidzas brīvā vieta", un iesaka pievienot datu failu. Noklusējuma innodb_data_file_path ir ibdata1:12M:autoextend, bez ierobežojuma. Ja kāds pievienoja :max: vai pēdējam failam vispār nav autoextend, sistēmas tablespace apstājas pie šī izmēra.

Sistēmas tablespace dokumentācija piedāvā divus ceļus, abiem vajag restartu:

1. Padarīt pēdējo datu failu automātiski augošu (un noņemt max).
2. Pievienot vēl vienu datu failu. Esošajam failam norādiet tā pašreizējo izmēru, noapaļotu uz leju līdz veseliem megabaitiem, un pievienojiet jauno failu:

# pirms: ibdata1 izaudzis līdz 988M un sasniedzis max
innodb_data_file_path = ibdata1:12M:autoextend:max:1G

# pēc: ibdata1 paliek pašreizējā izmērā, pievieno otru failu
innodb_data_file_path = ibdata1:988M;ibdata2:50M:autoextend

Nemainiet vienkārši esošā faila izmēru iestatījumā: rokasgrāmata parāda, ka tad MySQL atsakās startēt ar kļūdu, ka datu fails ir "of a different size", nekā norādīts .cnf failā. Ilgtermiņā apsveriet lielo tabulu pārvietošanu no sistēmas tablespace uz file-per-table tablespace, jo ibdata1 nekad nesamazinās.

3. cēlonis: pagaidu tabulas pārauga savu vietu

Šis gadījums ir aiz vairuma #sql nosaukumu. MySQL 8.4 iekšēja pagaidu tabula sākas atmiņā TempTable dzinējā. Saskaņā ar iekšējo pagaidu tabulu dokumentāciju:

• Viena pagaidu tabula atmiņā drīkst izmantot līdz tmp_table_size (pēc noklusējuma 16 MiB). Pēc tam MySQL to pārveido par InnoDB pagaidu tabulu diskā.
• Visas TempTable tabulas kopā drīkst izmantot temptable_max_ram. Ja tas nav iestatīts tieši, tie ir 3% no servera atmiņas, vismaz 1 GB un ne vairāk kā 4 GB.
• temptable_max_mmap MySQL 8.4 pēc noklusējuma ir 0, tāpēc atmiņā kartēti pārpildes faili ir izslēgti un pārpilde iet uzreiz uz InnoDB diskā. MySQL 8.0 bija citādi: temptable_use_mmap pēc noklusējuma bija ON un temptable_max_mmap 1 GiB, tāpēc pārbaudiet, kuru versiju un iestatījumus jūs patiešām izmantojat.

Pagaidu tabulas diskā glabājas sesiju pagaidu tablespace, .ibt failos direktorijā innodb_temp_tablespaces_dir (pēc noklusējuma #innodb_temp datu direktorijā). Katra sesija no pūla saņem līdz diviem šādiem failiem, un tie tiek saīsināti un atgriezti pūlā, kad sesija atvienojas. Tāpēc ilgi dzīvojoši savienojumi no pūla var turēt lielu .ibt failu ilgi pēc tam, kad vaicājums, kas to uzpūta, jau beidzies. Pagaidu tablespace dokumentācija apraksta arī globālo pagaidu tablespace ibtmp1, kurā glabājas rollback segmenti lietotāja izveidotām pagaidu tabulām; tas pēc noklusējuma aug automātiski un samazinās tikai pēc restarta. To var ierobežot ar innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M, kas aizsargā disku, bet vaicājumi, kam tur vajag vairāk vietas, pēc limita sasniegšanas beigsies ar kļūdu.

Atrodiet, kas tur vietu:

SELECT ID AS session_id, PATH, SIZE / 1024 / 1024 AS size_mb, STATE, PURPOSE
FROM information_schema.INNODB_SESSION_TEMP_TABLESPACES
ORDER BY SIZE DESC
LIMIT 10;

SELECT FILE_NAME, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS size_mb, MAXIMUM_SIZE
FROM information_schema.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary';

INNODB_SESSION_TEMP_TABLESPACES kolonna ID ir sesijas ID, tāpēc to var salīdzināt ar procesu sarakstu. Šīs sesijas pārtraukšana atbrīvo tās vietu. Pēc tam atrodiet vaicājumus, kas regulāri pārplūst uz disku:

SELECT query, exec_count, disk_tmp_tables, tmp_tables_to_disk_pct
FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC
LIMIT 10;

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Skats sys.statements_with_temp_tables katram normalizētajam vaicājumam rāda, cik pagaidu tabulu diskā tas izveidoja un kāda daļa tā pagaidu tabulu tika pārveidota par diska tabulām. Īstais risinājums parasti ir vaicājumā: trūkstošs indekss GROUP BY vai ORDER BY vajadzībām, atskaite, kas kārto miljoniem platu rindu, SELECT DISTINCT pār join, kas pavairo rindas. Mūsu slow query log ceļvedis palīdz šos vaicājumus vispirms atrast. tmp_table_size palielināšana patur vairāk RAM, bet nepadara sliktu vaicājumu kopumā mazāk prasīgu pēc vietas.

4. cēlonis: MEMORY tabula sasniedza max_heap_table_size

Tabulas, ko izveidojāt ar ENGINE=MEMORY, netiek pārveidotas par diska tabulām; tās vienkārši pārstāj pieņemt rindas. MEMORY dzinēja dokumentācija limitu nosaka ar max_heap_table_size, pēc noklusējuma 16 MB, un piebilst detaļu, kas bieži pārsteidz: vērtība, kas ir spēkā CREATE TABLE, ALTER TABLE vai TRUNCATE TABLE brīdī, tiek izmantota visu tabulas mūžu, un servera restarts esošajām MEMORY tabulām atiestata globālo vērtību.

SELECT @@max_heap_table_size;

-- the limit is fixed when the table is created, altered or truncated
SET SESSION max_heap_table_size = 256 * 1024 * 1024;
ALTER TABLE session_cache ENGINE = MEMORY;

Globālās vērtības palielināšana ietekmē katru vēlāk izveidoto MEMORY tabulu, tāpēc dariet to apzināti. Un, ja dati ir svarīgi, atcerieties, ka MEMORY tabulas pēc restarta jebkurā gadījumā ir tukšas; InnoDB tabula bieži ir labāka atbilde.

5. cēlonis: MyISAM un failu sistēmas limiti

Mūsdienās reti, bet limitu lapa to joprojām min. MyISAM tabula pēc noklusējuma var izaugt līdz 256 TB, ko nosaka tās iekšējā rādītāja izmērs (myisam_data_pointer_size, pēc noklusējuma 6 baiti). Tabula, kas izveidota ar mazām MAX_ROWS vai AVG_ROW_LENGTH vērtībām, savus griestus var sasniegt daudz agrāk; ALTER TABLE ... MAX_ROWS=... AVG_ROW_LENGTH=... tos paaugstina. Ļoti vecas vai neparastas failu sistēmas ar mazu maksimālo faila izmēru rada to pašu simptomu. Abos gadījumos tabulas pārvietošana uz InnoDB parasti ir labāks ilgtermiņa risinājums.

Kā novērst nākamo reizi

• Iestatiet brīdinājumus par brīvo vietu katram MySQL ceļam: datu direktorijam, tmpdir, pagaidu tablespace direktorijam un binlog atrašanās vietai, kā arī inodiem.
• Sekojiet pagaidu tabulu pārplūdei: Created_tmp_disk_tables pieauguma ātrumam un #innodb_temp izmēram. Pēkšņs lēciens parasti norāda uz vienu jaunu atskaiti vai vienu trūkstošu indeksu.
• Ziniet savus limitus. Ja innodb_data_file_path vai innodb_temp_data_file_path ir max, pierakstiet, kāpēc, un sekojiet, cik tuvu tam esat.
• Laiku pa laikam atjaunojiet savienojumus pūlā, lai sesijas pagaidu tablespace, ko uzpūta viens vaicājums, neturētu vietu dienām ilgi.