Responses below.
Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
Tom Lane wrote:
Terry Fielder <[EMAIL PROTECTED]> writes:
Under pg 7.x the system performed fine.
In 8.1.9, the insert statements seem to take a long time sometimes, upto
several seconds or more.
There is no primary key, but the table is never updated, only inserted.
I removed the only index, with no improvement in performance (in case
the 8.2 "resolves index locking issues" was the concern for an 8.1 install.
Hmm. With no indexes, inserts ought to be basically a constant-time
operation.
My sentiments exactly.
I suspect what you are looking at is stalls caused by
checkpoints or other competing disk activity.
There were warnings in the logs when I first deployed the 8.1 version.
Sorry, I should have mentioned. Here are the postgresql.conf changes I
made based on what I know I need from my 7.4 install:
max_connections increased to 250
shared_buffers increased to 11000
The below I increased based on HINT's in the log file.
max_fsm_pages increased to 800000 (I have
max_fsm_relations to 1000
checkpoint_segments increased to 300
And much playing around with logging settings, done on my own.
I'd suggest watching the
output of "vmstat 1" or local equivalent, and seeing if you can
correlate the slow inserts with bursts of disk activity.
I can do that, next peak period (tomorrow).
Have you tuned the 8.1 installation?
I have tweaked the settings based on HINT's as described above. Is
there a document or something else you are referring to?
I'm wondering about things like
checkpoint_segments and wal_buffers maybe being set lower than you had
them in 7.4.
I left the default in 8.1, which is:
#fsync = on # turns forced synchronization
on or off
#wal_sync_method = fsync # the default is the first option
#full_page_writes = on # recover from partial page writes
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1,
16MB each
#increased by terry 20070402
checkpoint_segments = 300 # in logfile segments, min 1,
16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
Any ideas based on this?
Thanks for your help.
Terry
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
