Re: [QUESTIONS] MySQL benchmark page

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

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

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

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:

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

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

[Moved to hackers.]

Can someone take this on?

Great, thanks for testing. I have a speedup patch I am working on now
that will help with any table that has char(), and I think you probably
have many of them. You will see my posting on the questions list.

Will test it that's for sure but I would like to ask if it's possible
that you run also the crash-me test and the benchmark. When running
the crash-me test some strange things happened. I didn't had time to
take a look at it but it crashed (postgres) that's for sure ....
Further I got some errors I think is interesting for development
while running the benchmark.
Some other things I found:
postgreSQL isn't compiling with pgcc.
the memory usage of postgres is big. Very big. Trying to test the
maximal number of joins is taking 500 mb of memory testing a join of
55 tables. When I run vacuum postgres takes approx 128 mb of memory.
That's a lot I think.

When there is a new snapshot out just let me know and I will try to
test it ...

Greetz...

Luuk
|-----------------------------------------------------------------------|
| Luuk de Boer -> <luuk@pi.net> |
| When you want to go fast ..... |
| Drive a cobra on the road and take a T3 line for internet ...... :-) |
| http://www.luuk.com/ -> The AC Cobra site |
|-----------------------------------------------------------------------|

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

On Thu, 5 Feb 1998, Bruce Momjian wrote:

[Moved to hackers.]

Can someone take this on?

I have it all downloaded at home...I dive into it and get it
included as part of our src/test directory...