prevent /var/lib/mysql from filling up the disk

[Andreas Erb]

The disk space on the API Gateway appliance has always been a problem for us.
The more traffic we got on the Gateway the bigger the folder /var/lib/mysql became. That's why we disabled the audit log.

Now comes the next problem which fills our disk space: service_usage.
On the clustered API Gateway (production) where we have node 1 and node 2, a mysql replication takes place, which fills the disk under /var/lib/mysql with following replication files:

501M ssgbin-log.001037
501M ssgbin-log.001038
501M ssgbin-log.001039
501M ssgbin-log.001040
501M ssgbin-log.001041
501M ssgbin-log.001042
501M ssgbin-log.001043
501M ssgbin-log.001044
501M ssgbin-log.001045
501M ssgbin-log.001046
501M ssgbin-log.001047
501M ssgbin-log.001048
501M ssgbin-log.001049
501M ssgbin-log.001050
501M ssgbin-log.001051
501M ssgbin-log.001052
501M ssgbin-log.001053
501M ssgbin-log.001054
501M ssgbin-log.001055
497M ssgbin-log.001056
501M ssgbin-log.001057
501M ssgbin-log.001058

These files have a limit of 500MB and will be deleted after 10 days.
The content of the files is basically only the insertion into the table service_usage like so:

insert into service_usage (authorizedreqnr, completedreqnr, requestnr, nodeid, serviceid) values (81, 30, 81, '80cdd287520d450193659fd965dcdb86', x'F2D10F25EBCFCC6D2F8C5DC8DB834DCB')

In order to prevent the disk from getting filled that much, I see three options:

1) Manually purge these binary files periodically (like described in this article: https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html)
2) Lower the expiry date to 1 day in the file /etc/my.cnf
3) Disable the unneccessary filling of the table service_usage in mysql db. Is it possible to disable it in the API Gateway?

Which one of these options do you suggest? Are they supported from Broadcom?

[Charles Lilienkamp]

1) In newer versions service_metrics can be off boxed.
https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-api-management/api-gateway/9-4/learning-center/overview-of-the-policy-manager/gateway-dashboard/configure-gateway-for-external-service-metrics.html

2) We have a script we normally suggest be implemented with replication for binary log management.
https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=9846

3) You may want to check show slave status because high binlog growth can also mean a breakage in replication.

[Andreas Erb]

service_metrics is not the same as service_usage. We already disabled service_metrics completely (serviceMetrics.enabled=false) but service_usage still gets written. But I guess, it is not possible to disable that entirely.

Thank you for the script, we are going to try that out.

[Jay MacDonald]

As a point of explanation, the binlog files are where the replay details for replication are stored before being sent to the slave for replaying into its database. Assuming replication is working they become redundant once rolled over. I.e. all of the records in the rolled-over files have already been replayed. The manage_binlogs.sh script confirms that replication is working and, if so, it purges all of the old binlog files. If not then it sends an alert somewhere.

Note that the expire_logs_days setting in /etc/my.cnf is supposed to remove binlogs over 10 days old. Under a very busy system one might end up filling the database before this period is reached. You could reduce that number in /etc/my.cnf and restart mysql to help with your problem, but IMO it is better to use the script since it also monitors the replication.

It is possible that someone enabled log-bin in /etc/my.cnf but it is not being used for replication. The binlogs are useful for troubleshooting, not just for replication. If that was done either disable the setting, run a purge command from crontab on a regular basis, or set expire_logs_days=1 so they don't hang around.

Cheers!

------------------------------
Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
------------------------------