Hi! On Sep 04, [email protected] wrote: > On 9/3/2019 4:27 PM, Sergei Golubchik wrote: > > On Sep 03, [email protected] 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 [email protected] _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
