Skip site navigation (1) Skip section navigation (2)

Slow insert performace, 8.3 Wal related?

From: Bill Preston <billpreston(at)crownepointe(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow insert performace, 8.3 Wal related?
Date: 2009-01-12 23:03:09
Message-ID: 496BCC2D.6060809@crownepointe.net (view raw or flat)
Thread:
Lists: pgsql-performance
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



Responses

pgsql-performance by date

Next:From: Gregory WilliamsonDate: 2009-01-12 23:38:10
Subject: Re: slow query
Previous:From: Scott MarloweDate: 2009-01-12 22:59:24
Subject: slow query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group