Re: pg_autovacuum next steps

Joe Conway wrote:

> Matthew T. O'Connor wrote:
>
>> * Inability to schedule vacuums during off-peak times
>
>
> This would be *really* nice to have. In my recent case, if
> pg_autovacuum could work for say 3 minutes, and then back off for 2
> minutes or so while the batch transactions hit, it would be ideal.
>
I'm not sure what you are suggesting here. As it stands right now,
pg_autovacuum just issues a standard vacuum command, so there isn't
anything pg_autovacuum can do until that command completes. There has
been a lot of work going on trying to reduce performance impact
associated with a vacuum (vacuum delay, ARC etc), hopefully that will
make a big difference.

> I really think pg_autovacuum ought to get folded into the backend now,
> for 7.5. I haven't had time yet to read the entire thread, but I saw
> others making the same comment. It would make some of the listed
> problems go away, or at least become far easier to deal with.
>
Yeah, that seems to be the consensus, I am going to work on that next.

>> 3.Single-Pass Mode (External Scheduling):
>
>
> It still might make sense. You could have a mode where the daemon
> essentially sleeps forever, until explicitly woken up by a signal.
> When woken, it makes one pass, and goes back to infinite sleep. Then
> provide a simple way to signal the autovacuum process -- maybe an
> extension of the current VACUUM syntax.
>
Well one thing we talked about was having the autovacuum process not be
a daemon that is running all the time but rather a process that is
spawned periodically by the postmaster, so you wouldn't have to worry
about the signal / wake up issues. I could see this working much like
checkpoints where the postmaster fires them off on a schedule, there is
nothing stopping you from issuing a checkpoint command to force one
immediately. So perhaps there could be a new SQL command like "VACUUM
AUTO" or something like that.

>> 4.Off-Peak Scheduling:
>>
>> In it's simplest form (which I will implement first) I would add the
>> ability to add a second set of thresholds that will be active only
>> during an “off-peak” time that can be specified in the pg_autovacuum
>> database, perhaps in a general_settings table.
>
>
> I don't know how this would work, but it is for sure important. In the
> recent testing I found that pg_autovacuum (well, lazy vacuum in
> general, but I was using pg_autovacuum to control it) made a huge
> difference in performance of batch transactions. They range from 4-5
> seconds without vacuum running, to as high as 15 minutes with vacuum
> running. With the vacuum delay patch, delay = 1, pagecount = 8, I
> still saw times go as high as 10 minutes. Backing vacuum off any more
> than that caused it to fall behind the transaction rate unrecoverably.
> But as I said above, if the transactions could complete without vacuum
> running in 4-5 seconds, then vacuuming resumes for the 3-to-4 minutes
> between batches, all would be well.

Again, once the vacuum command is issued, it's out of pg_autovacuums
control. There has been some talk of pg_autovacuum looking at the
system load to see if it should wait, or passing some different delay
settings to vacuum based on system activity, so maybe some of that will
help.

What I was talking about with Off-Peak scheduling is really just setting
different thresholds for different times of the day, (or perhaps for
different systems loads) so that if you know your app is typically busy
from 8AM - 8PM, then you can set more conservative thresholds during
that time window, and more aggressive thresholds during the Off-Peak time.

Thanks for the feedback,

Matthew