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

1) Terms

2) Symptoms

3) Troubleshooting

3.1) Operating System

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

3.2) MySQL

void Time_zone_system::gmt_sec_to_TIME(MYSQL_TIME *tmp, my_time_t t) const {  struct tm tmp_tm;  time_t tmp_t = (time_t)t;  localtime_r(&tmp_t, &tmp_tm);  localtime_to_TIME(tmp, &tmp_tm);  tmp->time_type = MYSQL_TIMESTAMP_DATETIME;  adjust_leap_second(tmp);}
/* Return the `struct tm' representation of *T in local time,   using *TP to store the result.  */struct tm *__localtime_r (t, tp)     const time_t *t;     struct tm *tp;{  return __tz_convert (t, 1, tp);}weak_alias (__localtime_r, localtime_r)
/* Return the `struct tm' representation of *TIMER in the local timezone. Use local time if USE_LOCALTIME is nonzero, UTC otherwise.  */struct tm *__tz_convert (const time_t *timer, int use_localtime, struct tm *tp){long int leap_correction;int leap_extra_secs;if (timer == NULL)
{
__set_errno (EINVAL);
return NULL;
}
__libc_lock_lock (tzset_lock);
/* Update internal database according to current TZ setting.
POSIX.1 8.3.7.2 says that localtime_r is not required to set tzname.
This is a good idea since this allows at least a bit more parallelism. */
tzset_internal (tp == &_tmbuf && use_localtime, 1);
if (__use_tzfile)
__tzfile_compute (*timer, use_localtime, &leap_correction,
&leap_extra_secs, tp);
else
{
if (! __offtime (timer, 0, tp))
tp = NULL;
else
__tz_compute (*timer, tp, use_localtime);
leap_correction = 0L;
leap_extra_secs = 0;
}
if (tp)
{
if (! use_localtime)
{
tp->tm_isdst = 0;
tp->tm_zone = "GMT";
tp->tm_gmtoff = 0L;
}
if (__offtime (timer, tp->tm_gmtoff - leap_correction, tp))
tp->tm_sec += leap_extra_secs;
else
tp = NULL;
}
__libc_lock_unlock (tzset_lock);
return tp;
}
#if IS_IN (libc) || IS_IN (libpthread)# ifndef __libc_lock_lock#  define __libc_lock_lock(NAME) \  ({ lll_lock (NAME, LLL_PRIVATE); 0; })# endif#else# undef __libc_lock_lock# define __libc_lock_lock(NAME) \  __libc_maybe_call (__pthread_mutex_lock, (&(NAME)), 0)#endif
static inline void
__attribute__ ((always_inline))
__lll_lock (int *futex, int private)
{
int val = atomic_compare_and_exchange_val_24_acq (futex, 1, 0);
if (__glibc_unlikely (val ! = 0))
{
if (__builtin_constant_p (private) && private == LLL_PRIVATE)
__lll_lock_wait_private (futex);
else
__lll_lock_wait (futex, private);
}
}
#define lll_lock(futex, private) __lll_lock (&(futex), private)
bool Field_timestampf::get_date_internal(MYSQL_TIME *ltime) {
THD *thd = table ? table->in_use : current_thd;
struct timeval tm;
my_timestamp_from_binary(&tm, ptr, dec);
if (tm.tv_sec == 0) return true;
thd->time_zone()->gmt_sec_to_TIME(ltime, tm);
return false;
}
# Slow query
SELECT
id,
......
create_time, update_time,
......
FROM mytab
WHERE duid IN (?,?,?,?,? )
and (state in (2, 3)
or ptype ! =0)
# Queried table
CREATE TABLE `mytab` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`duid` char(32) NOT NULL,
......
`state` tinyint(2) unsigned NOT NULL DEFAULT '0',
`ptype` tinyint(4) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
......,
PRIMARY KEY (`id`),
) ENGINE=InnoDB

4) Troubleshooting

5) Best Practices

  • 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:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com