Zauzeće prostora na disku kod InnoDB Storage Engine-a

Desilo vam se sigurno da imate malu bazu a fajl u kome se ona nalazi je veliki, nekad cak i ogroman i ma sta radili, fajl ostaje nekoliko puta veci od same baze. Razlog je prilicno jednostavan, InnoDB storage engine koristi table space tako da isti moze samo da raste. Jasno je da ako imamo tabelu od 1M slogova i onda obrisemo srednjih 500K slogova da ce tabela da ima rupu, onda opet dodamo 100K pa obrisemo 50K pa … i tako posle nekog vremena imamo jako fragmentisanu tabelu koja zauzima puno mesta (zbog tih silnih „rupa“).

Fragmentacija se na MySQL-u uglavnom resava sa „OPTIMIZE TABLE t1“ no, optimize stvarno „optimizuje“ tabelu u slucaju MyISAM, Archive i jos nekih komercijalnih storage engine-a a za InnoDB radi „kompletan rebuild tabele“. Rebuild tabele se moze direktno uraditi sa „null alter-om“ ili „ALTER TABLE t1 ENGINE=InnoDB“ sto ce rebildovati celu tabelu.

Posle optimizacije (rebuild-a) InnoDB tabela ce biti defragmentirana i zauzimace samo onaj prostor koji je potreban, ali prazan prostor na kom se tabela prethodno nalazila i dalje ce biti u InnoDB table space-u i nece biti vracen file sistemu. Taj prostor ce se koristiti pri rastu te tabele ili bilo koje druge InnoDB tabele. Da bi „povratili“ taj prostor, jedini nacin je dump/restore.

Jedini nacin da InnoDB tabele zauzimaju „onoliko mesta koliko im treba“ je da koristimo innodb_file_per_table koji kaze InnoDB-u da za svaku tabelu pravi zaseban table space. U ovom slucaju ce posle OPTIMIZE TABLE InnoDB tabela „vratiti“ file sistemu neiskoristen prostor.

Obratite paznju da se promena vrednosti ovog paramatre ne primenjuje retroaktivno, dakle, ako imamo 20 tabela na MySQL serveru u InnoDB table space-u i nije nam bio setovan innodb_file_per_table, sve ce biti u jednom fajlu (ok ili vise njih ali nikako tabela po fajlu), ako sada upalimo ovaj fleg, samo nove tabele ce biti kreirane kao zasebni fajlovi dok ce sve prethodno kreirane tabele funkcionisati kako su i bile ranije. Najbrzi nacin da se predje potpuno na tabela po fajlu sistem je da se uradi dump, setuje taj fleg pa onda uradi restore.

Ako koristite MySQL server na windozi, obratite paznju na limit od 2048 fajlova koje MySQL moze da otvori na windozi. Ovaj limit postoji zato sto windoze imaju hardcoded limit na broj otvorenih fajlova posix funkcijama. MySQL Server u verziji 6.x vise na windozi ne koristi posix funkcije tako da je taj limit prevazidjen.