Real time web analytics, Heat map tracking

С версии PostgreSQL 9.2 введена потоковая репликация для организации master-slave систем для реализации failover и loadbalance. Она работает на физическом уровне и настраивается из коробки. Далее разработчиками было заявлено о реализации, обеспечивающую настройку multi-master репликации, в версиях PostgreSQL >= 9.4. Однако как это становится видно, данной фичи можно не ожидать и в версии 9.6. Попробуем разобраться как это настраивается, работает, и почему не включают в основную ветку PostgreSQL.

Термины

WAL (Write Ahead Logs) – журнал упредупреждающей записи. В этот журнал пишется всё что должно произойти в БД, транзакции DDL и т.д. По логике его работы происходит следующее, если транзакция была записана в этот журнал, то она проходит в СУБД и записывается на диск или в зависимости от настроек хранится в оперативной памяти сервере, и ждёт момента для записи на диск (Зависит от настроек сервера). Благодаря такому журналу, всегда имеется возможность восстановить транзакции при сбое сервера. На основе этого журнала и построена встроенная потоковая репликация.

Stream Replication (SR) – Потоковая репликация, для организации failover и loadbalance систем. Очень классная штука, а главное работает из коробки начиная с версий >= 9.2. Также её называют физической репликацией, так как она основана на WAL журналах. Всё, что пишится в WAL отправляются на slave сервера. Реплицируется весь кластер, что можно отнести как к недостаткам, так и к достоинствам данной системы.

Physical Log Streaming Replication (PLSR) – физическая потоковая репликация (она же Stream Replication), основана на WAL логах, которые отправляются slave серверам без разбора, абсолютно всё что происходит с кластером (схемы, данные, full page writes, vacuum, hit bit settings). Уже реализована и готова к использованию в продакшене.

Logical Log Streaming Replication – логическая потоковая репликация. Обещали что она появится в версии 9.4, но уже 9.6 на момент написании статьи на подходе, а данной репликации в ядре так и нет. Главное чем она отличается от PLSR (SR), это в разборе WAL логов, отсеивая низкоуровневые изменения, и оставляя только изменения схем и данных кластера. На основе LLSR и построена Bi-Directional Replication (BDR).

Bi-Directional Replication (BDR) – Данная репликация позволяет создавать территориально распределённые мульти-мастер системы, основываясь на LLSR. Как и LLSR так и BDR разработаны 2ndQuadrant. Для LLSR нужно накладывать патчи на PostgreSQL или скачивать из репозитарев уже пропатченный. BDR ставится как расширение с preload библиотекой в файле конфигурации PostgreSQL. То есть в теории, если LLSR будет включено в основную ветку PostgreSQL, то настройка логической репликации сведётся к установки двух расширений и включения preload библиотеки.

Приступим к разбору технологии BDR/LLSR.

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

Установка, настройка и работа BDR.

Всё устанавливалось на на домашнем сервере:

  • 2 x Intel(R) Xeon(R) CPU L5640  @ 2.27GHz (24)
  • 36Gb оперативки
  • Винты простые
  • VirtualBox 5.1.6.

На виртуальных машинах устанавливалась UbuntuServer 16.04.

Виртуальные машины:

  • 4 CPU
  • 4 Gb опертbвки

 

Хочется отметить, что в отличии от установки и первоначальной настройки, в документации очень скудно описано всё остальное, например, разрешение конфликтов.

Всего была организована сеть из трёх серверов 172.x.x.10, 172.x.x.11, 172.x.x.12 (Соответственно BDR 0, BDR 1, BDR 2).

