2

My target is to equally separate mysql database across the number of drives, using symbolic links on tables.

Now I can not seem to find the way to check IOs, reads, writes per table on mysql.

So now I am thinking maybe there is some way in linux to monitor ios, reads, writes per file ?

Iostat shows IOPS per device; Iotop shows IOPS per process; Is there anything to show IOPS per file ?

1
  • Have a look at my answer to "Linux IO monitoring per file?" here, on Server Fault. Jun 11, 2014 at 0:09

3 Answers 3

4

There is a camp in DBA-land (or was at one point, I haven't been through there recently) that held that optimal performance was to be found by micromanaging the storage infrastructure to such an extent that the systems and storage administrators had best get out of the way and just give them a pile of hard-drives, a server or two, and an occasional helping hand when asked.

These systems usually end up with a large number of RAID1 pairs with datafiles purposely and systematically spread across each and every one of them. Log drives would be used and similarly segregated. The ideas behind this method are pretty simple:

  • By doing it this way, you segregate databases from other big IOP consumers so big users would never affect the rest of the system.
  • By having lots of drives, the aggregate IOPS for the system is very high.
  • By minimizing the storage abstraction layers you make the system overall more reliable, and importantly have lower latency.

There is a significant problem with this approach, and Chopper3 nailed it: maintainability.

A system like that is going to need constant and continual attention and fine tuning to keep performing at 'optimum' as databases grow/shrink, usecases change, applications and their usage patterns evolve, recovery from run-away conditions happen, and maintenance cycles change. The sort of architecture I describe above works best for write-almost-never/read-lots sorts of workloads.

It also is used when it really is important for the database to eeke every last fractional percentage point of performance out of the hardware. This is a budget decision in many cases, where it is deemed that DBA time is cheaper than more hardware. However, there are some HPC cases where it just isn't possible to make a bigger box so you have to optimize what you have. For the rest of us who aren't using monster database servers there are always upgrades.

The other camp, and the one I ascribe to, handles it differently. It puts more faith in storage abstraction, and tends to scale a lot better. Instead of umpty R10 pairs, you put all of those pairs into a RAID10 set; or alternately a small number of RAID10 sets. This allows IOP aggregation same as above, but each datafile/tablespace/database gets access to quite a bit of surge capacity. By using multiple R10 sets you can provide I/O separation for those critical databases and log-files that need it. The need to micromanage per-file peformance is greatly reduced.

6

This idea of yours seems overly complex, fragile and hard to manage - can't you just put the disks into a RAID 10 array, that'll spread the data, and IOPS, across the disks evenly - it's what everyone else does.

2
  • Agree that the proposed solution is probably not as good as RAID 10, but I'm curious about the answer to the question itself. Is there a way to measure IO access to a particular file?
    – Yoav Aner
    Feb 11, 2012 at 14:41
  • If I had a physical server, or my VMs would support software raid - I would go to RAID 10, hands down. But the issue is that so far this is the least problematic solution to my problem.
    – Katafalkas
    Feb 11, 2012 at 16:08
1

You can monitor the I/O per file with a systemtap script. See: iotime.stp

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .