@Anton_habr_2020
Дано:
1. Интернет сервис, работающий 24×7. Ну или хотелось бы чтобы он так работал.
2. База данных этого сервиса, которая растет и занимает 100+ GB.
БД работает на одном сервере или на небольшом кластере и требует периодического обслуживания:
1. Внесение изменений в структуру, т.к. система развивается
2. Создание дампов
До последнего времени для выполнения этих задач сервис останавливался и все операции проводили максимально аккуратно ночью за 10-20 мин. Однако система растет и близится момент, когда и 1-2 часа на определенные операции будут достижимы, что крайне неприятно. Дампы можно делать используя репликацию, настроим, а вот с правками структуры непонятно…
Есть ли у Вас опыт подобной работы? Как внести правки в структуру большой таблицы, например, при этом не остановив работу всего сервиса?
Решения вопроса 1
@Vamp
1. Утилита pt-online-schema-change. Создаёт пустую копию исходной таблицы, делает на ней alter, копирует данные из исходной таблицы и в конце меняет местами старую и новую таблицы.
Пользовался этой утилитой пару раз. Хорошо работает.
2. В mysql 5.6 появилась возможность делать alter без блокировок средствами самой субд. Нужно в alter добавить парочку новых параметров:
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;Этот вариант я сам не пробовал, поэтому прокомментировать не могу.
3. Самый сложный вариант с использованием двух инстансов mysql, связанных master-master репликацией:
- Делаем alter на втором сервере
- Ждём, когда второй сервер догонится по репликации
- Переключаем сервис на вторую базу
- Ждем какое-то время, смотрим, нормально ли приложение работает с новой схемой, нет ли деградации или ошибок
- Делаем alter на первой базе
- Ждём догона репликации
- Возвращаем сервис на первую базу
С этим вариантом я работаю постоянно. Выглядит просто, но на деле много нюансов.
Нужно обязательно пропускать alter мимо репликации:
SET sql_log_bin = 0;
ALTER TABLE tbl_name ...;Важно не забыть про sql_log_bin = 0, иначе alter по репликации переедет на соседний сервер и залочит таблицу уже там. А переключать сервис нельзя, пока репликация не догонится.
Если меняется структура таблицы — добавляется/удаляется колонка или меняется их порядок, нужно обязательно проследить чтобы тип репликации обязательно был STATEMENT. Иначе репликация приляжет на первом же запросе в формате ROW с примерно такой странной ошибкой:
Column 25 of table ‘mydb.mytable’ cannot be converted from type ‘varchar(255)’ to type ‘bigint(20) unsigned’
А со STATEMENT нужно следить чтобы приложение нигде не понижало уровень изоляции ниже REPEATABLE READ, иначе получит ошибку:
Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
Комментировать
Ответы на вопрос 3
@Bedrosova
@vitaly_il1
— 100ГБ — это мелочи, но надо следить чтобы база не росла бесконтрольно, т.е. чистить старые данные которые уже неревантны
— «Создание дампов» — mysqldump c —single-transaction, есть и более продвинутые методы
Но в целом — или взять DBA, или — это обычно дешевле — использовать managed service — AWS RDS если вы уже в AWS, или https://aiven.io/.
Потому что в дополнение к в вопросам которые вы уже задали, для продакшен надо задать еще много — например, как откатиться к на полчаса или на сутки?
@ComodoHacker
1. Внесение изменений в структуру, т.к. система развивается
Нужно изучить средства, которые предоставляет для этого ваша СУБД, и правильно их использовать. А вообще это больше забота разработчика, а не админа.
2. Создание дампов
Уточните, что вы подразумеваете под «дампами», и с какой целью они делаются. Если вы имеете в виду резервное копирование данных, то любая современная СУБД предоставляет для этого инструменты, позволяющие делать бэкапы, не прерывая работы пользователей. То есть см. предыдущий пункт.