PostgreSQL: Performance issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn Kusardi - 25 Feb 2005 23:52 GMT

Hello,

I'm experiencing performance problems with PostgreSQL 7.4.3 on a
OpenBSD 3.6 machine.

UPDATEs are taking 4-6 seconds on a table with 8845 rows (with 7195
affected rows). EXPLAIN tells me, that a Seq Scan is done with using a
WHERE filter. The table got some columns and indexes. The same UPDATE
statement is working on other systems and MySQL or Oracle and is just
using < 1s.

VACUUM FULL; isn't optimizing that too much. The configuration file
wasn't changed and fsync is being used, but altough I tried to disable
fsync it wasn't much faster.

I'm using a Xeon 3,x GHz with 2 GB RAM.

Anyone got an idea where I can start to search for some option to
optimize the UPDATE/INSERT/DELETE statements?

Sincerely TIA,
Glenn

Reply to this Message

Christopher Browne - 26 Feb 2005 02:44 GMT

> I'm experiencing performance problems with PostgreSQL 7.4.3 on a
> OpenBSD 3.6 machine.
[quoted text clipped - 4 lines]
> UPDATE statement is working on other systems and MySQL or Oracle and
> is just using < 1s.

Well, if you're modifying over 80% of the table, a Seq Scan is
certainly the right query plan. There isn't ANY index scan that would
help if you're updating 7195 out of 8845 rows.

> VACUUM FULL; isn't optimizing that too much. The configuration file
> wasn't changed and fsync is being used, but altough I tried to disable
[quoted text clipped - 4 lines]
> Anyone got an idea where I can start to search for some option to
> optimize the UPDATE/INSERT/DELETE statements?

It sure sounds like something's off if it takes multiple seconds to
update a table like that. You're running Oracle/MySQL(tm) on the very
same hardware, with better results?

I'd be really suspicious of there being something somehow busted with
your disk subsystem. I'd suggest pointing the issue over to the
PostgreSQL "Performance" mailing list, and giving a bit more detail,
such as the output from EXPLAIN ANALYZE...

Signature

output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/finances.html
Coming Soon to a Mainframe Near You! MICROS~1 Windows NT 6.0,
complete with VISUAL JCL...

Reply to this Message

Glenn Kusardi - 26 Feb 2005 12:17 GMT

> It sure sounds like something's off if it takes multiple seconds to
> update a table like that. You're running Oracle/MySQL(tm) on the very
> same hardware, with better results?

Not on the same hardware but on a similar one.

> I'd be really suspicious of there being something somehow busted with
> your disk subsystem. I'd suggest pointing the issue over to the
> PostgreSQL "Performance" mailing list, and giving a bit more detail,
> such as the output from EXPLAIN ANALYZE...

Without indizes the query just runs fine. 22 columns with 10 indizes, all
integer, varchar or timestamp isn't that much usually.

And I can't isolate one index which is causing the query to be slow.

Glenn

Reply to this Message

Glenn Kusardi - 26 Feb 2005 14:56 GMT

> [PostgreSQL performance]

It seems that MVCC is my problem.

If I run VACUUM and REINDEX before I run the UPDATE query everything is
working fine (2 seconds for the update). But after some of these queries
runtime is increasing.

I suppose my UPDATE is generating thousands of deleted rows (without
actually deleting something) and the new size of the table is slowing down
my query (+ the indexing after the UPDATE)?

Reply to this Message

Christopher Browne - 27 Feb 2005 01:46 GMT

Oops! Glenn Kusardi <usenet@kusardi.de> was seen spray-painting on a wall:

>> [PostgreSQL performance]
>
[quoted text clipped - 7 lines]
> actually deleting something) and the new size of the table is
> slowing down my query (+ the indexing after the UPDATE)?

What it is more precisely doing is to generate thousands of dead
tuples; every time a row is updated, the old values become "dead."

That's a big win when keeping the dead tuples around allows the system
to essentially eliminate the need for read locks.

Unfortunately, the scenario you are observing is the exact
pathological case where MVCC does _NOT_ play well. :-(

If you run a VACUUM after every time you do one of these 'massive'
updates, that should allow things to not slow down perilously.

Signature

let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/rdbms.html
"The classic Common Lisp defmacro is like a cook's knife; an elegant
idea which seems dangerous, but which experts use with confidence."
-- Paul Graham, _On Lisp_

Reply to this Message

Glenn Kusardi - 27 Feb 2005 10:35 GMT

>> It seems that MVCC is my problem.
> Unfortunately, the scenario you are observing is the exact
> pathological case where MVCC does _NOT_ play well. :-(
> If you run a VACUUM after every time you do one of these 'massive'
> updates, that should allow things to not slow down perilously.

Well, unfortunately that isn't a massive update with some thousands of
affected rows, and our system is doing that _heavily_. I try to run
pg_autovacuum parallel to let PostgreSQL decide whether to run a vacuum.

VACUUM after every of these UPDATEs isn't possible, because that happens
just too often and VACUUM would slow down the operation (the user would
have to wait for the VACUUM to finish). pg_autovacuum would run in the
background, so it _should_ not be visible for the user, I hope.

Reply to this Message

Christopher Browne - 27 Feb 2005 12:17 GMT

After takin a swig o' Arrakan spice grog, Glenn Kusardi <usenet@kusardi.de> belched out:

>>> It seems that MVCC is my problem.
>> Unfortunately, the scenario you are observing is the exact
[quoted text clipped - 11 lines]
> would run in the background, so it _should_ not be visible for the
> user, I hope.

Yes, pg_autovacuum ought to catch this. If you could submit a VACUUM
request via a separate connection, after each 'mass update,' that
surely ought to work out OK, and not require the user to wait...

Signature

(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/rdbms.html
"I would guess that he really believes whatever is politically
advantageous for him to believe." -- Alison Brooks, referring to
Michael Portillo, on soc.history.what-if

Reply to this Message

Glenn Kusardi - 27 Feb 2005 12:33 GMT

> Yes, pg_autovacuum ought to catch this. If you could submit a VACUUM
> request via a separate connection, after each 'mass update,' that
> surely ought to work out OK, and not require the user to wait...

That would be another idea, but I hope autovacuum is good enough. Thanks
for your help.

Reply to this Message

Christopher Browne - 26 Feb 2005 15:24 GMT

Clinging to sanity, Glenn Kusardi <usenet@kusardi.de> mumbled into her beard:

>> It sure sounds like something's off if it takes multiple seconds to
>> update a table like that. You're running Oracle/MySQL(tm) on the very
[quoted text clipped - 11 lines]
>
> And I can't isolate one index which is causing the query to be slow.

Oh, well, 10 indices kind of points to the problem...

You only have 8000 rows in the table, so it probably only occupies a
hundred pages of disk space. Maintaining 10 indices on a table is
rather expensive, and fairly futile, as a Seq Scan on a table that
small won't actually be that expensive...

Signature

"cbbrowne","@","cbbrowne.com"
http://linuxfinances.info/info/slony.html
"not red, not light red, but gay-pride triangle, poodle-dress pink."
-- Seen in alt.food.sushi

Reply to this Message