pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRandom server overload
9+ messages / 6 participants
[nested] [flat]
* Random server overload
@ 2013-10-01 13:19 Viktor <viktor@okservers.eu>
0 siblings, 2 replies; 9+ messages in thread
From: Viktor @ 2013-10-01 13:19 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-01 13:45 Igor Neyman <ineyman@perceptron.com>
parent: Viktor <viktor@okservers.eu>
1 sibling, 1 reply; 9+ messages in thread
From: Igor Neyman @ 2013-10-01 13:45 UTC (permalink / raw)
To: Viktor <viktor@okservers.eu>; pgsql-admin
> -----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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-01 15:15 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Viktor <viktor@okservers.eu>
1 sibling, 0 replies; 9+ messages in thread
From: Albe Laurenz @ 2013-10-01 15:15 UTC (permalink / raw)
To: Viktor *EXTERN* <viktor@okservers.eu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-02 08:12 Viktor <viktor@okservers.eu>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 9+ messages in thread
From: Viktor @ 2013-10-02 08:12 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-02 13:34 Igor Neyman <ineyman@perceptron.com>
parent: Viktor <viktor@okservers.eu>
0 siblings, 1 reply; 9+ messages in thread
From: Igor Neyman @ 2013-10-02 13:34 UTC (permalink / raw)
To: Viktor <viktor@okservers.eu>; pgsql-admin
> -----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 as PgBouncer.
As you stated in original message you have multiple idle connections, that's waste of resources.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-02 14:58 Kevin Grittner <kgrittn@ymail.com>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2013-10-02 14:58 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; Viktor <viktor@okservers.eu>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-08 13:20 Viktor Juhanson <spoon@planet.ee>
0 siblings, 1 reply; 9+ messages in thread
From: Viktor Juhanson @ 2013-10-08 13:20 UTC (permalink / raw)
To: pgsql-admin
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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-08 15:59 Kevin Grittner <kgrittn@ymail.com>
parent: Viktor Juhanson <spoon@planet.ee>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Grittner @ 2013-10-08 15:59 UTC (permalink / raw)
To: Viktor Juhanson <spoon@planet.ee>; pgsql-admin
Viktor Juhanson <spoon@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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Random server overload
@ 2013-10-10 09:27 Viktor <spoon@planet.ee>
0 siblings, 0 replies; 9+ messages in thread
From: Viktor @ 2013-10-10 09:27 UTC (permalink / raw)
To: pgsql-admin
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-10-10 09:27 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-01 13:19 Random server overload Viktor <viktor@okservers.eu>
2013-10-01 13:45 ` Igor Neyman <ineyman@perceptron.com>
2013-10-02 08:12 ` Viktor <viktor@okservers.eu>
2013-10-02 13:34 ` Igor Neyman <ineyman@perceptron.com>
2013-10-02 14:58 ` Kevin Grittner <kgrittn@ymail.com>
2013-10-01 15:15 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-10-08 13:20 Re: Random server overload Viktor Juhanson <spoon@planet.ee>
2013-10-08 15:59 ` Kevin Grittner <kgrittn@ymail.com>
2013-10-10 09:27 Re: Random server overload Viktor <spoon@planet.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox