Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E2E3E632F34 for ; Tue, 20 Apr 2010 15:28:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 70115-07 for ; Tue, 20 Apr 2010 18:28:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from vexbert.mr-paradox.net (vexbert.mr-paradox.net [198.145.180.174]) by mail.postgresql.org (Postfix) with ESMTP id 3D58B6326BE for ; Tue, 20 Apr 2010 15:28:33 -0300 (ADT) Received: by vexbert.mr-paradox.net (Postfix, from userid 18391) id 43364845CD; Tue, 20 Apr 2010 14:28:32 -0400 (EDT) Date: Tue, 20 Apr 2010 11:28:32 -0700 From: David Kerr To: Scott Marlowe Cc: Nikolas Everett , Robert Haas , pgsql-performance@postgresql.org Subject: Re: Very high effective_cache_size == worse performance? Message-ID: <20100420182832.GB53489@mr-paradox.net> References: <20100420173936.GA50886@mr-paradox.net> <20100420180351.GA51940@mr-paradox.net> <20100420182030.GA53489@mr-paradox.net> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.4.2.3i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.084 tagged_above=-10 required=5 tests=AWL=-0.174, BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/253 X-Sequence-Number: 38503 On Tue, Apr 20, 2010 at 12:23:51PM -0600, Scott Marlowe wrote: - On Tue, Apr 20, 2010 at 12:20 PM, David Kerr wrote: - > On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote: - > - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr wrote: - > - - > - You can absolutely use copy if you like but you need to use a non-standard - > - jdbc driver:  kato.iki.fi/sw/db/postgresql/jdbc/copy/.  I've used it in the - > - past and it worked. - > - - > - Is the whole thing going in in one transaction?  I'm reasonably sure - > - statistics aren't kept for uncommited transactions. - > - - > - For inserts the prepared statements can only help.  For selects they can - > - hurt because eventually the JDBC driver will turn them into back end - > - prepared statements that are only planned once.  The price here is that that - > - plan may not be the best plan for the data that you throw at it. - > - - > - What was log_min_duration_statement logging that it killed performance? - > - - > - --Nik - > - > Good to know about the jdbc-copy. but this is a huge project and the load is - > just one very very tiny component, I don't think we could introduce anything - > new to assist that. - > - > It's not all in one tx. I don't have visibility to the code to determine how - > it's broken down, but most likely each while loop is a tx. - > - > I set it to log all statements (i.e., = 0.). that doubled the load time from - > ~15 to ~30 hours. I could, of course, be more granular if it would be helpful. - - So are you logging to the same drive that has pg_xlog and your - data/base directory on this machine? - the db, xlog and logs are all on separate areas of the SAN. separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect contention there. I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng on my dev environments that mostly resoved the probelm for me. but these machines still have vanilla syslog. Dave