MySQL Bugs: #28183: SHOW ENGINE INNODB MUTEX output is incorrect

Bug #28183	SHOW ENGINE INNODB MUTEX output is incorrect
Submitted:	1 May 2007 19:30	Modified:	7 Apr 2008 14:19
Reporter:	Baron Schwartz (Basic Quality Contributor)
Status:	Won't fix
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.1.17-beta	OS:	Any
Assigned to:	Heikki Tuuri	Target Version:
Tags:	show, mutex, innodb, qc
Triage:	D4 (Minor)

Description:
In MySQL 5.0.x, SHOW MUTEX STATUS produced lots of information.  In 5.1, the manual
indicates it should show the same data in a different format, but it doesn't.  Only part
of the data is there, and there is a lot of duplicated data.

Refer to:
http://dev.mysql.com/doc/refman/5.0/en/show-mutex-status.html
and
http://dev.mysql.com/doc/refman/5.1/en/show-engine.html

The old format had these columns:

        Mutex: &rw_lock_list_mutex
       Module: sync0sync.c
        Count: 93129
   Spin_waits: 0
  Spin_rounds: 0
     OS_waits: 0
    OS_yields: 0
OS_waits_time: 0

The new looks like this:

  Type: InnoDB
  Name: srv0srv.c:862
Status: os_waits=0

According to the manual, I would expect to see the name of the mutex itself, not just the
file:line where it is defined:

"The mutex name and the source file where it is implemented. Example:
&pool->mutex:mem0pool.c"

Without the name, it is difficult to know the mutex's purpose, which is an important part
of using it for monitoring.

Also, the  "count" value is absent, as are the others such as "os_yields" and so on.  I
guess these could be assumed to be zero when absent, except for "count" which should
certainly not be zero.

Finally, there is a great deal of redundant output.  There are hundreds of lines of this,
for example:

| InnoDB | buf0buf.c:547    | os_waits=0 | 
| InnoDB | buf0buf.c:545    | os_waits=0 | 
| InnoDB | buf0buf.c:547    | os_waits=0 | 
| InnoDB | buf0buf.c:545    | os_waits=0 | 
| InnoDB | buf0buf.c:547    | os_waits=0 | 
| InnoDB | buf0buf.c:545    | os_waits=0 | 
| InnoDB | buf0buf.c:547    | os_waits=0 | 

How to repeat:
Run SHOW ENGINE INNODB MUTEX (on 5.1) and SHOW MUTEX STATUS (on 5.0).

A followup comment -- please disregard my comment about duplicated data.  I see SHOW MUTEX
STATUS also has rows that appear to be duplicated.  I think I should open a separate bug
on that, as it looks like just a documentation issue, not functionality.

As a side note, I don't think the newer format is an improvement over the old -- one of
our servers has been up only a few hours, and already there are 164806 rows returned, and
it only seems to grow.  It takes many seconds just to return to the client.  Analysis
seems difficult or impossible when there are 164806 rows of this:

| buf0buf.c   |  497 |        0 |
| buf0buf.c   |  494 |        0 |
| buf0buf.c   |  497 |        0 |
| buf0buf.c   |  494 |        0 |
| buf0buf.c   |  497 |        0 |
| buf0buf.c   |  494 |        0 |
| buf0buf.c   |  497 |        0 |
| buf0buf.c   |  494 |        0 |
| buf0buf.c   |  497 |        0 |
| buf0buf.c   |  494 |        0 |
| buf0buf.c   |  497 |        0 |

Thank you for a bug report (there is no mutex name in the output). Verified just as
described with latest 5.1.18-BK.

Baron,

we removed the full monitoring code from InnoDB because we suspected it could cause
'thread thrashing' and increased CPU usage. I believe the full information can be seen if
you compile mysqld with the appropriate InnoDB debug option in /innobase/include/univ.i.

Regards,

Heikki