Обсуждение: [MASSMAIL]Postgresql - Pgbouncer Connection and Query Performance Problem
We are using prepared transactions in PostgreSQL. We use pgbouncer as our pooling software. From time to time, I encountered connection issues in my database sessions, and to resolve them, I had to restart pgbouncer. While the
pool_mode parameter in pgbouncer was set to session, I changed it to transaction, and the connection issue was resolved. However, I am now experiencing significant slowdowns in my queries. I attribute this to two parameters in my PostgreSQL clusters and three parameters in pgbouncer. Firstly, on my server, I have 15 PostgreSQL clusters, and the values of these two parameters in the postgresql.conf file of each cluster seem very high;max_connection = 4096
max_prepared_transaction = 4096These two parameters are set to 4096 in all 15 clusters.
Do these values seem too high, and could they be causing my queries to run slowly?
On the other hand, the values I find high in pgbouncer are;
max_db_connections = 8192
default_pool_size = 4096
max_client_conn = 2048Could these high values be causing excessive load on pgbouncer?
I have actively 15,000 customers on my server, and there is an average data flow of 5 million daily (including updates). What should be the value of these parameters? Especially, what should I pay attention to when setting pgbouncer parameters?
I changed the specified three parameters in pgbouncer to the following values;
max_db_connections = 500
default_pool_size = 300
max_client_conn = 500On Tue, 2024-04-02 at 15:39 +0300, Abdullah Ergin wrote: > We are using prepared transactions in PostgreSQL. We use pgbouncer as our pooling software. > [...] I am now experiencing significant slowdowns in my queries. > I attribute this to two parameters in my PostgreSQL clusters and three parameters in pgbouncer. > Firstly, on my server, I have 15 PostgreSQL clusters, and the values of these two > parameters in the postgresql.conf file of each cluster seem very high; > > max_connection = 4096 > max_prepared_transaction = 4096 Yes, they are very high, but they are just limits. As long as you don't actually establish hundreds of connections, that is not a problem. > On the other hand, the values I find high in pgbouncer are; > max_db_connections = 8192 > default_pool_size = 4096 > max_client_conn = 2048 > Could these high values be causing excessive load on pgbouncer? > I have actively 15,000 customers on my server, and there is an average data flow > of 5 million daily (including updates). What should be the value of these parameters? > I changed the specified three parameters in pgbouncer to the following values; > max_db_connections = 500 > default_pool_size = 300 > max_client_conn = 500 > > Currently, it seems like my query performance has improved, but what should I do to > permanently solve this? What other parameters should I pay attention to besides these? "max_client_conn" is not the problem. The problem is the excessively high pool size of 300. The size should be so that when all those connections are busy running statements, your database should not be overloaded. Yours, Laurenz Albe
Thank you for the information. What would you recommend as the value for this parameter? Would 100 be too large of a number? Or maybe 50?
Additionally, before lowering these parameters, I had a lot of "LWLocks" in my database. Normally, I know that these lightweight locks don't cause significant issues, but during the slowdown, I was consistently seeing 70-80 LWLocks. After lowering the parameters and restarting pooling, these locks disappeared. Is there any correlation?
Best regards.
Laurenz Albe <laurenz.albe@cybertec.at>, 2 Nis 2024 Sal, 16:26 tarihinde şunu yazdı:
On Tue, 2024-04-02 at 15:39 +0300, Abdullah Ergin wrote:
> We are using prepared transactions in PostgreSQL. We use pgbouncer as our pooling software.
> [...] I am now experiencing significant slowdowns in my queries.
> I attribute this to two parameters in my PostgreSQL clusters and three parameters in pgbouncer.
> Firstly, on my server, I have 15 PostgreSQL clusters, and the values of these two
> parameters in the postgresql.conf file of each cluster seem very high;
>
> max_connection = 4096
> max_prepared_transaction = 4096
Yes, they are very high, but they are just limits. As long as you don't actually
establish hundreds of connections, that is not a problem.
> On the other hand, the values I find high in pgbouncer are;
> max_db_connections = 8192
> default_pool_size = 4096
> max_client_conn = 2048
> Could these high values be causing excessive load on pgbouncer?
> I have actively 15,000 customers on my server, and there is an average data flow
> of 5 million daily (including updates). What should be the value of these parameters?
> I changed the specified three parameters in pgbouncer to the following values;
> max_db_connections = 500
> default_pool_size = 300
> max_client_conn = 500
>
> Currently, it seems like my query performance has improved, but what should I do to
> permanently solve this? What other parameters should I pay attention to besides these?
"max_client_conn" is not the problem. The problem is the excessively high pool
size of 300. The size should be so that when all those connections are busy running
statements, your database should not be overloaded.
Yours,
Laurenz Albe
On Tue, 2024-04-02 at 17:04 +0300, Abdullah Ergin wrote: > [large connection pool size] > Thank you for the information. What would you recommend as the value > for this parameter? Would 100 be too large of a number? Or maybe 50? That depends entirely on your hardware and the database workload. Perhaps reading this article can help you: https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-statistics/ > Additionally, before lowering these parameters, I had a lot of "LWLocks" > in my database. Normally, I know that these lightweight locks don't cause > significant issues, but during the slowdown, I was consistently seeing > 70-80 LWLocks. After lowering the parameters and restarting pooling, > these locks disappeared. Is there any correlation? Very likely yes. If you see many LWLocks, that will cause a significant performance issue. Essentially, it is contention inside the database. Yours, Laurenz Albe
Hi Abdullah,
What pooling mode you use with PgBouncer strictly depends on the application and what features of the database you use at the application side. For example, if you need to use session level features of PostgreSQL, such as setting search_path, then you need to use session mode. Most of the time, transaction mode suffices. But, you need to consider features the application uses before doing such a change.
Having 4096 client connections and prepared transactions on the database raises too many questions for me. If I were the person who was hired as a database consultant for this setup, I would want to talk to the person who configured the database first. Those numbers may have some logical explanation. But, I would consider those numbers to be set arbitrarily because of the accompanying PgBouncer configuration. You set the default pool size to 4096 while keeping max_client_conn as 2048. I don't see any point in having a pool with twice the maximum number of client connections.
Having 15k active customers is data. But, it is not necessarily enough to decide how many database connections you need. More important data is their database usage pattern.
I translate "5 million data flow per day" as "5 million transactions per day". Let's say those transactions happen during business hours only. It means, you have approximately ~175 transactions per second (5000000/(8*60*60)). I would say it is an ordinary number. However, it depends on how heavy those transactions are.
Regarding lightweight locks, type of the LWLock is important. Having too many idle connections on the database has its own costs. LWLocks you saw on your database is probably related to idle connections.
To conclude, there is no magic number that I can give you to set so as to get better performance right now. I recommend you to monitor the database, talk to the person who configured the database, and talk to your software development team to understand the database usage pattern of the application.
Best regards.
Samed YILDIRIM
On Wed, 3 Apr 2024 at 09:25, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2024-04-02 at 17:04 +0300, Abdullah Ergin wrote:
> [large connection pool size]
> Thank you for the information. What would you recommend as the value
> for this parameter? Would 100 be too large of a number? Or maybe 50?
That depends entirely on your hardware and the database workload.
Perhaps reading this article can help you:
https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-statistics/
> Additionally, before lowering these parameters, I had a lot of "LWLocks"
> in my database. Normally, I know that these lightweight locks don't cause
> significant issues, but during the slowdown, I was consistently seeing
> 70-80 LWLocks. After lowering the parameters and restarting pooling,
> these locks disappeared. Is there any correlation?
Very likely yes.
If you see many LWLocks, that will cause a significant performance issue.
Essentially, it is contention inside the database.
Yours,
Laurenz Albe
Hello Samed,
Thank you for the information you provided about LWLocks. When I look at the type of these locks I see "BufferIO" and "BufferMapping" it really slows down the system. However, I have not yet fully understood what action I should take in this situation, especially why this situation is happening? and what should i do?
Best Regards.
Thank you for the information you provided about LWLocks. When I look at the type of these locks I see "BufferIO" and "BufferMapping" it really slows down the system. However, I have not yet fully understood what action I should take in this situation, especially why this situation is happening? and what should i do?
Best Regards.
Hi Abdullah,
BufferIO and BufferMapping are different types of events than previous ones. Your server is probably suffering because of intensive IO operations. There can be big queries who are causing big read or write operations and/or a significant number of active connections. I would check it at the operating system level by using top command to see what is the percentage for IO Wait (wa), system (sy), and software interrupts (si).
Judging by PgBouncer configuration, I suspect that shared_buffers would be set to a huge number, proportional to the available memory. I would check this configuration too. You would want to check swap usage too.
Best regards.
Samed YILDIRIM
On Tue, 16 Apr 2024 at 18:41, Abdullah Ergin <abdullaherginwork@gmail.com> wrote:
Hello Samed,
Thank you for the information you provided about LWLocks. When I look at the type of these locks I see "BufferIO" and "BufferMapping" it really slows down the system. However, I have not yet fully understood what action I should take in this situation, especially why this situation is happening? and what should i do?
Best Regards.