After all this time I finally got around to vacuuming the database
with dbt2 with pg_autovacuum. :)
[url]http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/[/url]

Doesn't look so good though, probably because I'm not using optimal
settings with pg_autovacuum. So far I have only tried the default
settings (running without any arguments, except -D).

The only thing that's peculiar is a number of unexpected rollbacks
across all of the transactions. I suspect it was something to do with
these messages coming from pg_autovacuum:

[2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information from the database dbt2.
[2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find parent key in "pk_district"
]

This is with 8.0rc1. I can get rc2 installed since it just came out.
So let me know what I can try and what not.

Mark

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Mark Wong writes:

Yikes. Is this reproducible?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo (AT) postgresql (DOT) org[/email]

I don't know what you mean by "Not Good" since I don't have graphs from
a similar test without pg_autovacuum handy. Do you have a link to such
a test?

As for better pg_autovacuum settings, It appears that the little
performance dips are happening about once every 5 minutes, which if I
remember correctly is the default sleep time. You might try playing
with the lazy vacuum settings to see if that smooths out the curve.
Beyond that all you can do is play with the thresholds to see if there
is a better sweet spot than the defaults (which by the way I have no
confidence in, they were just conservative guesses)

Not sure what this is all about, but if you turn up the debug level to 4
or greater (pg_autovacuum -d4), pg_autovacuum will log the query that is
causing the problems, that would be helpful output to have.


I don't think anything has changed for pg_autovacuum between rc1 and rc2.


thanks again for the good work!!!


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo (AT) postgresql (DOT) org[/email])

The overall throughput is better for a run like this:
[url]http://www.osdl.org/projects/dbt2dev/results/dev4-010/207/[/url]

A drop from 3865 to 2679 (31%) by just adding pg_autovacuum. That's
what I meant by "not good". :)

I'll start with the additional debug messages, with 8.0rc2, before
I start changing the other settings, if that sounds good.

Mark

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Yes, and I think there is one for each of the rollbacks that are
occuring in the workload. Except for the 1% that's supposed to happen
for the new-order transaction.

Mark

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo (AT) postgresql (DOT) org[/email])

Mark Wong writes:

Well, we need to find out what's causing that. There are two possible
sources of that error (one elog in src/backend/access/nbtree/nbtinsert.c,
and one in src/backend/access/nbtree/nbtpage.c) and neither of them
should ever fire.

If you want to track it yourself, please change those elog(ERROR)s to
elog(PANIC) so that they'll generate core dumps, then build with
--enable-debug if you didn't already (--enable-cassert would be good too)
and get a debugger stack trace from the core dump.

Otherwise, can you extract a test case that causes this without needing
vast resources to run?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo (AT) postgresql (DOT) org[/email] so that your
message can get through to the mailing list cleanly

I was going to try Matthew's suggestion of turning up the debug on
pg_autovacuum, unless you don't that'll help find the cause. I'm not
sure if I can more easily reproduce the problem but i can try.

I'll go ahead and make the elog() changes you recommended and do a run
overnight either way.

Mark

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo (AT) postgresql (DOT) org[/email])

Mark Wong writes:

It won't help --- this is a backend-internal bug of some kind.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

I would agree that is "not good" :-) It sounds like pg_autovacuum is
being to aggressive for this type of load, that is vacuuming more often
than needed, however the lazy vacuum options were added so as to reduce
the performance impact of running a vacuum while doing other things, so,
I would recommend both higher autovacuum thresholds and trying out some
of the lazy vacuum settings.


Sounds fine. From Tom Lane's response, we have a backend bug that needs
to be resolved and I think that is the priority.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo (AT) postgresql (DOT) org[/email])