Here is a situation I’ve run into a few times when dealing with mysql databases. We’re trying to run a one-off query against a high-traffic, large table and the WHERE condition is against a non-indexed field. Let’s say our table is 5GB in size. We issue the following:
SELECT count(*) from five_gb_myisam_table WHERE
non_idx_field = 'asdf';
and we wait…
and wait some more.
5GB is not a small table, but this ideally should not take more than a few minutes on a relatively modern system.
iostat is your friend
In cases like this, iostat -x 5 is your friend.
While the query was running, this was a typical 5 second
interval:
avg-cpu: %user %nice %sys %iowait %idle
2.30 0.00 1.30 96.40 0.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 0.70 …