Tietokannan koko

Tietokannan koko tarkoittaa yksinkertaisesti sanottuna tietojen ja niihin liittyvien komponenttien käyttämää kokonaistallennustilaa tietokannassa.

Tietokannan kokoon vaikuttavat keskeiset komponentit

Tietokannan kokonaiskoko on sen taulukoiden kokojen summa.

Taulukon koko ei ole vain taulukossa näkyvien rivien koko, vaan se sisältää myös taulukon lisätoiminnot (erityyppiset avaimet ja indeksit) sekä pyyntöjen ja toimien seurauksena jääneen käyttämättömän tilan.

Tietokantataulukon koko koostuu kolmesta osasta:

• data
• indeksit
• käyttämättä tila (overhead)

Kunkin tietotyypin tiedot tallennetaan tietokantapalvelimelle tiedostoihin, jotka vievät vapaata levytilaa palvelimella.

Kuinka tarkastella tietokannan ja sen taulukoiden kokoa?

Tietokannan koko ja sen taulukoiden koko on tarkastettava eri asiakasohjelmien ja phpMyAdminin kautta tai suoraan SSH-päätteestä.

Käytämme tähän phpMyAdminia.

Kun olet kirjautunut phpMyAdminiin, valitse vasemmasta sarakkeesta haluamasi tietokanta, jonka kaikki taulukot näkyvät ensimmäisellä Structure -välilehdellä ja aivan lopussa, taulukon viimeisellä rivillä, näytetään taulukoiden kokonaiskoko. Tämä ei kuitenkaan sisällä taulukoiden käyttämättä tilaa:

Nähdäksesi taulukon todellisen koon (tiedot + indeksit + lisäkustannukset), sinun on siirryttävä taulukonStructure -välilehdelle ja etsittävä Space usage -taulukko Information -alaosiosta. Sen Effective -rivi on tietokantapalvelimella olevan taulukon todellinen koko:

Mikä on taulukon käyttämäton tila?

Overhead on MySQL/MariaDB-tietokantataulukon vapauttamaton tila, joka on varattu taulukolle, mutta joka ei ole käytössä.

Tämä näkyy tauluko information_schema.TABLES sisällä Data_free sarakkeessa. Koko näytetään tavuina.

Data_free johtuu käyttämättömästä ja pirstoutuneesta tilasta, joka johtuu rivien poistamisesta taulukosta (DELETE), rivien päivittämisestä (UPDATE) suurempiin arvoihin, taulukkotilan pienentämiseen ja uudelleenjärjestelyyn, tilan varaamiseen tulevia kyselyitä varten (INSERT, UPDATE).

Kuinka löytää taulukon, jossa on isot  vapauttamatot tilat?

Jos tietokannassa on suuri määrä taulukoita etkä tiedä, millä niistä voi olla iso vapauttamaton tila, sen sijaan, että katsoisit kunkin taulukon STRUCTURE-välilehteä, nopeampi tapa löytää ongelmallinen taulukko on käyttää seuraavaa SQL-kyselyä.

SHOW TABLE STATUS WHERE `Data_free` > 0;

Kyselyn tulostaulukon Data_free -sarake näyttää taulukon ylärajan, koot näytetään tavuina:

Yksityiskohtaisempi SQL-kysely, joka näyttää kunkin kolmen tietotyypin koot megatavuina erikseen ja taulukon todellisen koon (tiedot + indeksit + lisäkustannukset).
Asenna [DATABASE_NAME] todellisella tietokannan nimellä komennossa:

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ähtävissä, että tämän WordPressin optiotaulukon koko on 113,3 MiB, mutta vapauttamattoman tilan alla on jumissa 8337 MiB tai 8,1 GiB – se on yli 70 kertaa taulukon datan kokoa!

Käyttämättömän tilan vapauttaminen – taulukon optimointi

Taulukon käyttämättömän tilan vapauttamiseksi taulukolle on suoritettava optimointi.

Taulukon optimointia varten on Structure -välilehden taulukossa Space usage ja myös Operations välilehden sivulla Optimize table linkki.

Vaihtoehtoisesti voidaan tehdä SQL-kysely taulukon optimoimiseksi (korvaa [DATABASE_NAME] ja [TABLE_NAME] komennossa tietokannan ja taulukon nimellä):

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