Re: Insert speed new post

Responses below.

Terry Fielder
terry(at)greatgulfhomes(dot)com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

Tom Lane wrote:
> Terry Fielder <terry(at)ashtonwoodshomes(dot)com> 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>