Re: Random server overload

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-
> owner(at)postgresql(dot)org] On Behalf Of Viktor
> Sent: Tuesday, October 01, 2013 9:19 AM
> To: pgsql-admin(at)postgresql(dot)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