MySQL time_zone and CPU Spike another performance troubleshooting

It was a transparent day and site visitors began to extend as we’re within the greatest sale occasion of the yr for considered one of our Managed companies shoppers.

This is likely one of the fascinating troubleshooting let’s dive in. Let me present some background. We had been working the Percona model of MySQL (5.7.30), on a 96 core VM with a 512GB RAM devoted DB node. That is the largest server we get within the self-hosted knowledge heart.

We began checking the efficiency graphs, and we use PMM for metric evaluation. In the meantime, we began getting calls out from the applying group on the latency and slowness

Under is the CPU utilization graph for the general problem interval, We are going to talk about what induced the sudden spike of the utilization ( Round 10:50 ) and the variable that was utilized to repair (dip within the graph)

Normally, it will be the ‘USER’ CPU could be excessive as a result of a foul question or as a result of excessive concurrency however right here it’s the opposite method across the system CPU is used as excessive as 60%.

We needed to see what’s inflicting this, so we ran a stack hint on a simulated system by utilizing percona toolkit’s pt-pmp is a read-only instrument that helps in accumulating GDB stack-traces for the hooked up program and printing stack traces for all threads

Warning: Run this instrument in manufacturing with absolute care since it will possibly make MySQL be unresponsive(freeze) for some time frame starting from seconds to longer based mostly on site visitors sample

Sorry for the picture, pls zoom in to have a more in-depth look

As highlighted within the above picture we will see “__tz_convert,my_system_gmt_sec,Time_zone_system::TIME_to_gmt_sec” calls being struck that are clearly associated to time zone conversion.

What’s actually occurring?

To know extra we had a contact base with the dev Staff, This is likely one of the distinctive monitoring purposes which is closely depending on the timestamp column for many of its operations with columns equivalent to “created_at, updated_at…”

MySQL depends on OS for time zone conversion ie., with time_zone variable set to ‘SYSTEM’ by default, each time a row is being inserted or up to date with timestamp MySQL obtains the OS session time_zone after which makes a subsequent OS API name to transform the corresponding conversion perform Time_zone_system::TIME_to_gmt_sec,

In our case, this OS name is inflicting the excessive system CPU utilization as excessive as 60%, since that is being known as at excessive concurrency. Thus inflicting the low-cost queries to be latent.

How did we repair this problem?

Now let’s see how we nailed the issue, We made the MySQL deal with time zone conversion by itself by altering the “time_zone” parameter in MySQL from default ‘SYSTEM’ to “+05:30”(IST) it is a dynamic change with MySQL and it doesn’t require of mysqld course of

International setting:

mysql> set international time_zone=’+05:30′;
Question OK, 0 rows affected (0.00 sec)

Config file:

default-time-zone = “+05:30”

After making use of the modifications we made a ‘connection refresh’ by doing a rolling restart for the applying, And we began witnessing an instantaneous dip (round 13:50 ) in system CPU utilization, sharing the picture once more

With the managed DBaaS like AWS RDS, you’ve the availability to decide on the named time zone within the parameter group as under.

We will additionally load the named timezone like “Asia/Calcutta” with self-hosted MySQL by changing and loading zone data with “mysql_tzinfo_to_sql” utility which comes by default as under

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Key takeaway:

With Fashionable closely timestamp dependent purposes could be closely affected with the default setting of time_zone however could be unrecognized usually, we’d advise having this variable set with MySQL for higher efficiency.

Completely satisfied Troubleshooting !!

Leave a Reply

Your email address will not be published.