Re: Slow insert performace, 8.3 Wal related?

On Monday 12 January 2009, Bill Preston <billpreston(at)crownepointe(dot)net>
wrote:
> 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.
>

That's well-known behaviour. If you don't do them in one big transaction,
PostgreSQL has to fsync after every insert, which effectively limits your
insert rate to the rotational speed of your WAL drive (roughly speaking).
If you don't explicitly start and end transactions, PostgreSQL does it for
you. For every statement.

> 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.

Are there foreign keys on any other table(s) that point to this one? Are the
relevant columns in those tables indexed?

--
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE