MySQL Forums
Forum List  »  InnoDB

Mysql8.0 event 'committing alter table to storage engine' hangs During DDL after high traffic load
Posted by: Amitz צור
Date: September 05, 2022 06:25AM

Hello
Regarding wait event "committing alter table to storage engine".

All i can find about it is "The server has finished an in-place ALTER TABLE and is committing the result."

The Issue I have is that when I run 'alter table drop partition' or 'alter table reorganize partition' (daily process to maintain retention) the event takes significantly longer after committing many transactions (rate of 600 inserts p/s) on the table for a couple of hours.

during this event all sessions are locked on "Waiting for table metada lock" and the app is down.

If i run the "alter table drop/reorganize partition" command shortly after another such command it completes in ms as if there is some cache i need to free to reduce the event.

I tried flush table ... with read lock - did not help (thought it might be related to dirty pages)

I am using mysql version 8.0.27

We did not have this issue in 5.7 (I am familiar with the ddl lock changes in mysql8) but it seems that there is something i am missing here since this happens only after massive rate inserts for a couple of hours and does not hang otherwise.

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql8.0 event 'committing alter table to storage engine' hangs During DDL after high traffic load
70
September 05, 2022 06:25AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.