Bug #34409 LOCK_plugin contention via ha_release_temporary_latches/plugin_foreach
Submitted: 8 Feb 2008 11:33 Modified: 28 Jul 2008 15:11
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Sergei Golubchik CPU Architecture:Any

[8 Feb 2008 11:33] Sergei Golubchik
Description:
Recent benchmarks has shows a noticeable contention on LOCK_plugin in plugin_foreach(), called from ha_release_temporary_latches().

How to repeat:
.

Suggested fix:
ha_release_temporary_latches() shouldn't use plugin_foreach(), but should iterate the list of locked plugins in lex.
[21 Feb 2008 2: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/42727

ChangeSet@1.2525, 2008-02-20 18:23:08-08:00, acurtis@xiphis.org +6 -0
  Bug#34409
    "LOCK_plugin contention via... plugin_foreach()"
    First commit draft for comments/review.
[3 Jun 2008 16:55] Heikki Tuuri
Since I presume only InnoDB uses this call, we could remove the LOCK_plugin mutex call altogether?

handler.cc in an oldish 5.1:

"
static my_bool release_temporary_latches(THD *thd, plugin_ref plugin,
                                 void *unused)
{
  handlerton *hton= plugin_data(plugin, handlerton *);

  if (hton->state == SHOW_OPTION_YES && hton->release_temporary_latches)
    hton->release_temporary_latches(hton, thd);

  return FALSE;
}

int ha_release_temporary_latches(THD *thd)
{
  plugin_foreach(thd, release_temporary_latches, MYSQL_STORAGE_ENGINE_PLUGIN,
                 NULL);

  return 0;
}
"
[15 Jul 2008 3:33] Mark Callaghan
When does this get into a RC? It doesn't appear to be in 5.1.26.
[15 Jul 2008 7:37] Sergei Golubchik
It doesn't, because it isn't fixed yet, there's no patch attached (no approved patch at least), the bug isn't closed yet.

But we plan  to fix it in the next 5.1 release.
[18 Jul 2008 13:16] Mark Callaghan
I ran a suite of benchmarks (sysbench oltp, sysbench oltp readonly, concurrent inserts, concurrent joins, tpc-b, wisconsin). Performance for 5.1.25 with InnoDB was similar to the faster 5.0 versions for all of the tests except tpc-b. In that case it was ~25% slower. I think this is the cause.

I hope 5.1 doesn't go GA without a fix for this.
[18 Jul 2008 16:23] Mark Callaghan
I will update with results after removing that lock so I can stop speculating. Thanks for the pointer to the other problem.
[18 Jul 2008 16:27] Mark Callaghan
The referenced bug is http://bugs.mysql.com/bug.php?id=38185
[21 Jul 2008 9:13] 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/50100

2671 Sergei Golubchik	2008-07-21
      Bug#34409 LOCK_plugin contention via ha_release_temporary_latches/plugin_foreach
      use thread-local data structures in ha_release_temporary_latches()
[21 Jul 2008 9:43] Konstantin Osipov
Approved on IRC with one review comment.
[21 Jul 2008 10:01] 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/50106

2671 Sergei Golubchik	2008-07-21
      Bug#34409 LOCK_plugin contention via ha_release_temporary_latches/plugin_foreach
      use thread-local data structures in ha_release_temporary_latches()
[22 Jul 2008 18:40] Bugs System
Pushed into 5.1.28
[25 Jul 2008 13:24] Paul Dubois
How can I describe this problem so that its impact is apparent to changelog readers? Plugins has excessive lock contention that affected performance?
[25 Jul 2008 15:56] Sergei Golubchik
Fixed a slowdown significant when many queries executing SELECTs that return many rows from InnoDB tables were running concurrently.
[25 Jul 2008 16:26] Paul Dubois
Noted in 5.1.28 changelog.

A significant slowdown occurred when many SELECT statements that
return many rows from InnoDB tables were running concurrently.

Setting report to Patch Queued pending push of fix into 6.0.x.
[28 Jul 2008 14:02] Georgi Kodinov
Pushed into 6.0.7-alpha
[28 Jul 2008 14:45] Bugs System
Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)
[28 Jul 2008 15:11] Paul Dubois
Noted in 6.0.7 changelog.
[28 Jul 2008 16:44] Bugs System
Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)
[14 Sep 2008 1:31] Bugs System
Pushed into 6.0.7-alpha  (revid:serg@mysql.com-20080721100122-bgt8gldl1hqoxafc) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)