Обсуждение: 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