Bug #36942 Performance problem in lock_get_n_rec_locks (SHOW INNODB STATUS)

• Submitted: 25 May 2008 14:28
• Modified: 16 Sep 2008 6:54
• Reporter: Domas Mituzas
• Status: Closed
• Category: Server: InnoDB
• Severity: S3 (Non-critical)
• Version: 5.0,5.1
• OS: Any
• Assigned to: Timothy Smith
• Target Version: 5.0+
• Triage: D3 (Medium)

[25 May 2008 14: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 15: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

[21 Aug 2008 0: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 19:59] Bugs System

Pushed into 5.1.28  (revid:[e-mail]-20080820221833-brmru9b77ddt3tfx) (version
source revid:[e-mail]-20080821081500-f2d61fh4u61owz3p) (pib:3)

[22 Aug 2008 1: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.

[22 Aug 2008 1: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 21: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.

[14 Sep 2008 1:42] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080820221833-brmru9b77ddt3tfx)
(version source revid:[e-mail]-20080808091208-ht48kyzsk7rim74g) (pib:3)

[15 Sep 2008 22: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 6:54] Paul DuBois

Noted in 6.0.7 changelog.