Hi!
On Sep 04, mariadb@xxxxxxxxxxxxxx wrote:
On 9/3/2019 4:27 PM, Sergei Golubchik wrote:
On Sep 03, mariadb@xxxxxxxxxxxxxx wrote:
Can someone fill me in as to what's happening behind the scenes
when a thread has the state "Unlocking tables?"
It literally does just that, unlock tables.
There's nothing that could cause a long wait, as far as I can see.
I looked here but did not see it listed:
https://mariadb.com/kb/en/library/general-thread-states/
Specifically, I sometimes see threads spending a lot of time in this
state, and am looking to do some troubleshooting as to the cause of the
delay. I.E., what could cause a thread to be delayed while in that state.
What statement spends a lot of time in this state?
May be the table unlocking was long done, but some rogue code didn't
reset the state.
The most common statements I see waiting in this state are multi-record
inserts (with thousands of records).
That might explain it, particularly if you have a really big keycache
(which you might have, given that your tables are MyISAM).
When a MyISAM table is unlocked, it writes all its dirty pages from the
keycache to disk. With a big keycache and after a big multi-record
insert, the table might have lots of dirty pages to write.
You can try --delay-key-write=ALL to see if that helps. But increases
the chance of a table corruption, if the server crashes.
I think you can try it for a short while just to see if it helps.
Or, may be, in a test environment, if you have one.
