OPTIMIZE TABLE VERY SLOW In one RDS instance

[Stanley Wong]

Current Spec:

RDS MYSQL version : 5.7.33
Instance class : db.m4.4xlarge
vCPU : 16
RAM : 64 GB
Database Size : 33 GB
Free Disk Space : 1 TB .
Storage type : General Purpose SSD (gp2)

Problem:

I have a monthly maintenance purging job to remove old record from the table . After the data deletion , there are few OPTIMIZE TABLE script (example: OPTIMIZE TABLE `abc`) . Before we upgrade from version 5.6 to 5.7.33 , the entire optimization script can complete within 1.5 hours.

After the DB upgrade to 5.7.33. The OPTIMIZE TABLE script have to run for 4 hour. When we drill down further to each table , there are 1 table with 5.2GB and the optimize table script for this table has consume 45 mins . This table is having same size when it is in version 5.6 . There are no other query running or blocking/deadlock when checking with show processlist and show engine innodb status. I also cross check the tempdb size and it does not grow when the OPTIMIZE TABLE script run.

I though this might be due to the version different , so I try to spin up another instance (5.7.33) with same spec and same database size and perform the list of optimize script. But the optimize script complete in 1.5 hours.

Does anyone face the same problem ? what could be the reason ?