API Gateway: Optimize Needed for MySQL ssg Database

Products

• CA API Gateway
• API SECURITY
• STARTER PACK-7

Issue/Introduction

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?

Environment

API Gateway: 9.3, 9.4

Resolution

You can optimize your databases to reclaim the space. A few things to consider:

= Take a back up of all your databases!=
mysqldump -all databases | gzip > /root/ibdata-full-backup.sql.gz

(1) When you are optimizing the tables in your database, those tables will be locked, so data cannot be inserted during this time.
(2) You can optimize one database at a time or you can do all of them at the same time. Consider #1.

Attached is an example where the ssg database is optimized and you can see some of the space is reclaimed.

There is no "preferred" method but using the mysqlcheck -o <database> command will work.

Additional Information

MySQL 5.7 Reference Manual: https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html