Diagnosing and Fixing MySQL 5.7 Memory Leak in Thread/sql/slave_sql

Background: After receiving memory‑alert notifications, monitoring revealed a MySQL 5.7 server whose memory usage kept growing despite restarts, eventually exhausting RAM within about two weeks.

Scenario: MySQL version 5.7.12, no special test environment.

Initial Investigation

Attempt 1 – Check buffer parameters: Reviewed buffer‑related settings (referencing a previous crash report) but found no obvious misconfiguration.

Memory Usage Inspection

Used pmap -d to examine process memory. The anon column showed that about 95% of the process memory was actively allocated, far exceeding the expected innodb_buffer_pool size (≈50% of total RAM).

Thread‑Level Analysis

Enabled memory‑related instruments in performance_schema:

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

After enabling, queried CURRENT_NUMBER_OF_BYTES_USED from memory tables to get an approximate total memory usage. Sorted threads by this metric to find the biggest consumers.

Results highlighted two threads with unusually high memory consumption: thread/sql/slave_sql – the SQL thread that replays binlog events during replication. thread/sql/one_connection (monitor user) – a monitoring plugin connection.

Root Cause Identification

Researching MySQL bug #71197 revealed a known issue where the thread/sql/slave_sql thread can retain large amounts of memory. The official fix was to disable parallel replication and store replication‑related info in files rather than tables.

For the monitor thread, inspection of the plugin’s source code showed that after executing a cursor‑based SQL query, the cursor was never closed, causing memory to accumulate in memory/sql/String::value.

Resolution

Applied the suggested fix for the replication thread by disabling parallel replication.

Modified the monitoring plugin to properly close cursors after use, then redeployed the updated code.

After these changes, the memory growth rate dropped dramatically. The issue was documented internally as a cautionary example.

Final Outcome

Although the SQL thread still cannot release all memory, the growth is now slow enough to be manageable with periodic restarts (approximately every two months). Ongoing monitoring confirms that the problematic instances have not exceeded critical memory levels.