Bug #36942 Performance problem in lock_get_n_rec_locks (SHOW INNODB STATUS)
Submitted: 25 May 2008 12:28 Modified: 20 Jun 2010 0:56
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0,5.1 OS:Any
Assigned to: CPU Architecture:Any

[25 May 2008 12:28] Domas Mituzas 
Description:
There's performance hotspot in 'SHOW INNODB STATUS', lock_print_info_summary() calls lock_get_n_rec_locks(), which traverses all lock structures.

By fixing Bug#36941 MySQL was able to run 20 'show innodb status' per second, instead of 3, but after fixing this one, instead of 20 it runs 5000 'show innodb status' a second. 

How to repeat:
SHOW INNODB STATUS

Suggested fix:
don't call, or maintain aggregate
[25 Jun 2008 13:33] Heikki Tuuri 
Vasil,

I think we can put also this behind #ifdef UNIV_DEBUG in 5.1 and greater. SHOW INNODB STATUS does print all transactions and their lock numbers. No need to print this aggregate.

I do not want to change the output in the 'old stable' 5.0, though.

Regards,

Heikki
[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:43] 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 20:43] James Day 
I've filed new bug #39460 for the removal of this output without following the deprecated feature process and suggested printing the output but with zero value as the fix.
[16 Sep 2008 4:54] Paul DuBois 
Noted in 6.0.7 changelog.
[5 May 2010 15:17] 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 15:53] Paul DuBois 
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:57] 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)