November 22
Derek
One change that lowered our DB I/O by 80%
Posted in Development | 
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).
