Posted on

Attached Storage Hindering Commit Performance

Even with SSD becoming more prolific for local-ish storage even on cloud servers, we still encounter attached storage (SAN) quite frequently.  This can be fine, but we often find that the performance of SANs is quite dismal.

  • SANs are very efficient with bulk sequential reads or writes, which a database server of course doesn’t care about;
  • SANs often have a large memory cache (sometimes with intermediate SSD), optimising frequent reads – again a database server won’t benefit from this, because it will already have any recent data in its own caches (for MySQL/MariaDB, that’d typically be the InnoDB Buffer Pool).  So usually any database disk read needs to access physical storage = slow;
  • SANs will cache data writes in (battery backed) memory.  That can work fine, provided they respond quickly enough.

Slow response on that last aspect can really damage performance. But we need a reference point.

So let’s compare with a fairly fast spinning HDD, 15000 RPM.  That sounds like a lot, but 15000 RPM / 60 seconds = only 250 spins per second.  It gets slightly better, but not that much: even presuming the disk is at the correct cylinder already (let’s hope it is because disk seeks are measured in milliseconds which is, relatively, really slow) to write to the InnoDB log file, you will -on average- still have an overhead of 1/2 round of the disk to get the head to the right point so you can write.  So you’ll get at most 500 writes per second.

Anyway, that’s spinning disk performance in an optimal situation. You’d expect an expensive SAN to deliver higher write/fsync performance than that, right?  So, you may well expect that, but you prepare to be disappointed.  We often see performance in the range of 500-600.

InnoDB tries to combine commits when it writes to and fsyncs the InnoDB log file, so that helps a bit.  We also see some SANs that do wonderfully well with numbers in the thousands, and of course local SSDs that easily do close to ten thousand and higher.

Just be aware that expensive “enterprise” hardware does not necessarily help database performance.

If you need assistance with recommendations for infrastructure design, or measuring on existing systems, contact Open Query.

Posted on