Thread: Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page
On Tue, 3 Feb 1998, Bruce Momjian wrote:
> > > This is correct. Vacuum is fast, vacuum analyze is pretty slow. We
> > > could separate them, I guess, and that would eliminate the write-lock
> > > and be only a readlock.
> >
> > Possible to slip it in for v6.3? Would make it so that an analyze
> > could be done nightly, to keep statistics up, and then a vacuum once a
> > week or so just for garbage collection...?
>
> When I added analyze, I did not understand the issues, so I was able to
> work from Vadim's code in vacuum. I put it on the TODO list. Don't
> know if it can make 6.3. I am working on cleaning up the cacheoffset
> code right now.
Okay...personally, I'm finding 'vacuum <table>' an acceptable work
around, so it isn't too big of a priority :)
On Tue, 3 Feb 1998, Bruce Momjian wrote:
> >
> > On Tue, 3 Feb 1998, Bruce Momjian wrote:
> >
> > > > > This is correct. Vacuum is fast, vacuum analyze is pretty slow. We
> > > > > could separate them, I guess, and that would eliminate the write-lock
> > > > > and be only a readlock.
> > > >
> > > > Possible to slip it in for v6.3? Would make it so that an analyze
> > > > could be done nightly, to keep statistics up, and then a vacuum once a
> > > > week or so just for garbage collection...?
> > >
> > > When I added analyze, I did not understand the issues, so I was able to
> > > work from Vadim's code in vacuum. I put it on the TODO list. Don't
> > > know if it can make 6.3. I am working on cleaning up the cacheoffset
> > > code right now.
> >
> > Okay...personally, I'm finding 'vacuum <table>' an acceptable work
> > around, so it isn't too big of a priority :)
> >
>
> Vacuum probably write-locks the pg_class table because it updates the
> table statistics. By vacuuming one table at a time, your lock is
> removed and re-asserted, allowing other people to get into pg_class, and
> a scan of pg_class is not necessary becuase you supply the table names.
Wait, then I think I got this backwards.
Vacuum right now locks pg_class because of the statistics? If
that is the case, if we made vacuum *just* garbage collecting,it wouldn't
have to lock pg_class, only "vacuum analyze" wouldhave to do that?
So, I was misunderstanding in that I was thinking that 'vacuum
analyze' only needed the read-lock :(
> > On Tue, 3 Feb 1998, Bruce Momjian wrote: > > > > > This is correct. Vacuum is fast, vacuum analyze is pretty slow. We > > > > could separate them, I guess, and that would eliminate the write-lock > > > > and be only a readlock. > > > > > > Possible to slip it in for v6.3? Would make it so that an analyze > > > could be done nightly, to keep statistics up, and then a vacuum once a > > > week or so just for garbage collection...? > > > > When I added analyze, I did not understand the issues, so I was able to > > work from Vadim's code in vacuum. I put it on the TODO list. Don't > > know if it can make 6.3. I am working on cleaning up the cacheoffset > > code right now. > > Okay...personally, I'm finding 'vacuum <table>' an acceptable work > around, so it isn't too big of a priority :) > Vacuum probably write-locks the pg_class table because it updates the table statistics. By vacuuming one table at a time, your lock is removed and re-asserted, allowing other people to get into pg_class, and a scan of pg_class is not necessary becuase you supply the table names. -- Bruce Momjian maillist@candle.pha.pa.us
> > On Tue, 3 Feb 1998, Bruce Momjian wrote: > > > > > > > On Tue, 3 Feb 1998, Bruce Momjian wrote: > > > > > > > > > This is correct. Vacuum is fast, vacuum analyze is pretty slow. We > > > > > > could separate them, I guess, and that would eliminate the write-lock > > > > > > and be only a readlock. > > > > > > > > > > Possible to slip it in for v6.3? Would make it so that an analyze > > > > > could be done nightly, to keep statistics up, and then a vacuum once a > > > > > week or so just for garbage collection...? > > > > > > > > When I added analyze, I did not understand the issues, so I was able to > > > > work from Vadim's code in vacuum. I put it on the TODO list. Don't > > > > know if it can make 6.3. I am working on cleaning up the cacheoffset > > > > code right now. > > > > > > Okay...personally, I'm finding 'vacuum <table>' an acceptable work > > > around, so it isn't too big of a priority :) > > > > > > > Vacuum probably write-locks the pg_class table because it updates the > > table statistics. By vacuuming one table at a time, your lock is > > removed and re-asserted, allowing other people to get into pg_class, and > > a scan of pg_class is not necessary becuase you supply the table names. > > Wait, then I think I got this backwards. > > Vacuum right now locks pg_class because of the statistics? If > that is the case, if we made vacuum *just* garbage collecting,it wouldn't > have to lock pg_class, only "vacuum analyze" wouldhave to do that? > > So, I was misunderstanding in that I was thinking that 'vacuum > analyze' only needed the read-lock :( Maybe I am wrong. I have not looked at it. -- Bruce Momjian maillist@candle.pha.pa.us
On Tue, 3 Feb 1998, Bruce Momjian wrote:
> > Vacuum right now locks pg_class because of the statistics? If
> > that is the case, if we made vacuum *just* garbage collecting,it wouldn't
> > have to lock pg_class, only "vacuum analyze" wouldhave to do that?
> >
> > So, I was misunderstanding in that I was thinking that 'vacuum
> > analyze' only needed the read-lock :(
>
> Maybe I am wrong. I have not looked at it.
Okay, just sitting here thinking about it, and that doesn't really
make sense (if its true)...
Vacuum should be locking the table itself for a garbage cleanup,
since it has to move around records, and I wouldn't imagine you'd want to
have someone doing a SELECT at the same time. So, that locks the *table*
itself, but shouldn't affect pg_class (statistically)
Once the vacuum is finished its garbage cleanup phase (which,
granted, could take several minutes), then the statistics phase would come
into play...but again, a lock on pg_class shouldn't have to be imposed
until the 'update' of the table takes place, should it?
So, why is pg_class locked for the duration of a vacuum when the
vacuum is being performed for the whole database when it should (I think)
only need to be locked when updates are happening to it?
The Hermit Hacker wrote: > > So, why is pg_class locked for the duration of a vacuum when the > vacuum is being performed for the whole database when it should (I think) > only need to be locked when updates are happening to it? Yes, this is how it should be. This long lock comes from very old time. When I visited vacuum last time (year ago) I hadn't time to decide why this lock is here and could it be just removed or not. Still no time :) Vadim
>
> The Hermit Hacker wrote:
> >
> > So, why is pg_class locked for the duration of a vacuum when the
> > vacuum is being performed for the whole database when it should (I think)
> > only need to be locked when updates are happening to it?
>
> Yes, this is how it should be. This long lock comes from very old time.
> When I visited vacuum last time (year ago) I hadn't time to decide
> why this lock is here and could it be just removed or not.
> Still no time :)
>
> Vadim
>
>
Added to TODO:
* Make VACUUM on database not lock pg_class
* Make VACUUM ANALYZE only use a readlock
--
Bruce Momjian
maillist@candle.pha.pa.us
Bruce Momjian wrote: > > Added to TODO: > > * Make VACUUM on database not lock pg_class Ok. > * Make VACUUM ANALYZE only use a readlock ??? Vacuum analyze means: do vacuuming and analyzing, yes ? Vacuuming need in write-lock... There should be seperate command - ANALYZE, - when only read-lock would be used. Vadim
> > Bruce Momjian wrote: > > > > Added to TODO: > > > > * Make VACUUM on database not lock pg_class > > Ok. > > > * Make VACUUM ANALYZE only use a readlock > > ??? Vacuum analyze means: do vacuuming and analyzing, yes ? > Vacuuming need in write-lock... > There should be seperate command - ANALYZE, - when only > read-lock would be used. OK. -- Bruce Momjian maillist@candle.pha.pa.us