В принципе описание компиляции, установки и настройки довольно хорошо написана на официальном сайте (http://bdr-project.org/docs/1.0/index.html), но есть один момент… У меня не получилось загрузить схему существующей базы уже на созданную мультимастер систему… Мой проект включает в себя порядка 70 таблиц и 4 расширений в публичной схеме, в остальных схемах реализовано партиционирование (кстати, к нему вернёмся позже как к одной проблеме в BDR). Так вот, при уже созданной BDR системе, установка EXTENSION’s у меня не получилась, на CREATE EXTENSION зависает psql, в логах ничего не вижу, нужно разбираться. Советую создавать базу, загружать схемы, а только потом начинать всё реплицировать.

Настройка.

Так как в документации описывается конфигурирование двух кластеров на одном хосте, а у меня на разных серверах, то в postgresql.conf нужно разрешить коннекты отовсюду (listen_addresses = ‘*’), ну или прописываете разрешённые хосты. И в pg_hba.conf прописываем trust доступ на репликацию.

Простой скриптик для конфигурирования узлов:

#!/bin/sh
HOST=172.x.x.10
HOME=/usr/local/pgsql-bdr/
DATA=$HOME/data/

NODE='BDR 0'
DSN='dbname=test host='$HOST;

pg_ctl -l $DATA/pg_log/postgresql.log -D $DATA -o "-p 5432" -w stop -m fast

rm -rf $DATA
initdb -D $DATA -A trust -U postgres
mkdir $DATA/pg_log
cp -rf $HOME/postgresql.conf $DATA
cp -rf $HOME/pg_hba.conf $DATA
pg_ctl -l $DATA/pg_log/postgresql.log -D $DATA -o "-p 5432" -w start

createdb -U postgres test
psql -U postgres test -c "CREATE EXTENSION btree_gist; CREATE EXTENSION bdr;"
psql -U postgres test -c "SELECT bdr.bdr_group_create(local_node_name := '$NODE', node_external_dsn := '$DSN');"
psql -U postgres test -c "SELECT bdr.bdr_node_join_wait_for_ready();"

На остальных серверах можно выполнить команды:

bdr_init_copy -D /usr/local/pgsql-bdr/data -n BDR\ 1 -d test -h 172.x.x.10 --local-dbname=test  --local-host=172.x.x.11
bdr_init_copy -D /usr/local/pgsql-bdr/data -n BDR\ 2 -d test -h 172.x.x.10 --local-dbname=test --local-host=172.x.x.12

Или следовать документации и использовать SQL.

Тестовая база данных:

CREATE TABLE parent (
    id SERIAL PRIMARY KEY,
    name varchar
);
CREATE TABLE child (
    id SERIAL PRIMARY KEY,
    parent_id integer not null,
    md5 text
);

ALTER TABLE child
    ADD CONSTRAINT gkey_parent
        FOREIGN KEY (parent_id)
            REFERENCES parent(id)
                ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO parent VALUES (1, 'parent 1');
INSERT INTO child SELECT generate_series(1,100) AS id, 1 AS parent_id, md5(random()::text) AS descr;

В данном случае у нас создастся связь NO ACTION (Удалить данные из parent нельзя пока есть зависимые данные). Проверим как поведёт себя эта связка, на одном из узлов запустим вставку большого количества данных в child таблицу, а на другом узле дропнем всё сначала из child, затем из parent таблиц, и посмотрим как BDR с этим справится:

DELETE FROM child;
DELETE FROM parent WHERE id = 1;
09:29:13  [DELETE - 1 rows, 0.116 secs]  Command processed
 09:29:13  [DELETE - 1 rows, 0.069 secs]  Command processed
... 2 statement(s) executed, 2 rows affected, exec/fetch time: 0.185/0.000 sec  [2 successful, 0 errors]

При этом на другом узле данные продолжают заливаться. По завершению получили:

09:33:10  [INSERT - 0 rows, 243.272 secs]  [Code: 0, SQL State: 23503]  ERROR: insert or update on table "child" violates foreign key constraint "gkey_parent"
  Подробности: Key (parent_id)=(1) is not present in table "parent".
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 243.272/0.000 sec  [0 successful, 1 errors]

И данные не попали в базу. Отлично. Но, на реальной базе данных, с большим количеством данных, и не в одной транзакции, при примерно таком же финте ушами, я получил в базе данные, которые ссылались на несуществующие данные, причём в логах BDR ловил этот конфликт, но почему то помечал его как skipped и продолжал заливать данные. Видимо, что бы такого не происходило, нужно всё оборачивать глобальными транзакциями/блокировками.

Иными словами, допустим у нас есть приложение, которое заносит данные в несколько потоков, и для каждого потока у нас открывается транзакция, так вот, те транзакции которые успели открыться/закрыться во время удаления данных попадут в базу. Я попробую это ещё раз проверить.

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

  1. Отключим от сети третий узел, изменим данные в первом и втором узле, потом в третьем узле, и подключим её к сети. Таким образом, по времени, в третьем узле произошла последняя транзакция. Итог, данные на всех узлах выровнялись по последней транзакции, которая была выполнена на третьем узле, без каких либо конфликтов в логах.
  2. Отключим от сети третий узел, изменим данные на третьем узле, изменим данные на первом или втором узле, включим в сеть третий узел. По логике вещей, данные третьего узла должны выровняться по данным первого и второго узла. Так и произошло, но в логах появился конфликт:
    LOG:  CONFLICT: remote UPDATE on relation public.parent originating at node 6337151361576223411:2:16385 at ts 2016-10-03 11:35:14.727181+03; row was previously updated at node 6337150840020309721:2. Resolution: last_update_wins_keep_local; PKEY: id[int4]:1 name[varchar]:parent updated from node 1
  3. Посмотрим как будет происходить выравнивание данных при нарушении Foreign Keys.

Посмотрим как будет происходить выравнивание данных при нарушении Foreign Keys.

На первом узле делаем:

DELETE FROM parent;
INSERT INTO parent VALUES (1, 'parent 1');
INSERT INTO child SELECT generate_series(1,100) AS id, 1 AS parent_id, md5(random()::text) AS descr;

Отключаем третий узел. На первом узле делаем:

DELETE FROM parent;
INSERT INTO parent VALUES (2, 'parent 2');
INSERT INTO child SELECT generate_series(101,200) AS id, 2 AS parent_id, md5(random()::text) AS descr;

А на третьем узле:

INSERT INTO child SELECT generate_series(101,200) AS id, 1 AS parent_id, md5(random()::text) AS descr;

Мы эмулируем вариант, когда один узел отвалился от сети, но работа с ним продолжалась локально, и к данным которые пропали на других узлах, добавились ещё данные на узле который не в сети. Как вы видите, мы продолжаем последовательность SERIAL PRIMARY KEY на child таблице, это важно.

Подключаем третий узел к сети.

В итоге на третьем узле, по всем новым записям с других узлов получаем конфликты.

LOG:  CONFLICT: remote UPDATE on relation public.child originating at node 6337176769772505916:1:16385 at ts 2016-10-03 13:10:39.911487+03; row was previously updated at node 0:0. Resolution: last_update_wins_keep_local; PKEY: id[int4]:101 parent_id[int4]:1 md5[text]:8c9c5951e45d6154657a3d2576ecea92
LOG:  CONFLICT: remote UPDATE on relation public.child originating at node 6337176769772505916:1:16385 at ts 2016-10-03 13:10:39.911487+03; row was previously updated at node 0:0. Resolution: last_update_wins_keep_local; PKEY: id[int4]:102 parent_id[int4]:1 md5[text]:ba53e92b2fe9c90b1d4fafc89793a9da

Это при условии, что у нас primary key пересекается, если нет, то данные с первого и второго узла попадут в таблицу, но и будут локально добавленные данные, которые будут ссылаться по primary key в никуда. В нашем случае, BDR не пропустил транзакции из вне, сохранив локальные, но в таблице parent изменения прошли. А затем на всех серверах выровнял данные по последней транзакции(третий узел).

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

Подведём итоги:

  1. BDR довольно надёжна, и при завершении написания статьи вышел PostgreSQL 9.6, где как я понял уже есть встроенная поддержка BDR (Нужно посмотреть)
  2. При наличии хорошей стабильной сети между узлами, возникновение ситуаций которые описаны выше минимальны
  3. BDR показала высокую производительность при больших базах (Тестировал на 300Gb геоданных)
  4. Не очень хорошая документация, например по поводу перехвата конфликтов и принятия решения отличающегося от решения по умолчанию
  5. При пропадании узла из сети, и продолжения локальной работы с ним, появляется конфликт данных, который по умолчанию решается по времени последней транзакции
  6. Какие то странности в создании расширений