Queries regarding the slow.log file in MySQL.

Products

• Network Observability

Issue/Introduction

1. Why the slow.log exists on the secondary server

2. Difference in update frequency

3. Reasons for slow logs on the secondary server

Environment

Spectrum - 24.3.x, 25.4.x

OS - Windows/Linux

Cause

Queries regarding the slow.log file in MySQL.

Resolution

1. Why the slow.log exists on the secondary server:

• Independent MySQL Configuration: Each SS (primary and secondary) has its own local MySQL instance.
• Host-Specific Activation: The slow query log is enabled at the database level on that specific server.
• Persistent Settings: If the log was manually enabled for troubleshooting or exists in the my-spectrum.cnf (or my.cnf) configuration file, it will continue to run regardless of the server's role in a fault-tolerant setup.

2. Difference in update frequency:

• Primary Server (Stale Log): The primary's log suggests that either no queries have exceeded the long_query_time threshold (default is 10 seconds) since then, or the slow_query_log setting was disabled.
• Secondary Server (Constantly Updated): The secondary server's log indicates that it is currently executing MySQL queries that are taking longer than the defined threshold.

3. Reasons for slow logs on the secondary server:

• Background Maintenance: Even when not the active primary, the secondary SS performs background tasks like database synchronization, Archive Manager (DDM) processing, and local health checks that involve SQL queries.
• Database Synchronization: Failover and synchronization processes between primary and secondary landscapes can involve large data transfers and intensive queries, especially if there is a high volume of events or alarms.
• Resource Contention: The secondary server might be experiencing local resource constraints (CPU, I/O, or Memory) that cause otherwise "normal" queries to execute more slowly and trigger the log.
• Configuration Mismatch: The secondary server may have a lower long_query_time threshold than the primary, or it may simply have the log globally enabled (ON) while the primary has it disabled (OFF). 

Please find the document on how to enable the slow query logging for MySQL in Spectrum:
https://knowledge.broadcom.com/external/article?articleNumber=100882