Re: [ADMIN] Random server overload
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
Re: [ADMIN] Random server overload
Hello, It's me again.. Log_connection inspection didn't help to find the cause, the connections come from our app-servers as usual.. Btw we have the max pool size of web application 50 connections and since we have 4 instances of application running it makes max 200. I don't really get how the database pool gets full when application can use 200 connections max and postrgesql config has set to 250 max connections ? Autovacuum and master-slave replication shouldn't use many connections also. Please explain that, maybe i'm missing something. Should we try to reduce this limit to 40 per application?
Re: [ADMIN] Random server overload
Viktor Juhanson sp...@planet.ee wrote: Btw we have the max pool size of web application 50 connections and since we have 4 instances of application running it makes max 200. I don't really get how the database pool gets full when application can use 200 connections max and postrgesql config has set to 250 max connections ? If the client application (or pooler) doesn't close the database connections gracefully, it might take some time before the server figures out the connection is dead and frees the resources on the server side. Does the server log say anything about broken connections or client not responding? Also, the pooler might maintanin some *minimum* number of connections but go beyond that on demand. Without knowing what pooler and how it is configured, it's hard to say what might be going on. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
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. 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! -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Random server overload
-Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- ow...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Random server overload
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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
