Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O

Question

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?

Answer 1

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.

Answer 2

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.