Andmebaasi suurus

Kuidas kujuneb andmebaasi suurus?

Andmebaasi kogumaht on selles olevate tabelite suuruste summa.

Tabeli mahu suuruseks ei ole ainult selles näha olevate ridade maht, vaid selle hulka kuulub ka tabeli lisafunktsioonide (erinevat tüüpi võtmed ja indeksid) ja tegevustega tagajärjel jäänud vabastamata maht (ing. k. overhead).

Andmebaasi tabeli suurus kujuneb kolmest osast:

• andmed
• indeksid
• vabastamata maht (overhead)

Iga andmetüübi andmed on salvestatud andmebaasiserveris failidesse, mis võtavad enda alla serveri kettapinnast vaba mahtu.

Kuidas vaadata andmebaasi ja selle tabelite suurust?

Andmebaasi ja selle tabelite suurust saab vaadata erinevate klient-programmide ja phpMyAdmin kaudu või otse SSH terminalist.

Kasutame siin selle jaoks phpMyAdmin’i.

Peale phpMyAdmin’i sisselogimist, valida vasakust tulbast vajalik andmebaas, mille jaoks kuvatakse esimesel vahelehel “Structure” kõik selle andmebaasi tabelid ja kõige lõpus, tabeli viimasel real, andmebaasi tabelite suurused kokku. Kuid selle hulka ei arvestata tabelite vabastamata mahtu:

Selleks, et näha tabeli tegeliku suurust (andmed + indeksid + vabastamata maht), tuleb minna tabeli vahelehele Structure ja vaadata seal alamjaotuses Information tabelit Space usage. Selles olev rida Effective on tabeli tegelik suurus andmebaasiserveris:

Mis on tabeli vabastamata maht (ing. k. overhead)?

MySQL / MariaDB andmebaasi tabeli vabastamata maht (overhead) on tabelile eraldatud, kuid mitte kasutusel olev maht.

Näha on see suurus baitides tabeli information_schema.TABLES tulbas Data_free.

Data_free tekkimise põhjusteks on mahu killustatuse tõttu, mis tuleneb tabelist ridade kustutamise (DELETE), ridade uuendamisega (UPDATE) suuremate väärtustega, tabeli mahu vähendamise ja ümber paigutamisega, mahu reserveerimisega tulevaste päringute (INSERT, UPDATE) jaoks.

Kuidas leida tabelid, millel on suur vabastamata maht (ing. k. overhead)?

Kui andmebaasis on tabeleid palju ja ei ole teada, millisel neist võib vabastamata maht olla suur, on iga tabeli vahelehe Structure vaatamise asemel, kiirem viis probleemse tabeli ülesleidmiseks kasutada järgmist SQL-päringut.

SHOW TABLE STATUS WHERE `Data_free` > 0;

Päringu tulemuste tabeli tulp Data_free näitab tabeli poolt kinnihoitavat mahtu (overhead’i), mahud kuvatakse baitides:

Põhjalikum SQL-päring, mis näitab megabaitides kõigi kolme andmetüübi suurust eraldi ja tabeli tegeliku suurust kokku (andmed + indeksid + vabastamata maht).
Asendada käsus [DATABASE_NAME] tegeliku andmebaasi nimega:

SELECT `TABLE_SCHEMA` AS "Database", `TABLE_NAME` AS "Table", `ENGINE`, `ROW_FORMAT`, `TABLE_ROWS`, ROUND(((`DATA_LENGTH`) / 1024 / 1024), 2) AS "Data Size (MiB)", ROUND(((`INDEX_LENGTH`) / 1024 / 1024), 2) AS "Index Size (MiB)", ROUND(((`DATA_FREE`) / 1024 / 1024), 2) AS "Overhead (MiB)", ROUND(((`DATA_LENGTH` + `INDEX_LENGTH` + `DATA_FREE`) / 1024 / 1024), 2) AS "Total Size (MiB)" FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '[DATABASE_NAME]' AND `DATA_FREE` > 0 ORDER BY `DATA_FREE` DESC;

On näha, et selle WordPress’i options tabeli suurus on 113,3 MiB, kuid vabastamata mahu all on kinni 8337 MiB ehk 8,1 GiB – see on üle 70 korra suurem kui tabeli andmete suurus!

Kinnihoitava mahu (ing. k. overhead) vabastamine – tabeli optimiseerimine

Selleks, et vabastada tabeli poolt kinnihoitav vaba maht, tuleb teha tabelile optimiseerimine.

Tabeli optimiseerimise tegemise jaoks on olemas link Optimize table tabeli vahelehel Structure, tabelis Space usage ja samuti ka tabeli vahelehel Operations.

Selle asemel võib teha järgmise SQL-päringu tabeli optimiseerimise jaoks (asendada käsus [DATABASE_NAME] ja [TABLE_NAME] andmebaasi ja tabeli nimega):

OPTIMIZE TABLE `[DATABASE_NAME]`.`[TABLE_NAME]`;