[Maria-discuss] Thread State: Unlocking tables
Can someone fill me in as to what's happening behind the scenes when a thread has the state "Unlocking tables?" 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. They're (currently) MyISAM tables if it makes a difference. Thanks! Dan
Hi, mariadb! 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. What other threads are doing at this time? "Unlocking tables" should be fast, but still there are some mutex locks there, so if another thread got stuck holding that mutex (I don't see how, but still), it might've blocked unlocking.
They're (currently) MyISAM tables if it makes a difference.
It does. With InnoDB it's a bit more complicated. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Thanks for the reply. On 9/3/2019 4:27 PM, Sergei Golubchik wrote:
Hi, mariadb!
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).
What other threads are doing at this time? "Unlocking tables" should be fast, but still there are some mutex locks there, so if another thread got stuck holding that mutex (I don't see how, but still), it might've blocked unlocking.
An example from this past weekend: INSERT IGNORE INTO t1 .... The thread appeared to sit in Unlocking tables for at least 17 seconds (not dreadful, but enough to throw the feeder app out of whack, since it normally only takes a few seconds to complete). The only other thread I see that was busy the entire time the problem thread was in "Unlocking tables" is: Query-Repair by sorting ALTER TABLE t2 ENABLE KEYS Note that they were working on different tables. The ALTER TABLE t2 started before, and finished after the INSERT IGNORE INTO t1 executed. Other queries came and went during the time, or sat sleeping. I don't see anything trying to do anything with t2, but admittedly my data is not perfectly granular (there may been things that completely executed between my data logs). The problem query was followed by another just like it (different data set being entered, but same table) that sat in Unlocking Tables for at least 10 seconds. "Normally" these inserts take a second each, if that. Again, only the ALTER TABLE t2 was running the whole time.
They're (currently) MyISAM tables if it makes a difference.
It does. With InnoDB it's a bit more complicated.
And I should mention we also have concurrent_insert set to ALWAYS (2). A possible factor? Dan
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
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
On 9/7/2019 6:54 AM, Sergei Golubchik wrote:
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.
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 security@mariadb.org
participants (2)
-
mariadb＠biblestuph.com -
Sergei Golubchik