search cancel

Audit Archiver still shows greater than 90% even after audit_purge.sh

book

Article ID: 203056

calendar_today

Updated On:

Products

CA API Gateway API SECURITY CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7) CA API Gateway Enterprise Service Manager (Layer 7) STARTER PACK-7 CA Microgateway

Issue/Introduction

We have no audit records in our internal database (audit_detail and audit_main are showing a count of 0).

The ibdata file is set to an initial size of 100M with autoextend max size of 15776M.

The ibdata file is currently only 104M in size.

The Audit Archiver is running  it keeps coming up with a message that the Database is at 96%.

I have restarted both SSG and MYSQLD services but this is still coming up with this error. 

How do I get this to reset?

Environment

Release : 10.X

Component : API GATEWAY

Cause

Changes in how mysql defrags to recovered space from mysql 5.x to mysql 8

Resolution

Mysql 8 is different from the older version optimize is not used anymore .


First set FOREIGN KEY 0
Truncate each AUDIT tables - 0 rows
Set FOREIGN KEY 1
Then analyze each Audit table

Commands tested to recover space mysql DB

Review the current size:

mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table                                | size  |
+--------------------------------------+-------+
| audit_main                           | 79.30 |
| audit_admin                          | 26.67 |
.

To truncate the audit tables :

SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
truncate table audit_detail;
truncate table audit_detail_params;
truncate table audit_main;
truncate table audit_message;
truncate table audit_system;
SET FOREIGN_KEY_CHECKS = 1;


analyze table ssg.audit_admin;
analyze table ssg.audit_detail;
analyze table ssg.audit_detail_params;
analyze table ssg.audit_main;
analyze table ssg.audit_message;
analyze table ssg.audit_system;

Review new size

mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table                                | size  |
+--------------------------------------+-------+
| audit_admin                          |  4.55 |
| audit_main                           |  0.06 |
.
.