Re: pg_autovacuum next steps - Mailing list pgsql-hackers

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