Скачать или смотреть Understanding Sysbench Results: Why You Might See No Disk Read with MySQL Testing

Discover why `Sysbench` might show no disk read for MySQL tests and learn how to effectively measure your database's I/O performance.
---
This video is based on the question https://stackoverflow.com/q/63386978/ asked by the user 'xiaoqiang yu' ( https://stackoverflow.com/u/9834775/ ) and on the answer https://stackoverflow.com/a/64560703/ provided by the user 'au kk' ( https://stackoverflow.com/u/11531128/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Sysbench test Mysql but no disk read

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Sysbench Results: Why You Might See No Disk Read with MySQL Testing

When running performance tests on MySQL databases, it's common to utilize tools like Sysbench. However, many users encounter a perplexing situation: the DISK READ speed remains at zero while observing only DISH WRITE speed. If you find yourself in this scenario, you are not alone—this guide will help you understand what might be causing this phenomenon and how to address it effectively.

The Problem at Hand

You might have observed during your testing that:

The DISK READ speed is consistently at zero (0).

The buffer/cache is increasing, suggesting that data is being stored somewhere other than the disk.

Why does this occur, and what does it mean? The main contributor here is likely that the data generated by Sysbench is indeed being written to a temporary area in memory—specifically, the buffer—rather than directly on the disk.

Analyzing the Performance Metrics

To get to the root of this issue, we can look at a few key elements:

Environment Isolation: Where is MySQL running? If you are running multiple services or containers, interactions may not reflect accurate I/O metrics.

Monitoring Tools: Tools like iotop can be misleading if they do not correctly capture I/O activity under different user accounts or configurations.

Suggested Solution to Identify I/O Activity

To get a clearer picture of your database's I/O activity and assess whether data is being processed as expected, consider the following steps:

Step 1: Use Docker for Isolation

You can isolate your MySQL database in a container environment. This approach allows you to monitor performance in a controlled setting. Here’s how to set this up:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Monitor Resource Usage

In a separate terminal, run the following command to monitor the stats of the Docker container:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Run Sysbench

You can now run Sysbench to put load on your database and view any activity:

Execute the necessary Sysbench calls to simulate database operations and observe the I/O in real-time.

Step 4: Accessing the Container

If you need to inspect processes inside the container, you can access it with:

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Consider Hardware Limitations

If you are testing on lower-end hardware, such as a Raspberry Pi or similar system with a micro SD card, you might be limited by the performance of your storage medium. In some cases, the controller/drivers may not report I/O metrics correctly, leading to apparent zero readings.

Final Thoughts

In conclusion, if you notice a zero disk read speed while running Sysbench tests on MySQL, it may not necessarily indicate a problem with your setup. Instead, it could point to the inherent limitations of your monitoring tools or the underlying hardware used in your tests. By isolating your database environment using Docker and monitoring the appropriate resources, you can gain clearer insights into your MySQL performance.

If you're running on a cloud instance, like EC2, you may observe different results due to more robust hardware provisions. Understanding the environment and the tools you are using should help eliminate confusion regarding I/O statistics.

Remember, effective performance monitoring is crucial for ensuring your database runs smoothly.