Baza danych MySQL udostępnia administratorowi kilka różnych silników, które może wykorzystać do przechowywania danych. W przypadku różnych silników różnie wygląda fizyczna struktura plików, które są zakładane na dysku. W szczególności, w InnoDB w domyślnej konfiguracji wszystkie dane wgrywane są do jednego, dużego pliku. Gdy w grę wchodzi większa ilość baz danych, trudno określić, ile danych znajduje się w każdej z nich. Na szczęście, tego typu informacje możemy uzyskać z innego źródła.

Twórcy MySQL, ku radości administratorów, zdecydowali się zaimplementować część 11 standardu ANSI/ISO SQL:2003 – Information Schema. Information Schema jest rozwiązaniem, które bardzo ułatwia administrowanie bazą danych MySQL i które pewnie nie raz będę wspominał na tym blogu. Trzeba pamiętać tylko o jednym – zapytania idące do tej meta-bazy mogą trwać długo – szczególnie gdy dany serwer MySQL serwuje dane z dużej ilości baz, dużej ilości tabel itd. Dobrze jest trzymać sobie drugie połączenie do serwera MySQL, tak aby można było monitorować sytuację i w razie potrzeby reagować.

Teraz, wracając do tematu, pokażę jak administrator MySQL można wykorzystać Information Schema do zebrania informacji na temat ilości danych na serwerze. Zobaczmy najpierw jakie dane zawiera tabela `tables` w bazie `information_schema`:

mysql> SHOW CREATE TABLE  INFORMATION_SCHEMA.TABLES\G
*************************** 1. row  ***************************
Table: TABLES
Create Table:  CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512)  DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE`  varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT`  varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT  NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH`  bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21)  unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT  NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT  NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION`  varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT  NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(80) NOT NULL DEFAULT ''
) ENGINE=MEMORY  DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

To, co konkretnie nas interesuje to wartości z kolumn `DATA_LENGTH` i `INDEX_LENGTH` – są to podane w bajtach ilości danych i indeksu dla danej tabeli.

mysql> SELECT table_schema,
-> CONCAT(ROUND(SUM(data_length/(1024*1024)),2),'M') data,
-> CONCAT(ROUND(SUM(index_length/(1024*1024)),2),'M') idx,
-> CONCAT(ROUND((SUM(data_length+index_length)/(1024*1024)),2),'M')  total_size
-> FROM information_schema.tables
->  GROUP BY table_schema
-> ORDER BY data_length+index_length  DESC;
+--------------------+--------+--------+------------+
|  table_schema       | data   | idx    | total_size |
+--------------------+--------+--------+------------+
|  sakila             | 61.25M | 64.26M | 125.52M    |
|  mysql              | 0.52M  | 0.09M  | 0.61M      |
|  test               | 0.24M  | 0.08M  | 0.32M      |
|  information_schema | 0.00M  | 0.01M  | 0.01M      |
+--------------------+--------+--------+------------+
4  rows in set (0,38 sec)

Powyższe zapytanie zwraca zajętość poszczególnych baz danych z rozbiciem na miejsce zajmowane przez dane i indeks. Do takiego zapytania można dorzucić dodatkowe warunki, na przykład jeśli administratora interesuje ile danych przechowywanych jest w tabelach z silnikiem InnoDB, może dodać odpowiedni warunek WHERE:

mysql> SELECT  table_schema,                                                                                                                                                            -> CONCAT(ROUND(SUM(data_length/(1024*1024)),2),'M') data,
-> CONCAT(ROUND(SUM(index_length/(1024*1024)),2),'M') idx,
-> CONCAT(ROUND((SUM(data_length+index_length)/(1024*1024)),2),'M')  total_size
-> FROM information_schema.tables
->  WHERE engine='InnoDB'
-> GROUP BY table_schema
->  ORDER BY data_length+index_length DESC;
+--------------+--------+--------+------------+
|  table_schema | data   | idx    | total_size |
+--------------+--------+--------+------------+
|  sakila       | 61.14M | 64.11M | 125.25M    |
+--------------+--------+--------+------------+
1  row in set (0,35 sec)

Jeśli chcemy uzyskać informację ile miejsca zajmują wszystkie dane, możemy wykonać zapytanie:

mysql>  SELECT CONCAT(ROUND((SUM(data_length+index_length)/(1024*1024)),2),'M')  total_size FROM information_schema.tables;
+------------+
|  total_size |
+------------+
| 126.45M    |
+------------+
1  row in set (0,32 sec)

Porównując z fizycznym zliczeniem objętości plików na dysku mogą wystąpić pewne różnice w wynikach. Zmniejszyć je można dokonując wcześniej operacji OPTIMIZE TABLE na wszystkich tabelach we wszystkich bazach danych. Operacja ta uaktualnia dane statystyczne dotyczące tabel, a także dokonuje ich defragmentacji, zmniejszając ich fizyczną wielkość na dysku.