From: Greg Smith <gsmith@gregsmith.com>
To: dforums <dforums@vieonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
Date: Tue, 4 Mar 2008 17:59:47 -0500 (EST)
Message-ID: <Pine.GSO.4.64.0803041743060.22870@westnet.com> (raw)
In-Reply-To: <47CDCB62.9070302@vieonet.com>
References: <47CDCB62.9070302@vieonet.com>
On Tue, 4 Mar 2008, dforums wrote:
> max_connections = 256
> shared_buffers = 1500 # min 16 or max_connections*2, 8KB each
> work_mem = 22000 # min 64, size in KB
> effective_cache_size = 2048 # typically 8KB each
Well, you're giving the main database server a whopping 1500*8K=12MB of
space to work with. Meanwhile you're giving each of the 256 clients up to
22MB of work_mem, which means they can use 5.6GB total. This is quite
backwards.
Increase shared_buffers to something like 250000 (2GB), decrease work_mem
to at most 10000 and probably lower, and raise effective_cache_size to
something like 5GB=625000. Whatever data you've collected about
performance with your current settings is pretty much meaningless with
only giving 12MB of memory to shared_buffers and having a tiny setting for
effective_cache_size.
Oh, and make sure you ANALYZE your tables regularly.
> random_page_cost = 3
And you shouldn't be playing with that until you've got the memory usage
to something sane.
Also, you didn't mention what version of PostgreSQL you're using. You'll
need 8.1 or later to have any hope of using 8GB of RAM effectively on a
4-core system.
> But My most fear is that for now the database is only of 10 Go. But I
> will have to increase it 10 times during the next six month I'm afraid
> that these problems will increase.
It's very unlikely you will be able to get good performance on a 100GB
database with a single SATA drive. You should be able to get great
performance with the current size though.
> In regards of update, I have around 10000 updates while a laps of 10
> minutes. Is there a settings to optimise updates ?
10000 updates / 600 seconds = 17 updates/second. That's trivial; even a
single boring drive can get 100/second. As someone already suggested your
real problem here is that you'll be hard pressed to handle the amount of
seeking that goes into a larger database with only a single drive.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
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: gsmith@gregsmith.com, dforums@vieonet.com
Subject: Re: Optimisation help
In-Reply-To: <Pine.GSO.4.64.0803041743060.22870@westnet.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