MySQL Bugs: #34409: LOCK_plugin contention via ha_release_temporary_latches/plugin

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

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.

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.

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;
}
"

When does this get into a RC? It doesn't appear to be in 5.1.26.

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.

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.

I will update with results after removing that lock so I can stop speculating. Thanks for the pointer to the other problem.

The referenced bug is http://bugs.mysql.com/bug.php?id=38185

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()

Approved on IRC with one review comment.

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()

Pushed into 5.1.28

How can I describe this problem so that its impact is apparent to changelog readers? Plugins has excessive lock contention that affected performance?

Fixed a slowdown significant when many queries executing SELECTs that return many rows from InnoDB tables were running concurrently.

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.

Pushed into 6.0.7-alpha

Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)

Noted in 6.0.7 changelog.

Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)

Pushed into 6.0.7-alpha  (revid:serg@mysql.com-20080721100122-bgt8gldl1hqoxafc) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)