We find that the disk space under /var/lib/mysql keeps growing, specifically the files under /var/lib/mysql/ssg. We ran the following command to utilize the "data_free" static for MySQL to find out the allocated but unused space for each DB:
SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free reclaimable space in MB" FROM information_schema.TABLES GROUP BY table_schema;
This gave us the following:
+-----------------------------+---------------------------+----------------------------------------+
| database name | database size in MB | free reclaimable space in MB |
+-----------------------------+---------------------------+----------------------------------------+
| information_schema | 0.15625000 | 80.00000000 |
| mysql | 2.57636261 | 4.00000000 |
| otk_db | 0.21875000 | 40236.00000000 |
| performance_schema | 0.00000000 | 0.00000000 |
| ssg | 9849.89062500 | 557572.00000000 |
| sys | 0.01562500 | 0.00000000 |
+-----------------------------+---------------------------+----------------------------------------+
Is it OK to run an optimize on all MySQL database tables to reclaim unused space? Is there another preferred process to do this for the API GW RHEL appliance 9.3/9.4 with MySQL?
API Gateway: 9.3, 9.4