Очень большой Postgres

Очень большой Postgres

431
ПОДЕЛИТЬСЯ

Как традиционно происходит оптимизация БД? Наверняка, в первую очередь глядят на то, как верно выбрать рациональные опции для работы и какие индексы можно сделать. Что делать далее? Горизонтальное масштабирование и шардинг данных. И вот уже все совершенно настроено, но наступает момент, когда всех этих телодвижения оказывается недостаточно. Так уж случилось , что крайнее время приходилось заниматься оптимизацией и масштабированием разных систем. Раз обойтись малой кровью не вышло, перебегают к наращиванию мощностей сервера, выносу файлов журнальчика на отдельный диск, балансировке перегрузки, разбиению таблиц на партиции и к всякого рода рефакторингу и перепроектированию модели. Одной из задач было масштабирование PostgreSQL.

Желаю поделиться опытом развертывания горизонтально масштабируемого кластера на СУБД Postgres-XL.

Так что для вас будут доступны практически все фичи версии 9.2. Postgres-XL — красивый инструмент, который дозволяет объединить несколько кластеров PostgreSQL таковым образом, чтобы они работали как один инстанс БД. Postgres-XL дает 2 режима распределения таблиц по кластеру: репликация и шардинг. Для клиента, который подключается в базе, нет никакой различия, работает он с единственным инстансом PostgreSQL либо с кластером Postgres-XL. Текущая реализация базирована на PostgreSQL-9.2. При репликации все узлы содержат схожую копию таблицы, а при шардинге данные умеренно распределяются посреди членов кластера.

Терминология
Postgres-XL состоит из 3-х типов компонентов: глобальный монитор транзакций (GTM), координатор (coordinator) и узел данных (datanode).

Для объединений множественных запросов и ответов от координаторов и узлов данных запущенных на одном сервере имеет смысл настроить GTM-Proxy. Так как является единой точкой отказа, то рекомендуется подпирать с помощью GTM Standby. GTM — отвечает за обеспечение требований ACID. Выделение отдельного сервера для GTM является неплохой идеей. Несет ответственность за выдачу идентификаторов. Таковым образом понижается перегрузка на GTM так как миниатюризируется общее количество взаимодействий с ним.

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

Узел данных — место где хранятся пользовательские данные и индексы. Для обеспечения высочайшей доступности можно подпереть каждый из узлов stanby сервером. Связь с узлами данных осуществляется лишь через координаторы.

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

Схема тестового кластера

Каждый узел это виртуальная машинка со умеренным аппаратным обеспечением: MemTotal: 501284 kB, cpu MHz: 2604.

Установка
Собирал на Ubuntu server 14.10. Здесь все стандартно: качаем исходники с офсайта, доставляем зависимости, компилируем.

$ sudo apt-get install flex bison docbook-dsssl jade iso8879 docbook libreadline-dev zlib1g-dev
$ ./configure —prefix=/home/${USER}/Develop/utils/postgres-xl —disable-rpath
$ make world
Опосля того как пакет собран заливаем его на узлы кластера и перебегаем к настройке компонентов.

Настройка GTM
Для обеспечения отказоустойчивости разглядим пример с настройкой 2-ух GTM серверов. На обоих серверах создаем рабочий каталог для GTM и инициализируем его.

$ mkdir ~/gtm
$ initgtm -Z gtm -D ~/gtm/
Опосля чего же перебегаем к настройке конфигов:

gtm1
gtm.conf…
nodename = ‘gtm_master’
listen_addressesПолностьюport = 6666
startup = ACT
log_file = ‘gtm.log’

gtm2
gtm.conf…
nodename = ‘gtm_slave’
listen_addressesПолностьюport = 6666
startup = STANDBY
active_host = ‘gtm1’
active_port = 6666
log_file = ‘gtm.log’

Сохраняем, стартуем:

$ gtm_ctl start -Z gtm -D ~/gtm/
В логах смотрим записи:
LOG: Started to run as GTM-Active.
LOG: Started to run as GTM-Standby.

Настройка GTM-Proxy
$ mkdir gtm_proxy
$ initgtm -Z gtm_proxy -D ~/gtm_proxy/
$ nano gtm_proxy/gtm_proxy.conf
gtm_proxy.conf…
nodename = ‘gtmproxy1’ # имя обязано быть неповторимо
listen_addressesПолностьюport = 6666
gtm_host = ‘gtm1’ #указываем ip либо имя хоста на котором развернут GTM мастер
gtm_port = 6666
log_file = ‘gtm_proxy.log’

Опосля правки конфига можно запускать:

$ gtm_ctl start -Z gtm_proxy -D ~/gtm_proxy/

Настройка координаторов
$ mkdir coordinator
$ initdb -D ~/coordinator/ -E UTF8 —locale=C -U postgres -W —nodename coordinator1
$ nano ~/coordinator/postgresql.conf
coordinator/postgresql.conf…
listen_addressesПолностьюport = 15432
pooler_port = 16667
gtm_host = ‘127.0.0.1’
pgxc_node_name = ‘coordinator1’

