MySQL & PostgreSQL: Часть 5. Масштабирование PostgreSQL

В предыдущей статье мы обсудили вопросы масштабирования MySQL. Рассмотрим эти моменты для сервера баз данных PostgreSQL.

Сергей Яковлев, Консультант, независимый специалист

Яковлев Сергей — независимый разработчик с многолетним опытом прикладного и системного программирования; вносит вклад в развитие open-source на своем персональном сайте www.iakovlev.org. Консультант.



18.01.2012

В PostgreSQL бывает несколько вариантов репликации.

  1. Асинхронный Master/Slave. Master – это сервер, на который идут модифицирующие запросы. Slave – сервер, который дублирует его. Носит асинхронный характер: если падает сетевое соединение между серверами, то после восстановления Slave сервер автоматически возобновляет репликацию.
  2. Синхронный Master/Slave: модификации Master синхронно дублируются на Slave.
  3. Синхронный Multi-Master: имеется несколько серверов, которые одновременно принимают на себя модифицирующие запросы insert, delete, update. До того момента, пока все серверы не подтвердили получение изменений, изменения не коммитятся: все серверы ждут.

Для масштабирования PostgreSQL существует несколько готовых решений. При этом можно получить различные преимущества, такие как:

  • копирование данных между нодами;
  • фоновый бэкап одной из копий без нагрузки на центральный сервер;
  • отдельный сервер для логирования;
  • создание неограниченного пула коннектов.

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

  1. Slony-I – асинхронная (master to multiple slaves) репликация: http://slony.info/.
  2. Pgpool-II – синхронный мульти-мастер репликации: http://pgfoundry.org/projects/pgpool/.
  3. Pgcluster – синхронный мульти-мастер репликации: http://pgfoundry.org/projects/pgcluster/.
  4. PL/Proxy – прокси от компании Skype: http://pgfoundry.org/projects/plproxy/.
  5. PgBouncer – менеджер соединений для PostgreSQL: https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer.

1. Slony-I

Slony – система репликации реального времени, позволяющая организовать синхронизацию нескольких серверов PostgreSQL по сети. Slony использует триггеры PostgreSQL для привязки к событиям INSERT/DELETE/UPDATE и хранимые процедуры для выполнения действий. Наши соотечественники вдоволь порезвились с аббревиатурами названий базовых утилит пакета.

Основные понятия системы:

  • кластер – набор баз данных;
  • нода – база данных, которая может быть распределена;
  • репликационный набор – набор таблиц, которые могут быть реплицированы между нодами кластера;
  • источник (или провайдер) и подписчик – у каждой репликации есть одна нода-источник, остальные ноды могут быть подписчиками;
  • slon – демон, присутствующий на каждой ноде и управляющий ее репликациями;
  • slonik – командная утилита для общего управления нодами.

Система Slony с точки зрения администратора состоит из двух главных компонентов – репликационного демона slon и административной консоли slonik. Slonik – это основная командная утилита для управления, slon следит в фоновом режиме за репликацией.

Сначала создается пользователь (slony). На каждой из нод кластера этот пользователь также должен быть создан, там же должен присутствовать демон slon. От лица этого пользователя с любой из нод (slave server) нужно будет подключаться к головному серверу – master server – с помощью команды:

  psql -d имя_базы_данных -h имя_мастер_хоста.com -U slony

На мастер-сервере нужно создать базу данных:

  createdb -O имя_пользователя -h имя_слэйв_хоста.com имя_базы

Для таблиц, которые будут реплицироваться, обязательно должен присутствовать primary key. После этого на слэйв-сервере запускаем команду:

   pg_dump -s имя_базы | psql -U slony -h имя_слэйв_хоста.com имя_базы

pg_dump -s сделает дамп структуры БД.

После этого на мастер-сервере нужно запустить скрипт, в нем будет запущена утилита slonik, которая создаст репликационный набор с перечисленными в скрипте таблицами. Для каждой реплицируемой таблицы утилита выполнит команду:

  store listen ( origin = 1, provider = 1, receiver = 2 );

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

  subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);

После создания подписки нужно запустить демона slon на мастере и на слэйве:

  slon customers_rep "dbname=имя_базы user=slony"

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

Чего нет в slony: система не занимается выявлением упавших нод.

2. Pgpool-II

Эта система масштабирования представляет собой прокси-прослойку между сервером и клиентами, прозрачна для обоих, может организовывать пул для ограничения соединений. Она реализует следующие возможности.

  1. Создает высокопроизводительную сетевую структуру между нодами, кластерами и пользователями.
  2. Синхронно реплицирует данные на множество серверов без остановки.
  3. Балансирует нагрузку, распределяя ее между нодами кластера.
  4. Распараллеливает запросы за счет разнесения данных по нодам.
  5. Failover – обнаружение отказа и переключение нагрузки.

