Alvaro Herrera wrote:

There are several things that are painfully evident with this thing on:

- TRUNCATE does not update stats.  It should send a stat message to
 which we can react.

How important is this really? The stats from before the truncate might be ok, especially since they might represent how the table will look in the future. Also, there isn't any free space in a table that was just truncated, so there is no need to run vacuum to update the FSM.

- If you empty a whole table using DELETE just after an
 automatically-issued VACUUM takes place, the new threshold may not be
 enough to trigger a new VACUUM.  Thus you end up with a bloated table,
 and it won't get vacuumed until it grows again.  This may be a problem
 with the cost equations, but those are AFAICT identical to those of
 pg_autovacuum, so we may need to rethink the equations.

I'm very open to a better equation if someone has one, but I'm not sure what the problem is. If there are 10,000 rows in a table and an autovacuum takes place, you will have a threshold of 5,000 (assuming you are using the default threshold parmeters: base = 1000, scaling factor = 0.4). So now when all the rows are deleted that will be enough activity to cross the threshold and cause another vacuum. I guess the problem is if the table is smaller say, 1,000 rows, now after a vacuum, the threshold will be 1,400, and deleting all the rows will not cause a vacuum. But that is OK because a 1,000 row table is probably not very big. The purpose of the base threshold value is so that vacuum commands don't get run continually on really small tables that are updated a lot, it's OK to have some slack space. If the default is deemed to high, we can always lower it.

- The default value of on for reset stats on server start is going to be
 painful with autovacuum, because it reacts badly to losing the info.

I agree, this is an issue. Is there any reason not to change stats_reset_on_restart to default to true?

- We should make VACUUM and ANALYZE update the pg_autovacuum relation,
 in order to make the autovacuum daemon behave sanely with manually

Agree completly. This way autovacuum can work in harmony with manually issued or cron isssued vacuum commands.

- Having an autovacuum process running on a database can be surprising
 if you want to drop a database, or create a new one using it as a
 template.  This happenned to me several times.

Not sure what to do about this. We could reduce the number of times autovacuum actually connects to a database by checking the stats flat file before we connect. If there hasn't been any activity since the last time we connected, then don't connect again. Better ideas anyone?

- The shutdown sequence is not debugged nor very well tested.  It may be
 all wrong.

Ok, I'm testing it now, i'll let you know if I see anything funny.

- The startup sequence is a mixture from pgarch, normal backend and
 pgstat.  I find it relatively clean but I can't swear it's bug-free.

Same as above.

- There are no docs

I can help here as long as I don't have to have the docs done before July 1.

- There are no ALTER TABLE commands to change the pg_autovacuum
 attributes for a table. (Enable/disable, set thresholds and scaling

I don't think we need this do we? Mucking around in the autovacuum table shouldn't cause the system any serious problems, if you do mess up your values, it's easy to just reset them all to 0 and start back with the defaults.

- I compiled with -DEXEC_BACKEND, but I didn't look to see if it
 actually worked on that case.

Apart from all these issues, it is completely functional :-)  It can
survive several "make installcheck" runs without problem, and the
regression database is vacuumed/analyzed as it runs.


Some of these issues are trivial to handle.  However I'd like to release
this right now, so I can go back to "shared dependencies" now that role
support is in.

Barring any objections I think this should be integrated, so these
issues can be tackled by interested parties.

Couple of other thoughts:
Do the vacuum commands respect the GUC vacuum delay settings?
Should we be able to set per table vacuum delay settings?
This patch doesn't have the "maintenance window" that was discussed a while ago. Can that be added after July 1?

Thanks Alvaro for doing the integration work!!!!

Matthew O'Connor

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Reply via email to