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

Re: Very big insert/join performance problem (bacula)

From: Marc Cousin <cousinmarc(at)gmail(dot)com>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Alvaro Herrera" <alvherre(at)alvh(dot)no-ip(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very big insert/join performance problem (bacula)
Date: 2009-07-16 22:03:24
Message-ID: 200907170003.24498.cousinmarc@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Le Thursday 16 July 2009 23:54:54, Kevin Grittner a écrit :
> Marc Cousin <cousinmarc(at)gmail(dot)com> wrote:
> > to sum it up, should I keep these values (I hate doing this :) ) ?
>
> Many people need to set the random_page_cost and/or seq_page_cost to
> reflect the overall affect of caching on the active portion of the
> data.  We set our fully-cached databases to 0.1 for both.  Databases
> with less caching usually wind up at 2 and 1.  We have one database
> which does best at 0.5 and 0.3.  My advice is to experiment and try to
> find a pair of settings which works well for most or all of your
> queries.  If you have a few which need a different setting, you can
> set a special value right before running the query, but I've always
> been able to avoid that (thankfully).
>
> > Would there be a way to approximately evaluate them regarding to
> > the expected buffer hit ratio of the query ?
>
> Nothing query-specific except setting them on the connection right
> before the query (and setting them back or discarding the connection
> afterward).  Well, that and making sure that effective_cache_size
> reflects reality.
>
> -Kevin


OK, thanks a lot.

A last thing :

As mentionned in another mail from the thread (from Richard Huxton), I felt 
this message in the documentation a bit misleading :

effective_cache_size (integer)
 Sets the planner's assumption about the effective size of the disk cache that 
is available to a single query

I don't really know what the 'a single query' means. I interpreted that as 
'divide it by the amount of queries typically running in parallel on the 
database'. Maybe it should be rephrased ? (I may not be the one 
misunderstanding it).

In response to

Responses

pgsql-performance by date

Next:From: Devin Ben-HurDate: 2009-07-16 22:29:48
Subject: Re: Very big insert/join performance problem (bacula)
Previous:From: Kevin GrittnerDate: 2009-07-16 21:54:54
Subject: Re: Very big insert/join performance problem (bacula)

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