Showing entries 1 to 1
Displaying posts with tag: mysql performance iostat (reset)
mysql I/O performance analysis with iostat

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 …

[Read more]
Showing entries 1 to 1