Layer 7 API Management

Expand all | Collapse all

/var/lib/mysql disk space management

Jump to Best Answer
  • 1.  /var/lib/mysql disk space management

    Posted 06-19-2017 07:59 PM

    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).



  • 2.  Re: /var/lib/mysql disk space management

    Posted 06-26-2017 02:01 AM

    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



  • 3.  Re: /var/lib/mysql disk space management

    Posted 06-26-2017 12:42 PM

    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.



  • 4.  Re: /var/lib/mysql disk space management
    Best Answer

    Posted 06-26-2017 01:23 PM

    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