Odzyskiwanie nieużywanej przestrzeni dyskowej w MySQL-u

Na pewno mieliście kiedyś problemy z odzyskiwaniem zajętej przestrzeni dyskowej przez MySQL, mimo iż większość danych została wcześniej usunięta. Jest to problem, przed którym stanie każdy administrator baz danych.


Aby sprawdzić jaka tabela ile miejsca zajmuje, a ile z tego miejsca można odzyskać, możemy użyć zapytania:

SELECT 
  table_schema as "Database",
  table_name AS "Tables",  
  round(((data_length) / (1024 * 1024 * 1024)), 2) "Size in GB", 
  round(((index_length) / (1024 * 1024 * 1024)), 2) "Index Size in GB", 
  round(((data_free) / (1024 * 1024 * 1024)), 2) "Data Free Size in GB"
FROM 
  information_schema.TABLES 
where table_schema = 'smsanalytics' 
order by data_length + index_length desc;

W zależności od konfiguracji naszego serwera i dostępności wolnego miejsca na dysku, nasze działa na odzyskanie nieużywanej przestrzeni, mogą być różne.

Jeśli mamy wystarczająco dużo wolnego miejsca i mamy włączoną zmienną innodb_file_per_table, możemy użyć polecenia OPTIMIZE TABLE.

mysql (root@ela_db)> SELECT 
 table_schema as "Database", 
 table_name AS "Tables", 
 round(((data_length) / (1024 * 1024)), 2) "Size in MB", 
 round(((index_length) / (1024 * 1024)), 2) "Index Size in MB", 
 round(((data_free) / (1024 * 1024)), 2) "Data Free Size in MB" 
FROM information_schema.TABLES 
where table_schema = 'smsanalytics' 
order by data_length + index_length desc 
limit 1;
+--------------+--------------------+------------+------------------+----------------------+
| Database     | Tables             | Size in MB | Index Size in MB | Data Free Size in MB |
+--------------+--------------------+------------+------------------+----------------------+
| database1    | test               |      60.59 |            49.66 |                 5.00 |
+--------------+--------------------+------------+------------------+----------------------+
5 rows in set (0.69 sec)

mysql (root@ela_db)> optimize table sub_grouprecipient;
+---------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                           | Op       | Msg_type | Msg_text                                                          |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
| database1.test                  | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| database1.test                  | optimize | status   | OK                                                                |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (38.57 sec)

mysql (root@ela_db)> SELECT 
 table_schema as "Database", table_name AS "Tables", 
 round(((data_length) / (1024 * 1024)), 2) "Size in MB", 
 round(((index_length) / (1024 * 1024)), 2) "Index Size in MB",
 round(((data_free) / (1024 * 1024)), 2) "Data Free Size in MB" 
FROM information_schema.TABLES 
 where table_schema = 'smsanalytics' 
order by data_length + index_length desc 
limit 1;
+--------------+--------------------+------------+------------------+----------------------+
| Database     | Tables             | Size in MB | Index Size in MB | Data Free Size in MB |
+--------------+--------------------+------------+------------------+----------------------+
| database1    | test               |      55.59 |            49.66 |                 6.00 |
+--------------+--------------------+------------+------------------+----------------------+
5 rows in set (0.32 sec)

Zmienna innodb_file_per_table od wersji 5.6 jest domyślnie włączona. Może być przydatna w wielu sytuacjach.

Zalety:

• każda tabela jest zapisywana w oddzielnym pliku z rozszerzeniem .idb
• przestrzeń dyskowa jest automatycznie zwraca do systemu operacyjnego po usunięciu (DROP TABLE ...) lub wyczyszczeniu (TRUNCATE TABLE ...) tabeli
• operacja czyszczenia (TRANCATE TABLE ...) jest szybsza
• dla optymalizacji tabeli można wykonać polecenie OPTIMIZE TABLE
• możliwość przenoszenia plików tabeli i łatwiejsze ich backupowanie
• ograniczenia na każdy plik tabeli jest taki sam jak dla pliku, w którym wcześniej trzymane były wszystkie tabele enginu InnoDB (64TB)

Możliwe wady:

• operacja fsync - musi działać na każdej otwartej tabeli niż na pojedynczym pliku
• mysqld musi utrzymywać na każdą tabelę otwarty uchwyt do jego pliku
• jeśli rozmiar tabel bardzo rośnie, rośnie też ryzyko fragmentacji danych, które zmniejsza wydajność skanowania tabeli

Więcej informacji możecie przeczytać w dokumentacji : InnoDB File-Per-Table Mode.


Jeśli jednak, nie mamy wystarczająco dużo miejsca lub zmienna innodb_file_per_table jest wyłączona, musimy:

1. Zdumpować tabelę (polecenie mysqldump)
2. Usunąć tabelę (DROP TABLE)
3. Przywrócić dane z dump-a (mysql)

Dodatkowo odsyłam do ciekawych postów:

• How to reclaim space in InnoDB when innodb_file_per_table is ON
• Why is the ibdata1 file continuously growing in MySQL?