Bug #82774 | Reasonably easy to generate mysqld semi-hang. Input file is 1K lines. | ||
---|---|---|---|
Submitted: | 29 Aug 2016 3:13 | Modified: | 13 Apr 2017 22:01 |
Reporter: | Roel Van de Paar | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
Version: | 5.7.13 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[29 Aug 2016 3:13]
Roel Van de Paar
[29 Aug 2016 3:17]
Roel Van de Paar
out3.sql, pquery_h1, and full processlist
Attachment: 82774_bug_bundle.tar.gz (application/x-gzip, text), 2.00 MiB.
[29 Aug 2016 3:24]
Roel Van de Paar
I've also been able to generate the lockup with 10 threads.
[29 Aug 2016 3:25]
Roel Van de Paar
As DROP TABLE t1; / t2; / t3; is part of the grammar, it's extremely unlikely that enough data is compiled for it to be the cause of the lockup.
[29 Aug 2016 3:26]
Roel Van de Paar
A GDB trace should be easy to grab.
[29 Aug 2016 4:48]
MySQL Verification Team
for tests, to avoid hanging you can set: --innodb-lock-wait-timeout=1 --lock-wait-timeout=1 i'm guessing the insert/update are getting locked for 51 seconds, then the flush/ddl follows that and waits for 1 year. but why is this a bug if the config allows it?
[30 Aug 2016 4:22]
Roel Van de Paar
Because it takes so long? With 10 threads and a high end machine (i7/ssd) and small queries (some larger), one would not expect something to lockup mysqld for > 30-40 seconds?
[30 Aug 2016 5:28]
MySQL Verification Team
I don't think it is a "lockup". it's probably a timed wait, due to those timeout settings mentioned. To troubleshoot exact cause you'd need to interrogate the sys schema or these : https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html if it was a real lockup then KILL sql command wouldn't work, and the lockup would never resolve itself after timeouts expire!
[30 Aug 2016 5:32]
Roel Van de Paar
Agreed that lockup is wrong word choice. Changed it to semi-hang, which I still believe it is. i.e. at the source of it there must be some interaction/step which goes wrong for it 'hang' as long as it does...
[30 Aug 2016 5:53]
MySQL Verification Team
But what is the bug here? Metadata locking is required since fix of Bug #989 Default timeouts are: innodb-lock-wait-timeout 50 lock-wait-timeout 31536000 to maintain backward compatibility. So, set those to 1 to reduce it. https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html Exact statements involved can be seen via I_S and P_S.
[30 Aug 2016 5:55]
Roel Van de Paar
Aha, think I am starting to understand your point. So would removing all locks from the grammar achieve the same?
[30 Aug 2016 5:57]
Roel Van de Paar
(inc transactions...)
[10 Apr 2017 15:35]
MySQL Verification Team
Hi Roel, I fail to understand your question. What exactly do you mean by: " So would removing all locks from the grammar achieve the same? " Thanks in advance.
[11 Apr 2017 13:54]
MySQL Verification Team
Still waiting.
[12 Apr 2017 23:39]
Roel Van de Paar
Did some further thinking on this bug. 1] First, to answer Sinisa's question; my thought was to remove locking from testing. Bad idea. Ignore my last comment. 2] So we know that at some point we run into a lock timeout, OK 3] But, the real question is; why do we run into a lock timeout when at the same time all of the transactions are long-running? i.e. should not one of them have been processing [quickly]? 4] It seems that (in this particular case) these two transactions are live/processing; DELETE FROM t2 INSERT INTO t2 VALUES (1.e+2,10-10-2014,2990) and both of those would presumably complete in a few microsec. Is all in all this not some sort of race condition or incorrect locking effect?
[13 Apr 2017 6:53]
MySQL Verification Team
IMHO this report is simply 'not a bug' in its current form.. At least the report is not specific enough to be taken as a bug. If we want to diagnose why two statements hang: DELETE FROM t2 INSERT INTO t2 VALUES (1.e+2,10-10-2014,2990) There are proper methods to do that. They start with checking: https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html
[13 Apr 2017 14:18]
MySQL Verification Team
Roel, Sorry, but I have to agree with Shane on this issue. With one small addition. Beside InnoDB locks, you seem to be hitting MDL locks. These locks also require waiting in certain situations. Not a bug, until provided with some reproducible test case, which would showcase some, totally, unnecessary or inappropriate locking.
[13 Apr 2017 14:21]
MySQL Verification Team
Roel, One recommended reading for you. Will not solve these problems, but might give you some ideas: http://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
[13 Apr 2017 22:01]
Roel Van de Paar
Fair enough, thanks Shane and Sinisa!