Thread: Patches with vacuum fixes available for 7.0.x
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 indexcreated 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 - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred Perlstein <bright@wintelcom.net> 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
* Tom Lane <tgl@sss.pgh.pa.us> [001207 17:10] wrote:
> Alfred Perlstein <bright@wintelcom.net> 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 - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."
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
* Tom Samplonius <tom@sdf.com> [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 - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
* Peter Schmidt <peterjs@home.com> [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 - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
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 - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk." > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
* Bruce Momjian <pgman@candle.pha.pa.us> [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.217&content-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 - [bright@wintelcom.net|alfred@freebsd.org] > > "I have the heart of a child; I keep it in a jar on my desk." > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Alfred, Is there a tarbar with the updated files for the vacuum patch? Or, is there some way to use the 'v.diff' file without the need to modify the files by hand? I started changing the files by hand, but realized that there is so much information that I'm bound to make a mistake in the manual update. Thanks. -Tony Reina > 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 >
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 - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk." > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------