Hi Group.
Recently upgraded from 8.1 to 8.3 on RHEL 5 64-bit.
I've noticed some performance problems that I am guessing are WAL
related based on my browsing around and wondered if someone had some
suggestions for tuning the WAL settings. It could also help if someone
just laughed at me and told me it wasn't WAL.
I have narrowed the problem down to two pretty simple descriptions.
I had a data load that I was doing with 8.1. It involved about 250k sql
statements that were inserts into a table with just one index. The index
has two fields.
With the upgrade to 8.3 that process started taking all night and 1/2 a
day. It inserted at the rate of 349 records a minute.
When I started working on the problem I decided to test by putting all
statements withing a single transaction. Just a simple BEGIN at the
start and COMMIT at the end. Magically it only took 7 minutes to do the
whole set, or 40k per minute. That seemed very odd to me, but at least I
solved the problem.
The most recently noticed simple problem.
I had a table with about 20k records. We issued the statement DELETE
FROM table where this=that.
This was part of a combined index and about 8k records should have been
deleted.
This statement caused all other queries to grind to a halt. It was only
when I killed it that normal operation resumed. It was acting like a
lock, but that table was not being used by any other process.
So that describes what I am seeing, let me relay what we are doing with
what I think to be the relevant settings.
For the log shipping, I am using scp to send the logs to a separate
server. And yes they are getting sent.
I have it set now to send the log about every two minutes since I am
comfortable with that amount of data loss. Here are the settings from
the log file that are not commented out relating to WAL. (everything
below WRITE AHEAD LOG section in the default config file)
synchronous_commit = off
checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.5 # checkpoint target duration,
0.0 - 1.0
checkpoint_warning = 30s # 0 is off
archive_mode = on # allows archiving to be done
archive_command = '/var/lib/pgsql/data/logship.sh %f %p'
archive_timeout = 120 # force a logfile segment switch after this
Thanks for any help (or laughter)
Rusty
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
