Analyzing and Fixing High CPU Usage Caused by MySQL Timestamp Time‑Zone Conversion

The author recounts a friend’s MySQL performance problem where the system load spiked to 40.4% CPU while processing TIMESTAMP columns. Perf and pstack data revealed many threads stuck in time‑zone conversion functions such as Time_zone_system::gmt_sec_to_TIME .

TIMESTAMP occupies 4 bytes and stores seconds since the Unix epoch; when displayed, MySQL must convert this value according to the session time_zone setting. The article demonstrates how to inspect the internal storage by creating a test table, inserting a TIMESTAMP value, and using the bcview tool to read the .ibd file, revealing the raw epoch seconds (e.g., 1546275661).

Converting the epoch to a human‑readable date depends on the time_zone variable:

If time_zone='SYSTEM' , MySQL uses the operating system’s time zone via Time_zone_system::gmt_sec_to_TIME .

If time_zone is set to an explicit offset (e.g., '+08:00' ), MySQL uses its own implementation Time_zone_offset::gmt_sec_to_TIME .

Both implementations inherit from the abstract Time_zone class, so the upper‑level call is always Time_zone::gmt_sec_to_TIME , but the concrete subclass determines the conversion method.

To fix the issue, the author suggests two approaches:

Set time_zone to a specific offset (e.g., '+08:00' ) to avoid the OS API path and use the internal conversion, noting that this may increase user‑space CPU usage.

Replace TIMESTAMP columns with DATETIME, which stores values without automatic time‑zone conversion and incurs only a 1‑byte overhead.

After applying the time_zone='+08:00' change, CPU usage dropped significantly, as shown by before‑and‑after screenshots.

The article also provides alternative stack frames for both SYSTEM and explicit offset conversions, illustrating the call chain from Time_zone_system::gmt_sec_to_TIME or Time_zone_offset::gmt_sec_to_TIME down to field handling and protocol transmission.

Finally, the author promotes his own MySQL replication book and provides contact information for further assistance.