MySQL Time Zone Setting Causes High CPU and Slow Queries Due to Mutex Contention

At 16:27 the production system experienced a sudden spike in system CPU usage, with many threads in the system state and the number of running threads reaching 1500, causing application timeouts while other resources (IO, network, memory) remained normal.

The offending SQL statement was:

select count(*) from db.table where create_time >= '2017-07-01 00:00:00' and create_time < '2017-08-01 00:00:00' AND type = 'A';

The table is simple (≈10 k rows) with proper indexes, and the query normally returns only a few thousand rows at a low QPS (~40) and executes in sub‑second time.

During the incident the QPS did not increase, so the high connection count and thread count were caused by slow responses rather than traffic spikes. Various checks (hardware faults, network latency, MySQL errors, other slow SQL) found no obvious issues.

Temporarily taking the service offline stopped the problem, but the root cause remained unknown.

To reproduce the issue, a test environment was set up with 30 concurrent threads using sysbench to run the same query. The reproduction showed the same symptoms: high system CPU, many running threads, and low QPS.

Further analysis revealed a ten‑fold increase in kernel context switches and a large amount of CPU time spent in spin locks. Stack traces showed many threads executing the Time_zone_system function.

Investigation uncovered that when MySQL’s time_zone variable is set to system , each access to a TIMESTAMP column triggers a libc time‑zone conversion protected by a global mutex ( __libc_lock_lock ). Under high concurrency this mutex becomes a bottleneck, causing threads to spin and perform frequent context switches, which explains the high CPU and low throughput.

Changing the server’s time_zone setting to a fixed offset (e.g., '+8:00' ) bypasses the system conversion, eliminates the mutex contention, and restores normal performance, as confirmed by a repeat sysbench test.

In summary, the incident was caused by MySQL’s timestamp conversion under time_zone=system , which introduces a global lock that severely degrades performance under concurrent access; setting an explicit time zone resolves the issue.

Post‑mortem notes that the problem appeared after the table grew to about 5 k rows and the workload increased, indicating that the combination of row count and QPS triggered the mutex contention.