Начинать нужно с настройки конфига pgpool.conf: в нем прописываются нодовые хосты, порты доступа, можно указать вес хоста, т.е. процент запросов, которые он будет обрабатывать. Для включения хоста в балансировку нагрузки:

  load_balance_mode = true

Для включения параллельных запросов:

  parallel_mode = true

Клиентские запросы будут обслуживаться одной машиной – мастер-хостом на условном порту 12345, на котором мы настраиваем конфиг. Для запуска выполняем на мастер-сервере команду:

   $ pgpool -n &

Создаем на мастере базу данных. Для клонирования базы данных на прописанных хостах нужно в запущенной утилите pgpool выполнить команду:

  $ createdb -p 12345 имя_реплицируемой_базы_данных
  $ pgbench -i -p 9999 имя_реплицируемой_базы_данных

Для включения параллельных запросов нужна системная база с именем pgpool и пользователь с тем же именем. Параметры также прописываются в таблице. Создание pgpool:

   $ createuser -p 5432 pgpool
   $ createdb -p 5432 -O pgpool pgpool

В системной базе pgpool нужно создать таблицу dist_def, в которой будут храниться правила распределения данных между хостами. В этой таблице прописываются ноды, названия столбцов, функции, занимающиеся распределением данных по нодам. В другой системной таблице – replicate_def – будут указаны таблицы, для которых будет производиться репликация.


3. Pgcluster

Для его установки необходимо поставить патч на postgresql. Схема работы сложнее, чем в pgpool. Pgcluster состоит из трех типов серверов:

  • балансер-сервер (front-end);
  • кластерный сервер(данные);
  • репликационный сервер.

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


4. PL/Proxy

PL/Proxy – это прокси-язык, используемый для вызова удаленных процедур и разделения данных между нодами. Проект представляет собой компилируемую си-шную библиотеку libplproxy.so. После её инсталляции необходимо запустить sql-скрипт из этого же пакета:

  >> psql -f plproxy.sql mydatabase

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

CREATE FUNCTION get_user_email(i_username text)
RETURNS SETOF text AS $$
  CONNECT 'dbname=remote_dbname';
  SELECT email FROM users WHERE username = $1;
$$ LANGUAGE plproxy;

и потом вызвать ее:

  SELECT * from get_user_email($1);.

Для того чтобы получать данные с нескольких кластерных нод, нужно написать несколько SQL конфиг-функции, которые будут настраивать параметры коннекта. Тогда аналогичная функция для получения списка емейлов из распределенной базы данных будет иметь вид:

CREATE OR REPLACE FUNCTION get_user_email(i_username text)
RETURNS SETOF text AS $$
  CLUSTER 'usercluster';
  RUN ON hashtext(i_username) ;
  SELECT email FROM users WHERE username = i_username;
$$ LANGUAGE plproxy;

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

CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
RETURNS integer AS $$
     INSERT INTO users (username, email) VALUES ($1,$2);
     SELECT 1;
$$ LANGUAGE SQL;

Для прокси-базы:
CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)
RETURNS integer AS $$
  CLUSTER 'usercluster';
  RUN ON hashtext(i_username);
$$ LANGUAGE plproxy;

5. PgBouncer

Это пул коннектов для постгрес от компании Skype. Существуют три режима управления.

  1. Session Pooling. Клиенту выделяется соединение с сервером; оно приписано ему в течение всей сессии и возвращается в пул только после отсоединения клиента.
  2. Transaction Pooling. Клиент владеет соединением только в течение транзакции.
  3. Statement Pooling. Соединение возвращается назад в пул сразу после завершения запроса.

К достоинствам PgBouncer относится:

  • малое потребление памяти (менее 2 КБ на соединение);
  • отсутствие привязки к одному серверу баз данных;
  • реконфигурация настроек без рестарта.

Базовая утилита запускается так:

  pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>

Простой пример для конфига:

[databases]
template1 = host=127.0.0.1 port=5432 dbname=template1
[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser

Нужно создать файл пользователей users.txt примерного содержания:

  "someuser" "same_password_as_in_server"

Админский доступ из консоли к базе данных pgbouncer:

  $ psql -h 127.0.0.1 -p 6543 pgbouncer

Здесь можно получить различную статистическую информацию с помощью команды SHOW.


Заключение

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

В продолжение цикла мы вернемся к MySQL и подробно осветим вопросы репликации баз данных.

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Open source, Linux
ArticleID=788069
ArticleTitle=MySQL & PostgreSQL: Часть 5. Масштабирование PostgreSQL
publish-date=01182012