From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Dzmitry <dzmitry.nikitsin@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres crashes on insert in 40 different threads
Date: Sat, 24 Aug 2013 09:14:23 -0600
Message-ID: <2A3A3AC7-5C4A-411E-B13C-E40A55D0CEF0@elevated-dev.com> (raw)
In-Reply-To: <CE3819CB.11A34%dzmitry.nikitsin@gmail.com>
References: <CE3819CB.11A34%dzmitry.nikitsin@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On Aug 19, 2013, at 9:55 AM, Dzmitry <dzmitry.nikitsin@gmail.com> wrote:
> No, I am not using pgbouncer, I am using pgpool.
>
> Total - I have 440 connections to postgres(I have rails application
> running on some servers - each application setup 60 connections to DB and
> keep if forever(until will not be killed), also I have some machines that
> do background processing, that keep connections too).
>
> Part that do a lot of writes(that update jobs from xml feed every night) -
> have 40 threads and keep 40 connections.
That's extreme, and probably counter-productive.
How many cores do you have on those rails servers? Probably not 64, right? Not 32? 16? 12? 8, even? Assuming <64, what advantage do you expect from 60 connections? Same comment applies to the 40 connections doing the update jobs--more connections than cores is unlikely to be helping anything, and more connections than 2x cores is almost guaranteed to be worse than fewer.
Postgres connections are of the heavy-weight variety: process per connection, not thread per connection, not thread-per core event-driven. In particular, I'd worry about work_mem in your configuration. You've either got to set it really low and live with queries going to disk too quickly for sorts and so on, or have it a decent size and have the risk that too many queries at once will trigger OOM.
Given your configuration, I wouldn't even start with pgbouncer for connection pooling. I'd first just slash the number of connections everywhere by 1/2, or even 1/4 and see what effect that had. Then as a second step I'd look at where connection pooling might be used effectively.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, dzmitry.nikitsin@gmail.com
Subject: Re: postgres crashes on insert in 40 different threads
In-Reply-To: <2A3A3AC7-5C4A-411E-B13C-E40A55D0CEF0@elevated-dev.com>
References: <CE3819CB.11A34%dzmitry.nikitsin@gmail.com> <2A3A3AC7-5C4A-411E-B13C-E40A55D0CEF0@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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