Message-ID: <47CDEB58.7070102@vieonet.com>
Date: Wed, 05 Mar 2008 00:37:44 +0000
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Greg Smith <gsmith@gregsmith.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
References: <47CDCB62.9070302@vieonet.com>
	<Pine.GSO.4.64.0803041743060.22870@westnet.com>
In-Reply-To: <Pine.GSO.4.64.0803041743060.22870@westnet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Thanks i'm trying with this new settings. I gain only 3 second (2:40 vs 
  2:37 min) on a treatment of 1000 lines, with it's done every 2 minutes.

For the database version, i'm under postgresql 8.1.11. x64

As i'm in a procedure it seems that postgresql explain analyse doesn't 
give details.

I suppose that I have to fragment my procedure to see exactly where i'm 
wasting so much time.

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