Обсуждение: Random server overload
Hello, We are experiencing database random overloads caused by IDLE processes. Their count jumps from normal ~70 connections to 250-300 with high I/O (30-40% wa, when normal ~ 1 % wa). The overload isn't long and lasts about 5 -10 minutes just a couple of times during the month. Please suggest how to debug this issue and find the cause of the overloads. Or mby we should tune our config file ? errorlog example: 2013-09-30 10:37:45 EEST FATAL: sorry, too many clients already 2013-09-30 10:37:45 EEST FATAL: remaining connection slots are reserved for non-replication superuser connections ... config file: max_connections = 250 shared_buffers = 16GB temp_buffers = 16MB max_prepared_transactions = 0 work_mem = 448MB maintenance_work_mem = 4GB max_stack_depth = 6MB wal_buffers = 18MB checkpoint_segments = 30 checkpoint_timeout = 5min checkpoint_warning = 30s random_page_cost = 4.0 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.005 effective_cache_size = 50GB default_statistics_target = 100 autovacuum = on othr values are defaults. System: RAM 74 GB PostgreSQL 9.1.9, Debian 6 Database size on disc: 84 GB data + 23 GB indexes. Different LVMs on RAID 10. -- Best regards
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Viktor > Sent: Tuesday, October 01, 2013 9:19 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Random server overload > > Hello, > > We are experiencing database random overloads caused by IDLE processes. > Their count jumps from normal ~70 connections to 250-300 with high I/O (30- > 40% wa, when normal ~ 1 % wa). > > The overload isn't long and lasts about 5 -10 minutes just a couple of times > during the month. > > Please suggest how to debug this issue and find the cause of the overloads. > Or mby we should tune our config file ? > > errorlog example: > 2013-09-30 10:37:45 EEST FATAL: sorry, too many clients already > 2013-09-30 10:37:45 EEST FATAL: remaining connection slots are reserved for > non-replication superuser connections ... > > config file: > > max_connections = 250 > shared_buffers = 16GB > temp_buffers = 16MB > max_prepared_transactions = 0 > work_mem = 448MB > maintenance_work_mem = 4GB > max_stack_depth = 6MB > wal_buffers = 18MB > checkpoint_segments = 30 > checkpoint_timeout = 5min > checkpoint_warning = 30s > random_page_cost = 4.0 > cpu_tuple_cost = 0.01 > cpu_index_tuple_cost = 0.005 > effective_cache_size = 50GB > default_statistics_target = 100 > autovacuum = on > > othr values are defaults. > > System: > RAM 74 GB > PostgreSQL 9.1.9, Debian 6 > Database size on disc: 84 GB data + 23 GB indexes. Different LVMs on RAID > 10. > > > -- > Best regards Did you try using any kind of connection pooler, e.g. PgBouncer? Should help. Regards, Igor Neyman
Viktor wrote: > We are experiencing database random overloads caused by IDLE processes. > Their count jumps from normal ~70 connections to 250-300 with high I/O > (30-40% wa, when normal ~ 1 % wa). > > The overload isn't long and lasts about 5 -10 minutes just a couple of > times during the month. > > Please suggest how to debug this issue and find the cause of the > overloads. Or mby we should tune our config file ? > > errorlog example: > 2013-09-30 10:37:45 EEST FATAL: sorry, too many clients already > 2013-09-30 10:37:45 EEST FATAL: remaining connection slots are reserved > for non-replication superuser connections Looks like something tries to open lots of connections at these times. Your best bet is to is to set log_connections = on and examine the log file when the problem happens. That way you can figure out where the connections come from. Yours, Laurenz Albe
On 10/1/2013 4:45 PM, Igor Neyman wrote: > Did you try using any kind of connection pooler, e.g. PgBouncer? > Should help. Our Java application uses c3p0 connection pooler and we don't think that it's the issue. On 10/1/2013 6:15 PM, Albe Laurenz wrote: > Looks like something tries to open lots of connections at these times. > > Your best bet is to is to set log_connections = on and examine the log file > when the problem happens. That way you can figure out where the connections > come from. > > Yours, > Laurenz Albe I have made the changes to the config file and will update this ticket later with the results. Thank you all!
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Viktor > Sent: Wednesday, October 02, 2013 4:12 AM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Random server overload > > On 10/1/2013 4:45 PM, Igor Neyman wrote: > > Did you try using any kind of connection pooler, e.g. PgBouncer? > > Should help. > > Our Java application uses c3p0 connection pooler and we don't think that it's > the issue. > > "Client-side" connection pooling is different from server-side (such as PgBouncer), and I believe is not as effective asPgBouncer. As you stated in original message you have multiple idle connections, that's waste of resources. Regards, Igor Neyman
Igor Neyman <ineyman@perceptron.com> wrote: >> Our Java application uses c3p0 connection pooler and we don't >> think that it's the issue. > > "Client-side" connection pooling is different from server-side > (such as PgBouncer), and I believe is not as effective as > PgBouncer. In my experience a good client-side pooler can be more effective -- if all significant traffic is going through a single pooler and the pool size is set appropriately. For example, we improved performance on a 16 code 256 GB server by reducing the pool size of the web application (handling hundreds of requests per second from 3000 concurrent users) from a maximum of 60 database connections to 35. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections > As you stated in original message you have multiple idle > connections, that's waste of resources. ... and a risk that if they all become active at one time, you can have a seemingly-random server overload. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
"Does the server log say anything about broken
connections or client not responding?"
Nope, no errors in server log, just high I/O and no free slots remaining.
We are thinking of adding more RAM to the server what should speed up the queries..
--
Best regards,
Viktor
connections or client not responding?"
Nope, no errors in server log, just high I/O and no free slots remaining.
We are thinking of adding more RAM to the server what should speed up the queries..
--
Best regards,
Viktor