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.
>

While I agree, there are also issues with the fact that getting
consistent results from this site are very much difficult to do, since
it is based on the whims of users visiting one of three sites hosted on
the same hardware.

Now that being said, having wal_buffers at 8 certainly would not be a
good idea, since the database logs themselves were warning of excessive
writes in that region.    I am not hoping for a perfect intermix ratio,
that will solve all my problems.

But a good idea on a base that will allow me to gain a fair load would
certainly be a good option.   Right now, the load being handled is not
much more then a single processor system did with half the memory.
Certainly this architecture should be able to take more of a beating
then this?

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org