Bug #24386 Performance degradation caused by instrumentation in mutex_struct
Submitted: 17 Nov 2006 10:53 Modified: 19 Jun 2010 0:10
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0, 5.1 OS:Any (all)
Assigned to: Marko Mäkelä

[17 Nov 2006 10:53] Marko Mäkelä
Description:
Someone (probably Vadim Tkachenko) added a number of fields to mutex_t in order
to implement SHOW MUTEX STATUS. Especially the added mutex->count_using++
in mutex_enter_func() may contribute to the contention originally reported in Bug #22868.

How to repeat:
Run InnoDB on a multiprocessor system with multiple client connections. Observe the poor scalability.

Suggested fix:
Enclose all added fields except count_os_wait in #ifdef UNIV_DEBUG and adapt the code.

To reduce the memory footprint further (to make mutex_t fit in fewer cache lines), also remove mutex->level, mutex->magic_n and possibly mutex->lock_word from non-debug builds.
[20 Nov 2006 13:50] Sergei Golubchik
What speedup do you get with your patch ?
[20 Nov 2006 13:57] Heikki Tuuri
The patch at least saves about 80 bytes per buffer pool frame (in the newest 5.0 tree), that is, 0.5 % of main memory. We do not yet have results if it affects scalability. Since a mutex acquisition with Vadim's code caused two cache lines to be shipped, it might have caused memory bus saturation.
[21 Dec 2006 20:02] Paul Dubois
Noted in 5.0.32 changelog.

The InnoDB mutex structure was simplified to reduce memory load.

Resetting report to In Progress pending decision about whether
to merge this patch into InnoDB 5.1. (The MySQL 5.0 patch has
been null-merged into MySQL 5.1.)
[30 Dec 2006 11:45] Vadim Tkachenko
Marko,

It would be interesting to look at benchmarks in real enviroment and under
profiler tool like Intel Vtune.

Yes, I made that change, but I don't think it is the reason of bad scalability.
[30 Dec 2006 15:18] Heikki Tuuri
Vadim,

looks like this had nothing to do with the thread thrashing problems. But Marko's patch is likely to save some computer memory system bandwidth, since there is less ping-pong of cache lines between processors after Marko's patch.

Best regards,

Heikki
[25 Jan 2007 15:09] Paul Dubois
No further documentation needed.
[5 May 2010 15:07] 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 2:32] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:09] 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:37] 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 7:05] 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 15:33] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:14] 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 13:01] 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:41] 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)