Настройка узла данных
$ mkdir ~/datanode
$ initdb -D ~/datanode/ -E UTF8 —locale=C -U postgres -W —nodename datanode1
$ nano ~/datanode/postgresql.conf
datanode/postgresql.conf…
listen_addressesПолностьюport = 25432
pooler_port = 26667
gtm_host = ‘127.0.0.1’
pgxc_node_name = ‘datanode1′

Для других узлов настройка различается лишь указанием другого имени.
Сейчас правим pg_hba.conf:

echo "host all all 192.168.1.0/24 trust" >> ~/datanode/pg_hba.conf
echo "host all all 192.168.1.0/24 trust" >> ~/coordinator/pg_hba.conf
Пуск и донастройка
Все готово и можно запускать.

$ pg_ctl start -Z datanode -D ~/datanode/ -l ~/datanode/datanode.log
$ pg_ctl start -Z coordinator -D ~/coordinator/ -l ~/coordinator/coordinator.log
Заходим на координатор:

psql -p15432
Исполняем запрос:

select * from pgxc_node;
Запрос указывает как текущей сервер лицезреет наш кластер.
Пример вывода:

node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
————-+————+————+————+—————-+——————+————
coordinator1 | C | 5432 | localhost | f | f | 1938253334
Эти опции неверны и их можно смело удалять.

delete from pgxc_node;
Создаем новое отображение нашего кластера:

create node coordinator1 with (type=coordinator, host=’192.168.1.151′, port=15432);
create node coordinator2 with (type=coordinator, host=’192.168.1.152′, port=15432);
create node coordinator3 with (type=coordinator, host=’192.168.1.161′, port=15432);
create node datanode1 with (type=datanode, host=’192.168.1.151′, primary=true, port=25432);
create node datanode2 with (type=datanode, host=’192.168.1.152′, primary=false, port=25432);
create node datanode3 with (type=datanode, host=’192.168.1.161′, primary=false, port=25432);
SELECT pgxc_pool_reload();
select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
—————+————+————+—————+—————-+——————+————-
datanode1 | D | 25432 | 192.168.1.151 | t | f | 888802358
coordinator1 | C | 15432 | 192.168.1.151 | f | f | 1938253334
coordinator2 | C | 15432 | 192.168.1.152 | f | f | -2089598990
coordinator3 | C | 15432 | 192.168.1.161 | f | f | -1483147149
datanode2 | D | 25432 | 192.168.1.152 | f | f | -905831925
datanode3 | D | 25432 | 192.168.1.161 | f | f | -1894792127
На других узлах необходимо выполнить тоже самое.
Узел данных не дозволит на сто процентов очистить информацию, но ее можно перезаписать:

psql -p 25432 -c "alter node datanode1 WITH ( TYPE=datanode, HOST =’192.168.1.151’, PORT=25432, PRIMARY=true);"
Тестирование кластера
Сейчас все настроено и работает. Сделаем несколько тестовых таблиц.

CREATE TABLE test1
( id bigint NOT NULL, profile bigint NOT NULL,
status integer NOT NULL, switch_date timestamp without time zone NOT NULL,
CONSTRAINT test1_id_pkey PRIMARY KEY (id)
) to node (datanode1, datanode2);

CREATE TABLE test2
( id bigint NOT NULL, profile bigint NOT NULL,
status integer NOT NULL, switch_date timestamp without time zone NOT NULL,
CONSTRAINT test2_id_pkey PRIMARY KEY (id)
) distribute by REPLICATION;

CREATE TABLE test3
( id bigint NOT NULL, profile bigint NOT NULL,
status integer NOT NULL, switch_date timestamp without time zone NOT NULL,
CONSTRAINT test3_id_pkey PRIMARY KEY (id)
) distribute by HASH(id);

CREATE TABLE test4
( id bigint NOT NULL, profile bigint NOT NULL,
status integer NOT NULL, switch_date timestamp without time zone NOT NULL
) distribute by MODULO(status);
Было сотворено 4 таблицы с схожей структурой, но разной логикой распределения по кластеру.

Данные таблицы test1 будут храниться лишь на 2х узлах данных — datanode1 и datanode2, а распределятся они будут по методу roundrobin. Для определения на каком сервере будут храниться данные таблицы test3 употребляется хеш-функция по полю id, а для определения логики распределения test4 берется модуль по полю status. Другие таблицы используют все узлы. Попробуем сейчас заполнить их: Таблица test2 работает в режиме репликации.

insert into test1 (id, profile, status, switch_date) select a, round(random()*10000), round(random()*4), now() — ‘1 year’::interval * round(random() * 40) from generate_series(1,10) a;
insert into test2 (id , profile,status, switch_date) select a, round(random()*10000), round(random()*4), now() — ‘1 year’::interval * round(random() * 40) from generate_series(1,10) a;
insert into test3 (id , profile,status, switch_date) select a, round(random()*10000), round(random()*4), now() — ‘1 year’::interval * round(random() * 40) from generate_series(1,10) a;
insert into test4 (id , profile,status, switch_date) select a, round(random()*10000), round(random()*4), now() — ‘1 year’::interval * round(random() * 40) from generate_series(1,10) a;
Запросим сейчас эти данные и поглядим, как работает планировщик

