1

On MySQL 8.0.33, I'm experiencing a form of contention (reproduced on multiple instances) that is not explained by the documentation.

As example:

  1. I boot an instance
  2. on a console client, I invoke a SELECT COUNT(*) on a relatively large table (a few GBs of total records+indexes)
  3. on a separate client, I invoke a ANALYZE TABLE on a separate, smaller table

the result is that the second statement (ANALYZE TABLE) hangs for a while, blocked in a System Lock state; this is a sample output:

+----+--------+------+-------+---------+------+-------------+---------------------------------+
| Id | User   | Host | db    | Command | Time | State       | Info                            |
+----+--------+------+-------+---------+------+-------------+---------------------------------+
| 18 | _user_ | _ip_ | _db_  | Query   |   72 | executing   | SELECT COUNT(*) FROM _table_1_  |
| 32 | _user_ | _ip_ | _db_  | Query   |   45 | System lock | ANALYZE TABLE _table_2_         |
+----+--------+------+-------+---------+------+-------------+---------------------------------+

Based on the documentation (here and here), MySQL seems to be locking (at least) both tables while paging in the records of the first (SELECT) tables.

What is exactly causing MySQL to lock multiple tables, while performing a SELECT on one?

2 Answers 2

3

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html says:

During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released.

1
  • Uh, I find this help snippet confusing. Intuitively, I read it as "if there are (long running statements or transactions) still using the table", but I take it needs to be interpreted as "(long running statements) or (transactions still using the table)"!
    – Marcus
    Commented Nov 21, 2023 at 6:07
0

Beginning with 5.6.6, innodb_stats_auto_recalc and Persistent stats virtually eliminated the need for ANALYZE TABLE.

I suggest you avoid doing the ANALYZE. If you still feel the need for such, please provide the query that it helps, together with SHOW CREATE TABLE. There may be a viable workaround.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.