Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5282B632DFD for ; Tue, 20 Apr 2010 15:12:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16696-04 for ; Tue, 20 Apr 2010 18:12:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 7F8626326BE for ; Tue, 20 Apr 2010 15:12:37 -0300 (ADT) Received: by wwb24 with SMTP id 24so3504944wwb.19 for ; Tue, 20 Apr 2010 11:12:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:received:message-id:subject:to:cc:content-type; bh=gvTDuGZUDsKQap141wEwU9tuOcFYfE+cQPfNYdOaYVU=; b=pW66rW+QX+H2CbfA566IjolnPGiyL8jTmU+Gt//AZtmxOfaf5aNacCuPrnvbYlbj6O 1UVkJVt/DbzkAIpH579mU6hnuqR6KkCGD3BaO4h72b1o2MbwMbfXFE0xzw2DSoDvJIwS guqoj6el9MIghrkBeVCS1wHfad4KnVgxzDBrw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=YYQ7lT/XjmwOZ9AAdojcfrrBPXDe8BKvS+0q028f1A3lis/duZ6zEmvS54TAMAeCm7 Su05DsSTIEdliHINCLAvc+R6fOeSM1Itp4A8bCH7WthPtAWEdHL/QhPeEkSG1n7puKlZ Q8kkUO2rA3Kdr0EsvknaECkKYUUartg4CMJv8= MIME-Version: 1.0 Received: by 10.216.58.204 with HTTP; Tue, 20 Apr 2010 11:12:15 -0700 (PDT) In-Reply-To: <20100420180351.GA51940@mr-paradox.net> References: <20100420173936.GA50886@mr-paradox.net> <20100420180351.GA51940@mr-paradox.net> From: Nikolas Everett Date: Tue, 20 Apr 2010 14:12:15 -0400 Received: by 10.216.87.75 with SMTP id x53mr405379wee.144.1271787155326; Tue, 20 Apr 2010 11:12:35 -0700 (PDT) Message-ID: Subject: Re: Very high effective_cache_size == worse performance? To: David Kerr Cc: Robert Haas , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6db29b5a032fe0484af05ac X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.345 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/245 X-Sequence-Number: 38495 --0016e6db29b5a032fe0484af05ac Content-Type: text/plain; charset=ISO-8859-1 On Tue, Apr 20, 2010 at 2:03 PM, David Kerr wrote: > that thought occured to me while I was testing this. I ran a vacuumdb -z > on my database during the load and it didn't impact performance at all. > > Incidentally the code is written to work like this : > > while (read X lines in file){ > Process those lines. > write lines to DB. > } > > So i would generally expect to get the benefits of the updated staticis > once the loop ended. no? (would prepared statements affect that possibly?) > > Also, while I was debugging the problem, I did load a 2nd file into the DB > ontop of one that had been loaded. So the statistics almost certinaly > should > have been decent at that point. > > I did turn on log_min_duration_statement but that caused performance to be > unbearable, > but i could turn it on again if it would help. > > Dave 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 --0016e6db29b5a032fe0484af05ac Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Tue, Apr 20, 2010 at 2:03 PM, David K= err <dmk@mr-para= dox.net> wrote:
that thought occured to me while I was testing this. I ra= n a vacuumdb -z
on my database during the load and it didn't impact performance at all.=

Incidentally the code is written to work like this :

while (read X lines in file){
Process those lines.
write lines to DB.
}

So i would generally expect to get the benefits of the updated staticis
once the loop ended. no? =A0(would prepared statements affect that possibly= ?)

Also, while I was debugging the problem, I did load a 2nd file into the DB<= br> ontop of one that had been loaded. So the statistics almost certinaly shoul= d
have been decent at that point.

I did turn on log_min_duration_statement but that caused performance to be = unbearable,
but i could turn it on again if it would help.

Dave

You can absolutely use copy if you lik= e but you need to use a non-standard jdbc driver: =A0kato.iki.fi/sw/db/postgresql/jdbc/copy= /. =A0I've used it in the past and it worked.

Is the whole thing going in in one transaction? =A0I= 9;m reasonably sure statistics aren't kept for uncommited transactions.=

For inserts the prepared statements can only help= . =A0For selects they can hurt because eventually the JDBC driver will turn= them into back end prepared statements that are only planned once. =A0The = 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 kil= led performance?

--Nik
--0016e6db29b5a032fe0484af05ac--