Уменьшение размера файла ibdata1 базы данных Asterisk/FreePBX

Весь описанный далее процесс производился на виртуальной машине с боевой Asterisk 16.30.0 / FreePBX 15.0.37.4

Все данные АТС хранит в базе данных MariaDB в таблицах формата InnoDB. К сожалению, по-умолчанию все таблицы хранятся в одном файле: /var/lib/mysql/ibdata1 Этот файл достаточно быстро разрастается (в основном за счет таблиц cdrdb), а способов уменьшить размер InnoDB файла не существует. Кроме того, попытка решить проблему в лоб (OPTIMIZE TABLE) приведет к еще большему разрастанию файла, т.к. сначала создастся новая, оптимизированная таблица, что и приведет к увеличению файла ibdata1, а потом старая таблица уничтожится. Это приведет к освобождению места внутри файла, но сам файл данных при этом не уменьшится. Для того чтобы решить эту проблему необходимо указать в настройках базы данных, что необходимо создавать индивидуальные файлы данных для каждой таблицы. Делается это добавлением параметра innodb_file_per_table в файл настроек /etc/my.cnf в секцию mysqld.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# * Fine Tuning
innodb_file_per_table = 1

Алгоритм перевода общего файла данных на индивидуальные, такой:

1. Делаем дамп баз asterisk и asteriskcdrdb

   mysqldump -h localhost -u root asterisk > asterisk.sql
   mysqldump -h localhost -u root asteriskcdrdb > asteriskcdrdb.sql
   

2. Удаляем базы asterisk и asteriskcdrdb

   mysqladmin -h localhost -u root drop asteriskcdrdb
   mysqladmin -h localhost -u root drop asterisk
   

3. Останавливаем MariaDB

   service mariadb stop

4. На случай, если что-то пойдет не так, перемещаем (т.е. копируем и удаляем исходные) к дампам файлы ibdata1, ib_logfile0 и ib_logfile1
5. Добавляем параметр, который включает индивидуальные файлы данных для таблиц, в файл настроек /etc/my.cnf
6. Запускаем MariaDB. При этом в /var/lib/mysql создаются ранее удаленные ib_logfile0 и ib_logfile1

   service mariadb start

7. Создаем базы данных asterisk и asteriskcdrdb

   mysqladmin -h localhost -u root create asterisk
   mysqladmin -h localhost -u root create asteriskcdrdb
   

8. Восстанавливаем базы из созданных ранее дампов. При этом в каталоге базы создаются файлы с именем таблицы и расширениями .frm и .ibd (.frm содержит описание структуры таблицы, а .ibd как раз и является тем самым индивидуальным файлом данных в формате хранения InnoDB)

   mysql -h localhost -u root  asterisk < asterisk.sql
   mysql -h localhost -u root  asteriskcdrdb < asteriskcdrdb.sql
   

9. Перезапускаем АТС

   reboot

Теперь при удалении старых/ненужных записей из таблицы, например, asteriskdcrdb/cel можно сделать оптимизацию этой таблицы, которая приведет к пересозданию файла cel.ibd и фактическому уменьшению занимаемого базой места на диске.

В организации, где я работаю, ИБ требует хранить данные о звонках один год. Так как эта информация из таблиц  базы asteriskdcrdb более нигде у нас не используется, раз в месяц устаревшие данные автоматически удаляются и выполняется оптимизация таблиц. Для этого в планировщике /etc/crontab прописываем задачу:

 0 3 1 * * root cd /usr/scripts && mysql -h localhost -u root asteriskcdrdb < clear_phone_rings.sql

Каждый первый день месяца в 3 часа ночи для базы asteriskdcrdb выполняется набор SQL-команд из файла clear_phone_rings.sql, который содержит:

DELETE FROM cdr WHERE calldate < NOW() - INTERVAL 1 YEAR;
DELETE FROM cel WHERE eventtime < NOW() - INTERVAL 1 YEAR; 
OPTIMIZE TABLE cdr;
OPTIMIZE TABLE cel;

В зависимости от количества звонков и скорости сервера с базой данных АТС время выполнения этих команд может сильно отличаться, но в любом случае это занимает весьма ощутимое время. Поэтому рекомендую выполнять эту процедуру когда нагрузка на сервер базы АТС минимальна.