What’s your disk I/O thoughtput?

MySQL uses disk. Ok, so everybody knew that. MySQL uses disk in two primary ways.

  • Random I/O (Reading & Writing Data/Index blocks)
  • Sequential I/O (Binary Log, InnoDB Redo Log)

Historically it’s been best practice to separate these onto different spindles, and also separating the OS and tmp space onto a third spindle. With commodity H/W that can be easily done, but today a lot of people use SAN. Is this a good thing for a MySQL Database?
That’s a topic of much discussion at a later time, however I’ll add two points. A lot of SAN configurations are RAID 5, and RAID 10 is a better choice due to removing the requirement to calculate the parity. Second, last week I observed a RAID disk failure and it took an incredible long time for the disk to be re-built. Just how many SAN uses our there have actually timed a disk rebuild on a loaded system and seen the impact on the system in general.

Back on topic, I don’t have access to any variety of hardware, so community here is where you can help. Those that can spare 5 mins, and have some free disk space (< 5GB), here is an exercise.

Commands
`
$ time dd if=/dev/zero count=100000 of=testfile.32k bs=32k
$ ls -lh testfile.32k
$ rm testfile.32k

You should see something like (FYI: from a 5400rpm laptop drive)


$ time dd if=/dev/zero count=100000 of=testfile.32k bs=32k
100000+0 records in
100000+0 records out
3276800000 bytes (3.3 GB) copied, 160.172 seconds, 20.5 MB/s
real 2m40.342s
user 0m0.120s
sys 0m15.277s
$ ls -lh testfile.32k
-rw-r--r-- 1 usr grp 3.1G 2007-06-28 10:02 testfile.32k
`

If your output doesn’t provide the dd M/B output (like Solaris for example) if you could also add:
<br /> $bc<br /> 3.2*1024/160<br /> 20<br /> ^D<br />

NOTE: Replace 160 with the number of seconds from the real time (e.g. 2*60+40)

Of course I’m not collecting a lot of stuff, like comparing different block sizes, or looking at iostat for existing load and introduced load. I thought I’d ask an easy question to see what type of response and output I’d find.

If you want to add any additional information such as Drive Types & Speeds (e.g. SATA 5400rpm), RAID configuration or SAN configuration (e.g. RAID and connection type), OS and File System type that would be great, but completely optional.

If you would like add your results anonymously, please email me directly.

Thanks in advance.

Tagged with: Databases MySQL

Related Posts

Why INSERT IGNORE should not be used

Why INSERT IGNORE should not be used

Let’s say you’re building a reference table from source data, e.g. a silver medallion table from a primary source. In this example I am using a file of random locations on the globe extracted from OpenStreetMap (OSM) as my primary source.

Read more
What Constitutes Clean Data?

What Constitutes Clean Data?

Clean data is one of those terms everyone uses and few define precisely. Row counts match? No nulls in key columns? Values within expected ranges? All useful checks — and all insufficient once you move from a table view to a visualization view.

Read more
Curated MySQL Data Sets for Realistic Testing

Curated MySQL Data Sets for Realistic Testing

Synthetic benchmarks have their place, but I have always preferred working with real data. Not client production data — that stays private — but publicly available datasets that reflect the messy shapes, skewed distributions, and indexing challenges you encounter in the wild.

Read more