From ahodgson@simkin.ca Tue Mar  4 21:03:16 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8A0012E01EB
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 17:03:16 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 92398-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 17:03:09 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from skynet.simkin.ca (skynet.simkin.ca [72.51.27.117])
	by postgresql.org (Postfix) with ESMTP id 236DB2E0088
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 17:03:13 -0400 (AST)
Received: from charon.medialogik.com (charon.medialogik.com [72.51.27.114])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by skynet.simkin.ca (Postfix) with ESMTP id 07A1C4714
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 13:03:11 -0800 (PST)
From: Alan Hodgson <ahodgson@simkin.ca>
Organization: Simkin Network Consulting
To: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
Date: Tue, 4 Mar 2008 13:03:58 -0800
User-Agent: KMail/1.9.6 (enterprise 0.20070907.709405)
References: <47CDCB62.9070302@vieonet.com>
In-Reply-To: <47CDCB62.9070302@vieonet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200803041303.58321@hal.medialogik.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/59
X-Sequence-Number: 29013

On Tuesday 04 March 2008, dforums <dforums@vieonet.com> wrote:
>  Hello
>
>
>  We hace a Quad Xeon server, with 8GO of ram, sata II 750Go
>
>
>  I suppose the main problem is from database server settings.

No, the problem is your hard drive is too slow. One drive can only do maybe 
150 seeks per second.

Oh, and updates in PostgreSQL are expensive. But mostly I'd say it's your 
drive.

-- 
Alan



From dforums@vieonet.com Tue Mar  4 20:55:51 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A2AA72E01F6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 16:55:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 08392-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 16:55:45 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42])
	by postgresql.org (Postfix) with SMTP id BF9B12E01BA
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 16:55:44 -0400 (AST)
Received: (qmail 23430 invoked by uid 503); 4 Mar 2008 20:55:46 -0000
Received: from gw2.ovh.net (HELO mail186.ha.ovh.net) (213.251.189.202)
	by 42.mail-out.ovh.net with SMTP; 4 Mar 2008 20:55:46 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 4 Mar 2008 20:55:20 -0000
Received: from ita73-2-82-246-12-243.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.246.12.243)
	by ns0.ovh.net with SMTP; 4 Mar 2008 20:55:19 -0000
