Diagnosing and Resolving High CPU Usage in MySQL 5.6 Caused by Mutex Contention and Excessive malloc/free

Background

The MySQL instance (5.6.29, master‑slave) on CentOS 6.8 experienced frequent CPU spikes when a specific query selecting many uid values (hundreds per execution) ran concurrently. The query uses the index idx_msg_uid_time and runs in seconds when executed alone, but exceeds an hour under concurrency, causing high CPU usage.

Diagnosis

Using mpstat -P ALL 1 revealed most CPU consumption in the sys (system call) portion. perf top showed the hotspot in _spin_lock . A detailed perf report indicated that the CPU was mainly spent on mutex contention.

Further tracing with pt‑pmp highlighted heavy activity in mem_heap_alloc and mem_heap_free . The InnoDB call stack for reading a row was examined, showing the path:

row_search_for_mysql → row_vers_build_for_consistent_read → mem_heap_create_block_func → mem_area_alloc → malloc → _L_unlock_10151 → __lll_unlock_wait_private

The function row_vers_build_for_consistent_read loops until a suitable snapshot is found, invoking mem_heap_alloc/free on each iteration. Because the table test_history is frequently updated, its undo history becomes long, making snapshot searches expensive and causing many malloc/free calls. The default Linux memory allocator (ptmalloc) suffers from lock contention under such heavy allocation patterns, eventually triggering OS‑level spinlocks.

Solution

Replace the default memory allocator with tcmalloc , which handles high‑concurrency allocations more efficiently. Add the following line to my.cnf under the [mysqld_safe] section and restart MySQL:

[mysqld_safe]
malloc-lib=tcmalloc

After applying the change, the instance ran for over 72 hours without recurring CPU spikes, and the CPU usage before and after the modification showed a significant reduction.