Тестирование горизонтального масштабирования SELECT запросов на реплику
Цель данного поста протестировать горизонтальное масштабирование SELECT запросов на реплику.
Схема горизонтального масштабирования примерно такая.
PostgreSQL кластер
Характеристики PostgreSQL кластера
Возьмем виртуальные машины для PostgreSQL по 2 ГБ ОЗУ, чтобы бд не ввлезала в память и 3 ноды Etcd по 1ГБ.
Установка
Устанавливаем PostgreSQL кластер из репозитория https://github.com/vitabaks/postgresql_cluster
git clone https://github.com/vitabaks/postgresql_clusterИзменяем адреса серверов в inventory на свои.
Правим параметры в var/main.yaml
Выставляем синхронный режим
synchronous_mode: trueАктивируем Haproxy, который может отпределять кто Leader, а кто c помощью health check
with_haproxy_load_balancing: trueВыключаем pgbouncer, так как оне будет мешать экперименту.
install_pgbouncer: falseДобавляем создание пользователя test с паролем password
postgresql_users:
- {name: "test", password: "password"}
- {name: "pgbenchwrite", password: "password"}
- {name: "pgbenchread", password: "password"}Добавляем создание бд test с owner test
postgresql_databases:
- {db: "test", encoding: "UTF8", lc_collate: "ru_RU.UTF-8", lc_ctype: "ru_RU.UTF-8", owner: "test"}
- {db: "pgbenchread", encoding: "UTF8", lc_collate: "ru_RU.UTF-8", lc_ctype: "ru_RU.UTF-8", owner: "pgbenchread"}Увеличиваем max_connections
postgresql_parameters:
- {option: "max_connections", value: "150"}Добавляем бд и юзера test в pg_hba
postgresql_pg_hba:
...
- {type: "host", database: "test", user: "test", address: "0.0.0.0/0", method: "md5"}
- {type: "host", database: "pgbenchwrite", user: "pgbenchwrite", address: "0.0.0.0/0", method: "md5"}
- {type: "host", database: "pgbenchread", user: "pgbenchread", address: "0.0.0.0/0", method: "md5"}Тюнинг параметров можно выполнить здесь: http://pgconfigurator.cybertec.at/
Проверка кластера
После установки у вас должно быть примерно такая картина
patronictl -c /etc/patroni/patroni.yml list
Тестирование с использованием pgbench
Так как pgbench-у нельзя указать ip для реплики, то запустим 2 экземпляра pgbench: первый будет создавать update, второй будетсоздавать только select-only нагрузку.
Заполняем тестовую базу
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchwrite -i -s 150 pgbenchwrite
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchread -i -s 150 pgbenchreadЗапускаем 2 консолях одновременно pgbench write-only и select-only, где все коннекты идут к master
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchwrite -c 50 -j 2 -P 60 -T 600 -N pgbenchwrite
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchread -c 50 -j 2 -P 60 -T 600 -S pgbenchreadВывод pgbench write-only:
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchwrite -c 50 -j 2 -P 60 -T 600 -N pgbenchwrite
Password:
starting vacuum...end.
progress: 60.0 s, 113.8 tps, lat 436.492 ms stddev 228.613
progress: 120.0 s, 112.1 tps, lat 445.698 ms stddev 181.140
progress: 180.0 s, 119.9 tps, lat 412.778 ms stddev 400.669
progress: 240.0 s, 110.7 tps, lat 452.843 ms stddev 364.284
progress: 300.0 s, 38.2 tps, lat 1284.131 ms stddev 868.801
progress: 360.0 s, 52.2 tps, lat 983.476 ms stddev 859.265
progress: 420.0 s, 62.9 tps, lat 791.075 ms stddev 704.830
progress: 480.0 s, 70.6 tps, lat 698.554 ms stddev 725.389
progress: 540.0 s, 68.9 tps, lat 739.978 ms stddev 787.998
progress: 600.0 s, 75.3 tps, lat 662.032 ms stddev 721.487
transaction type:
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 49527
latency average = 606.825 ms
latency stddev = 608.772 ms
tps = 82.005351 (including connections establishing)
tps = 82.006115 (excluding connections establishing) Вывод pgbench select-only:
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchread -c 50 -j 2 -P 60 -T 600 -S pgbenchread
Password:
starting vacuum...end.
progress: 60.0 s, 88.6 tps, lat 559.665 ms stddev 169.444
progress: 120.0 s, 99.5 tps, lat 503.239 ms stddev 191.487
progress: 180.0 s, 111.4 tps, lat 448.638 ms stddev 823.392
progress: 240.0 s, 115.4 tps, lat 433.728 ms stddev 232.107
progress: 300.0 s, 75.2 tps, lat 664.727 ms stddev 442.582
progress: 360.0 s, 115.1 tps, lat 433.675 ms stddev 392.391
progress: 420.0 s, 123.1 tps, lat 407.399 ms stddev 461.501
progress: 480.0 s, 135.7 tps, lat 366.747 ms stddev 514.208
progress: 540.0 s, 119.9 tps, lat 416.024 ms stddev 529.415
progress: 600.0 s, 112.5 tps, lat 446.807 ms stddev 607.408
transaction type:
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 65823
latency average = 455.912 ms
latency stddev = 490.338 ms
tps = 109.546152 (including connections establishing)
tps = 109.547312 (excluding connections establishing) Запускаем 2 консолях одновременно pgbench write-only и select-only, где коннект write-only идет к master, а коннект select-only идет на реплику.
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchwrite -c 50 -j 2 -P 60 -T 600 -N pgbenchwrite
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5002 -U pgbenchread -c 50 -j 2 -P 60 -T 600 -S pgbenchreadВывод pgbench write-only:
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5000 -U pgbenchwrite -c 50 -j 2 -P 60 -T 600 -N pgbenchwrite
Password:
starting vacuum...end.
progress: 60.0 s, 171.5 tps, lat 290.534 ms stddev 198.945
progress: 120.0 s, 95.2 tps, lat 524.225 ms stddev 836.995
progress: 180.0 s, 41.6 tps, lat 1172.546 ms stddev 1184.899
progress: 240.0 s, 106.4 tps, lat 479.830 ms stddev 613.741
progress: 300.0 s, 107.4 tps, lat 456.684 ms stddev 554.722
progress: 360.0 s, 126.7 tps, lat 403.261 ms stddev 425.490
progress: 420.0 s, 171.8 tps, lat 290.589 ms stddev 306.722
progress: 480.0 s, 119.9 tps, lat 413.012 ms stddev 433.962
progress: 540.0 s, 165.5 tps, lat 305.434 ms stddev 309.429
progress: 600.0 s, 134.4 tps, lat 363.495 ms stddev 312.672
transaction type:
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 74483
latency average = 402.763 ms
latency stddev = 515.695 ms
tps = 124.006808 (including connections establishing)
tps = 124.008050 (excluding connections establishing) Вывод pgbench select-only:
/usr/pgsql-12/bin/pgbench -h 172.26.10.73 -p 5002 -U pgbenchread -c 50 -j 2 -P 60 -T 600 -S pgbenchread
Password:
starting vacuum...ERROR: cannot execute VACUUM during recovery
(ignoring this error and continuing anyway)
ERROR: cannot execute VACUUM during recovery
(ignoring this error and continuing anyway)
ERROR: cannot execute TRUNCATE TABLE in a read-only transaction
(ignoring this error and continuing anyway)
end.
progress: 60.0 s, 170.8 tps, lat 291.310 ms stddev 115.079
progress: 120.0 s, 155.4 tps, lat 320.284 ms stddev 232.217
progress: 180.0 s, 153.7 tps, lat 326.370 ms stddev 284.169
progress: 240.0 s, 211.0 tps, lat 237.428 ms stddev 210.316
progress: 300.0 s, 269.6 tps, lat 185.419 ms stddev 169.064
progress: 360.0 s, 273.1 tps, lat 183.099 ms stddev 144.569
progress: 420.0 s, 294.4 tps, lat 169.912 ms stddev 128.209
progress: 480.0 s, 311.2 tps, lat 160.646 ms stddev 115.194
progress: 540.0 s, 317.8 tps, lat 157.084 ms stddev 113.825
progress: 600.0 s, 319.5 tps, lat 156.751 ms stddev 112.012
transaction type:
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 148638
latency average = 201.815 ms
latency stddev = 169.752 ms
tps = 247.553623 (including connections establishing)
tps = 247.556199 (excluding connections establishing) Улучшение для write запросов в тесте pgbench при переводе SELECT запросов на реплику:
(124–82)/82=0.51 или 51%
Улучшение для select запросов в тесте pgbench при переводе SELECT запросов на реплику:
(247–109)/109=1.26 или 126%
Устанавливаем зависимости на Leader, так как на нем будем запускать Java приложение
yum install -y java-1.8.0-openjdk-devel git mcПроверяем Read-Only реплику
/usr/pgsql-12/bin/psql --host=172.26.10.74 -U test test
Password for user test:
psql (12.3)
Type "help" for help.
test=> create user test1 with password 'password';
ERROR: cannot execute CREATE ROLE in a read-only transaction
test=> Создаем таблицу scale_data в бд test от пользователя test
/usr/pgsql-12/bin/psql --host=172.26.10.73 -U test testCREATE TABLE scale_data (
section NUMERIC NOT NULL,
id1 NUMERIC NOT NULL,
id2 NUMERIC NOT NULL
);Генерируем данные в таблице scale_data
INSERT INTO scale_data
SELECT sections.*, gen.*
, CEIL(RANDOM()*100)
FROM GENERATE_SERIES(1, 300) sections,
GENERATE_SERIES(1, 900000) gen
WHERE gen <= sections * 3000;Создаем индекс и кластеризуем таблицу scale_data
Без индекса update и select будут упираться в диск, если бд не влазит в ОЗУ. А это мешает эксперименту.
CREATE INDEX scale_slow ON scale_data (section, id1, id2);
ALTER TABLE scale_data CLUSTER ON scale_slow;
CLUSTER scale_data;Проверяем размер БД после генерации данных:
Клонируем репо jdbc-read-only-requests
git clone https://github.com/patsevanton/jdbc-read-only-requests.git
cd jdbc-read-only-requests
wget https://jdbc.postgresql.org/download/postgresql-42.2.14.jarТестирование. Все запросы идут на Leader. Запуск 1 экземпляра приложения
Правим строку String nodes в файле JavaPostgreSqlRepl.java
String nodes = "172.26.10.73:5000";Поменять на
String nodes = "ip-адрес-Leader:5000";А строку содержащую несколько нод закоментировать
String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";меняем на
//String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";Должно получиться примерно так: 
Компилируем код и запускаем его
Компилируем код
javac -cp "./postgresql-42.2.14.jar" JavaPostgreSqlRepl.javaЗапускаем Java приложение
java -classpath .:./postgresql-42.2.14.jar JavaPostgreSqlReplВремя выполнения транзакций, которые идут на Leader, и select, которые идут на Replica
Master: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Slave: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
transact: 6.00 (2.60) ms select: 31.07 (10.99) ms
transact: 0.80 (0.11) ms select: 0.72 (0.13) ms
transact: 0.75 (0.10) ms select: 0.56 (0.10) ms
transact: 0.75 (0.12) ms select: 0.60 (0.09) ms
transact: 0.82 (0.13) ms select: 0.59 (0.09) ms
transact: 1.30 (0.10) ms select: 1.04 (0.09) ms
transact: 1.74 (0.10) ms select: 2.90 (0.10) ms
transact: 2.25 (0.11) ms select: 1.48 (0.10) ms
transact: 1.55 (0.12) ms select: 1.14 (0.11) ms
transact: 1.11 (0.11) ms select: 1.31 (0.12) msТестирование. Транзакции идут на Leader. Select идут на Sync Standby. Запуск 1 экземпляра приложения
Правим строку String nodes в файле JavaPostgreSqlRepl.java
String nodes = "ip-адрес-Leader:5000";Поменять на
//String nodes = "ip-адрес-Leader:5000";А строку содержащую несколько нод раскоментировать
//String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";меняем на
String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";Должно получиться примерно так: 
Компилируем код и запускаем его
Компилируем код
javac -cp "./postgresql-42.2.14.jar" JavaPostgreSqlRepl.javaЗапускаем Java приложение
java -classpath .:./postgresql-42.2.14.jar JavaPostgreSqlReplВремя выполнения транзакций и select, если идет обращение только на Leader
Master: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Slave: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
transact: 4.14 (0.89) ms select: 63.41 (37.40) ms
transact: 0.80 (0.10) ms select: 0.96 (0.12) ms
transact: 0.74 (0.10) ms select: 0.76 (0.10) ms
transact: 0.86 (0.14) ms select: 0.72 (0.09) ms
transact: 0.82 (0.10) ms select: 4.94 (0.11) ms
transact: 1.44 (0.12) ms select: 0.84 (0.10) ms
transact: 0.78 (0.10) ms select: 1.64 (0.10) ms
transact: 1.56 (0.10) ms select: 0.79 (0.09) ms
transact: 0.80 (0.10) ms select: 0.94 (0.09) ms
transact: 0.86 (0.12) ms select: 0.79 (0.09) msКак видим время запросов поменялось не сильно.
Запуск нескольких экземпляров Java приложения
Активируем бесконечный цикл SQL запросов в Java приложении. Переходим на 108 строку и расскоментируем while(true) {, комментируем for(int i=0; i < 100; i++ ) {
while(true) {
//for(int i=0; i < 100; i++ ) {Должно получиться примерно так:
Тестирование. Все запросы идут на Leader. Запуск 50 экземпляров приложения
Правим строку String nodes в файле JavaPostgreSqlRepl.java
String nodes = "172.26.10.73:5000";Поменять на
String nodes = "ip-адрес-Leader:5000";А строку содержащую несколько нод закоментировать
String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";меняем на
//String nodes = "ip-адрес-Leader:5000,ip-адрес-Leader:5002";Должно получиться примерно так:
Компилируем код
javac -cp "./postgresql-42.2.14.jar" JavaPostgreSqlRepl.javaЗапускаем 49 раз Java приложение в фоне в бесконечном цикле.
java -classpath .:./postgresql-42.2.14.jar JavaPostgreSqlRepl > /dev/null 2>&1 &Проверяем что у нас запущено 49 приложений java
ps aux | grep java | grep -v grep | wc -lСмотрим какие процессы postgres запущены на реплике
Запускаем Java приложение чтобы увидеть среднее время SQL запросов.
Время выполнения транзакций и select, если идет обращение только на Leader
Время выполнения транзакций, которые идут на Leader и select, которые идут на Replica
Исходные данные можно посмотреть по ссылке
https://docs.google.com/spreadsheets/d/1jw5DAsHFNsO4wmYUxR2TmbGc1CS9J0w2beNhfI0NLhQ/edit? usp=sharing
Чтобы подтвердить тесты pgbench из java приложений и потдвердить улучшение времени SQL запросов (TPS) на самом сервере приложений, нужно тестировать многопоточное Java приложение с Connection Pool (Например, HikariCP, C3PO), которое будет одновременно отправлять несколько десятков SQL запросов так как это делает pgbench.
Возможно, кто-то это сделает.
