Re: Performance Bottleneck

Tom Lane wrote:

> Martin Foster  writes:
> 
>>Gaetano Mendola wrote:
>>
>>>change this values in:
>>>shared_buffers = 50000
>>>sort_mem = 16084
>>>
>>>wal_buffers = 1500
> 
> 
> This value of wal_buffers is simply ridiculous.

Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default.

> There isn't any reason to set wal_buffers higher than the amount of
> WAL log data that will be generated by a single transaction, because
> whatever is in the buffers will be flushed at transaction commit.
> If you are mainly dealing with heavy concurrency then it's the mean time
> between transaction commits that matters, and that's even less than the
> average transaction length.

I partially agree with you, tell me how decide that value without
even now the typical queries, the tipical load ... nothing.
I suggested to OP to keep the wal_buffers so high in order to eliminate one
freedom of degree in his performance problems. You can see from following reply,


========================================================================
Gaetano Mendola wrote:
Unfortunately there is no a "wizard tuning" for postgres so each one of
us have a own "school". The data I gave you are oversized to be sure
to achieve improvements. Now you can start to decrease these values
( starting from the wal_buffers ) in order to find the good compromise
with your HW.
========================================================================

However wal_buffers = 1500 means ~12 MB that are not so expensive considering
a server with 2GB of ram and I think that is a good compromise if you are not
starving for RAM.


I had a discussion about how fine tuning a postgres server with a client,
my question was: are you planning to have someone that periodically take a
look at your server activities in order to use your hardware at the best?
Easy answer: No, because when the server is overloaded I will buy a bigger
one that is less expensive that pay someone, considering also that shareolders
prefer increase the capex that pay salaries ( if the company close the hardware
can be selled :-(  ).

This is the real world out there.



Regards
Gaetano Mendola