API Gateway: Cleaning up a large ibdata file after upgrade to 9.3 and newer.

Products

• CA API Gateway

Issue/Introduction

This article will discuss the topic of saving disk space from a large MySQL ibdata file after upgrade of an API Gateway appliance to 9.3 and newer. Please note this process is considered to be "at your own risk", as there can be side effects that need attention at a later time. In general, the ibdata file should not be removed after upgrading to MySQL 5.7 (included in Gateway 9.3 and later). The ideal scenario is to shrink the ibdata file before upgrading to Gateway 9.3 and later.

In a particular set of circumstances, there may be a large ibdata file consuming a lot of disk space in the /var/lib/mysql directory. After upgrading to 9.3 and newer, the MySQL database was upgraded from 5.5 to 5.7 (or newer) and the defaults changed from using the ibdata file to store the information of the database to the innodb_files_per_table being enabled which creates a directory for each database within the /var/lib/mysql directory and files within each subdirectory representing each table in the database. So for example, if using 'ssg' as the database name, there will be a directory created as /var/lib/mysql/ssg which holds all the files for the tables within the SSG database.

The benefit of this new innodb_files_per_table functionality is to allow for dynamic resizing which eliminates the need to frequently purge/maintain the ibdata file (since it always grew and never shrank on it's own) which was a difficult issue for administrators of API Gateway 9.2 and lower. Now with MySQL 5.7 and newer (on Gateway 9.3 and newer appliances), if a user purges the audit data, it will automatically reflect in the consumption of the disk in the /var/lib/mysql partition, as opposed to before when the ibdata file wouldn't shrink and would need to be purged at some point, causing downtime. Now, no more downtime.

However, if the database size was quite large before upgrading in-place to API Gateway 9.3 or newer, the ibdata file size will still remain quite large even after the upgrade, requiring a one-time purge to bring it back to it's default size of 100 MB. If there is a need to reduce this ibdata file size, please follow the steps in this article.

Environment

This article applies to API Gateway appliances, starting with version 9.3 and up to the most recent supported version. Specifically, this applies to situations where there was an in-place upgrade of the API Gateway from 9.2 and lower to 9.3 and higher.

Cause

This situation typically occurs when the ibdata file had grown prior to the upgrade of the API Gateway appliance from earlier versions (9.2 and lower) to 9.3 and higher.

Resolution

As long as the innodb_files_per_table is enabled which it is by default in Gateway 9.3 and newer (a lack of this value in the /etc/my.cnf file means it is enabled if MySQL is version 5.7 or newer), the following steps should be followed to reduce the size of the ibdata file to it's default 100 MB size.

Important note! Take a full VM snapshot of the image before applying the steps below so that it is easy to recover if there are any unexpected issues. Broadcom Support will be unable to assist if there is no full system backup to restore to if anything goes wrong.

1. Backup all databases: mysqldump -u root -p7layer --all-databases > /tmp/all-databases.sql
2. Stop the ssg service: service ssg stop
3. Drop the ssg database within MySQL using this command: drop database ssg
   • Do not drop any databases other than ssg. Note that dropping ssg may take a while to complete.
   • The command to run above is assuming the user is already in the mysql application.
4. Stop MySQL service: service mysqld stop
5. Remove the ibdata file and files that start with ib_logfile, so the command to run would be the two below assuming you’re in the /var/lib/mysql/ directory: rm -rf ibdata && rm -rf ib_*
6. Start MySQL service: service mysqld start
7. Restore the database dump using this command (ignore the errors presented, this may take several minutes): mysql -f < /tmp/all-databases.sql
8. Start the SSG service: service ssg start

Now test that the SSG service is operational again after a few minutes of restarting the ssg service.

Additional Information

Depending on the circumstances, the following KB article may need to be followed as well to resolve MySQL errors: https://knowledge.broadcom.com/external/article/137801/how-to-resolve-mysql-database-errors-on.html -- Particularly if the following error is seen (any table name can be mentioned though): [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.