Optimisation help

by dforums@[EMAIL PROTECTED] (dforums) Mar 4, 2008 at 10:21 PM

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

10 Posts in Topic:

Optimisation help

dforums@[EMAIL PROTECTED]

2008-03-04 22:21:22

Re: Optimisation help

ahodgson@[EMAIL PROTECTED

2008-03-04 13:03:58

Re: Optimisation help

dforums@[EMAIL PROTECTED]

2008-03-05 00:10:18

Re: Optimisation help

dforums@[EMAIL PROTECTED]