Replication updates falling behind on the secondary mysql

Products

• CA API Gateway

Issue/Introduction

The APIM deployment team added  “Apply Throughput Quota Assertion” to all policies to limit the number of requests gateway sends to the backend router.

Broken replication overtime caused by the secondary mysql falling behind replication updates 

#  mysql -e "show slave status\G" | grep -e "Master_Log_File" -e "Read_Master_Log_Pos" -e "Relay_Log_File" -e "Relay_Log_Pos" -e "Relay_Master_Log_File" -e "Exec_Master_Log_Pos" -e “Slave_SQL_Running_State” -e "Seconds_Behind_Master"

              Master_Log_File: ssgbin-log.000074

          Read_Master_Log_Pos: 519056078

               Relay_Log_File: ssgrelay-bin.000187

                Relay_Log_Pos: 121234576

        Relay_Master_Log_File: ssgbin-log.000063

          Exec_Master_Log_Pos: 121234358

              Relay_Log_Space: 6286282107

        Seconds_Behind_Master: 36145

36145s  ~10 hours behind

Environment

Gateway 11.X

Cause

The default mysql configuration is not tuned to handle the amount of load/updates per hour

Resolution

mysql can be tuned so the “SQL thread” can handle load - NOTE make changes to both Master and Secondary mysql

1. Increase innodb_buffer_pool_size is the total cache size for MySQL. Whenever there is a read request, MySQL server caches the data in the RAM in the form of LinkedList of BufferPages

# innodb_buffer_pool_size=64M

# 70% available memory ~12 GB

innodb_buffer_pool_size=12000M 

2. The mysql  "SQL threads" two operations:

1. Reads from the relay file maintaining position

2. Executes statements on the mysql db

Add additional worker thread to spilt the workload 

# Change to multithreaded replication:

replica_parallel_workers=10