Message-ID: <47CDCB62.9070302@vieonet.com>
Date: Tue, 04 Mar 2008 22:21:22 +0000
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Optimisation help
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Ovh-Remote: 82.246.12.243 (ita73-2-82-246-12-243.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.673 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672
X-Spam-Level: *
X-Archive-Number: 200803/58
X-Sequence-Number: 29012

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000099">
<div class="moz-text-html" lang="x-western">
Hello<br>
<br>
<br>
We hace a Quad Xeon server, with 8GO of ram, sata II 750Go<br>
<br>
An postgresql database, of 10 Go<br>
<br>
I have several treatment every 2 minutes who select, insert, update
thousand of data in a table. It take a lot of time (0.3300 ms per line)
just to check if a string of 15 char is present, and decide to update
it under few constraint<br>
<br>
I suppose the main problem is from database server settings.<br>
<br>
This is my settings : <br>
<br>
<br>
max_connections = 256<br>
shared_buffers = 1500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min 16 or max_connections*2,
8KB each<br>
temp_buffers = 500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min 100, 8KB each<br>
max_prepared_transactions = 100 <br>
<br>
work_mem = 22000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min 64, size in KB<br>
maintenance_work_mem = 500000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min 1024, size in KB<br>
max_stack_depth = 8192 <br>
<br>
<br>
max_fsm_pages = 100000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min max_fsm_relations*16, 6
bytes each<br>
max_fsm_relations = 5000&nbsp; <br>
<br>
<br>
vacuum_cost_delay = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-1000 milliseconds<br>
vacuum_cost_page_hit = 1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-10000 credits<br>
vacuum_cost_page_miss = 1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-10000 credits<br>
vacuum_cost_page_dirty = 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-10000 credits<br>
vacuum_cost_limit = 2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-10000 credits<br>
<br>
# - Background writer -<br>
<br>
bgwriter_delay = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 10-10000 milliseconds between
rounds<br>
bgwriter_lru_percent = 1.0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-100% of LRU buffers
scanned/round<br>
bgwriter_lru_maxpages = 25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-1000 buffers max
written/round<br>
bgwriter_all_percent = 0.333&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-100% of all buffers
scanned/round<br>
bgwriter_all_maxpages = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # 0-1000 buffers max
written/round<br>
<br>
wal_buffers = 16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min 4, 8KB each<br>
commit_delay = 500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # range 0-100000, in
microseconds<br>
commit_siblings = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # range 1-1000<br>
<br>
# - Checkpoints -<br>
<br>
checkpoint_segments = 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # in logfile segments, min 1,
16MB each<br>
checkpoint_timeout = 1800&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # range 30-3600, in seconds<br>
checkpoint_warning = 180&nbsp;&nbsp;&nbsp; <br>
<br>
effective_cache_size = 2048&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # typically 8KB each<br>
random_page_cost = 3&nbsp;&nbsp; <br>
<br>
<br>
Shared memory set to :<br>
echo /proc/sys/kernel/shmmax = 256000000<br>
<br>
Could you help&nbsp; please...<br>
<br>
tx<br>
<br>
<br>
David<br>
<br>
<br>
<br>
<br>
<br>
<br>
</div>
</body>
</html>



From sgunderson@bigfoot.com Tue Mar  4 23:34:48 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id D640F2E014E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:34:48 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 33093-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:34:39 -0400 (AST)
X-Greylist: delayed 00:39:48.597162 by SQLgrey-1.7.5
Received: from cassarossa.samfundet.no (cassarossa.samfundet.no
	[129.241.93.19])
	by postgresql.org (Postfix) with ESMTP id 556D72E0B78
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 19:34:44 -0400 (AST)
Received: from trofast.ipv6.sesse.net ([2001:700:300:1803:20e:cff:fe36:a766]
	helo=trofast.sesse.net) by cassarossa.samfundet.no with esmtps
	(TLS-1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.63)
	(envelope-from <sesse@samfundet.no>)
	id 1JWg21-0000Mp-QJ; Tue, 04 Mar 2008 23:54:46 +0100
Received: from root by trofast.sesse.net with local (Exim 4.69)
	(envelope-from <sesse@samfundet.no>)
	id 1JWg21-0003qQ-4e; Tue, 04 Mar 2008 23:54:45 +0100
Date: Tue, 4 Mar 2008 23:54:45 +0100
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: dforums <dforums@vieonet.com>
Cc: Alan Hodgson <ahodgson@simkin.ca>, pgsql-performance@postgresql.org
Subject: Re: Optimisation help
Message-ID: <20080304225445.GA14720@uio.no>
Mail-Followup-To: dforums <dforums@vieonet.com>,
	Alan Hodgson <ahodgson@simkin.ca>, pgsql-performance@postgresql.org
References: <47CDCB62.9070302@vieonet.com>
	<200803041303.58321@hal.medialogik.com>
	<47CDE61D.9070906@vieonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <47CDE61D.9070906@vieonet.com>
X-Operating-System: Linux 2.6.20.4 on a x86_64
User-Agent: Mutt/1.5.17+20080114 (2008-01-14)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/65
X-Sequence-Number: 29019

On Wed, Mar 05, 2008 at 12:15:25AM +0000, dforums wrote:
> In regards of update, I have around 10000 updates while a laps of 10 minutes
>
> Is there a settings to optimise updates ?

If you can, batch them into a single transaction.

If you can, upgrade to 8.3. HOT might help you here.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



From gsmith@gregsmith.com Tue Mar  4 22:59:52 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 33E8C2E2E06
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:59:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 50605-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:59:48 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from westnet.com (westnet.com [216.187.52.2])
	by postgresql.org (Postfix) with ESMTP id B525E2E2D60
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 18:59:48 -0400 (AST)
Received: from westnet.com (localhost [127.0.0.1])
	by westnet.com (8.14.0/8.14.0) with ESMTP id m24MxlkO027660;
	Tue, 4 Mar 2008 17:59:47 -0500 (EST)
Received: from localhost (gsmith@localhost)
	by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m24Mxl2R027655;
	Tue, 4 Mar 2008 17:59:47 -0500 (EST)
X-Authentication-Warning: westnet.com: gsmith owned process doing -bs
Date: Tue, 4 Mar 2008 17:59:47 -0500 (EST)
From: Greg Smith <gsmith@gregsmith.com>
X-X-Sender: gsmith@westnet.com
To: dforums <dforums@vieonet.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
In-Reply-To: <47CDCB62.9070302@vieonet.com>
Message-ID: <Pine.GSO.4.64.0803041743060.22870@westnet.com>
References: <47CDCB62.9070302@vieonet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/62
X-Sequence-Number: 29016

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



From dforums@vieonet.com Tue Mar  4 22:44:47 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 615762E2D48
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:44:47 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 19089-02-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:44:39 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42])
	by postgresql.org (Postfix) with SMTP id 36C522E2974
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 18:44:44 -0400 (AST)
Received: (qmail 4433 invoked by uid 503); 4 Mar 2008 22:44:45 -0000
Received: from gw2.ovh.net (HELO mail186.ha.ovh.net) (213.251.189.202)
	by 42.mail-out.ovh.net with SMTP; 4 Mar 2008 22:44:45 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 4 Mar 2008 22:44:19 -0000
