MySQL Bugs: #87065: Release lock on table statistics after query plan created

Bug #87065	Release lock on table statistics after query plan created
Submitted:	13 Jul 2017 18:49	Modified:	9 Oct 2023 12:52
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7.18, 5.6.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Under concurrent environment running ANALYZE TABLE could lead to situation when hundreds of tables are in state "Waiting for table flush".

How to repeat:
Read https://www.percona.com/blog/2013/02/27/mysql-optimizer-analyze-table-and-waiting-for-tabl...

Suggested fix:
Implement new type of lock which holds statistics only and release it immediately after Optimizer creates execution plan.

HI Sveta,

Thank you for your report.

I consider this as a very positive and beneficial feature request.

Verifed.

Hi, Dear MySQL Developers!

Are there any chances to prioritize this ticket? Our production bases are loaded 24/7 and every time we run the ANALYZE it locks the tables for several hours.

P.S. Looks like Percona engineers have created the fix two years ago - https://www.percona.com/blog/2018/03/27/analyze-table-is-no-longer-a-blocking-operation/

Hi,

It has been considered as a feature for some 8.0 release, but no decision has been made yet. Also, these decisions are made on the level to which we do not have any access.

What about the change log in 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)"

Did that fix address the same problem?

Hi Mr. Malkowski,

We analysed carefully what you wrote and we fully agree with you.

This is indeed a patch that resolves this issue !!!!!!

This is now closed.