воскресенье, 12 июля 2015 г.

Миграция Zabbix с MySQL на PostgreSQL

Периодически замечал в iotop на своём домашнем компьютере, что самую высокую нагрузку по вводу-выводу создаёт MySQL. В интернете встречал мнения, что PostgreSQL по сравнению с MySQL более стабильно ведёт себя в условиях дефицита производительности дисковой подсистемы. Больше всего MySQL на этом компьютере нагружался Zabbix'ом, поэтому ради эксперимента решил попробовать перевести Zabbix на использование PostgreSQL.

Довольно долго я пытался экспортировать данные из MySQL при помощи штатного инструмента mysqldump в виде, пригодном для последующего экспорта в PostgreSQL. У этой утилиты имеется опция, позволяющая экспортировать в формате, совместимом с PostgreSQL. Несколько дополнительных опций, чтобы отключить директивы, отключить создание таблиц, добавить явные имена колонок в запросы INSERT, позволили получить результат с виду пригодный для импорта в PostgreSQL.

Тут, однако, возникла проблема. Данные таблиц в дампе шли в порядке, соответствюущем алфавитному порядку имён таблиц. Это вызывало проблемы с обработкой внешних ключей. Почему-то на тот момент я не догадался заглянуть в файл scheme.sql и создавал схему базы данных сразу с ограничениями и внешними ключами. Для решения этой проблемы я написал скрипт, который брал информацию о внешних ключах из базы данных information_schema и сортировал таблицы так, чтобы ссылающиеся таблицы шли после тех, на которые они ссылаются. Экспортировал данные я именно в этом порядке.

Однако когда проблема с внешними ключами была решена не самым простым путём, всплыло несколько досадных мелочей:
  • в полученном дампе внутри одинарных кавычек двойные кавычки экранировались обратным слэшем, в то время как PostgreSQL принимал их без экранирования,
  • одинарные кавычки в тех же строках тоже экранировались обратным слэшем, но PostgreSQL ждал, что они просто будут продублированы,
  • двоичные данные сохранялись в виде закавыченной последовательности байтов, в то время как PostgreSQL ждал двоичные данные в виде шестнадцатеричных цифр с префиксом 0x.
Попытки воспользоваться sed'ом для исправления недостатков не привели к успеху и я решил попробовать pgloader.

После продолжительных мучений с документацией pgloader и PostgreSQL в поисках правильной обработки зависимостей внешних ключей, я наконец-то догадался заглянуть в файл schema.sql и поделил его на две части. После этого pgloader отработал без запинки. Получившимся рецептом миграции и спешу поделиться.

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

1. Установка пакетов

Установим СУБД, если она ещё не установлена:
# apt-get install postgresql
Установим пакет pgloader, с помощью которого будем переносить содержимое базы из MySQL в PostgreSQL:
# apt-get install pgloader
Доустановим пакет для работы веб-интерфейса с СУБД PostgreSQL:
# apt-get install php5-pgsql
Чтобы новый модуль можно было использовать из PHP, перезапустим php5-fpm:
# systemctl restart php5-fpm
2. Подготовка СУБД

Правим файл конфигурации аутентификации пользователей /etc/postgresql/9.4/pg_hba.conf, заменяя первую строку на вторую:
local   all             all                                     peer
local   all             all                                     md5
По умолчанию, при подключении к UNIX-сокету, PostgreSQL определяет учётную запись, под которой работает подключившийся процесс и автоматически создаёт подключение от имени одноимённого пользователя из СУБД. Пароль при этом не запрашивается. Меняя эту строчку, мы будем требовать у подключившегося процесса явным образом указать имя пользователя СУБД и его пароль.

После этого перезапустим сервер базы данных, чтобы новые настройки вступили в силу:
# systemctl restart postgresql
Теперь из сеанса пользователя root заходим под пользователем postgres:
# su - postgres
От имени пользователя postgres создаём пользователя базы данных с именем zabbix:
$ createuser -P zabbix
Ключ -P означает, что будет запрошен пароль нового пользователя.

От имени пользователя postgres создаём саму базу данных с именем zabbix, владеть которой будет только что созданный пользователь с именем zabbix:
$ createdb -E UTF-8 -O zabbix zabbix
Опция -E UTF-8 означает, что текстовая информация в базе данных будет храниться в кодировке UTF-8, а опция -O задаёт пользователя, который будет владельцем базы данных.

3. Подготовка к переносу данных

Теперь возьмём файл database/postgresql/schema.sql, имеющийся в дистрибутиве Zabbix и поделим его на две части. В первой части будут запросы, создающие таблицы (CREATE TABLE), а во второй - создающие внешние ключи и ограничения (ALTER TABLE). Назовём эти файлы schema1.sql и schema2.sql

Создадим файл zabbix.load, содержащий настройки для переноса данных:
LOAD DATABASE
  FROM mysql://zabbix:zabbix_password@localhost/zabbix
  INTO postgresql://zabbix:zabbix_password@localhost/zabbix

WITH include no drop,
     truncate,
     create no tables,
     create no indexes,
     no foreign keys,
     reset sequences,
     data only

SET maintenance_work_mem TO '128MB',
    work_mem to '12MB'

BEFORE LOAD EXECUTE schema1.sql

AFTER LOAD EXECUTE schema2.sql;
Подготовим пакет с Zabbix-сервером, работающим с PostgreSQL или подключим репозиторий с этим пакетом. Как это сделать - решайте сами.

4. Перенос данных

