Dreamcatcher.ru

Очередная противозабывательная заметка. Как обычно, всё начинается с того, что архитектура системы отсутствует, нагрузка возрастает, пользователи жалуются. Сервер PostgreSQL перестает справляться с нагрузкой, надо срочно подпереть костылём.

Возникает логичное(на взгляд инфраструктуры) желание отправить все SELECT на мастер и реплику, а все остальные запросы только на мастер. Попутно прочитано про мультиплексор PgBouncer, сразу ясно, что такой хорошей вещью хуже не сделаешь.

Итак, делаем такую схему(элементы отказоустойчивости не отображаем):

У нас есть сервер приложений, которые отправляет запросы на pgbouncer. Со стороны приложения это будут запросы к двум разным базам, находящимся на одном адресе и порту.

Далее, pgbouncer отправляет запросы на haproxy, при этом порты баз отличаются.

Haproxy, в свою очередь, имеет две разные точки балансировки, отправляя запросы непосредственно к базам данных.

Вопросы установки haproxy и pgbouncer оставим за скобками, приведу только файлы конфигурации.

Haproxy:

global
        log /dev/log    local0
        log /dev/log    local1 notice
        chroot /var/lib/haproxy
        stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
        stats timeout 30s
        user haproxy
        group haproxy
        daemon

        # Default SSL material locations
        ca-base /etc/ssl/certs
        crt-base /etc/ssl/private

        ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:RSA+AESGCM:RSA+AES:!aNULL:!MD5:!DSS
        ssl-default-bind-options no-sslv3

defaults
        log     global
        mode    http
        option  httplog
        option  dontlognull
        timeout connect 5000
        timeout client  50000
        timeout server  50000
        errorfile 400 /etc/haproxy/errors/400.http
        errorfile 403 /etc/haproxy/errors/403.http
        errorfile 408 /etc/haproxy/errors/408.http
        errorfile 500 /etc/haproxy/errors/500.http
        errorfile 502 /etc/haproxy/errors/502.http
        errorfile 503 /etc/haproxy/errors/503.http
        errorfile 504 /etc/haproxy/errors/504.http

# Connections to port 5432
listen Master
    bind *:5433
    mode tcp
    #mode http
    option pgsql-check user pgbouncer
    default-server inter 3s fall 3
    server master 10.10.10.10:5432 maxconn 500 check port 5432

# Connections to port 5433
listen Replica
    bind *:5432
    mode tcp
    option pgsql-check user pgbouncer
    default-server inter 3s fall 3
    server master 10.10.10.10:5432 maxconn 500 check port 5432
    server replica 10.10.10.20:5432 maxconn 500 check port 5432


listen stats
        mode http
        bind 10.10.10.30:8080
        log         localhost   local1  #debug
        log-tag stats
        option dontlognull
        option dontlog-normal

        stats enable
        stats show-node PGBouncer-Proxy
        stats show-desc "service for balance PostgreSQL session"
        stats uri /
        stats hide-version
        stats refresh 5s
        stats show-legends

Для работы pgsql-check нам необходимо иметь пользователя в базе, который будет проходить первый этап создания подключения (проверка наличия подходящей записи в pg_hba.conf). Создателями haproxy считается необходимым и достаточным этого для определения доступности СУБД и готовности ее к приему подключений. Так как нам потребуется пользователь для pgbouncer то выполняем на мастере:

#vi /etc/postgresql/10/our_cluster/pg_hba.conf

# PGBouncer
host pgbouncer pgbouncer 10.10.10.30/32 md5

#psql -p 5432 -U postgres
create user pgbouncer with password 'pgbouncer';
select * from pg_shadow;
select pg_reload_conf();

В качестве базы для пользователя pgbouncer указываем несуществующую базу, это предотвратит его подключения в СУБД. Нам надо скопировать строчку с md5 пользователя pgbouncer, она нам пригодится в дальнейшем.

Конфигурация pgbouncer выглядит следующим образом:

[databases]
master = host=10.10.10.30 port=5432 dbname=dvdrental auth_user=postgres
replica = host=10.10.10.30 port=5433 dbname=dvdrental auth_user=postgres

[pgbouncer]

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

listen_addr = 10.10.10.40
listen_port = 6432

unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

admin_users = pgbouncer
stats_users = pgbouncer

pool_mode = transaction

server_reset_query = DISCARD ALL

# Тестировать доступность сервера раз в 3600 секунд
server_check_query = select 1
server_check_delay = 3600 

# Общее число клиентов, которые могут подключиться (если все коннекты от одного пользователя, ставим больше max_user_connections).
max_client_conn = 500 

# Сколько дополнительных процессов может создать подключившийся пользователь
default_pool_size = 20 

# Если выбрали пул, можем подключить еще 5 сверху
reserve_pool_size = 5 

# Если клиент ждет больше 5 секунд, используем резерв
reserve_pool_timeout = 5 

# Сколько вообще возможно коннектов к базе (должно коррелировать с настройками PostgreSQL)
max_db_connections = 200
# Лимит подключений от одного юзера ко всем базам (если ходят от одного пользователя ко всем базам, то ориентируемся на max_db_connections и число баз)
max_user_connections = 400

# Переиспользуем коннекты по методу FIFO
server_round_robin = 1 

В нашем примере dvdrental — имя нашей базы данных. В файл /etc/pgbouncer/userlist.txt прописываем имя и хэш пользователя pgbouncer

"pgbouncer" "7d6a64110c600b34f0caa8385dd3847d"

Вот и все, проверяем:

#/usr/bin/psql -h 10.10.10.40 -p6432 -U ouruser master -c "SELECT 1;"
#/usr/bin/psql -h 10.10.10.40 -p6432 -U ouruser replica -c "SELECT 1;"

Насколько я понял, настройки pgbouncer подбираются под каждую базу.