Matthew T. O'Connor wrote: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.
* 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 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.
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.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.
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