Received: from ita73-2-82-246-12-243.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.246.12.243)
	by ns0.ovh.net with SMTP; 4 Mar 2008 22:44:18 -0000
Message-ID: <47CDE4EA.8020206@vieonet.com>
Date: Wed, 05 Mar 2008 00:10:18 +0000
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Alan Hodgson <ahodgson@simkin.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
References: <47CDCB62.9070302@vieonet.com>
	<200803041303.58321@hal.medialogik.com>
In-Reply-To: <200803041303.58321@hal.medialogik.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Ovh-Remote: 82.246.12.243 (ita73-2-82-246-12-243.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.527 tagged_above=0 required=5 tests=AWL=0.527
X-Spam-Level: 
X-Archive-Number: 200803/60
X-Sequence-Number: 29014

tX for your reply,

I do not have more information on disk speed. I'll get it latter.

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.

Regards

David

Alan Hodgson a écrit :
> On Tuesday 04 March 2008, dforums <dforums@vieonet.com> wrote:
>>  Hello
>>
>>
>>  We hace a Quad Xeon server, with 8GO of ram, sata II 750Go
>>
>>
>>  I suppose the main problem is from database server settings.
> 
> No, the problem is your hard drive is too slow. One drive can only do maybe 
> 150 seeks per second.
> 
> Oh, and updates in PostgreSQL are expensive. But mostly I'd say it's your 
> drive.
> 



From dforums@vieonet.com Tue Mar  4 22:49:54 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 105892E2E5A
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:49:54 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 48300-01-8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 18:49:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42])
	by postgresql.org (Postfix) with SMTP id 034322E2E3F
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 18:49:50 -0400 (AST)
Received: (qmail 18097 invoked by uid 503); 4 Mar 2008 22:49:52 -0000
Received: from gw2.ovh.net (HELO mail186.ha.ovh.net) (213.251.189.202)
	by 42.mail-out.ovh.net with SMTP; 4 Mar 2008 22:49:52 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 4 Mar 2008 22:49:27 -0000
Received: from ita73-2-82-246-12-243.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.246.12.243)
	by ns0.ovh.net with SMTP; 4 Mar 2008 22:49:25 -0000
Message-ID: <47CDE61D.9070906@vieonet.com>
Date: Wed, 05 Mar 2008 00:15:25 +0000
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Alan Hodgson <ahodgson@simkin.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimisation help
References: <47CDCB62.9070302@vieonet.com>
	<200803041303.58321@hal.medialogik.com>
