Audit Tables Purged by the audit_purge.sh Script

Document ID : KB000123190
Last Modified Date : 04/01/2019
Show Technical Document Details
Issue:
After referencing the following KB article to configure audit_purge.sh to clean up the old audit records:

https://comm.support.ca.com/kb/configuring-and-installing-the-audit-record-maintenance-script/kb000042480

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. Login to the MySQL database on the primary node 
4. Run the following mysql command: use ssg; 
5. Run the attached trunc_opt_tables.sql script 
6. Exit MySQL 
7. Start the ssg service on all gateway nodes 
File Attachments:
trunc_opt_tables.sql