Comments - Migrated from 10.1.22 to 10.6.7 and queries get stuck unless we manually analyze table.
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
There's not much to go on with the information above, but I suggest looking to see if there's been a bug reported that matches your issue, for example MDEV-28073, which was fixed in MariaDB 10.6.8, or reporting as a new bug with more details so that someone could try recreate it.
That in itself is probably helpful and I would not have found it with what I was looking for.
I can't think of a way to make it reproducible on our (or any) system, except just have a heavy workload with a lot of writes, and sometimes you'll get the problem after a few days, sometimes much longer. And if its inconsistent like that its very tricky to deal with.
Queries don't just run slow, they start running forever, because of this I couldn't use the slow query log, queries have to complete first. "Explain" doesn't help and there is no error nor anything to go on in the status or logs.
Like I said the key to it seems to be that once it gets into that state, running analyze table, restores the functionality - hence my guess is that it has to be to do with optimization.
I could add more details - what do you suggest? The query itself need not be complex and its not the query per-se as we see it on more than one query on more than one table.
More information:
I mention ZFS as I've had other issues with maria db on that file system. Along the lines of writes fail and it never recovers when previous snapshops are copied.
I am experiencing the exact same thing except that my database is comparatively small (100 MB) and has very little traffic. ANALYZE immediately solves the problem. Updating to a newer 10.6 version didn't solve the problem for me. May I ask if you ever found a solution?