Reduce size of MariaDB undo logs

Firewall Threat Defense devices may experience high disk utilization and degraded performance when MariaDB undo logs grow to an unacceptable size. A normal and acceptable size for these undo files is approximately 10 MB. Files over 1 GB may trigger health alerts on the Firewall Management Center. When the undo files exceed the 1 GB threshold, they consume critical disk space and require manual intervention to re-initialize the database files and restore the logs to their normal operating size.

Before you begin

Verify that the Firewall Threat Defense device has enough disk space to perform a backup. The available space in the Volume must be at least twice the size of the directory /ngfw/var/lib/mysql/sfsnort.

  1. Run du -sh /ngfw/var/lib/mysql/sfsnort command to determine the size of the /ngfw/var/lib/mysql/sfsnort directory.

    root@firepower:/home/admin# du -sh /ngfw/var/lib/mysql/sfsnort
414M    /ngfw/var/lib/mysql/sfsnort

  2. Run df -h command to check the available disk space in /ngfw/Volume. Ensure that it is at least twice the size of the directory.

Procedure

Step 1

Disable the Data Correlator service and verify that it is successfully disabled.

  1. Disable the Data Correlator service by executing the command pmtool disablebyid SFDataCorrelator.

  2. Confirm that the Data Correlator service is successfully disabled by running pmtool status | egrep -i 'SFData.*- '.

Step 2

Set the necessary environment variables for your database session.

  1. Prepare the database environment for the current session by running the command source "${SF_ROOT_PATH}/usr/local/sf/lib/datastore/functions.db".

  2. Retrieve and store the database root password in an environment variable by running command MYPASS=$(getdbpassword "root").

Note that these variables must be reset if your SSH connection disconnects.

Step 3

Create a full database backup using the mysqldump utility by running this command:

nohup mysqldump -p${MYPASS} -c -q --routines --events --triggers --single-transaction
          --hex-blob --flush-privileges --all-databases >
        /ngfw/var/common/full-dump.sql

Step 4

Validate that the backup file size is appropriate and confirm the dump is complete by running the tail -f /ngfw/var/common/full-dump.sql command and checking the end of the file for Dump completed timestamp.

If the backup file contains warnings regarding the max_allowed_packet option at the beginning of the file, open the file and comment out those lines by adding two dashes in front of each line.

Step 5

Stop the MariaDB service and ensure that no active mysqld processes are running.

  1. Stop the MariaDB service by executing the command pmtool disablebyid mysqld.

  2. Confirm that the MariaDB service is disabled and that no active mysqld processes are running by using the ps -ef | grep mysql command.

Step 6

Archive the existing MariaDB error logs for future analysis by running this command:

tar -cvf /ngfw/var/common/mysql_rca.tar
        /ngfw/var/lib/mysql/mysql-server.err

Step 7

Delete all contents within the /ngfw/var/lib/mysql/ directory by running the command rm -rf /ngfw/var/lib/mysql/*.

Step 8

Re-initialize the MariaDB database structure and set the correct permissions.

  1. Install the default MariaDB database structure by running the command mariadb-install-db --defaults-file=/ngfw/etc/my.cnf.

  2. Set the correct ownership permissions for the MySQL directory by running the command chown -R mysql:mysql /ngfw/var/lib/mysql/.

Step 9

Set the correct ownership permissions for the MySQL directory by running the command chown -R mysql:mysql /ngfw/var/lib/mysql/.

Step 10

Restart the MariaDB service and verify that it starts correctly.

  1. Restart the MariaDB service by running the command pmtool enablebyid mysqld.

  2. Verify that the MariaDB service has started correctly and the default databases are present by running the command mysql -p${MYPASS} -e "show databases;".

Step 11

Restore the database from the backup file created in step 4 and verify that the restoration completed successfully.

  1. Restore the database from the backup file by executing nohup mysql -p${MYPASS} < /ngfw/var/common/full-dump.sql.

    Note that this operation can take some time depending on the size of the backup file.

  2. Examine the nohup.out file in the current directory to ensure the restoration process completed without any errors. If the file is empty, the restore was successful.

Step 12

Reset the MariaDB privileges by executing the command mysql -p${MYPASS} -e "flush privileges;".

Step 13

Re-enable the Data Correlator service by running pmtool enablebyid SFDataCorrelator.

Step 14

Confirm that the Data Correlator service has started successfully by checking its status with pmtool status.

What to do next

  • Navigate to the /ngfw/var/lib/mysql/ directory and check the size of the undo files using the command ls -lrth *undo*. The procedure is successful if the undo files, such as undo001 and undo002, return to their normal size of approximately 10 MB.

  • Verify that the Firewall Management Center no longer displays health alerts regarding MariaDB undo log thresholds.