counter.ibd size huge - consuming /var/lib/mysql to 90%

Products

• CA API Gateway

Issue/Introduction

What does counters.ibd contain under /var/lib/mysql? It is increasing day by day and can I use a mysql query to remove old date timestamps without impacting our production environment? For example, can I delete any records earlier than 2020 from counters.ibd?

The below snapshot shows the counter.ibd file is 20 GB in size and is consuming a major portion of our DB.  I want to reduce the DB size and know if it's safe to remove old data without impacting the gateway.

Environment

Release : 9.x 10.x

Component : CA API Gateway

Resolution

Two APPROACHES

1. FAST truncate counters table

2. Steps that mysql optimize performs

Approach 1 

The counters.idb mysql file holds data for the counter table.  The data/info are populated by the “Apply Throughput Quota” assertion.  There is a new row for each queue limit defined: (Authenticated User, Client IP, SOAP operation, SOAP namespace, Gateway cluster,  Custom) 

There is no mechanism to remove rows from the ssg.counters table.

Purge all data for mysql OLD 

SET FOREIGN_KEY_CHECKS = 0;

truncate table counters;

optimize table counters;

SET FOREIGN_KEY_CHECKS = 1;

To purge Counters table mysql 8.0

SET FOREIGN_KEY_CHECKS = 0;

truncate table ssg.counters;

SET FOREIGN_KEY_CHECKS = 1;

analyze table ssg.counters;

NOTE if the mysql is multimode the purge needs to occur on both nodes OR after purge of the master reinit replication between master/slave cloning master DB to clave

Replication KB 

https://knowledge.broadcom.com/external/article?articleId=44402

ALSO Important:  After the purge each gateway MUST be restarted to clear memory of purged  “Apply Throughput Quota” data

Approach 2

If truncating all records is not desired you can do the steps that mysql optimize performs 

Steps:

Creates a new table like counters

INSERTS records for a specific WHERE clause into the new counters 

Rename counters table to back name 

Rename new counters table to counters 

Drop the old counters backup table 

Then analysis counters 

Mysql commands: 

mysql> use ssg;

mysql> CREATE TABLE newcounters LIKE counters;

mysql> INSERT INTO newcounters SELECT * FROM counters WHERE last_update / 1000 > UNIX_TIMESTAMP(NOW() - INTERVAL 15 DAY);

Need to run in Background to prevent session time (NOTE this could take anywhere frm 1 to 2 hours depending on how many rows) 

# mysql -e "INSERT INTO ssg.newcounters SELECT * FROM ssg.counters WHERE last_update / 1000 > UNIX_TIMESTAMP(NOW() - INTERVAL 20 DAY)" & 

Support test 18 million rows:

Started 07:16 

root      8702  7333  0 07:16 pts/1    00:00:00 mysql -e INSERT INTO ssg.newcounters SELECT * FROM ssg.counters WHERE last_update / 1000 > UNIX_TIMESTAMP(NOW() - INTERVAL 20 DAY)

Finished: 08:29

1 hour 13 minutes 

mysql>RENAME TABLE counters TO countersbackup;

mysql>RENAME TABLE newcounters TO counters;

mysql>DROP TABLE countersbackup;

mysql>analyze table counters;