Bug #36941 Performance problem in ha_print_info (SHOW INNODB STATUS)
Submitted: 25 May 2008 11:58 Modified: 20 Jun 2010 0:55
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to:
Tags: innodb, performance, regression
Triage: D3 (Medium)

[25 May 2008 11:58] Domas Mituzas
Description:
There's performance hotspot in 'SHOW INNODB STATUS', ha_print_info() has this code:

        for (i = 0; i < hash_get_n_cells(table); i++) {
                cell = hash_get_nth_cell(table, i);
                if (cell->node) {
                        cells++;
                }
        }

It is used to show how many nodes are free/used in such line:
Hash table size 3112859, used cells 1050451, node heap has 1890 buffer(s)

With freshly started server and >10GB buffer pool this code takes 0.3s, and makes 'SHOW INNODB STATUS' unusable in high load environments. 

How to repeat:
run 'show innodb status' on modern database machine

Suggested fix:
either maintain aggregate value, or strip this from status output. I chose latter.
[25 May 2008 12:28] Domas Mituzas
Opened an offspring at Bug#36942
[25 Jun 2008 13:26] Heikki Tuuri
Vasil,

you could this inside #ifdef UNIV_DEBUG:

ha_print_info() in 5.1:

"
        for (i = 0; i < hash_get_n_cells(table); i++) {

                cell = hash_get_nth_cell(table, i);

                if (cell->node) {

                        cells++;
                }
        }

        fprintf(file,
                "Hash table size %lu, used cells %lu",
                (ulong) hash_get_n_cells(table), (ulong) cells);
"

in versions 6.0 and greater. I think 5.0 as the 'old stable version' should be left as is.

Regards,

Heikki
[26 Jun 2008 7:14] Vasil Dimov
Patch for 5.1 created, waiting for approval.
Will not be fixed in 5.0.
[26 Jun 2008 14:20] Vasil Dimov
The fix removes ", used cells %lu" from SHOW INNODB STATUS
[20 Aug 2008 22:23] Bugs System
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/52096

2715 Timothy Smith	2008-08-20
      Cherry-pick some changes from innodb-5.1-ss2545 snapshot.  Includes fixes for
      Bug#37531, Bug#36941, Bug#36941, Bug#36942, Bug#38185.
      
      Also include test case from Bug 34300 which was left out from earlier snapshot
      (5.1-ss2387).
      
      Also include fix for Bug #29507, "TRUNCATE shows to many rows effected", since
      the fix for Bug 37531 depends on it.
[21 Aug 2008 17:59] Bugs System
Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080820221833-brmru9b77ddt3tfx) (version source revid:azundris@mysql.com-20080821081500-f2d61fh4u61owz3p) (pib:3)
[21 Aug 2008 23:40] Bugs System
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/52267

2794 Timothy Smith	2008-08-21 [merge]
      Merge up from 5.1-bugteam.  Among other things, this applies some changes from
      the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941,
      Bug#36942, and Bug#38185.
[21 Aug 2008 23:42] Bugs System
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/52268

2794 Timothy Smith	2008-08-21 [merge]
      Merge up from 5.1-bugteam.  Among other things, this applies some changes from
      the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941,
      Bug#36942, and Bug#38185.
[26 Aug 2008 19:49] Paul Dubois
Noted in 5.1.28 changelog.

Some performance problems of SHOW ENGINE INNODB STATUS were reduced
by removing the "used cells" and "Total number of lock structs in row
lock hash table" clauses from the output. These are now present only
if UNIV_DEBUG is defined at MySQL build time.

Setting report to NDI pending push into 6.0.x.
[13 Sep 2008 23:42] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080820221833-brmru9b77ddt3tfx) (version source revid:john.embretsen@sun.com-20080808091208-ht48kyzsk7rim74g) (pib:3)
[15 Sep 2008 21:10] James Day
I've filed bug #39462 about the removal of this output without following the deprecation process, suggesting using zero values instead of complete removal.
[16 Sep 2008 4:51] Paul Dubois
Noted in 6.0.7 changelog.
[5 May 2010 15:10] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 14:57] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:25] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:53] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:02] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:58] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:38] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:24] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)