Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
Here is another open item. What are we doing with LAZY vacuum? We recently had a very satisfactory contract completed by Vadim. Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. We've been running with these patches under heavy load for about a week now without any problems except one: don't 'lazy' (new option for vacuum) a table which has just had an index created on it, or at least don't expect it to take any less time than a normal vacuum would. There's three patchsets and they are available at: http://people.freebsd.org/~alfred/vacfix/ complete diff: http://people.freebsd.org/~alfred/vacfix/v.diff only lazy vacuum option to speed up index vacuums: http://people.freebsd.org/~alfred/vacfix/vlazy.tgz only lazy vacuum option to only scan from start of modified data: http://people.freebsd.org/~alfred/vacfix/mnmb.tgz Although the patches are for 7.0.x I'm hoping that they can be forward ported (if Vadim hasn't done it already) to 7.1. enjoy! -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk." -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
On Wednesday 24 January 2001 20:37, Bruce Momjian wrote: Here is another open item. What are we doing with LAZY vacuum? Sorry for inserting in the middle. I would like to say that when I tried LAZY vacuum on 7.0.3, I had a lockup on one of the table which disappeared after I did usual vacuum. I have sent an original version of a table from the backup to Vadim, but did not get any response. Just for your info. -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
* Bruce Momjian [EMAIL PROTECTED] [010122 19:55] wrote: Vadim, did these patches ever make it into 7.1? According to: http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/parser/gram.y?rev=2.217content-type=text/x-cvsweb-markup nope. :( We recently had a very satisfactory contract completed by Vadim. Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. We've been running with these patches under heavy load for about a week now without any problems except one: don't 'lazy' (new option for vacuum) a table which has just had an index created on it, or at least don't expect it to take any less time than a normal vacuum would. There's three patchsets and they are available at: http://people.freebsd.org/~alfred/vacfix/ complete diff: http://people.freebsd.org/~alfred/vacfix/v.diff only lazy vacuum option to speed up index vacuums: http://people.freebsd.org/~alfred/vacfix/vlazy.tgz only lazy vacuum option to only scan from start of modified data: http://people.freebsd.org/~alfred/vacfix/mnmb.tgz Although the patches are for 7.0.x I'm hoping that they can be forward ported (if Vadim hasn't done it already) to 7.1. enjoy! -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk." -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
Vadim, did these patches ever make it into 7.1? We recently had a very satisfactory contract completed by Vadim. Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. We've been running with these patches under heavy load for about a week now without any problems except one: don't 'lazy' (new option for vacuum) a table which has just had an index created on it, or at least don't expect it to take any less time than a normal vacuum would. There's three patchsets and they are available at: http://people.freebsd.org/~alfred/vacfix/ complete diff: http://people.freebsd.org/~alfred/vacfix/v.diff only lazy vacuum option to speed up index vacuums: http://people.freebsd.org/~alfred/vacfix/vlazy.tgz only lazy vacuum option to only scan from start of modified data: http://people.freebsd.org/~alfred/vacfix/mnmb.tgz Although the patches are for 7.0.x I'm hoping that they can be forward ported (if Vadim hasn't done it already) to 7.1. enjoy! -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk." -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
* Peter Schmidt [EMAIL PROTECTED] [010102 12:53] wrote: Will these patchsets be available to the public? I get: "You don't have permission to access /~alfred/vacfix/vlazy.tgz on this server" Thanks. Peter There's three patchsets and they are available at: http://people.freebsd.org/~alfred/vacfix/ complete diff: http://people.freebsd.org/~alfred/vacfix/v.diff only lazy vacuum option to speed up index vacuums: http://people.freebsd.org/~alfred/vacfix/vlazy.tgz only lazy vacuum option to only scan from start of modified data: http://people.freebsd.org/~alfred/vacfix/mnmb.tgz Oops! The permissions should be fixed now, if anyone wants to grab these feel free. Peter, thanks for pointing it out. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
Alfred Perlstein [EMAIL PROTECTED] writes: Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. Cool. What's it do, exactly? regards, tom lane
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
* Tom Lane [EMAIL PROTECTED] [001207 17:10] wrote: Alfred Perlstein [EMAIL PROTECTED] writes: Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. Cool. What's it do, exactly? The first is a bonus that Vadim gave us to speed up index vacuums, I'm not sure I understand it completely, but it work really well. :) here's the README he gave us: Vacuum LAZY index cleanup option LAZY vacuum option introduces new way of indices cleanup. Instead of reading entire index file to remove index tuples pointing to deleted table records, with LAZY option vacuum performes index scans using keys fetched from table record to be deleted. Vacuum checks each result returned by index scan if it points to target heap record and removes corresponding index tuple. This can greatly speed up indices cleaning if not so many table records were deleted/modified between vacuum runs. Vacuum uses new option on user' demand. New vacuum syntax is: vacuum [verbose] [analyze] [lazy] [table [(columns)]] The second is one of the suggestions I gave on the lists a while back, keeping track of the "last dirtied" block in the data files to only scan the tail end of the file for deleted rows, I think what he instead did was keep a table that holds all the modified blocks and vacuum only scans those: Minimal Number Modified Block (MNMB) This feature is to track MNMB of required tables with triggers to avoid reading unmodified table pages by vacuum. Triggers store MNMB in per-table files in specified directory ($LIBDIR/contrib/mnmb by default) and create these files if not existed. Vacuum first looks up functions mnmb_getblock(Oid databaseId, Oid tableId) mnmb_setblock(Oid databaseId, Oid tableId, Oid block) in catalog. If *both* functions were found *and* there was no ANALYZE option specified then vacuum calls mnmb_getblock to obtain MNMB for table being vacuumed and starts reading this table from block number returned. After table was processed vacuum calls mnmb_setblock to update data in file to last table block number. Neither mnmb_getblock nor mnmb_setblock try to create file. If there was no file for table being vacuumed then mnmb_getblock returns 0 and mnmb_setblock does nothing. mnmb_setblock() may be used to set in file MNMB to 0 and force vacuum to read entire table if required. To compile MNMB you have to add -DMNMB to CUSTOM_COPT in src/Makefile.custom. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
On Thu, 7 Dec 2000, Alfred Perlstein wrote: We recently had a very satisfactory contract completed by Vadim. Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. ... What size database was that on? I looking at moving a 2GB database from MySQL to Postgres. Most of that data is one table with 12 million records, to which we post about 1.5 million records a month. MySQL's table locking sucks, but as long as are careful about what reports we run and when, we can avoid the problem. However, Postgres' vacuum also sucks. I have no idea how long our particular database would take to vacuum, but I don't think it would be very nice. That also leads to the erserver thing. erserver sounds nice, but I sure wish it was possible to get more details on it. It seems rather intangible right now. If erserver is payware, where do I buy it? This is getting a bit off-topic now... Tom
Re: [HACKERS] Patches with vacuum fixes available for 7.0.x
* Tom Samplonius [EMAIL PROTECTED] [001207 18:55] wrote: On Thu, 7 Dec 2000, Alfred Perlstein wrote: We recently had a very satisfactory contract completed by Vadim. Basically Vadim has been able to reduce the amount of time taken by a vacuum from 10-15 minutes down to under 10 seconds. ... What size database was that on? Tables were around 300 megabytes. I looking at moving a 2GB database from MySQL to Postgres. Most of that data is one table with 12 million records, to which we post about 1.5 million records a month. MySQL's table locking sucks, but as long as are careful about what reports we run and when, we can avoid the problem. However, Postgres' vacuum also sucks. I have no idea how long our particular database would take to vacuum, but I don't think it would be very nice. We only do about 54,000,000 updates to a single table per-month. That also leads to the erserver thing. erserver sounds nice, but I sure wish it was possible to get more details on it. It seems rather intangible right now. If erserver is payware, where do I buy it? Contact Pgsql Inc. I think it's free, but you have to discuss terms with them. This is getting a bit off-topic now... Scalabilty is hardly ever off-topic. :) -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
