Layer7 API Management

My innodb files are starting to get pretty large (80% of the mysql partition).  I'm thinking that I want to enable the innodb_file_per_table option (which is enabled by default for mysql version 5.6.6 and up where the gateway is using 5.5.53).  I found the /etc/my.cnf and noticed there was also a my.cnf.ssg (not used in the startup according to ps aufx | grep mysql).  so, now I have two questions; first can I add that option without breaking anything, and second do I have to add it to the second file (my.cnf.ssg) as well (which I'm guessing is a backup used for updating database configuration options in the patching process)?

I ask because I have seen manually editing some files causes issues (i.e. changing iptables breaks the gateway's ownership of it in policy manager, etc).

Hello Ben.Deutsch ,

I personally believe it should not impact the gateway, but can it be implemented on 5.5.53, and how to implement, you have better to consult mysql DBA.

I would recommend the "conventional" way(s) to maintain disk space for mysql database,

Configuring and installing the audit record maintenance script 

Shrinking MySQL ibdata file 

Increasing the disk space available to a virtual appliance using Logical Volume Management (LVM) in API Gateway version … 

Regards,

Mark

Since the mysql database is a critical component of the gateway, I would like for you to consult with your mysql DBA and give me a hard yes or no answer if this acceptable (I realize that the short term answer may likely be something along the lines of 'we cannot confirm, this will not be covered under your maintenance agreement, proceed at your own risk, and even if it works it may break with later patches, etc), which is fine.  However, this may become a long term solution that allows for disk space maintenance of the mysql partition without incuring downtime, as the 'Shrinking MySQL ibdata file' disclaimer warns.  Also, reading that procedure, I think I can perfrom it without incuring an outage... if it works I'll post it here so you can update that document.

The referenced procedure deletes all the audits in the database.  This will only release the unused pages from the ibdata file, which in most cases is probably enough (and from an operations perspective I appreciate keeping my audits).

1. ssh into both database nodes

2. on both nodes:
   mysqladmin stop-slave

3. compete the following steps on one database node, then the other so that one of the two remains available at all times to prevent an outage (I can't guarantee there won't be an interruption, but the gateway application process should failover to the database that is available):
   mysqldump --all-databases | gzip > /root/ibdata-full-backup.sql.gz
   mysql
   use ssg
   optimize table audit_admin;
   optimize table audit_detail;
   optimize table audit_detail_params;
   optimize table audit_main;
   optimize table audit_message;
   optimize table audit_system;
   exit
   service mysql stop
   rm -rf /var/lib/mysql/ib*
   service mysql start
   gzip -d /root/ibdata-full-backup.sql.gz
   mysql < /root/ibdata-full-backup.sql
   rm -f /root/ibdata-full-backup.sql

4. on both nodes:
   /opt/SecureSpan/Appliance/bin/restart_replication.sh