From: Nikolas Everett <nik9000@gmail.com>
To: David Kerr <dmk@mr-paradox.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very high effective_cache_size == worse performance?
Date: Tue, 20 Apr 2010 14:12:15 -0400
Message-ID: <w2ld4e11e981004201112v3571f350zaaa0112d1e035097@mail.gmail.com> (raw)
In-Reply-To: <20100420180351.GA51940@mr-paradox.net>
References: <20100420173936.GA50886@mr-paradox.net>
<h2w603c8f071004201044tbc8ce8fenb846b7643a2b9376@mail.gmail.com>
<20100420180351.GA51940@mr-paradox.net>
On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> 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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nik9000@gmail.com, dmk@mr-paradox.net, robertmhaas@gmail.com
Subject: Re: Very high effective_cache_size == worse performance?
In-Reply-To: <w2ld4e11e981004201112v3571f350zaaa0112d1e035097@mail.gmail.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