Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VDFXu-0004uE-1Y for pgsql-admin@arkaria.postgresql.org; Sat, 24 Aug 2013 15:14:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VDFXt-0001XP-Hb for pgsql-admin@arkaria.postgresql.org; Sat, 24 Aug 2013 15:14:33 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VDFXs-0001XI-DC for pgsql-admin@postgresql.org; Sat, 24 Aug 2013 15:14:32 +0000 Received: from asbnvacz-mailrelay01.megapath.net ([207.145.128.243]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VDFXo-0006bP-8g for pgsql-admin@postgresql.org; Sat, 24 Aug 2013 15:14:30 +0000 Received: from mail6.sea5.speakeasy.net (mail6.sea5.speakeasy.net [69.17.117.50]) by asbnvacz-mailrelay01.megapath.net (Postfix) with ESMTP id 7BBEF1EE503F for ; Sat, 24 Aug 2013 11:14:26 -0400 (EDT) Received: (qmail 11468 invoked from network); 24 Aug 2013 15:14:25 -0000 Received: by simscan 1.4.0 ppid: 5859, pid: 25450, t: 1.2199s scanners: clamav: 0.88.2/m:52/d:10739 spam: 3.0.4 Received: from 205-168-220-137.dia.static.qwest.net (HELO [192.168.4.16]) (sribe@elevated-dev.com@[205.168.220.137]) (envelope-sender ) by mail6.sea5.speakeasy.net (qmail-ldap-1.03) with AES128-SHA encrypted SMTP for ; 24 Aug 2013 15:14:24 -0000 Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\)) Subject: Re: postgres crashes on insert in 40 different threads From: Scott Ribe In-Reply-To: Date: Sat, 24 Aug 2013 09:14:23 -0600 Cc: Content-Transfer-Encoding: quoted-printable Message-Id: <2A3A3AC7-5C4A-411E-B13C-E40A55D0CEF0@elevated-dev.com> References: To: Dzmitry X-Mailer: Apple Mail (2.1508) X-Spam-Checker-Version: SpamAssassin 3.0.4 (2005-06-05) on mail6.sea5 X-Spam-Level: X-Spam-Status: No, score=0.0 required=8.0 tests=none autolearn=disabled version=3.0.4 X-Pg-Spam-Score: -0.0 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org On Aug 19, 2013, at 9:55 AM, Dzmitry wrote: > No, I am not using pgbouncer, I am using pgpool. >=20 > 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). >=20 > 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 j= obs--more connections than cores is unlikely to be helping anything, and mo= re connections than 2x cores is almost guaranteed to be worse than fewer.=20 Postgres connections are of the heavy-weight variety: process per connectio= n, not thread per connection, not thread-per core event-driven. In particul= ar, I'd worry about work_mem in your configuration. You've either got to se= t it really low and live with queries going to disk too quickly for sorts a= nd 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 connecti= on 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. --=20 Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin