This is the blog for Scout, Enterprise-grade server monitoring without the bloat.
70+ plugins, realtime charts, alerts, and Chef/Puppet-friendly configuration.

One change that lowered our DB I/O by 80%

Posted in Development | Comments Comments

When a server reports data to Scout, a small bit of data is updated in a MySQL MyISAM table. Each of these updates doesn’t change much data:

UPDATE `plugins` SET `last_reported_at` = '2010-11-17 19:53:00' WHERE `id` = 999999

I assumed that only the updated data was written to disk. I was wrong: Jesse Newland of Rails Machine pointed out that updating a row in a MyISAM table rewrites the entire row to disk (not just the updated columns).

These updates were expensive: we were updating small bits of data, but our table contained a large text column that accounted for 75% of the data stored in the table. Every update to a row in our plugins table would rewrite the large text column (even though the text column wasn’t changing).

The fix? Move the large text column to another table.

The impact was remarkable – here’s the change in the rate of data written to disk:

Detecting I/O Problems

The bottleneck for databases is often disk I/O. Hard drives are slow: most operations you conduct on a hard drive are several orders of magnitude slower than comparable operations in memory. For us, the warning sign was a burst of slow queries accompanying high disk utilization periods.

We use the following Scout plugins on our database server:

If your database writes to disk, make sure you’re monitoring disk I/O. We’d like to see our customers monitor disk I/O more at Scout (there are 3x more installs of the MySQL Stats plugin than Device Input/Output plugin).

Comments

blog comments powered by Disqus
comments powered by Disqus