Re: [HACKERS] Deadline-Based Vacuum Delay
Tom Lane wrote: I think the context for this is that you have an agreed-on maintenance window, say extending from 2AM to 6AM local time, and you want to get all your vacuuming done in that window without undue spikes in the system load (because you do still have live users then, just not as many as during prime time). If there were a decent way to estimate the amount of work to be done then it'd be possible to spread the work fairly evenly across the window. What I do not see is where you get that estimate from --- especially since you probably have more than one table to vacuum in your window. It is true that there is not a decent way to estimate the amount of work to be done. But the purpose in here is not “spread the vacuum over 6 hours exactly”, it is “finish vacuum within 6 hours, and spread the spikes as much as possible”. So the maximum estimation of the work is enough to refine the vacuum within the window, it is fine if vacuum run quickly than schedule. Also we don’t need to estimate the time of vacuum, we only need to compare the actual progress of time window and the progress of the work, and then adjust them to have the same pace in the delay point. The maximum of the work of vacuum can be estimated by size of the heap, the size of the index, and the number of dead tuples. For example the lazy vacuum has the following works: 1. scan heap 2. vacuum index 3. vacuum heap 4. truncate heap Although 2 and 4 are quite unpredictable, but the total amount of work including 1, 2, 3, and 4 can be estimated. The other problem is that vacuum only during a maintenance window doesn't seem all that compelling a policy anyway. We see a lot of examples of tables that need to be vacuumed much more often than once a day. So I'd rather put effort into making sure that vacuum can be run in the background even under high load, instead of designing around a maintenance-window assumption. This feature is not necessary has a maintenance window assumption. For example, if a table needs to be vacuumed every 3 hours to sweep the garbage, then instead of tuning cost delay GUC hardly to refine vacuum in 3 hours, we can make vacuum finish within the time frame by “VACUUM IN time” feature. If we can find a good way to tune the cost delay GUC to enable vacuum to catch up with the speed of garbage generation in the high frequency update system, then we won’t need this feature. For example, the interval of two vacuums can be estimated by tracking the speed of the dead tuple generation, but how can you tune the vacuum time to fit in the interval of two vacuums? It seems that there is not easy to tune the delay time of vacuum correctly. Best Regards -- Galy Lee lee.galy _at_ oss.ntt.co.jp NTT Open Source Software Center ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Deadline-Based Vacuum Delay
Galy Lee [EMAIL PROTECTED] writes: It is true that there is not a decent way to estimate the amount of work to be done. But the purpose in here is not âspread the vacuum over 6 hours exactlyâ, it is âfinish vacuum within 6 hours, and spread the spikes as much as possibleâ. So the maximum estimation of the work is enough to refine the vacuum within the window, it is fine if vacuum run quickly than schedule. Is it? If I tell the thing to take 6 hours and it finishes in 5 minutes, why would I be happy? It could obviously have spread out the work more, and presumably if I'm using this feature at all then I want the least possible load added from vacuum while it's running. But this is all academic, because there's no way to produce a trustworthy maximum estimate either. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Deadline-Based Vacuum Delay
On Dec 29, 2006, at 12:30 PM, Chris Browne wrote: How you get the work to spread consistently across 6 hours is a challenge; personally, my preference would generally be to try to get the work done ASAP, so the goal seems a tad off to me... Agreed. If we're going to monkey with automatically setting vacuum cost GUCs I'd *much* rather work towards having some kind of I/O priority scheme; that would allow vacuum to use as much I/O as it wants, provided nothing else in the system needs it. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Deadline-Based Vacuum Delay
[EMAIL PROTECTED] (Jaime Casanova) writes: On 12/28/06, Tom Lane [EMAIL PROTECTED] wrote: Galy Lee [EMAIL PROTECTED] writes: So I am thinking another way to perform vacuum. For example vacuum can be refined in a maintenance time frame like VACUUM IN 6 HOURS, and then vacuum operation will be performed within the window. The delay time is adjusted internally to spread the disk I/O over the time frame. And you will manage that how? The total amount of work to be done is quite unpredictable. specially for something you already can do with cron (*nix) or job scheduler (windows) That seems like a nonsequitor here... Using cron to try to make vacuums spread over 6 hours seems to me like an attempt to try to do that in as terrible a way possible. If you're trying to spread work over time, you need to use something that actively monitors the results, as opposed to a tool that is entirely fire and forget (and perhaps forget to work), like cron. It seems to me that the answer is much more along the lines of making a greatly more intelligent autovacuum. Something offering: - Queueing work (e.g. - in this case, we want to schedule a vacuum of everything) - Processing that work; perhaps sometimes with multiple threads to do multiple vacuums - Perhaps using time estimates to determine any vacuum delay GUC values to be applied How you get the work to spread consistently across 6 hours is a challenge; personally, my preference would generally be to try to get the work done ASAP, so the goal seems a tad off to me... -- let name=cbbrowne and tld=linuxdatabases.info in String.concat @ [name;tld];; http://www3.sympatico.ca/cbbrowne/x.html The human race will decree from time to time: There is something at which it is absolutely forbidden to laugh. -- Nietzche on Common Lisp ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Deadline-Based Vacuum Delay
On 12/29/06, Chris Browne [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Jaime Casanova) writes: On 12/28/06, Tom Lane [EMAIL PROTECTED] wrote: Galy Lee [EMAIL PROTECTED] writes: So I am thinking another way to perform vacuum. For example vacuum can be refined in a maintenance time frame like VACUUM IN 6 HOURS, and then vacuum operation will be performed within the window. The delay time is adjusted internally to spread the disk I/O over the time frame. And you will manage that how? The total amount of work to be done is quite unpredictable. specially for something you already can do with cron (*nix) or job scheduler (windows) That seems like a nonsequitor here... Using cron to try to make vacuums spread over 6 hours seems to me like an attempt to try to do that in as terrible a way possible. that's entirely my point... what th op was proposing was a command VACUUM IN n HOURS... he want to write the command... he didn't talk about a tool for monitoring jus a tool that he fire and forget... at least that was the way a read it... -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Deadline-Based Vacuum Delay
Chris Browne [EMAIL PROTECTED] writes: How you get the work to spread consistently across 6 hours is a challenge; personally, my preference would generally be to try to get the work done ASAP, so the goal seems a tad off to me... I think the context for this is that you have an agreed-on maintenance window, say extending from 2AM to 6AM local time, and you want to get all your vacuuming done in that window without undue spikes in the system load (because you do still have live users then, just not as many as during prime time). If there were a decent way to estimate the amount of work to be done then it'd be possible to spread the work fairly evenly across the window. What I do not see is where you get that estimate from --- especially since you probably have more than one table to vacuum in your window. The other problem is that vacuum only during a maintenance window doesn't seem all that compelling a policy anyway. We see a lot of examples of tables that need to be vacuumed much more often than once a day. So I'd rather put effort into making sure that vacuum can be run in the background even under high load, instead of designing around a maintenance-window assumption. regards, tom lane ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Deadline-Based Vacuum Delay
Galy Lee [EMAIL PROTECTED] writes: So I am thinking another way to perform vacuum. For example vacuum can be refined in a maintenance time frame like VACUUM IN 6 HOURS, and then vacuum operation will be performed within the window. The delay time is adjusted internally to spread the disk I/O over the time frame. And you will manage that how? The total amount of work to be done is quite unpredictable. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deadline-Based Vacuum Delay
On 12/28/06, Tom Lane [EMAIL PROTECTED] wrote: Galy Lee [EMAIL PROTECTED] writes: So I am thinking another way to perform vacuum. For example vacuum can be refined in a maintenance time frame like VACUUM IN 6 HOURS, and then vacuum operation will be performed within the window. The delay time is adjusted internally to spread the disk I/O over the time frame. And you will manage that how? The total amount of work to be done is quite unpredictable. specially for something you already can do with cron (*nix) or job scheduler (windows) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
