MySQL Storage Maintenance

Prerequisite:

1. Take the downtime from the client.

2. Pause the scheduler 

3. Scale down the webrunner pod.

4. Run the below query to identify the free space in the Database.

SELECT table_schema "DataBase Name", 

sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB", 

sum( data_free )/ 1024 / 1024 "Free Space in MB" 

FROM information_schema.TABLES GROUP BY table_schema;

5. Refer the below link for the table for which we have to perform cleanup 

https://support.adeptia.com/hc/en-us/articles/207881923-Truncate-Log-Tables-Manually

6. Run the optimize query “OPTIMIZE TABLE tablename;” This query will create a lock on the table and then create a new table with the same name and same data.

Post maintenance:

1. Run the above query (point4) to verify the free space

2. Scale up the webrunner pod

3. Resume the scheduler

4. Drop confirmation email to client.