Skip site navigation (1) Skip section navigation (2)

Re: Very high effective_cache_size == worse performance?

From: Nikolas Everett <nik9000(at)gmail(dot)com>
To: David Kerr <dmk(at)mr-paradox(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very high effective_cache_size == worse performance?
Date: 2010-04-20 18:12:15
Message-ID: w2ld4e11e981004201112v3571f350zaaa0112d1e035097@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk(at)mr-paradox(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-04-20 18:15:19
Subject: Re: Very high effective_cache_size == worse performance?
Previous:From: David KerrDate: 2010-04-20 18:03:51
Subject: Re: Very high effective_cache_size == worse performance?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group