From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Performance Bottleneck |
Date: | 2004-08-07 10:11:09 |
Message-ID: | 4114AABD.7050505@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane wrote:
> Martin Foster <martin(at)ethereal-realms(dot)org> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2004-08-07 10:49:50 | Re: Performance Bottleneck |
Previous Message | Gaetano Mendola | 2004-08-07 10:03:44 | Re: Performance Bottleneck |