Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.3.18
Description
I'm having this issue in my production system where it appears that only 1 query can execute with a temporary table at a time, and all other queries that require a temporary table are getting stuck in a "waiting for metadata lock" state.
This is causing an issue when the executing query is taking a considerable time to execute, and bots/crons then consume all available connections until the system crashes, I log into the system and kill the stalled query and all other queries just execute.
Is there a setting that I can use to allow multiple temporary tables at the same time? Or is this a known problem where the creation of temporary table is not releasing the metadata lock until the deletion of the temporary table?
Attachments
Activity
4dallasc, temporary tables cannot cause "Waiting for table metadata lock" state, they're not locked with metadata locks. But I can see huge RENAME TABLES statement, which is likely to be guilty for these waits.
Howdy svoj
Yeah you say what should be happening, but that doesn't mean it's actually happening the way you are expect. When you look at the Innodb Status, there's only one transaction operating, and it's not renaming a table. The transaction/connection might have originally renamed a temporary table, but this query is not renaming a table. When you look at the show processlist, you can see all the waiting queries that should be free to execute, but are consuming all the connections.
Also I can't remember any code that does rename a temporary table. Each query obtains a temporary table name, and uses the same name through out the query. In the particular query that's causing the issue, the temporary table is used to sort results by relavents and adds that field via a join with a temporary table. I don't know if MariaDB creates the temporary table then renames them.
4dallasc, in the "full process list.txt" that you've attached there is this RENAME:
RENAME TABLE catalog_category_product_index_store1 TO catalog_category_product_index_store1_outdated,catalog_category_product_index_store1_replica TO catalog_category_product_index_store1,catalog_category_product_index_store1_outdated TO catalog_category_product_index_store1_replica,catalog_category_product_index_store2 TO catalog_category_product_index_store2_outdated,catalog_category_product_index_store2_replica TO catalog_category_product_index_store2,catalog_category_product_index_store2_outdated TO catalog_category_product_index_store2_replica,catalog_category_product_index_store3 TO catalog_category_product_index_store3_outdated,catalog_category_product_index_store3_replica TO catalog_category_product_index_store3,catalog_category_product_index_store3_outdated TO catalog_category_product_index_store3_replica,catalog_category_product_index_store4 TO catalog_category_product_index_store4_outdated,catalog_category_product_index_store4_replica TO catalog_category_product_index_store4,catalog_category_product_index_store4_outdated TO catalog_category_product_index_store4_replica,catalog_category_product_index_store5 TO catalog_category_product_index_store5_outdated,catalog_category_product_index_store5_replica TO catalog_category_product_index_store5,catalog_category_product_index_store5_outdated TO catalog_category_product_index_store5_replica,catalog_category_product_index_store6 TO catalog_category_product_index_store6_outdated,catalog_category_product_index_store6_replica TO catalog_category_product_index_store6,catalog_category_product_index_store6_outdated TO catalog_category_product_index_store6_replica,catalog_category_product_index_store7 TO catalog_category_product_index_store7_outdated,catalog_category_product_index_store7_replica TO catalog_category_product_index_store7,catalog_category_product_index_store7_outdated TO catalog_category_product_index_store7_replica,catalog_category_product_index_store8 TO catalog_category_product_index_store8_outdated,catalog_category_product_index_store8_replica TO catalog_category_product_index_store8,catalog_category_product_index_store8_outdated TO catalog_category_product_index_store8_replica,catalog_category_product_index_store9 TO catalog_category_product_index_store9_outdated,catalog_category_product_index_store9_replica TO catalog_category_product_index_store9,catalog_category_product_index_store9_outdated TO catalog_category_product_index_store9_replica,catalog_category_product_index_store10 TO catalog_category_product_index_store10_outdated,catalog_category_product_index_store10_replica TO catalog_category_product_index_store10,catalog_category_product_index_store10_outdated TO catalog_category_product_index_store10_replica,catalog_category_product_index_store11 TO catalog_category_product_index_store11_outdated,catalog_category_product_index_store11_replica TO catalog_category_product_index_store11,catalog_category_product_index_store11_outdated TO catalog_category_product_index_store11_replica,catalog_category_product_index_store12 TO catalog_category_product_index_store12_outdated,catalog_category_product_index_store12_replica TO catalog_category_product_index_store12,catalog_category_product_index_store12_outdated TO catalog_category_product_index_store12_replica,catalog_category_product_index_store13 TO catalog_category_product_index_store13_outdated,catalog_category_product_index_store13_replica TO catalog_category_product_index_store13,catalog_category_product_index_store13_outdated TO catalog_category_product_index_store13_replica,catalog_category_product_index_store14 TO catalog_category_product_index_store14_outdated,catalog_category_product_index_store14_replica TO catalog_category_product_index_store14,catalog_category_product_index_store14_outdated TO catalog_category_product_index_store14_replica,catalog_category_product_index_store15 TO catalog_category_product_index_store15_outdated,catalog_category_product_index_store15_replica TO catalog_category_product_index_store15,catalog_category_product_index_store15_outdated TO catalog_category_product_index_store15_replica,catalog_category_product_index_store16 TO catalog_category_product_index_store16_outdated,catalog_category_product_index_store16_replica TO catalog_category_product_index_store16,catalog_category_product_index_store16_outdated TO catalog_category_product_index_store16_replica,catalog_category_product_index_store17 TO catalog_category_product_index_store17_outdated,catalog_category_product_index_store17_replica TO catalog_category_product_index_store17,catalog_category_product_index_store17_outdated TO catalog_category_product_index_store17_replica,catalog_category_product_index_store18 TO catalog_category_product_index_store18_outdated,catalog_category_product_index_store18_replica TO catalog_category_product_index_store18,catalog_category_product_index_store18_outdated TO catalog_category_product_index_store18_replica,catalog_category_product_index_store19 TO catalog_category_product_index_store19_outdated,catalog_category_product_index_store19_replica TO catalog_category_product_index_store19,catalog_category_product_index_store19_outdated TO catalog_category_product_index_store19_replica,catalog_category_product_index_store20 TO catalog_category_product_index_store20_outdated,catalog_category_product_index_store20_replica TO catalog_category_product_index_store20,catalog_category_product_index_store20_outdated TO catalog_category_product_index_store20_replica,catalog_category_product_index_store21 TO catalog_category_product_index_store21_outdated,catalog_category_product_index_store21_replica TO catalog_category_product_index_store21,catalog_category_product_index_store21_outdated TO catalog_category_product_index_store21_replica,catalog_category_product_index_store22 TO catalog_category_product_index_store22_outdated,catalog_category_product_index_store22_replica TO catalog_category_product_index_store22,catalog_category_product_index_store22_outdated TO catalog_category_product_index_store22_replica,catalog_category_product_index_store23 TO catalog_category_product_index_store23_outdated,catalog_category_product_index_store23_replica TO catalog_category_product_index_store23,catalog_category_product_index_store23_outdated TO catalog_category_product_index_store23_replica,catalog_category_product_index_store24 TO catalog_category_product_index_store24_outdated,catalog_category_product_index_store24_replica TO catalog_category_product_index_store24,catalog_category_product_index_store24_outdated TO catalog_category_product_index_store24_replica,catalog_category_product_index_store25 TO catalog_category_product_index_store25_outdated,catalog_category_product_index_store25_replica TO catalog_category_product_index_store25,catalog_category_product_index_store25_outdated TO catalog_category_product_index_store25_replica,catalog_category_product_index_store26 TO catalog_category_product_index_store26_outdated,catalog_category_product_index_store26_replica TO catalog_category_product_index_store26,catalog_category_product_index_store26_outdated TO catalog_category_product_index_store26_replica,catalog_category_product_index_store27 TO catalog_category_product_index_store27_outdated,catalog_category_product_index_store27_replica TO catalog_category_product_index_store27,catalog_category_product_index_store27_outdated TO catalog_category_product_index_store27_replica,catalog_category_product_index_store28 TO catalog_category_product_index_store28_outdated,catalog_category_product_index_store28_replica TO catalog_category_product_index_store28,catalog_category_product_index_store28_outdated TO catalog_category_product_index_store28_replica,catalog_category_product_index_store29 TO catalog_category_product_index_store29_outdated,catalog_category_product_index_store29_replica TO catalog_category_product_index_store29,catalog_category_product_index_store29_outdated TO catalog_category_product_index_store29_replica,catalog_category_product_index_store30 TO catalog_category_product_index_store30_outdated,catalog_category_product_index_store30_replica TO catalog_category_product_index_store30,catalog_category_product_index_store30_outdated TO catalog_category_product_index_store30_replica,catalog_category_product_index_store31 TO catalog_category_product_index_store31_outdated,catalog_category_product_index_store31_replica TO catalog_category_product_index_store31,catalog_category_product_index_store31_outdated TO catalog_category_product_index_store31_replica,catalog_category_product_index_store32 TO catalog_category_product_index_store32_outdated,catalog_category_product_index_store32_replica TO catalog_category_product_index_store32,catalog_category_product_index_store32_outdated TO catalog_category_product_index_store32_replica,catalog_category_product_index_store33 TO catalog_category_product_index_store33_outdated,catalog_category_product_index_store33_replica TO catalog_category_product_index_store33,catalog_category_product_index_store33_outdated TO catalog_category_product_index_store33_replica,catalog_category_product_index_store34 TO catalog_category_product_index_store34_outdated,catalog_category_product_index_store34_replica TO catalog_category_product_index_store34,catalog_category_product_index_store34_outdated TO catalog_category_product_index_store34_replica,catalog_category_product_index_store35 TO catalog_category_product_index_store35_outdated,catalog_category_product_index_store35_replica TO catalog_category_product_index_store35,catalog_category_product_index_store35_outdated TO catalog_category_product_index_store35_replica,catalog_category_product_index_store36 TO catalog_category_product_index_store36_outdated,catalog_category_product_index_store36_replica TO catalog_category_product_index_store36,catalog_category_product_index_store36_outdated TO catalog_category_product_index_store36_replica,catalog_category_product_index_store37 TO catalog_category_product_index_store37_outdated,catalog_category_product_index_store37_replica TO catalog_category_product_index_store37,catalog_category_product_index_store37_outdated TO catalog_category_product_index_store37_replica,catalog_category_product_index_store38 TO catalog_category_product_index_store38_outdated,catalog_category_product_index_store38_replica TO catalog_category_product_index_store38,catalog_category_product_index_store38_outdated TO catalog_category_product_index_store38_replica,catalog_category_product_index_store39 TO catalog_category_product_index_store39_outdated,catalog_category_product_index_store39_replica TO catalog_category_product_index_store39,catalog_category_product_index_store39_outdated TO catalog_category_product_index_store39_replica,catalog_category_product_index_store40 TO catalog_category_product_index_store40_outdated,catalog_category_product_index_store40_replica TO catalog_category_product_index_store40,catalog_category_product_index_store40_outdated TO catalog_category_product_index_store40_replica,catalog_category_product_index_store41 TO catalog_category_product_index_store41_outdated,catalog_category_product_index_store41_replica TO catalog_category_product_index_store41,catalog_category_product_index_store41_outdated TO catalog_category_product_index_store41_replica,catalog_category_product_index_store42 TO catalog_category_product_index_store42_outdated,catalog_category_product_index_store42_replica TO catalog_category_product_index_store42,catalog_category_product_index_store42_outdated TO catalog_category_product_index_store42_replica,catalog_category_product_index_store43 TO catalog_category_product_index_store43_outdated,catalog_category_product_index_store43_replica TO catalog_category_product_index_store43,catalog_category_product_index_store43_outdated TO catalog_category_product_index_store43_replica,catalog_category_product_index_store44 TO catalog_category_product_index_store44_outdated,catalog_category_product_index_store44_replica TO catalog_category_product_index_store44,catalog_category_product_index_store44_outdated TO catalog_category_product_index_store44_replica,catalog_category_product_index_store45 TO catalog_category_product_index_store45_outdated,catalog_category_product_index_store45_replica TO catalog_category_product_index_store45,catalog_category_product_index_store45_outdated TO catalog_category_product_index_store45_replica,catalog_category_product_index_store46 TO catalog_category_product_index_store46_outdated,catalog_category_product_index_store46_replica TO catalog_category_product_index_store46,catalog_category_product_index_store46_outdated TO catalog_category_product_index_store46_replica,catalog_category_product_index_store47 TO catalog_category_product_index_store47_outdated,catalog_category_product_index_store47_replica TO catalog_category_product_index_store47,catalog_category_product_index_store47_outdated TO catalog_category_product_index_store47_replica,catalog_category_product_index_store48 TO catalog_category_product_index_store48_outdated,catalog_category_product_index_store48_replica TO catalog_category_product_index_store48,catalog_category_product_index_store48_outdated TO catalog_category_product_index_store48_replica,catalog_category_product_index_store49 TO catalog_category_product_index_store49_outdated,catalog_category_product_index_store49_replica TO catalog_category_product_index_store49,catalog_category_product_index_store49_outdated TO catalog_category_product_index_store49_replica,catalog_category_product_index_store50 TO catalog_category_product_index_store50_outdated,catalog_category_product_index_store50_replica TO catalog_category_product_index_store50,catalog_category_product_index_store50_outdated TO catalog_category_product_index_store50_replica,catalog_category_product_index_store51 TO catalog_category_product_index_store51_outdated,catalog_category_product_index_store51_replica TO catalog_category_product_index_store51,catalog_category_product_index_store51_outdated TO catalog_category_product_index_store51_replica,catalog_category_product_index_store52 TO catalog_category_product_index_store52_outdated,catalog_category_product_index_store52_replica TO catalog_category_product_index_store52,catalog_category_product_index_store52_outdated TO catalog_category_product_index_store52_replica,catalog_category_product_index_store53 TO catalog_category_product_index_store53_outdated,catalog_category_product_index_store53_replica TO catalog_category_product_index_store53,catalog_category_product_index_store53_outdated TO catalog_category_product_index_store53_replica,catalog_category_product_index_store54 TO catalog_category_product_index_store54_outdated,catalog_category_product_index_store54_replica TO catalog_category_product_index_store54,catalog_category_product_index_store54_outdated TO catalog_category_product_index_store54_replica,catalog_category_product_index_store55 TO catalog_category_product_index_store55_outdated,catalog_category_product_index_store55_replica TO catalog_category_product_index_store55,catalog_category_product_index_store55_outdated TO catalog_category_product_index_store55_replica,catalog_category_product_index_store56 TO catalog_category_product_index_store56_outdated,catalog_category_product_index_store56_replica TO catalog_category_product_index_store56,catalog_category_product_index_store56_outdated TO catalog_category_product_index_store56_replica,catalog_category_product_index_store57 TO catalog_category_product_index_store57_outdated,catalog_category_product_index_store57_replica TO catalog_category_product_index_store57,catalog_category_product_index_store57_outdated TO catalog_category_product_index_store57_replica,catalog_category_product_index_store58 TO catalog_category_product_index_store58_outdated,catalog_category_product_index_store58_replica TO catalog_category_product_index_store58,catalog_category_product_index_store58_outdated TO catalog_category_product_index_store58_replica,catalog_category_product_index_store59 TO catalog_category_product_index_store59_outdated,catalog_category_product_index_store59_replica TO catalog_category_product_index_store59,catalog_category_product_index_store59_outdated TO catalog_category_product_index_store59_replica,catalog_category_product_index_store60 TO catalog_category_product_index_store60_outdated,catalog_category_product_index_store60_replica TO catalog_category_product_index_store60,catalog_category_product_index_store60_outdated TO catalog_category_product_index_store60_replica,catalog_category_product_index_store61 TO catalog_category_product_index_store61_outdated,catalog_category_product_index_store61_replica TO catalog_category_product_index_store61,catalog_category_product_index_store61_outdated TO catalog_category_product_index_store61_replica,catalog_category_product_index_store62 TO catalog_category_product_index_store62_outdated,catalog_category_product_index_store62_replica TO catalog_category_product_index_store62,catalog_category_product_index_store62_outdated TO catalog_category_product_index_store62_replica,catalog_category_product_index_store63 TO catalog_category_product_index_store63_outdated,catalog_category_product_index_store63_replica TO catalog_category_product_index_store63,catalog_category_product_index_store63_outdated TO catalog_category_product_index_store63_replica,catalog_category_product_index_store64 TO catalog_category_product_index_store64_outdated,catalog_category_product_index_store64_replica TO catalog_category_product_index_store64,catalog_category_product_index_store64_outdated TO catalog_category_product_index_store64_replica,catalog_category_product_index_store65 TO catalog_category_product_index_store65_outdated,catalog_category_product_index_store65_replica TO catalog_category_product_index_store65,catalog_category_product_index_store65_outdated TO catalog_category_product_index_store65_replica,catalog_category_product_index_store66 TO catalog_category_product_index_store66_outdated,catalog_category_product_index_store66_replica TO catalog_category_product_index_store66,catalog_category_product_index_store66_outdated TO catalog_category_product_index_store66_replica,catalog_category_product_index_store67 TO catalog_category_product_index_store67_outdated,catalog_category_product_index_store67_replica TO catalog_category_product_index_store67,catalog_category_product_index_store67_outdated TO catalog_category_product_index_store67_replica,catalog_category_product_index_store68 TO catalog_category_product_index_store68_outdated,catalog_category_product_index_store68_replica TO catalog_category_product_index_store68,catalog_category_product_index_store68_outdated TO catalog_category_product_index_store68_replica,catalog_category_product_index_store69 TO catalog_category_product_index_store69_outdated,catalog_category_product_index_store69_replica TO catalog_category_product_index_store69,catalog_category_product_index_store69_outdated TO catalog_category_product_index_store69_replica,catalog_category_product_index_store70 TO catalog_category_product_index_store70_outdated,catalog_category_product_index_store70_replica TO catalog_category_product_index_store70,catalog_category_product_index_store70_outdated TO catalog_category_product_index_store70_replica |
I don't think InnoDB status is relevant here. And even if it were, RENAME TABLES didn't yet reach out to InnoDB as it is waiting for metadata locks.
What we should hunt for is a query holding RENAME TABLES. Temporary table may be indirectly involved though.
4dallasc, please install metadata_lock_info plugin, and provide output of
SELECT * FROM information_schema.metadata_lock_info;
I think Sergej Vortovich was correct, and the huge RENAME is the culprint, and temporary tables not so much, but perhaps the output of the query above would make it more obvious.
Howdy svoj
Sorry I can't help you more, as it's not only a query related problem, but a data related problem and a user related problem. That is, these queries only became an issue once the database got nearly 1 million entries and the joins got so large that it would take nearly 1 minute on a standalone machine to complete. But once we added the load of numerous bots hitting the website, and 100's of system users at the same time, then this problem really came to the forefront.
When debugging my own database system, where I have access to the source code, I would just add Sleep(1000) statements to simulate a slow query. Then I would have ample time to launch another query to see if it executed okay. In the above processlist & Innodb status, you can see that the query executing was joining a temporary table, while all the waiting queries were trying to create a temporary table. And while this was happening, all other queries that didn't use a temporary table were executing without hindrance.
I don't know if that's enough information for you to track down and squash this bug, but it's all I can really give you, because you need simulate the concurrent users to interfere with the original query.