Re: [GENERAL] Re: VACUUM and 24/7 database operation
Any type of commerce or site that tracks user profiles... That's a LOT of sites that would have a need for a enterprise level DB. Remember, the goal is 5 nines uptime. :) Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com - Original Message - From: "Steve Wolfe" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 23, 2001 3:52 PM Subject: Re: [GENERAL] Re: VACUUM and 24/7 database operation Shouldn't it be possible to build vacuum as an ongoing internal PG process, instead of a seperate operation? How does Oracle byepass this? Must be some way that can be implemented. Well, here's what it comes down to: Do you want updates to happen quickly, and vacuum when load is low, or do you want updates to be slow all the time? I suppose that there are some sites that can't find two minutes per day when updates will block (not selects), but I imagine they're very few. steve
[GENERAL] Re: VACUUM and 24/7 database operation
Tom, Shouldn't it be possible to build vacuum as an ongoing internal PG process, instead of a seperate operation? How does Oracle byepass this? Must be some way that can be implemented. Any pointers to further reading to brush up my theory in this regard please? IAC, regarding the actual inquiry, wouldn't be a replicated database on a second server be more cheaper than Oracle, if the party is satisfied with PG performance? I browsed some PG commercial organization site that told about a Replication Server being available for PG. I am about to look into that next month. Is it any good like PG? Will provide failover too..rather than using Oracle. With best regards. Sanjay. At 05:53 PM 1/23/01 , Tom Lane wrote: [EMAIL PROTECTED] writes: - Is 2 minutes a standard time for vacuuming a 500.000 rows table ? - Can it be reduced ? - In a far future, what are the problems we can run into not vacuuming that table ? We have already seen that after a month, some transactions involving where id = some_value take forever, so we supressed them. If it takes a month before query performance gets bad, then perhaps you could vacuum the table only once a month. However, that vacuum would probably take longer than two minutes, so it's a tradeoff... We have plans for 7.2 to reduce the need for periodic vacuums, but that won't help you much now. There are patches available for a "lazy vacuum" process on 7.0.3, which can be a win if vacuum only needs to get rid of a few rows. But they're not very thoroughly tested IMHO. See http://people.freebsd.org/~alfred/vacfix/ regards, tom lane
Re: [GENERAL] Re: VACUUM and 24/7 database operation
With best regards. Sanjay. At 05:53 PM 1/23/01 , Tom Lane wrote: [EMAIL PROTECTED] writes: - Is 2 minutes a standard time for vacuuming a 500.000 rows table ? - Can it be reduced ? - In a far future, what are the problems we can run into not vacuuming that table ? We have already seen that after a month, some transactions involving where id = some_value take forever, so we supressed them. If it takes a month before query performance gets bad, then perhaps you could vacuum the table only once a month. However, that vacuum would probably take longer than two minutes, so it's a tradeoff... We have plans for 7.2 to reduce the need for periodic vacuums, but that won't help you much now. There are patches available for a "lazy vacuum" process on 7.0.3, which can be a win if vacuum only needs to get rid of a few rows. But they're not very thoroughly tested IMHO. See http://people.freebsd.org/~alfred/vacfix/ We've been running them since I released them with only a single problem that has never resurfaced. I would say they are pretty stable. It's not "just a few rows" by the way, it's several thousand and up to probably 50,000 rows we get about a 20-40x speedup in the time taken to vacuum (10-15 minutes to 13-40 seconds). This is on tables that are over 300megabytes and indecies that are even larger (multiple column indicies). It's a shame this still hasn't made it into 7.1 :( * Sanjay Arora [EMAIL PROTECTED] [010123 12:10] wrote: Tom, Shouldn't it be possible to build vacuum as an ongoing internal PG process, instead of a seperate operation? How does Oracle byepass this? Must be some way that can be implemented. Any pointers to further reading to brush up my theory in this regard please? Follow the long trail of my messages on the lists about it, I'd say about 1/3 of my posts have to do with the problems we were facing before contracting Vadim to do the patches available at: http://people.freebsd.org/~alfred/vacfix/ IAC, regarding the actual inquiry, wouldn't be a replicated database on a second server be more cheaper than Oracle, if the party is satisfied with PG performance? I browsed some PG commercial organization site that told about a Replication Server being available for PG. I am about to look into that next month. Is it any good like PG? Will provide failover too..rather than using Oracle. It should, but I havne't read up on it much. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [GENERAL] Re: VACUUM and 24/7 database operation
Shouldn't it be possible to build vacuum as an ongoing internal PG process, instead of a seperate operation? How does Oracle byepass this? Must be some way that can be implemented. Well, here's what it comes down to: Do you want updates to happen quickly, and vacuum when load is low, or do you want updates to be slow all the time? I suppose that there are some sites that can't find two minutes per day when updates will block (not selects), but I imagine they're very few. steve
