| Bug #80060 | Concurrent TRUNCATE TABLEs cause stalls | ||
|---|---|---|---|
| Submitted: | 19 Jan 2016 23:43 | Modified: | 12 Jan 2017 12:44 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.6.* | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Jan 2016 23:43]
Domas Mituzas
[29 Jan 2016 16:33]
MySQL Verification Team
Hi Domas, Thank you for your bug report. I need to try to repeat it, but I used rather small tables, so no luck so far , as it is tough to repeat a necessary concurrency. So, how big should be tables to be TRUNCATEd in order to get the same outcome as you did ??
[1 Feb 2016 18:19]
MySQL Verification Team
Domas, I have tried with very large tables. I made sure that adaptive hash index is on. Still, I am not able to repeat it. I have run it several times with 5.6.25 and each time first two tables were truncated, while I got the output of the third one, without any problems. Just like you, I ran those 3 statements in parallel, just by using bash shell and separating each command with '&'. Can you tell me other parameters which might influence that the bug pops up ??? I must also tell you that my iMac is 6.5 years old, runs 10.6.8, with 2.66 GHz Intel Core Duo and 4 Gb of RAM. Please, provide further info that would help me repeat the bug.
[1 Feb 2016 21:52]
Domas Mituzas
table being truncated can be empty, you just need lots of pages on the LRU (so, large non-free buffer pool)
[2 Feb 2016 17:42]
MySQL Verification Team
Trying to make a test case.
[3 Feb 2016 16:55]
MySQL Verification Team
Hi Domas, I have done lots of work in attempting to reproduce this bug. I used buffer pool of 2 Gb. I have ran several range queries (each using index) , which covered entire contents of four different tables, each containing at least over 15.000 rows and being at least 200 Mb big. I ran those several times, after which I attempted to run your test case. I ran your test case and it worked quite well. I then restored first two tables (that have been truncated) and repeated the entire procedure all over. There were no problems, except that it was not lightning fast. So, no luck yet. What else do you think that I should attempt ??? Have a nice time.
[3 Feb 2016 18:13]
Domas Mituzas
Sinisa, oh yes, buffer pool has to be large enough. In our case it was probably more like 50GB, not 5GB, but it is not unlikely to have 200GB buffer pools nowadays (though some people take it far further). If you want to verify bugs like this on your 6.5 year old iMac, you should actually pay attention to details in the bug and either somehow mimic large LRU scans, add sleeps or something. Or you could repro this with a large enough machine. Even 6.5 years ago server class machines could have tens of gigabytes of buffer pool.
[7 Apr 2016 13:24]
MySQL Verification Team
Domas, I have been running experiments with 200 Gb buffer pool for over a month. I managed to fill up the tables (each with 8 columns) with 1.5 to 2.0 million rows. I had three indices and ran queries that filled up the AHI. I was not able to cause hangup with: truncate & truncate & select ... & The best that I was able to achieve is to cause a long stall. Last night, it took the longest, 16 hours for those three commands to finish. I can verify this bug as performance bug, but not as hangup bug. Your thoughts ????
[7 Apr 2016 16:00]
Domas Mituzas
Sinisa, what the heck are you smoking again. I never said it is "hangup". I said - stall. Let's look a dictionary definition and see if it implies "hangup": verb 1 [ no obj. ] (of a motor vehicle or its engine) stop running, typically because of an overload on the engine: her car stalled at the crossroads. • (of an aircraft or its pilot) reach a condition where the speed is too low to allow effective operation of the controls. • Sailing have insufficient wind power in the sails to give controlled motion. • [ with obj. ] cause (an engine, vehicle, aircraft, or boat) to stall. 2 stop or cause to stop making progress: [ no obj. ] : his career had stalled, hers taken off | [ with obj. ] : the government has stalled the much-needed project. • speak or act in a deliberately vague way in order to gain more time to deal with a question or issue; prevaricate: she was stalling for time. • [ with obj. ] delay or divert (someone) by stalling: stall him until I've had time to take a look. Nope, it does not. Just server operation stops for a while and then resumes.
[7 Apr 2016 16:04]
MySQL Verification Team
Domas, Then I have verified your bug !!!! Fully verified stalling bug in AHI.
[7 Apr 2016 16:20]
Domas Mituzas
this is a bug with AHI disabled too.
[7 Apr 2016 16:38]
MySQL Verification Team
Domas, Thanks for the info. Detailed instructions to repeat the bug are huge, so they are prepared for a developer that will be assigned. Also, I had to generate a very large amount of data.
[7 Apr 2016 16:52]
Domas Mituzas
thats weird, one can prepare this testcase quite quick by writing tens of gigs of blob data (to populate buffer pool), and then creating and dropping empty tables.
[12 Jan 2017 12:44]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.7.18, 8.0.1 release, and here's the changelog entry: A TRUNCATE TABLE operation held the dict_sys mutex while scanning for and removing pages from the buffer pool, causing concurrent DDL operations to stall. The mutex is now released during the scan and acquired again when the scan is completed. Thank you for the bug report.
[27 Sep 2017 19:45]
MySQL Verification Team
https://bugs.mysql.com/bug.php?id=87884 marked as duplicate of this one.
