Re: Performance Bottleneck - Mailing list pgsql-performance

Scott Marlowe wrote:
> On Fri, 2004-08-06 at 22:02, Martin Foster wrote:
>
>>Scott Marlowe wrote:
>>
>>
>>>On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
>>>
>>>
>>>>Martin Foster wrote:
>>>>
>>>>
>>>>
>>>>>Gaetano Mendola wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Let start from your postgres configuration:
>>>>>>
>>>>>>shared_buffers = 8192    <==== This is really too small for your
>>>>>>configuration
>>>>>>sort_mem = 2048
>>>>>>
>>>>>>wal_buffers = 128    <==== This is really too small for your
>>>>>>configuration
>>>>>>
>>>>>>effective_cache_size = 16000
>>>>>>
>>>>>>change this values in:
>>>>>>
>>>>>>shared_buffers = 50000
>>>>>>sort_mem = 16084
>>>>>>
>>>>>>wal_buffers = 1500
>>>>>>
>>>>>>effective_cache_size = 32000
>>>>>>
>>>>>>
>>>>>>to bump up the shm usage you have to configure your OS in order to be
>>>>>>allowed to use that ammount of SHM.
>>>>>>
>>>>>>This are the numbers that I feel good for your HW, the second step now is
>>>>>>analyze your queries
>>>>>>
>>>>>
>>>>>These changes have yielded some visible improvements, with load averages
>>>>>rarely going over the anything noticeable.   However, I do have a
>>>>>question on the matter, why do these values seem to be far higher then
>>>>>what a frequently pointed to document would indicate as necessary?
>>>>>
>>>>>http://www.varlena.com/GeneralBits/Tidbits/perf.html
>>>>>
>>>>>I am simply curious, as this clearly shows that my understanding of
>>>>>PostgreSQL is clearly lacking when it comes to tweaking for the hardware.
>>>>
>>>>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.
>>>
>>>
>>>FYI, my school of tuning is to change one thing at a time some
>>>reasonable percentage (shared_buffers from 1000 to 2000) and measure the
>>>change under simulated load.  Make another change, test it, chart the
>>>shape of the change line.  It should look something like this for most
>>>folks:
>>>
>>>shared_buffers | q/s (more is better)
>>>100 | 20
>>>200 | 45
>>>400 | 80
>>>1000 | 100
>>>... levels out here...
>>>8000 | 110
>>>10000 | 108
>>>20000 | 40
>>>30000 | 20
>>>
>>>Note it going back down as we exceed our memory and start swapping
>>>shared_buffers.  Where that happens on your machine is determined by
>>>many things like your machine's memory, memory bandwidth, type of load,
>>>etc... but it will happen on most machines and when it does, it often
>>>happens at the worst times, under heavy parallel load.
>>>
>>>Unless testing shows it's faster, 10000 or 25% of mem (whichever is
>>>less) is usually a pretty good setting for shared_buffers.  Large data
>>>sets may require more than 10000, but going over 25% on machines with
>>>large memory is usually a mistake, especially servers that do anything
>>>other than just PostgreSQL.
>>>
>>>You're absolutely right about one thing, there's no automatic wizard for
>>>tuning this stuff.
>>>
>>
>>Which rather points out the crux of the problem.  This is a live system,
>>meaning changes made need to be as informed as possible, and that
>>changing values for the sake of testing can lead to potential problems
>>in service.
>
>
> But if you make those changes slowly, as I was showing, you should see
> the small deleterious effects like I was showing long before they become
> catastrophic.  To just jump shared_buffers to 50000 is not a good idea,
> especially if the sweet spot is likely lower than that.

As you can see 50000 are less then 20% of his total memory and I strongly
fell that 50000 is not oversized for his hardware ( as wal_buffers isn't),
may be could be for his database activity but for sure that value ( values )
can not be source of problems.

I'd like to have a wizard that could be run also for hours in order to find the
good compromise for all GUC parameters , may be a genetic algoritm can help.

Regards
Gaetano Mendola