Question 49: How to quickly judge the impact of IO delay on MySQL performance

ask

Is there an easy way to judge the impact of IO latency on MySQL performance?

We are looking for a virtual machine with poor IO, if you can't find it, then you are really rich.

Still start a MySQL instance:

First prepare a table with sysbench:

Then test the performance

Remember to run several times to warm up, only the last result is taken here

When running the pressure, take iostat at the same time:

We found that the disk IO is not saturated, so how much will the normal delay of disk IO affect the performance of this group of MySQL?

Let's simulate a disk in memory using the following command:

We stop the database and move the data directory to the emulated disk

Let's run the performance test again. The sysbench command used is the same as before. Take a look at the results:

We see a fourfold increase in performance, and we can apply for a disk replacement with this set of data

Let's compare the select pressure again:

Select using physical disks:

Emulate a selection of disks using memory:

It can be seen that for this set of select pressures, it is not very useful to replace the disk, and other optimization methods need to be used

What else do you want to know about the technical content of MySQL? Let me know by leaving a message!