Re: Insert speed new post

[Terry Fielder <terry@xxxxxxxxxxxxxxxxxxxx>]

Responses below.

Terry Fielder
terry@xxxxxxxxxxxxxxxxxx
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

Tom Lane wrote:

Terry Fielder <terry@xxxxxxxxxxxxxxxxxxxx> 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 majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly