MySQL Storage Maintenance

1. Maintenance Prerequisite

1.1 Take the Downtime from the Client
Coordinate with the client to schedule a downtime period for maintenance.

1.2 Pause the Scheduler
Pause the scheduler to prevent any scheduled tasks from running during maintenance.

1.3 Scale Down the Webrunner Pod
Scale down the webrunner pod to minimize resource usage and prevent conflicts during the health check.

1.4 Identify Free Space in the Database

1.4.1 SQL Query to Check Free Space
Run the following query to identify free space in the database: SQL Code

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;

Open

1.5 Refer to Log Tables for Cleanup

1.5.1 Cleanup Reference Link
Refer to the following link for the tables that need to be cleaned up:
Truncate Log Tables Manually

1.6 Run the Optimize Query

1.6.1 Optimizing Table Query
Run the following query to optimize the table. This query will create a lock on the table and then create a new table with the same name and data:

SQL code: OPTIMIZE TABLE tablename;

2. Post Maintenance

2.1 Verify Free Space After Maintenance
Run the query from section 1.4.1 to verify the free space.

2.2 Scale Up the Webrunner Pod
Scale up the webrunner pod to restore its original capacity.

2.3 Resume the Scheduler
Resume the scheduler to restart any scheduled tasks.

2.4 Send Confirmation Email to Client
Send a confirmation email to the client informing them that the maintenance has been completed.