Управляемые базы данных позволяют масштабировать БД PostgreSQL несколькими способами. Одним из них является встроенный преобразователь подключений, который позволяет эффективно обрабатывать большое количество клиентских подключений и уменьшить использование их ЦП и памяти. Используя пул соединений и распределяя фиксированный набор перерабатываемых соединений, вы можете обрабатывать значительно больше одновременных клиентских соединений и повысить производительность вашей базы данных PostgreSQL.
В этом мануале вы научитесь использовать pgbench, встроенный в PostgreSQL инструмент тестирования производительности, для запуска нагрузочных тестов в управляемой базе данных PostgreSQL. Мы расскажем вам про пулы соединений, опишем, как они работают, и покажем, как их создавать. По результатам тестов pgbench вы увидите, что использование пула соединений может стать недорогим средством увеличения пропускной способности базы данных.
Требования
- Управляемая БД PostgreSQL.
- Клиентская машина с экземпляром PostgreSQL. По умолчанию установка PostgreSQL содержит утилиту тестирования производительности pgbench и клиент psql, которые мы будем использовать в этом мануале. Чтобы узнать, как установить PostgreSQL, читайте мануал Установка и использование PostgreSQL в Ubuntu 18.04.
1: Создание и инициализация БД benchmark
Прежде чем создать пул соединений для базы данных, создайте базу данных benchmark в вашем кластере PostgreSQL и заполните ее фиктивными данными, на которых pgbench будет выполнять свои тесты. Утилита pgbench многократно запускает серию из пяти SQL команд (включая запросы SELECT, UPDATE и INSERT), используя несколько потоков и клиентов, и вычисляет показатель полезной производительности под названием «транзакции в секунду» (TPS, transactions per second). TPS – это показатель пропускной способности базы данных, который показывает количество транзакций, обработанных базой данных за одну секунду. Чтобы узнать больше о командах, выполняемых pgbench, обратитесь к официальной документации pgbench.
Итак, подключитесь к кластеру PostgreSQL и создайте БД benchmark.
Процесс создания будет зависеть от вашего провайдера.
При этом вам понадобятся такие данные (мы используем условные данные, которые вы должны заменить):
- Имя администратора: myadmin
- your_password
- Пароль: your_password
- Конечная точка кластера: укажите свою точку
- Порт: 25060
- База данных: defaultdb
- Режим SSL: require (чтобы использовать зашифрованное соединение SSL)
Эти параметры вам понадобятся в дальнейшей работе с клиентом psql и инструментом pgbench, потому запомните или запишите их.
Подключитесь к кластеру с помощью клиента psql (для этого вам может понадобиться строка подключения).
Вы попадете в командную строку клиента PostgreSQL, если подключение будет удачно создано.
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
defaultdb=>
Создайте БД benchmark:
CREATE DATABASE benchmark;
CREATE DATABASE
Выйдите из кластера:
\q
Перед запуском тестов pgbench необходимо заполнить эту тестовую базу данных несколькими таблицами и фиктивными данными.
Для этого мы запустим pgbench со следующими флагами:
- -h: конечная точка кластера PostgreSQL
- -p: порт кластера PostgreSQL
- -U: имя пользователя базы данных
- -i: инициализирует базу данных benchmark с помощью таблиц и их фиктивных данных.
- -s: устанавливает масштабный коэффициент 150, который умножит размеры таблицы на 150. Стандартный масштабный коэффициент 1 создает таблицу следующих размеров:
table # of rows
---------------------------------
pgbench_branches 1
pgbench_tellers 10
pgbench_accounts 100000
pgbench_history 0
Масштабный коэффициент 150 создаст таблицу pgbench_accounts в 15 000 000 строк.
Примечание: Чтобы избежать чрезмерного количества заблокированных транзакций, убедитесь, что значение масштабного коэффициента как минимум равно количеству одновременно работающих клиентов, которые используются в тестировании. В этом мануале мы протестируем максимум 150 клиентов, поэтому мы установили здесь значение -s 150. Чтобы узнать больше, ознакомьтесь с этими рекомендациями из официальной документации pgbench.
Запустите команду pgbench:
pgbench -h your_cluster_endpoint -p 25060 -U myadmin -i -s 150 benchmark
После этого вам будет предложено ввести пароль указанного пользователя БД. Введите пароль и нажмите Enter.
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 15000000 tuples (0%) done (elapsed 0.19 s, remaining 27.93 s)
200000 of 15000000 tuples (1%) done (elapsed 0.85 s, remaining 62.62 s)
300000 of 15000000 tuples (2%) done (elapsed 1.21 s, remaining 59.23 s)
400000 of 15000000 tuples (2%) done (elapsed 1.63 s, remaining 59.44 s)
500000 of 15000000 tuples (3%) done (elapsed 2.05 s, remaining 59.51 s)
. . .
14700000 of 15000000 tuples (98%) done (elapsed 70.87 s, remaining 1.45 s)
14800000 of 15000000 tuples (98%) done (elapsed 71.39 s, remaining 0.96 s)
14900000 of 15000000 tuples (99%) done (elapsed 71.91 s, remaining 0.48 s)
15000000 of 15000000 tuples (100%) done (elapsed 72.42 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
На данный момент у вас есть база данных benchmark, заполненная таблицами и данными, необходимыми для запуска тестов pgbench. Теперь можно запустить базовый тест, который позволит нам сравнить производительность до и после включения пула соединений.
2: Запуск базового теста pgbench
Прежде чем начать первый тест, стоит остановиться на том, что именно мы пытаемся оптимизировать с помощью пулов соединений.
Обычно, когда клиент подключается к БД PostgreSQL, основной процесс PostgreSQL переходит в дочерний процесс, соответствующий этому новому соединению. Если у вас только несколько соединений, это не вызовет проблем. Однако по мере масштабирования клиентов и соединений расходы ресурсов процессора и памяти на создание и поддержание этих соединений начинают накладываться, особенно если рассматриваемое приложение неэффективно использует соединения с БД. Кроме того, параметр PostgreSQL max_connections может ограничивать количество разрешенных клиентских подключений, в результате чего после превышения этого значения соединения будут отклонены или сброшены.
Пул соединений сохраняет открытым фиксированное количество соединений с базой данных (это размер пула, который он затем использует для распределения и выполнения клиентских запросов). Это означает, что вы можете обслужить гораздо больше одновременных соединений, эффективно обработать неактивных или застойных клиентов, а также поставить клиентские запросы в очередь во время всплесков трафика (вместо того чтобы отклонять их). Повторно используя соединения, вы можете более эффективно применить ресурсы вашего компьютера в среде с большим объемом подключений и оптимизировать производительность БД.
Пул соединений можно реализовать либо на стороне приложения, либо в качестве промежуточного программного обеспечения между БД и приложением. Пул соединений для управляемых баз данных разработан на основе pgBouncer (это легкий промежуточный пул соединений с открытым исходным кодом для PostgreSQL).
Откройте панель управления своими базами данных. в меню найдите опцию типа Connection Pools и создайте пул соединений.
Для этого вам понадобятся такие данные:
- Pool Name: уникальное имя вашего пула соединений.
- Database: База данных, соединения которой вы хотите объединить в пул.
- User: пользователь PostgreSQL, с помощью которого пул соединений будет аутентифицироваться
- Mode: режим. Эта опция контролирует, как долго пул назначает клиентскому серверу бэкэнд-соединение.
-
- Session: Клиент удерживает соединение до тех пор, пока оно явно не отключится.
- Transaction: Клиент получает соединение до завершения транзакции, после чего соединение возвращается в пул.
- Statement: Пул агрессивно перезапускает соединения после каждого оператора клиента. В этом режиме транзакции с несколькими операторами не поддерживаются.
- Pool Size: количество соединений, которые пул будет держать открытым между собой и базой данных.
Прежде чем создать пул соединений, давайте запустим базовый тест, с результатами которого вы сможете сравнить производительность базы данных до и после внедрения пула соединений.
В этом мануале мы используем 4 ГБ RAM, 2 vCPU, 80 ГБ дискового пространства, управляемую базу данных (только ведущая нода). Вы можете масштабировать параметры теста производительности в соответствии с вашими спецификациями кластера PostgreSQL.
Итак, давайте запустим первый тест pgbench.
Войдите на клиентскую машину. Запустите pgbench, указав конечную точку, порт и пользователя. Также нужно добавить эти опции:
- -c: количество одновременных клиентов или сеансов БД, которое нужно симулировать. Мы установим здесь 50, чтобы имитировать количество одновременных соединений, меньшее, чем параметр max_connections данного кластера PostgreSQL.
- -j: количество рабочих потоков, которые pgbench будет использовать во время теста. Если вы используете многопроцессорную машину, вы можете увеличить зто значение, чтоб распределить клиентов по потокам. На двухъядерном компьютере нужно установить значение 2.
- -P: отображает прогресс и метрики каждые 60 секунд.
- -T: запустит тест на 600 секунд (10 минут). Чтобы получить согласованные, воспроизводимые результаты, важно, чтобы тест длился в течение нескольких минут или один цикл контрольных точек.
Также нужно указать, что тест нужно запустить в БД benchmark.
Запустите эту команду:
pgbench -h your_db_endpoint -p 25060 -U myadmin -c 50 -j 2 -P 60 -T 600 benchmark
Нажмите клавишу Enter и введите пароль пользователя myadmin, чтобы начать выполнение теста. Вы должны увидеть подобный вывод (результаты будут зависеть от характеристик вашего кластера PostgreSQL):
starting vacuum...end.
progress: 60.0 s, 157.4 tps, lat 282.988 ms stddev 40.261
progress: 120.0 s, 176.2 tps, lat 283.726 ms stddev 38.722
progress: 180.0 s, 167.4 tps, lat 298.663 ms stddev 238.124
progress: 240.0 s, 178.9 tps, lat 279.564 ms stddev 43.619
progress: 300.0 s, 178.5 tps, lat 280.016 ms stddev 43.235
progress: 360.0 s, 178.8 tps, lat 279.737 ms stddev 43.307
progress: 420.0 s, 179.3 tps, lat 278.837 ms stddev 43.783
progress: 480.0 s, 178.5 tps, lat 280.203 ms stddev 43.921
progress: 540.0 s, 180.0 tps, lat 277.816 ms stddev 43.742
progress: 600.0 s, 178.5 tps, lat 280.044 ms stddev 43.705
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 105256
latency average = 282.039 ms
latency stddev = 84.244 ms
tps = 175.329321 (including connections establishing)
tps = 175.404174 (excluding connections establishing)
Здесь можно видеть, что за 10 минут работы с 50 одновременными соединениями кластер обработал 105 256 транзакций с пропускной способностью примерно 175 транзакций в секунду.
Теперь давайте запустим тот же тест, но на этот раз используем 150 одновременных клиентов. Это значение выше, чем max_connections для этой базы данных, это позволяет симулировать массовый приток клиентских подключений:
pgbench -h your_db_endpoint -p 25060 -U myadmin -c 150 -j 2 -P 60 -T 600 benchmark
Вы получите результат:
starting vacuum...end.
connection to database "pgbench" failed:
FATAL: remaining connection slots are reserved for non-replication superuser connections
progress: 60.0 s, 182.6 tps, lat 280.069 ms stddev 42.009
progress: 120.0 s, 253.8 tps, lat 295.612 ms stddev 237.448
progress: 180.0 s, 271.3 tps, lat 276.411 ms stddev 40.643
progress: 240.0 s, 273.0 tps, lat 274.653 ms stddev 40.942
progress: 300.0 s, 272.8 tps, lat 274.977 ms stddev 41.660
progress: 360.0 s, 250.0 tps, lat 300.033 ms stddev 282.712
progress: 420.0 s, 272.1 tps, lat 275.614 ms stddev 42.901
progress: 480.0 s, 261.1 tps, lat 287.226 ms stddev 112.499
progress: 540.0 s, 272.5 tps, lat 275.309 ms stddev 41.740
progress: 600.0 s, 271.2 tps, lat 276.585 ms stddev 41.221
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 150
number of threads: 2
duration: 600 s
number of transactions actually processed: 154892
latency average = 281.421 ms
latency stddev = 125.929 ms
tps = 257.994941 (including connections establishing)
tps = 258.049251 (excluding connections establishing)
Обратите внимание на ошибку FATAL – она указывает на то, что pgbench достиг порогового значения в 100 соединений (max_connections), что привело к отказу в соединении. И все же pgbench смог закончить тест с TPS примерно в 257.
На этом этапе вы можете определить, как пул соединений потенциально может улучшить пропускную способность базы данных.
3: Создание и тестирование пула соединений
Теперь давайте создадим пул соединений и повторно запустим предыдущий тест pgbench, чтобы посмотреть, сможет ли это улучшить пропускную способность базы данных.
В общем, настройки max_connections и параметры пула соединений поднимаются вместе, чтобы максимально увеличить нагрузку на базу данных. Однако если вы не можете настроить max_connections в управляемых базах данных, используйте настройки режима и размера пула соединений.
Для начала нужно создать пул соединений в режиме Transaction, который сохраняет открытыми все доступные внутренние соединения.
Перейдите панели управления откройте меню баз данных, а затем кликните по кластеру PostgreSQL. Откройте меню пулов подключения и создайте такой пул.
В появившемся окне конфигурации заполните предложенные поля и нажмите Создать.
Скопируйте появившийся URI.
Вы должны заметить, что в результате в URI будет другой порт и, возможно, другая конечная точка и имя базы данных, соответствующее имени пула (в данном случае test-pool).
Теперь, когда мы создали пул соединений test-pool, мы можем снова запустить тест pgbench, который мы запускали ранее.
Повторный запуск pgbench
На своем клиентском компьютере выполните следующую команду pgbench (укажите 150 одновременных клиентов), убедившись, что выделенные значения заменены значениями в URI пула соединений:
pgbench -h pool_endpoint -p pool_port -U myadmin -c 150 -j 2 -P 60 -T 600 test-pool
Здесь мы снова используем 150 одновременных клиентов, запуская тест в двух потоках. Команда будет отображать прогресс каждые 60 секунд, тест будет продолжаться в течение 600 секунд. Имя базы данных должно быть test-pool, как и имя пула соединений.
После завершения теста вы должны увидеть подобный вывод (обратите внимание, что эти результаты будут отличаться в зависимости от характеристик вашей ноды БД).
starting vacuum...end.
progress: 60.0 s, 240.0 tps, lat 425.251 ms stddev 59.773
progress: 120.0 s, 350.0 tps, lat 428.647 ms stddev 57.084
progress: 180.0 s, 340.3 tps, lat 440.680 ms stddev 313.631
progress: 240.0 s, 364.9 tps, lat 411.083 ms stddev 61.106
progress: 300.0 s, 366.5 tps, lat 409.367 ms stddev 60.165
progress: 360.0 s, 362.5 tps, lat 413.750 ms stddev 59.005
progress: 420.0 s, 359.5 tps, lat 417.292 ms stddev 60.395
progress: 480.0 s, 363.8 tps, lat 412.130 ms stddev 60.361
progress: 540.0 s, 351.6 tps, lat 426.661 ms stddev 62.960
progress: 600.0 s, 344.5 tps, lat 435.516 ms stddev 65.182
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 150
number of threads: 2
duration: 600 s
number of transactions actually processed: 206768
latency average = 421.719 ms
latency stddev = 114.676 ms
tps = 344.240797 (including connections establishing)
tps = 344.385646 (excluding connections establishing)
Обратите внимание, мы смогли увеличить пропускную способность базы данных с 257 TPS до 344 TPS при 150 одновременных соединениях (это увеличение на 33%) и не столкнулись с превышением max_connections, как было без пула соединений. Пул соединений позволяет избежать разрыва соединений и значительно увеличить пропускную способность базы данных в среде с большим количеством одновременных соединений.
Если вы запустите этот же тест, но со значением -c 50 (уменьшив количество клиентов), плюсы использования пула соединений станут гораздо менее очевидными:
starting vacuum...end.
progress: 60.0 s, 154.0 tps, lat 290.592 ms stddev 35.530
progress: 120.0 s, 162.7 tps, lat 307.168 ms stddev 241.003
progress: 180.0 s, 172.0 tps, lat 290.678 ms stddev 36.225
progress: 240.0 s, 172.4 tps, lat 290.169 ms stddev 37.603
progress: 300.0 s, 177.8 tps, lat 281.214 ms stddev 35.365
progress: 360.0 s, 177.7 tps, lat 281.402 ms stddev 35.227
progress: 420.0 s, 174.5 tps, lat 286.404 ms stddev 34.797
progress: 480.0 s, 176.1 tps, lat 284.107 ms stddev 36.540
progress: 540.0 s, 173.1 tps, lat 288.771 ms stddev 38.059
progress: 600.0 s, 174.5 tps, lat 286.508 ms stddev 59.941
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 102938
latency average = 288.509 ms
latency stddev = 83.503 ms
tps = 171.482966 (including connections establishing)
tps = 171.553434 (excluding connections establishing)
Этот вывод говорит, что вы не смогли увеличить пропускную способность с помощью пула соединений. Пропускная способность снизилась до 171 TPS со 175 TPS.
В этом мануале мы используем pgbench с фиктивной БД benchmark. Но чтобы лучше понять, следует ли использовать пул соединений, вы должны тестировать нагрузку, которая точно отражает производственную нагрузку на БД. Создание пользовательских сценариев тестирования и данных выходит за рамки этого мануала, но дополнительную информацию вы можете получить в официальной документации pgbench.
Примечание: Настройка размера пула сильно зависит от рабочей нагрузки. В этом мануале мы настроили пул соединений для использования всех доступных подключений к базе данных. Это связано с тем, что на протяжении всего теста база данных редко использовалась по максимуму. В зависимости от загрузки вашей базы данных, это значение параметра может быть не оптимальным. При постоянной загрузке БД сокращение пула соединений может увеличить пропускную способность и повысить производительность: дополнительные запросы попадут в очередь вместо того, БД не будет пытаться выполнить их все одновременно на уже загруженном сервере.
Заключение
В этом мануале вы научились использовать встроенный инструмент PostgreSQL pgbench для тестирования вашей БД. В любом сценарии производства необходимо тестировать нагрузку, и чем ближе она к реальной ситуации, тем лучше – так вы сможете настроить вашу БД под конкретные условия работы.
Существуют и другие инструменты для нагрузочного тестирования базы данных. Одним из таких инструментов является sysbench-tpcc, разработанный Percona. Еще есть Apache JMeter, который может загружать тестовые базы данных и веб-приложения.
Читайте также: