On 9/7/2019 6:54 AM, Sergei Golubchik wrote:
Hi!
On Sep 04, mari...@biblestuph.com wrote:
On 9/3/2019 4:27 PM, Sergei Golubchik wrote:
On Sep 03, mari...@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.
Thanks, yes, we have delay-key-write=OFF. Long ago we tried =ALL, but
ended up going back to =OFF for exactly the reason you mention. Back
then we had more crashes than we do today and it was causing too much
pain to rebuild the keys. Not so much these days, but not sure if I'm
willing to make that change.
Still it's something to play with, thanks. I might at least give it a
look in our development systems.
Regards,
Sergei
VP of MariaDB Server Engineering
and secur...@mariadb.org
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
