API Gateway: MySQL replication issues with the service_metrics and service_metrics_details tables

book

Article ID: 141122

calendar_today

Updated On:

Products

CA API Gateway

Issue/Introduction

This article will discuss some common issues seen when replication for service metrics is enabled, and the recommendations from Broadcom.

Common issues seen:

  • Replication breaks while ssg backup is running (due to slow queries against the database).
  • Replication breaks due to duplicate entries:
    Error 'Duplicate entry '20c25295fdfc4e81bcf84945596e4543-\x00\x00\x00\x00\x00\x00\x00\x0' for key 'nodeid'' on query. Default database: 'ssg'. Query: 'insert into service_metrics (nodeid, end_time, interval_size, back_max, front_max, back_min, front_min, attempted, authorized, completed, period_start, resolution, published_service_goid, service_state, start_time, back_sum, front_sum, goid) values ('20c25295fdfc4e81bcf84945596e4543', 1573200430000, 5000, null, null, null, null, 0, 0, 0, 1573200425000, 0, x'0000000000000000FFFFFFFFFFFFFFFF', null, 1573200425000, 0, 0, x'D201C5A7A9D6517C4C31A18990B76F22')'​
  • Replication breaks due to key contraints:
    Error 'Cannot add or update a child row: a foreign key constraint fails (`ssg`.`service_metrics_details`, CONSTRAINT `service_metrics_details_ibfk_1` FOREIGN KEY (`mapping_values_goid`) REFERENCES `message_context_mapping_values` (`goid`) ON DELETE NO ACTION ON UPD)' on query. Default database: 'ssg'. Query: 'insert into service_metrics_details (back_max, front_max, back_min, front_min, attempted, authorized, completed, back_sum, front_sum, mapping_values_goid, service_metrics_goid) values (27, 29, 27, 29, 1, 1, 1, 27, 29, x'BD0182D9CE24D1A3AE79E38B9ADF2B95', x'BD0182D9CE24D1A3AE79E38B9ADF62C6')'​

Cause

There are many root causes for the various different replication errors, but the common theme is replication of service metrics, creating additional overhead and making a storm for new issues.

Environment

This article applies to all supported API Gateway versions.

Resolution

Broadcom recommends disabling the replication of the service_metrics and service_metrics_details tables in MySQL in production systems. The solution below will exclude the service metrics-related tables from MySQL replication.

  1. Edit the /etc/my.cnf MySQL configuration file, and add the following three lines:
    • replicate-ignore-table=ssg.service_metrics
      replicate-ignore-table=ssg.service_metrics_details
      replicate-ignore-table=ssg.service_usage
    • THIS SHOULD BE DONE Just below the following section in my.cnf,
    • # Slave reliability items:
      slave-skip-errors=126,1053,1105,1129,1158,1159,1160,1161
      slave-net-timeout=30
      slave_exec_mode=IDEMPOTENT
      #Add replication ignores here
      replicate-ignore-table=ssg.service_metrics
      replicate-ignore-table=ssg.service_metrics_details
      replicate-ignore-table=ssg.service_usage
  2. While editing the /etc/my.cnf MySQL configuration file, edit the existing line below by changing it from 1 to 0 to read as follows:
    • innodb_flush_log_at_trx_commit=0
  3. Restart the mysqld service: service mysqld restart

The above steps should be completed on each database node in the cluster.

Additional Information

If replication broke after making these changes and after restarting the mysqld service, restart replication with the restart_replication.sh script.