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>