| Bug #45813 | Warning: a long semaphore wait | ||
|---|---|---|---|
| Submitted: | 28 Jun 23:12 | Modified: | 30 Jun 19:55 |
| Reporter: | Nickolay Vinogradov | ||
| Status: | Duplicate | ||
| Category: | Server: InnoDB | Severity: | S2 (Serious) |
| Version: | 5.1.34 | OS: | Linux (SUSE Linux Enterprise Server 10 SP2 (x86_64)) |
| Assigned to: | Target Version: | ||
| Tags: | long semaphore wait | ||
[28 Jun 23:12]
Nickolay Vinogradov
[28 Jun 23:16]
Nickolay Vinogradov
InnoDB Monitor Output
Attachment: Innodb_Montor.log (application/octet-stream, text), 140.63 KiB.
[28 Jun 23:22]
Nickolay Vinogradov
Hmmm. it's very interesting: "Thread 1168980288 has waited Thread 1168980288". Is it OK? --Thread 1168980288 has waited at row/row0mysql.c line 1720 for 247.00 seconds the semaphore: X-lock on RW-latch at 0xc73b20 created in file dict/dict0dict.c line 728 a writer (thread id 1168980288) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 ---TRANSACTION 11 2785646909, ACTIVE 247 sec, process no 8320, OS thread id 1168980288 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 ROLLING BACK 2 lock struct(s), heap size 368, 1 row lock(s) MySQL thread id 212786, query id 792903208 10.0.0.6 spaces update INSERT INTO spaces_blacklist SET user_id='384522',enemy_id='899930'
[29 Jun 10:29]
Valeriy Kravchuk
Thank you for the problem report. Please, send your my.cnf. How many CPUs/cores do you have?
[29 Jun 11:41]
Nickolay Vinogradov
my.cnf
Attachment: my.cnf (application/octet-stream, text), 20.73 KiB.
[29 Jun 11:45]
Nickolay Vinogradov
8 cores (2 x Xeon E5405) Mem: 32G
[29 Jun 12:05]
Valeriy Kravchuk
I think this is related to large number of active InnoDB threads and/or purge activity. If the problem is easily repeatable, can you, please, try to use different values for the following parameters: innodb_max_purge_lag = 100000 innodb_max_purge_lag = 1000000 innodb_max_purge_lag = 10000000 and smaller thread concurrency: innodb_thread_concurrency = 16
[29 Jun 13:42]
Nickolay Vinogradov
The problem is NOT easily repeatable. This is production server, under heavy load.
[29 Jun 13:57]
Valeriy Kravchuk
We had similar bug reports in the past (see bug #45070, for example). They are hardly repeatable and are related to high concurrent load. So, I'd say it's more a matter of server tuning than a bug by itself. Assertion and resulting crash are intended and were added by InnoDB to prevent hangs and force DBA to pay attention to server's tuning for the load.
[29 Jun 15:49]
Valeriy Kravchuk
Isn't it a duplicate of your other open bug report, http://bugs.mysql.com/bug.php?id=44841, actually?
[29 Jun 18:10]
Nickolay Vinogradov
One thread deadlock himself, a you call it problem with the server tuning?
[29 Jun 18:12]
Nickolay Vinogradov
No, it's not a DUP, because now a thread deadlock himself.
[29 Jun 18:44]
Valeriy Kravchuk
In that older bug you had similar situation with thread "deadlocking" itself, see initial description: --Thread 1173395776 has waited at row/row0mysql.c line 1720 for 250.00 seconds the semaphore: X-lock on RW-latch at 0xc73b20 created in file dict/dict0dict.c line 728 a writer (thread id 1173395776) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file row/row0mysql.c line 1685 Last time write locked in file row/row0mysql.c line 1720 and it was doing ROLLBACK: ---TRANSACTION 9 1144471130, ACTIVE 250 sec, process no 10361, OS thread id 1173395776 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 ROLLING BACK 2 lock struct(s), heap size 368, 1 row lock(s) That's why I consider this report a duplicate. Please, check.
[29 Jun 19:07]
Nickolay Vinogradov
OK, it's DUP. But why you call it 'tuning problem' ?
[30 Jun 19:55]
Valeriy Kravchuk
So, this is a duplicate of bug #44841. Let's continue discussion there...
