Considerations for Duration to Preserve MySQL Binary Logs in Cluster Tuning Options

Products

• CA Privileged Access Manager (PAM)
• CA Privileged Access Manager - Cloakware Password Authority (PA)
• CA Privileged Access Manager - Server Control (PAMSC)

Issue/Introduction

If there are lots of transactions to CA PAM, e.g. by heavily using the CLI or REST API in high frequency loops, the underlying MySQL database might grow quickly since basically all transactions are recorded in the MySQL binary logs.
The binary log is a set of log files that contain information about data modifications made to a MySQL server instance.
Its data are utilised e.g. to replay transactions in PAM Cluster members upon initial synchronisation or later on.
This article explains how to control the size of the binary logs which largely affect the database dump size transmitted to a cluster member, e.g. while starting the cluster.

Environment

Release : 3.3

Component : PRIVILEGED ACCESS MANAGEMENT

Cause

In PAM r3.3.0 and 3.3.1
the default is 3 days and the lowest value to set it is a single day.

It might turn out that under heavy use circumstances the minimum is still too big. The bin logs are packaged into the MySQL dump that is sent to all of the cluster site members on cluster startup and resync. 
E.g. when importing accounts into PAM using the APIs at a very high rate, the number of DB updates that occurs in a single day is very high.  This results in a very large collection of bin logs. 
So even though PAM prunes old logs when the DB dump is created, it is still carrying over data of at least a days worth.

Resolution

Starting with PAM 3.3.2 and higher version as of October 2023, there are certain changes as explained below.

  
The configuration setting for how long to preserve bin logs is now set in hours rather than days and these setting can be modified.

Note also that this value can not be less than the Primary Member Recovery Period setting.
If a primary site member goes further out of sync than this period, the entire database must be delivered to the member.

Basically, the smaller the value of these settings, the smaller the DB backups for replication will be.

 

It should be considered that there is a down side to setting this too low.
If a secondary site member loses contact with the primary site for longer than this setting, there is a risk that bin log changes needed to synchronise it  will have been purged from the primary site.  Or in reverse, changes made at the secondary site that have not yet been replicated to the primary site will have been purged from its bin logs.
This will prevent automatic database replication from continuing until that site is manually resynchronised, e.g using the Re-sync Site button


Keep in mind to enable the Cluster Tuning Mode (Configuration / Diagnostics / System ) in each potential primary site node and set the value for "Duration to Preserve MySQL Binary Logs" accordingly.
This setting does not replicate - that is why it needs to be set explicitly on all the nodes - but basically it is effective only on the dynamically determined master node where a new dump is created when needed.

Additional Information

Configure a Cluster

Promotion, and Recovery