Re: explanation of some configs - Mailing list pgsql-performance

Thomas Finneid wrote:
> Joshua D. Drake wrote:
>>> On Sat, 2009-02-07 at 01:52 +0100, Thomas Finneid wrote:
> >>
>>> effective_cache_size
> >>
>> This is just a hint to tell the planner how much cache will generally be
>> available.
>
> ok, but available for what?
for storing the data/tables/rows in memory so it does not have the disk
subsystem.
>
>> The number should be reflective of your shared buffers +
>> available operating system cache. If you database is postgresql only you
>> can generally set this very high 75% of available ram. If not then you
>> need to tone it down.
>
> So that means, if I have 8GB ram and have set shared buffer to e.g.
> 4GB, I should set the effective_cache_size to at least 4GB otherwise
> the planner will assume I dont have as much memory available so it
> would be sort of pointless so set shared_buffer to 4GB?
No because other parts of Postgresql use the shared_buffer to store
tables/data in memory.  If shared_buffer is set low then the system will
be during more disk IO as it can't fit that much into memory.  Another
critical setting to look at is work_mem where all the complex sorting,
and joins are done which is not related to shared buffers.  So leave
room in memory for these processes
>
>
>>> checkpoint_segments
>>>     - specifies the number of segments?
>>
>> The number of segments that will be used before a checkpoint is forced.
>
> So to sum it up:
>
> - Specifies the number of memory segments the WAL will use before a
> checkpoint occur. (A checkpoint guarantees the data has been written
> to disk, including dirty pages.)
> - A segment is 16MB and the number of actually used segments are dynamic.
> - If this number is too low or the transaction is large, PG will spend
> more time on performing checkpoint operations which decreases
> performance.
>
> Q1:
>
> So checkpoint_time is then just another way of expressing the same?
> I.e. to ensure that if the segments have not been filled, which would
> foce a checkpoint, a checkpoint is at least forced at the specified
> time lapse?
Yes and No  Checkpoint_time does forces a check point regardless if the
segment is full or not.  Checkpoint_segment is used to force a check
point  based on size.  In a big databases a checkpoint could get very
large before time had elapsed and if server cashed all that work would
be rolled back.

>
> Q2:
>
> So how does this relate to WAL buffers? It seems to me that
> wal_buffers are not needed. Based on the above explanation.
This is  number of pages in shared memory the Postgresql uses before WAL
is written to disk this is used to improve performance for large writes.

>
>
> regards
>
> thomas
>