Уменьшение размера БД MySQL/MariaDB (Zabbix)

Заметка по вопросу уменьшения занимаемого места БД Mysql или MariaDB.

У меня есть несколько Zabbix серверов. На одном Mysql, на другом MariaDB. Всё ниже описанное относится к MariaDB (версия 10.3.23).

Надо выполнить вход в СУБД и выбрать нужную базу данных:

mysql -u root -p

use zabbix;

В моём случае это база данных zabbix.

Чтобы посмотреть информацию по таблицам и увидеть сколько места они занимают, необходимо выполнить запрос в базе:

SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024 / 1024),2) "Size in GB" FROM information_schema.tables WHERE table_schema = "zabbix" ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESC LIMIT 8;

Вот пример моего вывода:

Здесь можно увидеть сколько реально занимает места таблица.

А если выполнить вот такой запрос:

select table_name, round(data_length/1024/1024) as data_length_mb,
round(data_free/1024/1024) as data_free_mb from 
information_schema.tables where round(data_free/1024/1024) > 50 order
by data_free_mb;

то можно получить сводку, сколько места можно освободить на диске, если сделать оптимизацию по указанный в выводе таблицам.

Если запрос выполнить без части подсвеченной синим цветом (не забывает точку с запятой в конце запроса), то выдаст данные по всем таблицам.

В моё случае history имеет самый большой потенциал к уменьшение занимаемого места на диске.

Для начала можно удалить старые данные из базы вручную, используя вот такой скрипт:

-- intervals in days
SET @history_interval = 7;
SET @trends_interval = 90;

DELETE FROM alerts WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM acknowledges WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM events WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM history WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_str WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_text WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_log WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM trends WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);
DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);

В начале скрипта задаются временные интервалы в днях, дальше которых данные не актуальны. Скрипт общий для всех таблиц (относительно zabbix).

Выполняю

OPTIMIZE TABLE history;

без предварительной очистки.

После выполнения данной процедуры я перелогинился в Zabbix, т.к. до этого он почему-то перестал отображать адекватно информацию.

Результат занимаемого места средствами мониторинга Zabbix:

Было 2.58GB, стало 2.36GB.

В процессе выполнения операции, как видно из графика, занимаемое место вначале расчёт. Это стоит учитывать при выполнении данной операции, т.к. для манёвров нужно свободное пространство на диске.

Я оптимизировал базу history_uint размером 13ГБ и на это время мониторинг Zabbix не отображал мне собираемую информацию. Делалось это на обычных дисках (не ssd и не nvme, но серверных), времени ушло - 1ч. 33м.). На время манёвров потребовалось +6ГБ на диске.  База с 13,04 -> 7,16ГБ. Уменьшилась почти в два раза.

Можно оптимизировать/очистить сразу все возможные базы:

mysqlcheck -o --all-databases -u root -p

В моё случае это снизило занимаемое место базы с 2.36GB (после результата выше) до 2.29GB.

В процессе выполнения выше описанных процедур по освобождению места сам Zabbix сервер работал, проблем с этим не было, данные собирались. Разве что подтормаживал веб-интерфейс.

#Проблемка

При выполнение команды optimize table history_uint; получают ошибку:

Index for table 'history_uint' is corrupt; try to repair it

Пока не разобрался с этой ошибкой.

Пробовал

mysqlcheck -r --databases mysql --use-frm

Команда repair table zabbix.history_uint; выдаёт:

The storage engine for the table doesn’t support repair

Использованный материал.

ip-calculator.ru/blog/ask/szhatie-defragmentatsiya-i-optimizatsiya-bazy-dannyh-mariadb-mysql/

https://dotclear.placeoweb.com/post/%5BERROR%5D-mysqld.exe-Index-for-table-mysqldb-is-corrupt-try-to-repair-it-Couldn-t-repair-table-mysql.db

https://github.com/burner1024/zabbix-sql/blob/master/check-old-data.my.sql

Добавить комментарий Отменить ответ