From: dforums <dforums@vieonet.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
Date: Wed, 05 Mar 2008 00:54:51 +0000
Message-ID: <47CDEF5B.8080905@vieonet.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0803041743060.22870@westnet.com>
References: <47CDCB62.9070302@vieonet.com>
<Pine.GSO.4.64.0803041743060.22870@westnet.com>
Hello,
After controling the settings I so, that shared_buffers is configurated
at 1024 (the default), however, in my postgresql.conf I set it to
250000, is it due to shared memory settings, should I increase shmmax?
regards
david
Greg Smith a écrit :
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your Subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
>
>
>
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: dforums@vieonet.com, gsmith@gregsmith.com
Subject: Re: Optimisation help
In-Reply-To: <47CDEF5B.8080905@vieonet.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