maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx