0

I have a server with postgresql database and pgbouncer connexion pooler. I testing with jmeter my server with different settings of pgbouncer and I managed to execute 5000 "simultaneous" requests without having any error. This result is good for now, but one day I would come close to its limits and I would like to konw how can I increase this limits ? For get this limits I have put this parameter for pgbouncer / psql :

  • MAX_CLIENT_CONN = 100000
  • DEFAULT_POOL_SIZE = 50000

It's not my CPU or Memory which saturates... Are there any solutions to deploy multiple instances of psql or pgbouncer to improve these results?

mmmmmmm
  • 67
  • 1
  • 7
  • 1
    Don't set `default_pool_size` much higher than the number of cores in your machine. There is usually only vertical scaling with relational databases, that is getting a stronger machine. Horizontal scaling (= sharding) is complicated and not always possible. – Laurenz Albe Jun 21 '21 at 14:24
  • Thanks @LaurenzAlbe, so I haven't no possibility to improve the performance of my database? – mmmmmmm Jun 22 '21 at 06:20
  • 1
    There may be lots of ways to improve performance. But this cannot be determined with the little data we have. For one, if you tune the queries to be shorter, you can handle more of them in the same time. However, randomly increasing the connection pool size will have an adverse effect when you are under load. – Laurenz Albe Jun 22 '21 at 06:46
  • Ok thank you @LaurenzAlbe , because my database works fine when I run a single request but for example if I run 1000 requests at the same time I get response times of 1.2 seconds and sometimes I even get an error. What are the solutions to achieve this? I tried using indexes but it doesn't change anything. – mmmmmmm Jun 22 '21 at 09:31
  • 1
    This requires a more in-depth analysis and cannot be answered in a SO answer. – Laurenz Albe Jun 22 '21 at 10:03
  • Thanks @LaurenzAlbe, Can you explain me how configure this parameter for optimize performance please : default_pool_size, min_pool_size, reserve_pool_size, reserve_pool_timeout, server_check_delay, server_lifetime, server_idle_timeout – mmmmmmm Jun 23 '21 at 13:50

0 Answers0