Bug #22868 'Thread thrashing' with > 50 concurrent conns under an upd-intensive workloadw
Submitted: 1 Oct 2006 2:18 Modified: 25 Jun 2008 1:30
Reporter: Sunny Bains Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1,5.0,5.1 OS:Linux (Linux x86_64)
Assigned to: CPU Architecture:Any
Tags: fixed in pre-v6, v6
Triage: D2 (Serious)

[1 Oct 2006 2:18] Sunny Bains
Description:
This bug was previously lumped together with http://bugs.mysql.com/bug.php?id=15815 (see Heikki's comment [15 Aug 13:16]).
Since it is independent of Bug # 15815, I've created this new bug report.

How to repeat:
Using sysbench with > 50 threads doing intensive updates

Suggested fix:
Introduce one event per InnoDB semaphore;
[20 Oct 2006 15:08] Laurent Goujon
Any news on this bug ? Status is patch pending but I see no patch in Files section.

I think I'm currently experiencing this issue on my production server (RHEL4, MySQL 4.1.12, 4xIntel Xeon 64bits) : I'm performing simultenously several INSERT ... ON DUPLICATE KEY UPDATE. However there's less connections (around 20).
[20 Oct 2006 19:03] James Day
Status update: the initial Oracle/InnoDB code for this and bug #15815 has taken a couple of person-months to write but it's currently producing good results and is in test/QA/refinement work at Oracle/InnoDB. That suggests that there's a fair chance that it'll be production-ready by the end of the year. Possibly sooner, possibly later, since it depends on how the testing goes. It involves several critical core components of InnoDB so it's going to have to go slowly with lots of testing, lest it break things.

Please do remember that timelines are always estimates and the real time is "when it's ready". I can estimate based on experience but that's all it is.

Laurent, I suggest that you test your application with 5.0 so that if necessary you can migrate to 5.0.

James Day
Support Engineer, MySQL AB.
[2 Nov 2006 18:22] Heikki Tuuri
I polished Sunny's latest patch a bit.
[8 Nov 2006 14:44] Marko Mäkelä
The patch is for 5.0 only. MySQL/InnoDB 5.1 tries to address this in a different manner.
[9 Nov 2006 9:20] Pascal Borghino
so we have no hope to see this issue fixed on 4.1? As we could not afford yet to migrate to 5.0, you are in troubles.
[10 Nov 2006 19:26] Timothy Smith
Hello, Pascal,

The decision about fixing this in 4.1 has not been made at this time.  Because of the fundamental nature of this fix, it could cause performance problems in some use patterns, even as it fixes performance problems in others.  So it was decided to not add these changes to the 4.1 code at this time.

I apologize for the inconvenience.

Regards,

Timothy
[11 Nov 2006 3:42] Paul Dubois
Noted in 5.0.30 (not 5.0.29) changelog.

InnoDB exhibited thread thrashing with more than 50 concurrent
connections under an update-intensive workload.

Setting report to NDI pending decision about whether to fix in 4.1.
[16 Nov 2006 14:02] Marko Mäkelä
Someone (probably Vadim Tkachenko) added a number of fields to mutex_t in order to implement SHOW ENGINE MUTEX STATUS in 5.0 and 5.1. The mutex->count_using++ in mutex_enter_func() may contribute to the contention. I am working on a patch to enclose most of these fields in #ifdef UNIV_DEBUG. That will also significantly reduce sizeof(mutex_t) and sizeof(buf_block_t).
[17 Nov 2006 10:54] Marko Mäkelä
I reported the issue with the added instrumentation as Bug #24386.
[4 Jan 2007 14:53] Heikki Tuuri
I have now approved the backporting of Marko's patch to 4.1 and 4.0.

I am setting this to 'Patch approved'.
[19 Jan 2007 15:31] Heikki Tuuri
The performance may further improve when http://bugs.mysql.com/bug.php?id=16979 is fixed.
[25 Jan 2007 0:56] Timothy Smith
Pushed to 4.0.29 and 4.1.23 now.
[25 Jan 2007 15:12] Paul Dubois
Noted in 4.0.29, 4.1.23 changelogs.
[25 Jan 2008 12:39] Bugs System
Pushed into 6.0.5-alpha
[20 Feb 2008 23:44] Timothy Smith
The previous comments of 28 Jan are irrelevant (but was mentioned in another bug fix, but no actual changes relative to this fix were pushed at that time).

The fix for this bug has been queued for 5.1 (but NOT 6.0) as part of snapshot 5.1-ss2298.  It is null-merged up to 6.0 at this point, but should have a separate 6.0 snapshot from InnoDB developers to handle it there.
[25 Feb 2008 15:59] Bugs System
Pushed into 5.1.24-rc
[25 Feb 2008 16:05] Bugs System
Pushed into 6.0.5-alpha
[25 Feb 2008 18:53] Paul Dubois
Noted in 5.1.24 changelog.

Resetting to Patch Pending waiting for push into 6.0.x.
[4 Mar 2008 16:28] Heikki Tuuri
The bug report is closed for 5.1, but not for 6.0.
[6 Mar 2008 5:30] Jon Stephens
Fix also noted for 5.1.23-ndb-6.2.14; left PP status pending push to 6.0.
[30 Mar 2008 0:02] Jon Stephens
Fix also noted for 5.1.23-ndb-6.3.11; left status unchanged.
[24 Jun 2008 21:07] Calvin Sun
Merged into 6.0.6-alpha, according to Tim.
[25 Jun 2008 1:30] Paul Dubois
Noted in 6.0.6 changelog.