Question 48: Why is it not encouraged to adjust the system time when MySQL is running?

Ask

When MySQL is running, if we adjust the system time, what impact will it have?

experiment

By convention, we build a database:

In a session, perform vsleep:

While sleeping, we adjust the server time to the future 10 seconds:

We will find that sleep exits immediately and only takes 0.82 seconds:

We rarely use sleep in our business, so will adjusting the system time have a greater impact? Let's take a look again:

We are in a session, lock a table:

In another session, we do the following things:

1. Print a timestamp first
2. Adjust lock_wait_timeout
3. Access test.a table

At this point, we adjust the system time, 10 seconds to the past:

After a while, when the request to access test.a times out, let's check the output:

We subtract the two timestamps and figure out how long the lock lasted:

5375908-5375891 = 17 seconds

From this we know: adjusting the system time will affect the calculation of the MDL waiting time

Tips

The way we get the system time here is a bit strange, it is obtained from /proc/timer_list instead of using functions such as date

The main reason is: When the system time is adjusted, the output of commands such as date will also be affected.

We want to objectively evaluate how long MySQL actually waits.In addition to manually pinching the stopwatch, we can also use the monotonic clock to calculate.

Monotonic clock will not be affected by system time changes, the output in /proc/timer_list is a kind of monotonic clock

In addition to the above experiments, adjusting the system time will have other effects on the running MySQL, such as the calculation of the waiting time of semi-synchronization, the calculation of delay time of delayed replication, etc.

We do not recommend adjusting the system time while MySQL is running. If you need to adjust, you should restart MySQL in time

Questions

In this article, we tested the waiting time of MDL. You can design an experiment to test the waiting time of InnoDB lock, and you will find a big difference.

You can consult the information to explain the difference.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!