In-Reply-To: <200803041303.58321@hal.medialogik.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Ovh-Remote: 82.246.12.243 (ita73-2-82-246-12-243.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.439 tagged_above=0 required=5 tests=AWL=0.439
X-Spam-Level: 
X-Archive-Number: 200803/61
X-Sequence-Number: 29015

In regards of update, I have around 10000 updates while a laps of 10 minutes

Is there a settings to optimise updates ?

regards

david

Alan Hodgson a écrit :
> On Tuesday 04 March 2008, dforums <dforums@vieonet.com> wrote:
>>  Hello
>>
>>
>>  We hace a Quad Xeon server, with 8GO of ram, sata II 750Go
>>
>>
>>  I suppose the main problem is from database server settings.
> 
> No, the problem is your hard drive is too slow. One drive can only do maybe 
> 150 seeks per second.
> 
> Oh, and updates in PostgreSQL are expensive. But mostly I'd say it's your 
> drive.
> 



From erik@myemma.com Wed Mar  5 00:19:43 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 92B692E2D3B
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 20:19:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 46357-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 20:19:33 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from smtp190.iad.emailsrvr.com (smtp190.iad.emailsrvr.com
	[207.97.245.190])
	by postgresql.org (Postfix) with ESMTP id C5F1D2E2B91
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 20:19:39 -0400 (AST)
Received: from relay9.relay.iad.mlsrvr.com (localhost [127.0.0.1])
	by relay9.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id BDE301B400F;
	Tue,  4 Mar 2008 19:19:38 -0500 (EST)
Received: by relay9.relay.iad.mlsrvr.com (Authenticated sender:
	erik-AT-myemma.com) with ESMTP id 91EE91B40E6;
	Tue,  4 Mar 2008 19:19:38 -0500 (EST)
Cc: Greg Smith <gsmith@gregsmith.com>,
 pgsql-performance@postgresql.org
Message-Id: <82845731-D41C-4A79-9937-4A03CF455B36@myemma.com>
From: Erik Jones <erik@myemma.com>
To: dforums <dforums@vieonet.com>
In-Reply-To: <47CDEF5B.8080905@vieonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v915)
Subject: Re: Optimisation help
Date: Tue, 4 Mar 2008 18:19:32 -0600
References: <47CDCB62.9070302@vieonet.com>
	<Pine.GSO.4.64.0803041743060.22870@westnet.com>
	<47CDEF5B.8080905@vieonet.com>
X-Mailer: Apple Mail (2.915)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/66
X-Sequence-Number: 29020


On Mar 4, 2008, at 6:54 PM, dforums wrote:

> Hello,
>
> After controling the settings I so, that shared_buffers is =20
> configurated at 1024 (the default), however, in my postgresql.conf I =20=

> set it to 250000, is it due to shared memory settings, should I =20
> increase shmmax?

Did you do a full restart of the db cluster?  Changes to shared memory =20=

settings require that.

Erik Jones

DBA | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com






From dforums@vieonet.com Tue Mar  4 23:12:12 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 7372A2E0280
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:12:12 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 24866-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:12:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42])
	by postgresql.org (Postfix) with SMTP id 54B7C2E0274
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 19:12:08 -0400 (AST)
Received: (qmail 31450 invoked by uid 503); 4 Mar 2008 23:12:09 -0000
Received: from gw2.ovh.net (HELO mail186.ha.ovh.net) (213.251.189.202)
	by 42.mail-out.ovh.net with SMTP; 4 Mar 2008 23:12:09 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 4 Mar 2008 23:11:44 -0000
