OPTIMIZE TABLE VERY SLOW In one RDS instance
Posted by: Stanley Wong
Date: September 20, 2021 01:59AM

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 ?

Options: ReplyQuote


Subject
Written By
Posted
OPTIMIZE TABLE VERY SLOW In one RDS instance
September 20, 2021 01:59AM


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.