Перед переносом данных остановим Zabbix-сервер:
# systemctl stop zabbix-server
Закроем доступ к веб-интерфейсу Zabbix, остановив php5-fpm (можно просто запретить доступ к веб-интерфейсу Zabbix через настройки nginx):
# systemctl stop php5-fpm
Теперь приступим к собственно переносу данных (в текущем каталоге должны быть подготовленные ранее файлы schema1.sql, schema2.sql, zabbix.load):
$ pgload zabbix.load
Пока данные переносятся, удалим старый Zabbix-сервер для MySQL:
# dpkg -r zabbix-server-mysql
Установим Zabbix-сервер для PostgreSQL :
# dpkg -i zabbix-server-pgsql_2.4.5-1+jessie_amd64.deb
Теперь, если данные уже перенеслись, можно запускать новый Zabbix-сервер:
# systemctl start zabbix-server
Отредактируем файл /etc/zabbix/web/zabbix.conf.php, заменив первую строчку на вторую:
$DB['TYPE']     = 'MYSQL';
$DB['TYPE']     = 'POSTGRESQL';
Запускаем php5-fpm (или открываем доступ к веб-интерфейсу Zabbix через настройки nginx):
# systemctl start php5-fpm
5. Проверка результата

Заглядываем в журналы Zabbix-сервера /var/log/zabbix/zabbix_server.log и заходим в веб-интерфейс Zabbix, проверяя, что всё работает нормально.

Кстати, наблюдение за нагрузкой на дисковую подсистему по такому не совсем чёткому параметру как iowait, показало, что нагрузка действительно упала:

13 комментариев:

Unknown комментирует...

Здравствуйте. Какая версия MySql и какой тип таблиц у Вас использовался?
У нас удалось снизить нагрузку на дисковую подсистему при миграции с MyIsam на InnoDB и переходе на MySql 5.6

morbo комментирует...

Версия 5.5.43. MySQL 5.6 в репозитории нет, поэтому не пробовал. Zabbix рассчитан на использование InnoDB, который и использовался.

По сравнению с дистрибутивным конфиг /etc/mysql/my.cnf был изменён следующим образом:
innodb_buffer_pool_size = 512M
innodb_file_per_table = 1
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT

В случае с PostgreSQL, в конфиге /etc/postgresql/9.4/main/postgresql.conf поменял только одну настройку (просто по аналогии с MySQL, не уверен что это было нужно):
shared_buffers = 512MB

Рома комментирует...

небольшое уточнение.
1) нужно использовать pgloader 3.2.0
2) В случае если до 2-рой версии вы проапгейдились, то файл схемы не подойдет, так как в 1.8 были дополнительные таблицы. используйте эту схему https://yadi.sk/d/KVM9wMg7kdcvd , либо если вы не доверяете, можете скачать исходники 1.8 и добавить не достающие таблицы в ручном режиме.

morbo комментирует...

Рома, я не понимаю, о каких таких дополнительных таблицах идёт речь? Мои файлы схемы взяты из Zabbix 2.4.5. Вообще, вопрос апгрейда Zabbix до более высоких версий - это отдельный вопрос, никак с миграцией на другую СУБД не связанный.

morbo комментирует...

Моя статья относится к Debian Jessie, о чём недвусмысленно намекают теги. Сейчас посмотрел на версию pgloader - у меня это версия 3.1.0+dfsg-2. То есть подойдёт не только версия 3.2.0.

Unknown комментирует...

Спасибо за статью!!! Успешно мигрировал zabbix 2.2.11

table name read imported errors time
--------------------- --------- --------- --------- --------------
Total import time 3455252 3455252 0 1m18.139s

Unknown комментирует...

а надо то было просто на лету поменять параметр... Ну и в конфиг конечно потом записать
set global innodb_flush_log_at_trx_commit=2;

morbo комментирует...

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

Unknown комментирует...

мне пришлось добавить в zabbix.load строку ALTER SCHEMA 'zabbix' RENAME TO 'public'
без этого выдавало ошибку FATAL error: pgloader failed to find schema "zabbix" in target catalog

версии: zabbix-server-3.4, mysql-5.6, postgres-9.6, pgloader-"3.4.1"

Unknown комментирует...

для тех, кто пришел в 2018 и pgloader выше чем 3.4:
вставьте

ALTER SCHEMA 'zabbix' RENAME TO 'public'

в файл .load

Анонимный комментирует...

>>а надо то было просто на лету поменять параметр... Ну и в конфиг конечно потом >>записать
>>set global innodb_flush_log_at_trx_commit=2;
Не поможет, вернее поможет, но очень слабо. Даже при innodb_doublewrite=0 mysql будет сильнее чем PostgreSQL нагружать СХД. Проверено.

morbo комментирует...

Сергей, когда такие советы даёте, то сразу пояснять нужно, чем это чревато. А то ПРОСТО можно и в русскую рулетку сыграть. Аноним пишет про innodb_doublewrite=0. Это, видимо, в одной и той же книжке написано, которую Григорий Остер написал.

Анонимный комментирует...

>>Сергей, когда такие советы даёте, то сразу пояснять нужно, чем это чревато. А то >>ПРОСТО можно и в русскую рулетку сыграть. Аноним пишет про innodb_doublewrite=0. >>Это, видимо, в одной и той же книжке написано, которую Григорий Остер написал.
Вообще смысл моего комментария был в том, что если надо снизить нагрузку на СХД то манипуляции с опциями innodb_flush_log_at_trx_commit и/или innodb_doublewrite помогут не так сильно как переход на PostgreSQL. А вот риски потерять целостность БД, как вы заметили, - возрастут многократно.