Received: from ita73-2-82-246-12-243.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.246.12.243)
	by ns0.ovh.net with SMTP; 4 Mar 2008 23:11:44 -0000
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
X-Ovh-Remote: 82.246.12.243 (ita73-2-82-246-12-243.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.500011/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.376 tagged_above=0 required=5 tests=AWL=0.376
X-Spam-Level: 
X-Archive-Number: 200803/63
X-Sequence-Number: 29017

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



From dforums@vieonet.com Tue Mar  4 23:29:23 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 812E62E2E92
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:29:23 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 31942-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Mar 2008 19:29:14 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from 42.mail-out.ovh.net (42.mail-out.ovh.net [213.251.189.42])
	by postgresql.org (Postfix) with SMTP id 7DD8C2E2E8D
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Mar 2008 19:29:16 -0400 (AST)
Received: (qmail 11347 invoked by uid 503); 4 Mar 2008 23:29:17 -0000
Received: from gw2.ovh.net (HELO mail186.ha.ovh.net) (213.251.189.202)
	by 42.mail-out.ovh.net with SMTP; 4 Mar 2008 23:29:17 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 4 Mar 2008 23:28:52 -0000
Received: from ita73-2-82-246-12-243.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.246.12.243)
	by ns0.ovh.net with SMTP; 4 Mar 2008 23:28:51 -0000
Message-ID: <47CDEF5B.8080905@vieonet.com>
Date: Wed, 05 Mar 2008 00:54:51 +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
X-Ovh-Remote: 82.246.12.243 (ita73-2-82-246-12-243.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.329 tagged_above=0 required=5 tests=AWL=0.329
X-Spam-Level: 
X-Archive-Number: 200803/64
X-Sequence-Number: 29018

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



From dforums@vieonet.com Wed Mar  5 08:25:18 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 86BBA2E00E8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed,  5 Mar 2008 04:25:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 98529-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed,  5 Mar 2008 04:25:09 -0400 (AST)
X-Greylist: delayed 00:06:40.861281 by SQLgrey-1.7.5
Received: from 26.mail-out.ovh.net (26.mail-out.ovh.net [91.121.27.225])
	by postgresql.org (Postfix) with SMTP id 939AA2E00E3
	for <pgsql-performance@postgresql.org>;
	Wed,  5 Mar 2008 04:25:12 -0400 (AST)
Received: (qmail 24523 invoked by uid 503); 5 Mar 2008 08:18:52 -0000
Received: (QMFILT: 1.0); 05 Mar 2008 08:18:52 -0000
Received: from b7.ovh.net (HELO mail153.ha.ovh.net) (213.186.33.57)
	by 26.mail-out.ovh.net with SMTP; 5 Mar 2008 08:18:52 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 5 Mar 2008 08:18:32 -0000
Received: from ita73-1-82-239-31-94.fbx.proxad.net (HELO
	localhost.localdomain) (dforums@vieonet.com@82.239.31.94)
	by ns0.ovh.net with SMTP; 5 Mar 2008 08:18:29 -0000
Message-ID: <47CE574B.3000703@vieonet.com>
Date: Wed, 05 Mar 2008 09:18:19 +0100
From: dforums <dforums@vieonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Erik Jones <erik@myemma.com>
CC: Greg Smith <gsmith@gregsmith.com>, pgsql-performance@postgresql.org
Subject: Re: Optimisation help
References: <47CDCB62.9070302@vieonet.com>
	<Pine.GSO.4.64.0803041743060.22870@westnet.com>
	<47CDEF5B.8080905@vieonet.com>
	<82845731-D41C-4A79-9937-4A03CF455B36@myemma.com>
In-Reply-To: <82845731-D41C-4A79-9937-4A03CF455B36@myemma.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Ovh-Remote: 82.239.31.94 (ita73-1-82-239-31-94.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200803/67
X-Sequence-Number: 29021

OK I found the cause, it was a default settings added on server start. 
(-B 1024) Grrrrrrrrr!!!!!

Now it works really better I devide the full time per 2.

I suppose I steal have to look deep in the procedure to see some hack, 
has somebody suggest, I will try to buffer all updates in one.

One question, Could I optimise the treatment if I'm doing the select on 
a view while updating the main table ????


regards

David


Erik Jones a écrit :
> 
> On Mar 4, 2008, at 6:54 PM, dforums wrote:
> 
>> 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?
> 
> Did you do a full restart of the db cluster?  Changes to shared memory 
> settings require that.
> 
> Erik Jones
> 
> DBA | Emma®
> erik@myemma.com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
> 
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
> 
> 
> 
> 
> -- 
> 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 
> 
> 
>