EmergencyEMERGENCY? Get 24/7 Help Now!

Using ioping to Evaluate Storage Performance for MySQL Workloads

 | January 8, 2018 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

In this blog post, we’ll look at how ioping can be used with other tools to understand and troubleshoot storage performance, specifically as it relates to MySQL workloads.

I recently ran into ioping, a nice little utility by Konstantin Khlebnikov that checks storage latency.  

For me, the main beauty of ioping is its simplicity and familiarity. It takes after the ubiquitous ping tool, but “pings” the storage instead of the network device.

ioping

First, let’s talk about what this tool isn’t: it isn’t a benchmark tool to stress load your storage as heavily as possible. For that, you can use iozone or sysbench (among many others). This also isn’t a tool for looking at storage latency history. Use PMM’s  Disk Perfomance Dashboard for that instead (among many others).

However, it is a great tool to evaluate the current storage latency. It allows you to see if storage is performing as expected, or if there are some performance issues that can express themselves as general slowness and as latency spikes for some requests. These latency issues are not always easily visible in historical graphs that are plotting averages.

What Storage Latencies Matter Most for MySQL ?

Before we look at using ioping to measure them, what IO latencies matter most for MySQL?      

The first is Sequential Synchronous writes to the Innodb Log File. Any stalls in these will stall write transaction commits, and all following transactions commits as well. Even though MySQL supports Group Commit, only one such Group Commit operation can process at any moment in time.

The second is Random Reads, which are submitted through Asynchronous IO, typically using a DirectIO operation. This is critical for serving your general IO intensive queries: Selects, Updates, Deletes and most likely Inserts will relay them on fetching such data from storage. Such fetches are latency sensitive: since they must be completed during query execution, they can’t be delayed.

You may ask, “What is about Random Writes?”  Random (non-sequential) writes happen in the background as InnoDB flushes dirty pages from its buffer pool. While it is important, storage has enough throughput to keep up with the workload. It is not latency sensitive since it is not in any query execution critical path.

One more access pattern important for MySQL performance is writing binary logs (especially with sync_binlog=1). This is different from writing to the InnoDB log file, because writes go to the end of file and cause the file to grow. As such, it requires constant updates to the file system metadata. Unfortunately, it doesn’t look like ioping supports this IO pattern yet.

Simulating MySQL IO Patterns with ioping

Now let’s see how we can simulate such IO patterns with ioping.

To simulate writing to the log file, we will use a medium-sized file (64M) and sequential 4K size writes to assess the latency:

For Read IO testing, we better use 16K IOs (InnoDB default page size) that are submitted through Asynchronous IO in O_DIRECT Mode:

The tests above were performed on the idle instance, so they show what you would expect: the best possible latency. If you run a similar test on a system with a real workload, you would likely see significantly more variance. Lets go ahead and simulate some extra load by using the sysbench tool:

This simulates a relatively light load on the system. For heavier loads, you can create a larger file set and/or increase the number of threads from one to a higher value.

Running this workload, we would get:

Simulated “Log Write”

Simulated Query Reads

Note that even with such trivial background loads you can see 10x or so outliers that can affect query and transaction latency.

Using  ioping for Monitoring

What if you not only want to run ioping periodically to check if your storage is performing well now, but also want to use it for monitoring so that you get an alert if storage latency spikes for any reason? For this, ioping supports the -B option. It suppresses all fancy human output and only print raw statistics. It also allows you to supply a ping interval in fractions of seconds, which is handy if you want to run enough pings for data to be statistically significant (but without the check taking too long):

The meaning of all numbers is described on the man page (run man ioping). For monitoring you might want to look at offsets 6,7,8 — which specify avg, max and stdev statistics for IO requests measured in nanoseconds (the manual for my version says it is microseconds, but it is incorrect).

Hope you find this tool helpful!

PREVIOUS POST
NEXT POST
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master’s Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

2 Comments

  • Hi Peter,

    nice writeup. In fact, I have been using ioping to test latency of storage used by MySQL since quite some time now!

    I have crafted a small script that leverages ioping and prints stats for every single LUN in a pretty way – if you
    have a SAN based volume (many LUNs) this may be handy to find hotspots etc… it’s on my GitHub page, link below.

    Give it a try!

    Rick

    https://github.com/RickPizzi/pztools/blob/master/pz-arrayperf.sh

Leave a Reply