explain analyze select count(*) from test1;
QUERY PLAN
————————————————————————————————————————————-
Aggregate (cost=27.50..27.51 rows=1 width=0) (actual time=0.649..0.649 rows=1 loops=1)
-> Remote Subquery Scan on all (datanode1,datanode2) (cost=0.00..24.00 rows=1400 width=0) (actual time=0.248..0.635 rows=2 loops=1)
Total runtime: 3.177 ms

explain analyze select count(*) from test2;
QUERY PLAN
————————————————————————————————————————————-
Remote Subquery Scan on all (datanode2) (cost=27.50..27.51 rows=1 width=0) (actual time=0.711..0.711 rows=1 loops=1)
Total runtime: 2.833 ms

explain analyze select count(*) from test3;
QUERY PLAN
————————————————————————————————————————————-
Aggregate (cost=27.50..27.51 rows=1 width=0) (actual time=1.453..1.453 rows=1 loops=1)
-> Remote Subquery Scan on all (datanode1,datanode2,datanode3) (cost=0.00..24.00 rows=1400 width=0) (actual time=0.465..1.430 rows=3 loops=1)
Total runtime: 3.014 ms

Так как table2 реплицируется на все узлы, то просканирован будет лишь 1 узел. Разумно было бы, чтобы он запрашивал данные с того же узла на котором и координатор. Планировщик докладывает нам о том сколько узлов будет участвовать в запросе. Кстати неясно по какой логике он выбирается.

Подключившись к узлу данных (на порт 25432) можно узреть как были распределены данные.

Сейчас давайте попробуем заполнить таблицы огромным объемом данных и сопоставить производительность запросов со standalone PostgreSQL.

insert into test3 (id , profile,status, switch_date) select a, round(random()*10000), round(random()*4), now() — ‘1 year’::interval * round(random() * 40) from generate_series(1,1000000) a;
Запрос в кластере Postgres-XL:

explain analyze select profile, count(status) from test3
where status<>2
and switch_date between ‘1970-01-01’ and ‘2015-01-01’ group by profile;
QUERY PLAN
—————————————————————————————————————————————
HashAggregate (cost=34.53..34.54 rows=1 width=12) (actual time=266.319..268.246 rows=10001 loops=1)
-> Remote Subquery Scan on all (datanode1,datanode2,datanode3) (cost=0.00..34.50 rows=7 width=12) (actual time=172.894..217.644 rows=30003 loops=1)
Total runtime: 276.690 ms
Этот же запрос на сервере с PostgreSQL:

explain analyze select profile, count(status) from test
where status<>2
and switch_date between ‘1970-01-01’ and ‘2015-01-01’ group by profile;
QUERY PLAN
—————————————————————————————————————————————
HashAggregate (cost=28556.44..28630.53 rows=7409 width=12) (actual time=598.448..600.495 rows=10001 loops=1)
-> Seq Scan on test (cost=0.00..24853.00 rows=740688 width=12) (actual time=0.418..329.145 rows=740579 loops=1)
Filter: ((status <> 2) AND (switch_date >= ‘1970-01-01 00:00:00’::timestamp without time zone) AND (switch_date <= ‘2015-01-01 00:00:00’::timestamp without time zone))
Rows Removed by Filter: 259421
Total runtime: 601.572 ms
Смотрим двукратное повышение скорости. Не так уж и плохо, раз у вас в распоряжении есть достаточное количество машин, то такое масштабирование смотрится достаточно перспективно.

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

create table test3_1 (id bigint NOT NULL, name text, CONSTRAINT test3_1_id_pkey PRIMARY KEY (id)) distribute by HASH(id);
insert into test3_1 (id , name) select round(random()*10000), md5(random()::text) from generate_series(1,10000) a;

explain analyze select test3.*,test3_1.name from test3 join test3_1 on test3.profile=test3_1.id;
QUERY PLAN
—————————————————————————————————————————————
Remote Subquery Scan on all (datanode1,datanode2,datanode3) (cost=35.88..79.12 rows=1400 width=61) (actual time=26.500..17491.685 rows=999948 loops=1)
Total runtime: 17830.984 ms
Запрос на тех же размерах данных, но на standalone сервере:
QUERY PLAN
—————————————————————————————————————————————
Hash Join (cost=319.00..42670.00 rows=999800 width=69) (actual time=99.697..19806.038 rows=999940 loops=1)
Hash Cond: (test.profile = test_1.id)
-> Seq Scan on test (cost=0.00..17353.00 rows=1000000 width=28) (actual time=0.031..6417.221 rows=1000000 loops=1)
-> Hash (cost=194.00..194.00 rows=10000 width=41) (actual time=99.631..99.631 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 713kB
-> Seq Scan on test_1 (cost=0.00..194.00 rows=10000 width=41) (actual time=0.011..46.190 rows=10000 loops=1)
Total runtime: 25834.613 ms
Здесь выигрыш всего только в 1,5 раза.

habrahabr.ru Благодарю за внимание. P.S. Комменты и дополнения приветствуются! Надеюсь, данный пост поможет кому-нибудь.