MySQL Bugs: #92996: ANALYZE TABLE still locks tables 10 years later

Bug #92996	ANALYZE TABLE still locks tables 10 years later
Submitted:	29 Oct 2018 19:01	Modified:	30 Oct 2018 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

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?

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>

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.

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.

Check how Percona managed to fix it here:

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

I think this was fixed in 8.0.24 (Bug#32224917)

Thanks .....

We shall check it out ......

Release Note from 8.0.24:

ANALYZE TABLE executed on a table concurrently with a long-running query on the same table caused subsequent queries on the table to wait for the long-running query to finish. This wait induced by ANALYZE TABLE is now eliminated, thus allowing the subsequent queries to execute with no wait. (Bug #32224917)

Thank you, Mr. weixiang.