Wow thanks for all the help Tom and Alan. Sadly I was un-aware of the
well-known behavior. Consider it more will known now.
Fsync is off in 8.3.
I am not too worried about what was before in 8.1 since we are not going
back.
For the first example (bad behavior when I am not using transactions).
Is there anyway to tell that it is going on at a given point and time?
Is their a buffer that fills up, a stat somewhere that I can read?
A lot of our code isn't using transactions yet so I would like a heads
up when this problem is happening or if possible increase some parameter
so it happens less.
As to the second example with the delete. There are no foreign keys.
For the index. If the table has fields a,b,c and d.
We have a btree index (a,b,c,d)
and we are saying DELETE FROM table_messed_up WHERE a=x.
So the WHERE statement is the first field in the the index.
Now that you have given me more knowledge, let me ask a question that
might lead to the answer.
Example 1 happens in isolation.
Example 2 happened on a live system with the parameters that I specified
and a whole lot of sql statements without transactions being run at the
same time. In fact their probably was a whole lot of inserts on this
very table before the delete statement was hit.
Is it possible that a problem like Example 1 caused the behavior that I
witnessed in Example 2? It was waiting for the WAL's to catch up or
something?
Thanks
Rusty
Alan Hodgson wrote:
On Monday 12 January 2009, Bill Preston <billpreston@xxxxxxxxxxxxxxxx>
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?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
