Уменьшение таблиц на узлах Percona XtraDB Cluster
Содержание
Введение
Имеется два узла Percona XtraDB Cluster, на одном из которых файлы базы данных занимают больше места, чем на другом, причём со временем разница увеличивается. На узле, который обслуживает запросы приложения, база данных почти не растёт в размерах. А на резервном узле по непонятным причинам база данных со временем увеличивается.
Наглядно рост размеров файлов базы данных на резервном узел можно увидеть на следующем графике:
Нашёл два обсуждения подобной темы, окончившиеся ничем и одну статью с банальными рекомендациями:
- Difference in MYSQL Master Slave DISK SPACE
- Slave DB bigger on disk than on Master
- Mysql – Difference in MYSQL Master Slave DISK SPACE
Найти решение мне помогла статья How to Perform Compatible Schema Changes in Percona XtraDB Cluster (Advanced Alternative)?, в которой описывается, как временно исключить узел из кластера, провести на нём сжатие таблицы (или изменение индекса) и аккуратно вернуть узел в кластер, так чтобы он синхронизировался с остальными узлами.
Уменьшение таблицы
Для примера найдём файл таблицы, размеры которого на резервном узле больше, чем на основном:
Файл на основном узле:
-rw-r----- 1 mysql mysql 5578424320 Apr 4 09:27 contract_status.ibd
Файл на резервном узле:
-rw-r----- 1 mysql mysql 5947523072 Apr 4 09:26 contract_status.ibd
Посмотрим статус таблицы на основном узле:
mysql> SHOW TABLE STATUS WHERE name = 'contract_status'\G
*************************** 1. row ***************************
Name: contract_status
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 29986501
Avg_row_length: 121
Data_length: 3643785216
Max_data_length: 0
Index_length: 1635549184
Data_free: 4194304
Auto_increment: 132888294
Create_time: 2023-06-23 14:22:44
Update_time: 2024-04-04 09:28:35
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.10 sec)
И сравним её со статусом таблицы на резервном узле:
mysql> SHOW TABLE STATUS WHERE name = 'contract_status'\G
*************************** 1. row ***************************
Name: contract_status
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 28864077
Avg_row_length: 113
Data_length: 3279929344
Max_data_length: 0
Index_length: 1503477760
Data_free: 5242880
Auto_increment: 132888296
Create_time: 2023-11-20 20:04:20
Update_time: 2024-04-04 09:27:33
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (1.12 sec)
Заметно, что на резервном узле индекс и данные занимают меньше места, но и средний размер строки тоже меньше. Это странно. На всякий случай проанализируем таблицу на резервном узле, чтобы обновить её статистику:
mysql> ANALYZE TABLE contract_status;
+-------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| billing.contract_status | analyze | status | OK |
+-------------------------+---------+----------+----------+
1 row in set (0.52 sec)
Смотрим снова статус таблицы на втором узле и видим данные, более соответствующие действительности:
mysql> SHOW TABLE STATUS WHERE name = 'contract_status'\G
*************************** 1. row ***************************
Name: contract_status
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 30579535
Avg_row_length: 136
Data_length: 4160733184
Max_data_length: 0
Index_length: 1698480128
Data_free: 5242880
Auto_increment: 132888362
Create_time: 2023-11-20 20:04:20
Update_time: 2024-04-04 09:28:49
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.13 sec)
Сжать таблицу на резервном узле можно при помощи такого запроса:
ALTER TABLE contract_status ENGINE=InnoDB;
Однако выполнять такой запрос в кластере не безопасно, поскольку его запуск даже на узле, не обслуживающем запросы, приведёт к синхронному выполнению такого же запроса на всех остальных узлах кластера и на всех зависящих от них репликах.
Для безопасного выполнения необходимо вывести узел из кластера и отключить запись в журнал репликации. На узел, выведенный из состава кластера, не должны поступать никакие
На основном узле, который обслуживает запросы, в том числе на запись, отключаем очистку кэша Galera, чтобы резервный узел после отключения смог найти пропущенные им операции и воспроизвести их у себя:
SET GLOBAL wsrep_provider_options="gcache.freeze_purge_at_seqno = now";
На резервном узле, не обслуживающем нагрузку, выполняем последовательность команд/запросов для пересоздания таблицы таким образом, чтобы эта операция не выполнялась основным узлом и не попала в журнал репликации:
SET GLOBAL wsrep_desync=1;
SET wsrep_on=0;
SET sql_log_bin = 0;
ALTER TABLE contract_status ENGINE=InnoDB;
SET sql_log_bin = 1;
SET wsrep_on=1;
SET GLOBAL wsrep_desync=0;
После выполнения команд/запросов резервный узел должен вернуться в кластер и нагнать изменения, произошедшие за время его отсутствия. В журнале ошибок MySQL при этом будут записи следующего вида:
2024-04-04T17:26:31.347180+05:00 0 [Note] WSREP: Member 1.0 (mysql1) desyncs itself from group
2024-04-04T17:26:31.347250+05:00 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 105844919755)
2024-04-04T17:26:49.342159+05:00 0 [Note] WSREP: Member 1.0 (mysql1) resyncs itself to group.
2024-04-04T17:26:49.342244+05:00 0 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 105844930863)
2024-04-04T17:26:49.343276+05:00 0 [Note] WSREP: Member 1.0 (mysql1) synced with group.
2024-04-04T17:26:49.343329+05:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 105844930863)
2024-04-04T17:26:49.343404+05:00 27 [Note] WSREP: Synchronized with group, ready for connections
2024-04-04T17:26:49.343479+05:00 27 [Note] WSREP: Setting wsrep_ready to true
После того, как резервный узел вернулся в кластер, можно разрешить основному узлу подчистить кэш Galera от операций, применённых на обоих узлах:
SET GLOBAL wsrep_provider_options="gcache.freeze_purge_at_seqno = -1";
Проверим снова размер файла таблицы на резервном узле:
-rw-r----- 1 mysql mysql 5414846464 Apr 4 09:36 contract_status.ibd
Как видно, файл стал занимать даже меньше места, чем на основном узле. Это именно то, чего мы и добивались.
Массовая обработка таблиц
Теперь было бы полезно обработать таким образом все таблицы. Я собираюсь обрабатывать таблицы в порядке снижения процента неиспользуемого места в файле. Сначала будут обрабатываться те таблицы, размер свободного места в которых по отношению к объёму данных и индексов максимален. Для получения списка таблиц в желаемом порядке их обработки я собираюсь использовать запрос следующего вида:
SELECT table_schema,
table_name
FROM tables
WHERE table_type = 'BASE TABLE'
AND engine = 'InnoDB'
AND data_free > 0
ORDER BY data_free / (data_length + index_length) DESC;
Но перед этим нужно запустить анализ таблиц, чтобы этот запрос возвращал верные данные. Поскольку анализ таблиц тоже выполняется по умолчанию на всех узлах кластера одноверменно, эта операция тоже может вызывать проблемы в работе основного узла и поэтому анализ таблиц необходимо выполнять при отключенной записи в журнал репликации и на узле, отсоединённом от узла.
Для автоматизации процесса написал скрипт compact.sh. В нём автоматически определяется список узлов в кластере (при необходимости его можно указать вручную, по одному адресу в строке), из него исключается текущий узел. Далее проверяется исправность узлов кластера, чтобы не выполнять опасные действия в кластере, который уже находится в недостаточно хорошем состоянии. Затем на остальных узлах отключается очистка кэша Galera, а на текущем узле выполняется сначала анализ всех таблиц (действие, наверное, не обязательное), потом их сжатие. Перед каждой операцией с таблицей выполняется отсоединение узла от кластера, а после - возврат узла в кластер и ожидание, когда текущий узел синхронизируется с кластером. После обработки всех таблиц на остальных узлах возвращается обычный режим очистки кэша Galera.
Запускать этот скрипт можно только на узле, не обслуживающем запросы. Если узел обслуживает запросы, то операции выборки данных будут возвращать устаревшие данные, а операции изменения данных приведут к поломке кластера, т.к. будут выполнены только на этом узле и не попадут на остальные узлы кластера.
После обработки всех таблиц размер файлов базы данных на резервном узле стал меньше, чем на основном, что можно увидеть на графике ниже:
В целом размеры файлов баз данных на обоих узлах в процессе их роста и сжатия изменялись следующим образом:
