Description:
Peter Z described the problem almost 1 year ago. The bug is still in the 5.1 InnoDB
plugin. I assume the bug is still in latest 5.0, but I am still on 5.0.37.

http://www.mysqlperformanceblog.com/2007/07/27/more-gotchas-with-mysql-50/

This consumes a huge amount of time on servers with large InnoDB buffer caches. From my
memory, it was saturating a CPU.

How to repeat:
Run a busy server with a large (8G+) InnoDB buffer cache. 
Call SHOW STATUS a few times.
Watch the CPU load.

Suggested fix:
Don't call SHOW STATUS, or change SHOW STATUS to not call buf_get_latched_pages_number.

I would really like to know is only SHOW STATUS is affected!

SHOW VARIABLES?
SHOW TABLE STATUS?
etc ..

????

Vasil,

please remove the latched pages calculation from 5.0 and 5.1 in SHOW STATUS, unless
UNIV_DEBUG is specified.

It probably scans all buffer page control blocks buf_block_t. For a big buffer pool, that
may cause millions of memory accesses and waste several seconds of CPU time.

Regards,

Heikki

Thank you for the bug report.

Peter, yes, only SHOW STATUS is affected and particularly the calculation of the value of
the variable Innodb_buffer_pool_pages_latched, shown in SHOW STATUS.

This variable will be removed from SHOW STATUS to fix the problem.

patch for 5.0 that removes the offending variable from SHOW STATUS

Attachment: bug36600-5.0.diff (application/octet-stream, text), 5.01 KiB.

Merged into 6.0.6-alpha, according to Tim. But the patch has not been pushed into 5.0, 5.1
yet.

This has been queued into the mysql-5.1-bugteam tree, and will be pushed to main along
with the other SR51SP bugs.  Docs: please return to Patch Queued or NDI once documented
for 6.0.

Noted in 6.0.6 changelog.

SHOW STATUS took a lot of CPU time for calculating the value of the
Innodb_buffer_pool_pages_latched status variable. Now this variable
is calculated and included in the output of SHOW STATUS only when the
UNIV_DEBUG symbol is defined at server build time.

Setting report to Patch queued pending push of fix into 5.1.x (and 5.0.x?).

Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues)
(version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)

Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version
source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)

Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version
source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)

Noted in 5.0.68 changelog.

Setting report to Patch Queued pending push of fix into 5.1.x.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52003

2713 Timothy Smith	2008-08-19
      Cherry-pick some changes from innodb-5.1-ss2479 snapshot.  Includes fixes for
      Bug#36600 and Bug#36793:
      
      Bug #36600 SHOW STATUS takes a lot of CPU in buf_get_latched_pages_number
      
      Fix by removing the Innodb_buffer_pool_pages_latched variable from SHOW
      STATUS output in non-UNIV_DEBUG compilation.
      
      Bug #36793 rpl_innodb_bug28430 fails on Solaris
      
      This is a back port from branches/zip. This code has been tested on a
      big-endian machine too.

Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080820003741-xe0zudmryth44k79) (version
source revid:azundris@mysql.com-20080821081500-f2d61fh4u61owz3p) (pib:3)

Noted in 5.1.28 changelog.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version
source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.8-alpha  (revid:timothy.smith@sun.com-20080820003741-xe0zudmryth44k79)
(version source revid:v.narayanan@sun.com-20080908113103-2e4vdpm695vk3ji4) (pib:3)

Mark, are you content with the removal of the variable from the output without any
deprecation process for it? Any way that you'd prefer to have it done? Zero value unless
compiled with UNIV_DEBUG? Something else?

Total or slow removal is fine by me. You can add a my.cnf variable to determine whether it
gets updated for someone who doesn't want this bug fix.