Hi! On Sep 04, mariadb@biblestuph.com wrote:
On 9/3/2019 4:27 PM, Sergei Golubchik wrote:
On Sep 03, mariadb@biblestuph.com 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. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org