MySQL Practices: How to Handle Slow SQL Execution Due to Time Zone Setting

By Tian Jie

Long database query times always trigger application access problems. However, we rarely pay attention to slow SQL execution caused by time zone setting. Thanks to one customer’s meticulousness and persistence, we have found the cause of the problem.

1) Terms

2) Symptoms

From 22:03:00 to 22:04:00 on March 19, 2020, a large number of active connections of MySQL 8.0 instances were accumulated, a large number of low-cost queries were recorded in slow logs, and the CPU utilization was low but the sys CPU utilization fluctuated abnormally.

3) Troubleshooting

3.1) Operating System

The following factors may cause lag:

  • Fluctuations on the operating system of the physical machine (troubleshoot based on the IO_WAIT metric)
  • MySQL mechanism

3.2) MySQL

After we exclude exceptions on the operating system, we start to analyze the call stacks of the MySQL process.

To better analyze MySQL operations, Alibaba databases provide the Bian Que system to track, count, and display the internal method calls of a specified process.

As shown in the preceding figure, 40.5% of the CPU time is consumed to call the Time_zone_system::gmt_sec_to_TIME() method, and the code is as follows:

According to the code, note that localtime_to_TIME() and adjust_leap_second() are simple format conversion and computation operations and do not involve system calls.

localtime_r() involves the __localtime_r() method in the GNU C Library (glibc), and the code is as follows:

The code for implementing __tz_convert() is as follows:

According to the code, lock and unlock operations are performed. The code for __libc_lock_lock() is as follows:

The code for lll_lock() is as follows:

According to the code, atomic_compare_and_exchange_val_24_acq() tries to lock futex.

As a memory area shared by multiple threads, futex triggers system calls and enters the system mode upon the contention of multiple client threads (multiple sessions and queries). This increases the sys CPU utilization.

In addition, the protection lock mechanism of the critical zone limits the concurrency of the time zone conversion method __tz_convert(). As a result, multiple sessions and queries are waiting to acquire the lock to enter the critical zone, which may cause lags upon conflicts or fierce contention.

But what triggers Time_zone_system::gmt_sec_to_TIME() calls?

The code of the Field_timestampf::get_date_internal() method is as follows:

The virtual function gmt_sec_to_TIME() of base class Time_zone is called here to convert UTC seconds since Epoch to time format. As the name of the Field_timestampf::get_date_internal() method implies, the query should involve access to the timestamp data type.

Based on the preceding speculation, let’s verify the slow query and its data type.

According to the preceding information, both the create_time and update_time fields are timestamps.

4) Troubleshooting

According to the previous analysis, the futex lock contention on the operating system due to the Time_zone_system::gmt_sec_to_TIME() method causes low-cost queries to be slow. To avoid calling the method, change the value of the time_zone parameter from the system to the local time zone in the instance console. For example, change the value to +8:00.

Then the Time_zone_offset::gmt_sec_to_TIME() method is called to compute MySQL data. This avoids locking and unlocking in the operating system caused by access to glibc functions.

A performance comparison before and after this modification is shown in the charts below (by comparing the time needed to query timestamps the same number of times).

About 15 minutes when time_zone is set to the system:

About 5 minutes when time_zone is set to +8:00:

5) Best Practices

If a high-concurrency application involves high-frequency access to timestamps:

  • If the data type is the timestamp, we recommend setting the time_zone parameter to a value in the UTC or GMT offset format in the console, such as +8:00. This effectively reduces the execution overhead of highly concurrent queries as well as the response time (RT).
  • After MySQL 5.7, Datetime types contain the default value of timestamp types and support the on update current_timestamp property. Therefore, we recommend using Datetime types instead of Timestamp types.

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.