如何增加pgbouncer的连接吞吐量？ [英] How to increase the connection throughput for pgbouncer?

问题描述

我在交易模式&中使用pgbouncer试图允许接近500笔有效交易。
的目的仅仅是对设置进行压力测试

当前设置：
['n'个客户端---> 1个pgbouncer ---- > 1个postgres]

我注意到当我使用pgbouncer而不是直接连接到postgres时，我的事务/秒（tps）大大降低了。

对于同一组交易（通过pgbench）

• 直接连接=> 10k（tps ）appx




• pgbouncer connection => 3k（tps）appx

pgbouncer中是否有任何配置需要调整以提高性能？

我了解pgbouncer是单线程应用程序，但我想将其调至最佳状态。
以下是我的pgbouncer配置：

pgbouncer.ini

  pool_mode =事务
 server_reset_query = 
 
＃超时
 server_lifetime = 6000 
 server_idle_timeout = 0 
 server_connect_timeout = 30 
 
 
 #pool配置
 max_client_conn = 10000 
 default_pool_size = 500 
 pool_size = 500 
 
 ##其他
 pkt_buf = 4096 
 server_login_retry = 2 
  

我唯一看到的应用是使用多个pgbouncers指向同一数据库服务器。

更新

测试：

cpu利用率：30％appx

磁盘利用率：40％appx

观察：许多交易处于闲置状态

测试详细信息：

10台计算机作为运行pgbench触发向数据库服务器发出请求的客户端。

命令：pgbench -h -p 6541 -c 512 -j 16 -f pgbench_SchemaScript.sql -T 360 -U postgres测试

pgbench_SchemaScript.sql

  \setrandom delta 0 100000 
插入t1.emplog值（nextval（'t1.employeeSeq'）,: delta）; 
  

1个安装了pgbouncer的数据库服务器（16core，24 Gb RAM）

解决方案

如果在与PostgreSQL相同的主机上运行PgBouncer，则应增加 pgbouncer 进程的CPU优先级与 renice 。

  renice -10 -p`pidof pgbouncer` 
  

默认的Linux调度程序是循环运行的，它使PgBouncer挨饿，因为它平等地对待所有进程-数百个 postgres 进程压倒了单个 pgbouncer 进程。

通常在中等负载下是一个问题，但是当运行许多小型事务时，这可能是一个非常重要的因素。

即使renice也无法做到：如果运行的TPS超过10k +，您可能必须考虑在（多个）PgBouncer前面进行一些负载平衡（HAProxy？）。 / p>

I am using pgbouncer in transaction mode & trying to allow close to 500 active transaction. The purpose is simply to stress test the setup

Current setup: [ 'n' clients --->1 pgbouncer ----> 1 postgres ]

I notice that my transaction/second (tps) decreases considerably when I use pgbouncer instead of a direct connection to postgres.

For the same set to transaction (via pgbench)

• Direct connections => 10k (tps) appx

• pgbouncer connection => 3k (tps) appx

Is there any configuration in pgbouncer that needs to be tuned to allow a better performance?

I understand pgbouncer is a single threaded application, but would like to tune it till its best. Following is my pgbouncer configuration:

pgbouncer.ini

pool_mode = transaction
server_reset_query =

# Time outs
server_lifetime=6000
server_idle_timeout=0
server_connect_timeout=30


#pool configuration
max_client_conn=10000
default_pool_size=500
pool_size=500

##other
pkt_buf=4096
server_login_retry=2

The only application I can see is to use multiple pgbouncers to point to the same db server.

UPDATE

while executing the test:

cpu utilization : 30% appx

disk utilization : 40% appx

Observation: many transactions in 'idle' state

TEST DETAILS:

10 machine acting as clients running pgbench firing request to the DB server.

command: pgbench -h -p 6541 -c 512 -j 16 -f pgbench_SchemaScript.sql -T 360 -U postgres test

pgbench_SchemaScript.sql

\setrandom delta 0 100000
insert into t1.emplog values(nextval('t1.employeeSeq'),:delta);

1 DB server with pgbouncer installed (16core , 24 Gb RAM)

解决方案

If you are running PgBouncer on the same host as PostgreSQL, you should increase CPU priority of pgbouncer process with renice.

renice -10 -p `pidof pgbouncer`

Default Linux scheduler is round-robin and it starves PgBouncer because it treats all processes equally - and hundreds of postgres processes overwhelm single pgbouncer process.

It's usually not a problem under moderate load, but it can be a really important factor when running lots of small transactions.

Even renice cannot do magic: if you run more than 10k+ TPS, you probably have to consider some load balancing (HAProxy?) in front of (multiple) PgBouncer.

这篇关于如何增加pgbouncer的连接吞吐量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！