From: Gaetano Mendola <mendola@bigfoot.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance Bottleneck
Date: Sat, 07 Aug 2004 12:11:09 +0200
Message-ID: <4114AABD.7050505@bigfoot.com> (raw)
In-Reply-To: <24609.1091836821@sss.pgh.pa.us>
References: <ktQPc.23297$yT2.16983@clgrps13>
<410FDC53.5080509@bigfoot.com>
<X0ZPc.23543$yT2.23053@clgrps13>
<4110FFF6.1030001@bigfoot.com>
<41140D17.7070101@ethereal-realms.org>
<24609.1091836821@sss.pgh.pa.us>
Tom Lane wrote:
> Martin Foster <martin@ethereal-realms.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mendola@bigfoot.com, tgl@sss.pgh.pa.us
Subject: Re: Performance Bottleneck
In-Reply-To: <4114AABD.7050505@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox