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

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Marc Cousin <cousinmarc(at)gmail(dot)com>, 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-24 03:48:27
Message-ID: 603c8f070907232048i58d90e5ep1170f2c51519cb6d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Jul 16, 2009 at 6:30 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Marc Cousin <cousinmarc(at)gmail(dot)com> wrote:
>
>> 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).
>
> I'm afraid I'll have to let someone else speak to that; I only have a
> vague sense of its impact.  I've generally gotten good results setting
> that to the available cache space on the machine.  If I'm running
> multiple database clusters on one machine, I tend to hedge a little
> and set it lower to allow for some competition.

It really has very little impact.  It only affects index scans, and
even then only if effective_cache_size is less than the size of the
table.

Essentially, when this kicks in, it models the effect that if you are
index scanning a table much larger than the size of your cache, you
might have to reread some blocks that you previously read in during
*that same index scan*.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Marc CousinDate: 2009-07-24 05:13:06
Subject: Re: Very big insert/join performance problem (bacula)
Previous:From: WANGRUNGVICHAISRI, SHIVESHDate: 2009-07-23 23:47:56
Subject: Configuring Postgresql for writing BLOB at a high-rate

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