Dropli ste nejakú rozsiahlejšiu MySQL tabuľku s veľkosťou niekoľko GB, ale miesto na disku sa neuvoľnilo? Čudujete sa prečo?
Zrejme prevádzkujete MySQL server verzie nižšej ako 5.6 a odstránená tabuľka bola typu InnoDB. Pátraním ste taktiež isto narazili na súbor ibdata1, ktorý na disku obsadil veľký objem dát. Veľkosť tohto súboru sa pravdepodobne takmer vôbec nemení, bez ohľadu na to, koľko dát z tabuliek vymažete, resp. do nich vložíte. Z tohto dôvodu môžte mať napríklad problém ak MySQL server prevádzkujete na súborovom systéme FAT32, kde môže mať jeden súbor maximálnu veľkosť 4GB, takže môžte ľahko naraziť na strop, keď súbor ibdata1 sa nebude môcť ďalej zväčšovať.
V čom je teda problém?
Štandardne do verzie 5.6 sú všetky InnoDB tabuľky s dátami aj indexami ukladané do jedného spoločného súboru, už spomínaného ibdata1, na rozdiel od MyISAM tabuliek, ktoré sú ukladané do samostatných súborov. Po vymazaní údajov z databázy sa teda tento súbor nezmenší, časť s vymazanými údajmi sa len interne označí ako voľná, ale k uvoľneniu miesta na disku nedôjde. Do tohto „voľného miesta“ sa potom následne zapisujú nové údaje. Preto sa vymazávaním/vkladaním údajov veľkosť tohto súboru väčšinou nemení, len dochádza k „recyklovaniu“ jeho obsahu.
Ako to vyriešiť?
Jednoducho. Konfiguráciou MySQL treba zabezpečiť, aby InnoDB tabuľky nevyužívali pre svoje dáta jeden spoločný súbor ibdata1, ale aby bola každá tabuľka uložená v samostatnom .ibd súbore, obdobne ako sú samostatne ukladané MyISAM tabuľky. Od verzie MySQL 5.6 je to takto nastavené automaticky, pre staršie verzie je túto zmenu nutné urobiť manuálne.
Do konfiguračného súboru MySQL doplníme do časti [mysqld] riadok:
1 2 | [mysqld] innodb_file_per_table |
Po reštarte MySQL servera sa pre každú InnoDB tabuľku vytvorí samostatný .ibd súbor, kde sa obsah týchto tabuliek automaticky preklopí, takže údaje tabuliek vrátane ich indexov už nebudú ukladané v ibdata1, ale v ich vlastných súboroch, čo má okrem iného výhodu aj v tom, že pri poškodení nejakého súboru prídete maximálne o jednu tabuľku. Ak sú však všetky InnoDB databázy/tabuľky ukladané do tohto jedného ibdata1 súboru, pri jeho poškodení prídete o komplet všetky tabuľky, ktoré sú v ňom uložené.
Touto konfiguračnou zmenou ale docielime len to, že pre každú InnoDB tabuľku bude existovať samostatný súbor. Nezabezpečíme tým zmenšenie veľkosti ibdata1 súboru. Ten sa zachová tak isto ako pred zmenou, údaje v ňom sa len označia ako voľné, ale k jeho fyzickému zmenšeniu na disku nedôjde.
Aby sme zároveň s doplnením innodb_file_per_table do konfiguračného súboru uvoľnili aj diskový priestor zaberaný ibdata1, je potrebné pred zmenou konfigurácie vykonať ďalšie kroky.
Ako zmenšiť ibdata1?
- Ako prvé je potrebné odzálohovať komplet všetky databázy, minimálne tie, ktoré využívajú InnoDB.
- Dropnúť všetky databázy (okrem databázy „mysql“) – taktiež stačí len tie, ktoré využívajú InnoDB.
- Stopnúť MySQL server.
- Do konfiguračného súboru doplniť innodb_file_per_table.
- Na disku vymazať súbor ibdata1, taktiež aj všetky ib_logfile.
- Naštartovať MySQL server.
- Obnoviť databázy zo zálohy.
Po týchto úkonoch sa pri spustení MySQL servera vytvorí nový čistý súbor ibdata1 s predvolenou veľkosťou (pár MB). Následným importovaním zálohy sa už budú všetky InnoDB tabuľky ukladať do ich samostatných .ibd súborov a ibdata1 nebude narastať. Keď teda následne nejakú tabuľku dropnete, odstráni sa zároveň aj jej .ibd súbor na disku.
Poznáte iný spôsob? Našli ste v článku chybu? Napíšte do komentára.
