Bug ID 941885: MariaDB keeps open large number of request log partition files

Last Modified: Jan 16, 2024

Affected Product(s):
BIG-IP ASM, AVR(all modules)

Known Affected Versions:
14.1.0, 14.1.0.1, 14.1.0.2, 14.1.0.3, 14.1.0.5, 14.1.0.6, 14.1.2, 14.1.2.1, 14.1.2.2, 14.1.2.3, 14.1.2.4, 14.1.2.5, 14.1.2.6, 14.1.2.7, 14.1.2.8, 14.1.3, 14.1.3.1, 14.1.4, 15.0.0, 15.0.1, 15.0.1.1, 15.0.1.2, 15.0.1.3, 15.0.1.4, 15.1.0, 15.1.0.1, 15.1.0.2, 15.1.0.3, 15.1.0.4, 15.1.0.5, 15.1.1, 15.1.2, 15.1.2.1, 16.0.0, 16.0.0.1, 16.0.1, 16.0.1.1, 16.1.0, 16.1.1

Fixed In:
17.0.0, 16.1.2, 16.0.1.2, 15.1.3, 14.1.4.1

Opened: Sep 02, 2020

Severity: 3-Major

Symptoms

The /var/lib/mysql partition is filling up. MariaDB encounters its limit on open files (open_files_limit=5000).

Impact

System stability.

Conditions

-- Large number of open partition files exist in the PRX database. -- Partitions are not being dropped.

Workaround

1. Log in to the CLI. 2. Stop MariaDB: bigstart stop mysql 3. Make enough space in /var/lib/mysql so the mysqld process is able to start up. The easiest way to do this is to remove AVR data (later steps rebuild a fresh AVR database): rm -f /var/lib/mysql/AVR/* 4. Prepare to reset AVR stats (K14956: Resetting F5 Application Visibility and Reporting statistics :: https://support.f5.com/csp/article/K14956): touch /var/avr/init_avrdb 5. Increase 'open_files_limit' to 10000 (twice the current default of 5000): sed -i 's/^open_files_limit=5000$/open_files_limit=10000/' /etc/my.cnf 6. Restart MariaDB: bigstart restart mysql 7. After this restarts, confirm that the soft and hard file limits are now 10000: grep -e ^L -e 'open files' /proc/$(pidof mysqld)/limits 8. Restart monpd bigstart restart monpd 9. Remove empty PRX.REQUEST_LOG partitions by running the following script: ---------------------- #!/bin/sh mysql -su root -p$(perl -MPassCrypt -nle 'print PassCrypt::decrypt_password($_)' /var/db/mysqlpw) -e "SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE table_name = 'REQUEST_LOG' AND table_schema = 'PRX' AND table_rows = 0" | while read empty_partition_name do echo "Dropping empty partition: $empty_partition_name" mysql -u root -p$(perl -MPassCrypt -nle 'print PassCrypt::decrypt_password($_)' /var/db/mysqlpw) -e "ALTER TABLE PRX.REQUEST_LOG DROP PARTITION $empty_partition_name" done ---------------------- Note: This command may return a benign warning that you can ignore: -- ERROR 1508 (HY000) at line 1: Cannot remove all partitions, use DROP TABLE instead. 10. Restart asmlogd pkill asmlogd

Fix Information

None

Behavior Change

Guides & references

K10134038: F5 Bug Tracker Filter Names and Tips