Rozmiar bazy danych MariaDB (MySQL)

Na początek logujemy się do bazy danych MariaDB (MySQL).

mysql -u root -p
Password : ***********

Jak już podłączyliśmy się możemy wyświetlić listę baz.

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| DIAGNOSTIC         |
| information_schema |
| mysql              |
| performance_schema |
| testowadb1         |
+--------------------+
5 rows in set (0.00 sec)

ROZMIAR BAZ

Sprawdzamy rozmiary baz

MariaDB [(none)]> SELECT table_schema, SUM((data_length+index_length)/power(1024,1)) tablesize_kb FROM information_schema.tables GROUP BY table_schema;

+--------------------+---------------+
| table_schema       | tablesize_kb  |
+--------------------+---------------+
| information_schema |           176 |
| mysql              | 682.896484375 |
| performance_schema |             0 |
| testowadb1         |            16 |
+--------------------+---------------+
4 rows in set (0.02 sec)

lub to samo tylko wynik w MB

MariaDB [testowadb1]> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| information_schema |           0.2 |
| mysql              |           0.7 |
| performance_schema |           0.0 |
| testowadb1         |           0.0 |
+--------------------+---------------+
4 rows in set (0.02 sec)

Lub mniej zaokrąglone rozmiary

MariaDB [testowadb1]> SELECT table_schema "Database Name", SUM(data_length + index_length)/1024/1024 "Database Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

+--------------------+--------------------+
| Database Name      | Database Size (MB) |
+--------------------+--------------------+
| information_schema |         0.17187500 |
| mysql              |         0.66689110 |
| performance_schema |         0.00000000 |
| testowadb1         |         0.01562500 |
+--------------------+--------------------+
4 rows in set (0.03 sec)

ROZMIAR TABEL

Podłączamy się do naszej bazy TESTOWADB1

MariaDB [(none)]> use testowadb1;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [testowadb1]> select * from testtable1;

+----+-----------+------------+------------------------------+
| id | firstname | lastname   | email                        |
+----+-----------+------------+------------------------------+
|  1 | Jan       | Kowalski   | jan.kowalski@mail.pl         |
|  3 | Anna      | Nowak      | anna.nowak@mail.pl           |
|  4 | Katarzyna | Polska     | katarzyna.polska@mail.pl     |
|  5 | Robert    | Barski     | robert.barski@mail.pl        |
|  6 | Andrzej   | Dulski     | andrzej.dulski@mail.pl       |
|  7 | Agnieszka | Frankowska | agnieszka.frankowska@mail.pl |
|  8 | Hubert    | Goglewski  | hubert.goglewski@mail.pl     |
+----+-----------+------------+------------------------------+
7 rows in set (0.00 sec)

Sprawdzamy rozmiar tabel w instancji. Np 10 największych tabel

MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND( (data_length + index_length) /1024, 2 ) AS "Total Size Kb" FROM information_schema.TABLES ORDER BY data_length DESC LIMIT 10;

+--------------------+--------------------+---------------+---------------+
| TABLE_SCHEMA       | Table Name         | Quant of Rows | Total Size Kb |
+--------------------+--------------------+---------------+---------------+
| mysql              | help_topic         |           508 |        415.97 |
| mysql              | help_keyword       |           464 |        105.27 |
| testowadb1         | testtable1         |             7 |         16.00 |
| mysql              | gtid_slave_pos     |             0 |         16.00 |
| mysql              | innodb_table_stats |             2 |         16.00 |
| mysql              | innodb_index_stats |             7 |         16.00 |
| mysql              | help_relation      |          1028 |         28.04 |
| information_schema | EVENTS             |          NULL |         16.00 |
| information_schema | SYSTEM_VARIABLES   |          NULL |         16.00 |
| information_schema | PROCESSLIST        |          NULL |         16.00 |
+--------------------+--------------------+---------------+---------------+
10 rows in set (0.02 sec)

Lub usuwając z zapytania LIMIT 10 wszystkie tabele w instancji.

Sprawdzamy rozmiar naszej tabeli w KB, MB lub GB

ROZMIAR W KB
MariaDB [testowadb1]> select table_name,(data_length+index_length)/power(1024,1) tablesize_kb FROM information_schema.tables WHERE table_schema='testowadb1' and table_name='testtable1';

+------------+--------------+
| table_name | tablesize_kb |
+------------+--------------+
| testtable1 |           16 |
+------------+--------------+
1 row in set (0.00 sec)

ROZMIAR W MB
MariaDB [(none)]> SELECT table_name,round((data_length+index_length)/power(1024,2),3) tablesize_mb FROM information_schema.tables WHERE table_schema='testowadb1' and table_name='testtable1';

+------------+--------------+
| table_name | tablesize_mb |
+------------+--------------+
| testtable1 |        0.016 |
+------------+--------------+
1 row in set (0.00 sec)

ROZMIAR W GB
MariaDB [testowadb1]> SELECT table_name,(data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='testowadb1' and table_name='testtable1';

+------------+--------------------+
| table_name | tablesize_gb       |
+------------+--------------------+
| testtable1 | 0.0000152587890625 |
+------------+--------------------+
1 row in set (0.00 sec)

Zapytanie zwracające rozmiar tabeli wraz z indeksami

MariaDB [testowadb1]> SELECT
CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
FROM
(
SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz
FROM information_schema.tables
WHERE table_schema='testowadb1'
AND table_name='testtable1'
) AA ) A,(SELECT 'B KBMBGBTB' units) B;

+----------+---------+----------+
| DATSIZE  | NDXSIZE | TBLSIZE  |
+----------+---------+----------+
| 16.00 KB | 0.00 B  | 16.00 KB |
+----------+---------+----------+
1 row in set (0.00 sec)

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz