3

I have a dedicated PostgreSQL server and a pgBouncer server. all connections are established through pgBouncer. I tested the system using Apache JMeter and PHP. The results are weird. The throughput with 500 connections are not bad but when I test using more connections it drops.

This is the test result:

enter image description here

The pgbouncer config:

[databases]

maindb = host=212.212.322.323 port=5432 user=myuser dbname=mydb pool_size=1000 pool_mode=transaction

[pgbouncer]

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

listen_addr = *
listen_port = 6432

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

pool_mode = session
max_client_conn = 1000
default_pool_size = 20
rostamiani
  • 2,859
  • 7
  • 38
  • 74

1 Answers1

5

It is known limit of pgbouncer. It uses only one CPU, and doesn't work well under very high number of connections (for smaller number of connects it is fast and effective). There are new projects, that can be used instead pgbouncer for this purpose - Odyssey or pgagroal.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • Oh, Thanks. I tried Odyssey too. It seems that it's still unstable. I had some errors. Did yu worked with pgagroal? can I trust it? – rostamiani Sep 14 '20 at 05:00
  • 2
    Both are fresh projects. pgagroal is supported by RedHat, but it is fresh project too. You can test it, and you will see. I think so Odyssey is used in production, so maybe your case is special. You can check pgpoolII too. On second hand, there can be bottleneck on database side too. You should to check CPU on database server side, and maybe you hit a limit there. – Pavel Stehule Sep 14 '20 at 05:08
  • @rostamiani - `pool_size = 1000` looks weird - Postgres doesn't work well with high number of active connections on server side (maybe if you have server with more than 50CPU), and then the problems can be on postgres side. – Pavel Stehule Sep 14 '20 at 05:20
  • That's right. My main problem is pooling 1000 connections into 500 pools. This is not working. I will try pgagroal. This is my Odyssey problem if you are interested: https://github.com/yandex/odyssey/issues/209 – rostamiani Sep 14 '20 at 05:31
  • @rostamiani - it looks like usual bug - it should be fixed quickly. These new poolers are not tested by mass users like pgbouncer or pgpool, so it is very probable you find some bugs. – Pavel Stehule Sep 14 '20 at 09:49
  • odyssey looks good, it manages multiple connections for you. I am curious why PGBouncer is still so popular if poolers like odyssey exist and pg-pool II – Rami Aug 21 '23 at 19:17
  • @Rami - pgbouncer is light, simple to use and good enough. Probably not too much users hit limits of pgbouncer. pgbouncer can be bottleneck only if your Postgres has really pretty brutal throughput, and it is possible only on massive hardware, and that harware is very expensive and not too usual. pg-pool II is very complex multi purpose software. – Pavel Stehule Aug 23 '23 at 03:38