Audit Tables Purged by the audit_purge.sh Script

Products

• CA API Gateway

Issue/Introduction

After referencing the following KB article to configure audit_purge.sh to clean up the old audit records:

https://knowledge.broadcom.com/external/article/42480/api-gateway-managing-audits-in-the-datab.html

All of the audit tables have not been purged.

1. Does the audit_purge.sh takes care of all the available audit tables clean up under SSG DB?
2. Will it take care clean up of service_metrics tables under SSG table?
3. Can the size of the SSG tables be restricted? If so, how do you increase the size limit for those tables?

Environment

CA API Gateway 9.1+

Cause

 

Resolution

1. Does the audit_purge.sh takes care of all the available audit tables clean up under SSG DB?
A: The audit_purge.sh script only deletes records from the audit_main table and optimizes all the audit tables (audit_main, audit_admin, audit_detail, audit_detail_params, audit_message, and audit_system). None of the other audit tables get truncated. 

2. Will it take care clean up of service_metrics tables under SSG table?
A: The service metrics table are not truncated by the audit_purge.sh script. 

3. Can the size of the SSG tables be restricted? If so, how do you increase the size limit for those tables?
A: There's no way to increase the size of particular tables but the following can be done to increase the overall DB size: 

1. Confirm the size of the 'ibdata' file to determine the size of the database. 
[root@host ~]# ls -lh /var/lib/mysql/ibdata 

2. Next, determine the maximum allocation for the MySQL database. 
[root@host ~]# grep innodb_data_file_path /etc/my.cnf 

3. This will return a line that should resemble something like the following: 
innodb_data_file_path=ibdata:100M:autoextend:max:3072M 

+ This setting caps the size of the database at 3072 Megabytes. 
+ This value may very based upon your configuration. 
+ Compare this value to the size of the 'ibdata' file to get an idea of the current database usage. 
+ If the size of 'ibdata' is exceeds the max allocation, you will experience database disruptions. 
+ This threshold is set at 90% for Gateways by default. 

4. Stop the Gateway and MySQL services 
[root@host ~]# service ssg stop 
[root@host ~]# service mysql stop 

5. Open the '/etc/my.cnf' file and increase the maximum allocation. Change "max:3072" in "innodb_data_file_path=ibdata:100M:autoextend:max:3072M " to reflect a new file size in Megabytes (i.e. 4072MB). 

6. Restart the Gateway and MySQL services 
[root@host ~]# service mysql start 
[root@host ~]# service ssg start 

Additional Information

You can run the following query to delete records and optimize all of the audit and service metrics tables: 

1. Stop the ssg service on all gateway nodes 
2. Take a backup the database: mysqldump --all-databases | gzip > /root/audit-record-removal.sql.gz 
3. Log in to the MySQL database on the primary node 
4. Run the following mysql command: use ssg; 
5. Run the appropriate attached script (truncate_opt_tables_mysql_5_7.sql or truncate_opt_tables_mysql_8.sql)
6. Exit MySQL 
7. Start the ssg service on all gateway nodes