Horton
Menu

Thread

  1. Re: [QUESTIONS] MySQL benchmark page

    The Hermit Hacker <scrappy@hub.org> — 1998-02-03T12:10:16Z

    
Nobody likes to take the time to move discussions, do they?  And ya, I'm
about as guilty *sigh*

Move to pgsql-hackers@postgresql.org...


On Tue, 3 Feb 1998, Herouth Maoz wrote:

> At 15:01 +0200 on 2/2/98, The Hermit Hacker wrote:
> 
> I think the garbage collection should be separated from the statistics.
> Garbage collection needs a write lock, statistics only a read lock. If they
> are not done at the same time, the various tables would be locked for
> shorter periods.

	Hrmmmm...good point, I think.  Bruce?  Vadim?  When vacuum'ng a
large table, how much time is spend 'garbage collecting' vs 'statistics'?
I thought that 'vacuum analyze' *was* the statistics aspect of it?  Where
just 'vacuum' was only garbage collection...?

  2. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T18:22:22Z

    > 
> 
> 
> Nobody likes to take the time to move discussions, do they?  And ya, I'm
> about as guilty *sigh*
> 
> Move to pgsql-hackers@postgresql.org...
> 
> 
> On Tue, 3 Feb 1998, Herouth Maoz wrote:
> 
> > At 15:01 +0200 on 2/2/98, The Hermit Hacker wrote:
> > 
> > I think the garbage collection should be separated from the statistics.
> > Garbage collection needs a write lock, statistics only a read lock. If they
> > are not done at the same time, the various tables would be locked for
> > shorter periods.
> 
> 	Hrmmmm...good point, I think.  Bruce?  Vadim?  When vacuum'ng a
> large table, how much time is spend 'garbage collecting' vs 'statistics'?
> I thought that 'vacuum analyze' *was* the statistics aspect of it?  Where
> just 'vacuum' was only garbage collection...?

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.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

  3. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    The Hermit Hacker <scrappy@hub.org> — 1998-02-03T18:46:22Z

    On Tue, 3 Feb 1998, Bruce Momjian wrote:

> > On Tue, 3 Feb 1998, Herouth Maoz wrote:
> > 
> > > At 15:01 +0200 on 2/2/98, The Hermit Hacker wrote:
> > > 
> > > I think the garbage collection should be separated from the statistics.
> > > Garbage collection needs a write lock, statistics only a read lock. If they
> > > are not done at the same time, the various tables would be locked for
> > > shorter periods.
> > 
> > 	Hrmmmm...good point, I think.  Bruce?  Vadim?  When vacuum'ng a
> > large table, how much time is spend 'garbage collecting' vs 'statistics'?
> > I thought that 'vacuum analyze' *was* the statistics aspect of it?  Where
> > just 'vacuum' was only garbage collection...?
> 
> 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...?

  4. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T19:30:09Z

    > > 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.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

  5. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    The Hermit Hacker <scrappy@hub.org> — 1998-02-03T19:30:57Z

    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 :)

  6. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T19:40:03Z

    > 
> 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

  7. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    The Hermit Hacker <scrappy@hub.org> — 1998-02-03T19:42:18Z

    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 :(

  8. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-03T19:43:31Z

    > 
> 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

  9. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    The Hermit Hacker <scrappy@hub.org> — 1998-02-03T20:02:00Z

    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?

  10. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

    Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-04T02:14:09Z

    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

  11. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page\

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-04T02:37:51Z

    > 
> 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

  12. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page\

    Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-04T02:59:35Z

    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

  13. Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page\

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-04T04:45:53Z

    > 
> 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