Bug #92996 ANALYZE TABLE still locks tables 10 years later
Submitted: 29 Oct 19:01 Modified: 30 Oct 12:54
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[29 Oct 19:01] Domas Mituzas
Description:
I'm just trying my luck ten years later to get issues identified in https://bugs.mysql.com/bug.php?id=33278 to be addressed today.

How to repeat:
... on a table that has long running query elsewhere...

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> select * from t1 limit 1;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    0 |    1 | NULL |
+---+------+------+------+
1 row in set (1 min 27.02 sec)

Suggested fix:
don't hold a lock?
[29 Oct 19:03] Domas Mituzas
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                   | Info                                 |
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 4884 | Waiting on empty queue  | NULL                                 |
|  9 | ubuntu          | localhost | test | Sleep   | 2549 |                         | NULL                                 |
| 14 | ubuntu          | localhost | test | Query   |   33 | User sleep              | select sleep(0.1) from t1 limit 1000 |
| 15 | ubuntu          | localhost | test | Query   |   26 | Waiting for table flush | select * from t1 limit 1             |
| 16 | ubuntu          | localhost | NULL | Query   |    0 | starting                | show processlist                     |
+----+-----------------+-----------+------+---------+------+-------------------------+--------------------------------------+
5 rows in set (0.00 sec)

mysql>
[30 Oct 12:54] Sinisa Milivojevic
Hi,

I have tested your example on the large InnoDB table with two connections, one ANALYZE and the other SELECT and indeed, the lock is still held.

However, I consider this a feature request and not a bug.

Verified as reported.
[30 Oct 16:43] Sinisa Milivojevic
Only one small notice from me.

I have analysed the code a bit and it seems to me that having ANALYZE and SELECT running in parallel would be possible only if scanning is used. With multi-indexed tables, it would be possible to use only indices that ANALYZE has done with prior to SELECT being run.
[30 Oct 16:53] Valeriy Kravchuk
Check how Percona managed to fix it here:

https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/