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

Bug #28183	SHOW ENGINE INNODB MUTEX output is incorrect
Submitted:	1 May 2007 17:30	Modified:	7 Apr 2008 12:19
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.17-beta	OS:	Any
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	innodb, mutex, qc, show

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