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 172.50 1.20 3340.00 15.20 1670.00 7.60 19.32 1.02 5.90 5.60 97.22

While the numbers fluctuated, the effective read throughput never went much over 1800 kb/s. The drive is certainly not the problem — a quick run of hdparm -T -t showed an average read throughput of nearly 50mb/s on this 7200RPM SATA drive.

The numbers for this 5 second period tell an interesting story.

The system sent an average 172.5 read requests/second to the drive, with each read fetching only 19.32 512-byte sectors (about 9.6kb), giving you 9.6*172.5 = 1656kb/s.
The average wait time (apart from queuing) was 5.6ms, so (172.5+1.20)*5.6 = 972ms of each second was spent waiting on some I/O.

Needless to say, the statistics suggest the single disk head is scrambling around fetching data from all different parts of the drive and returning the data back quite slowly overall.

myisamchk statistics

Taking the server down to do some deeper investigation, myisamchk -r -i reports a key statistic:

Blocks/Record 1.34

The table has about 6 million rows, so over 8 million records are stored with as many as 34% of the rows fragmented. This is a common problem for myisam tables containing variable-length fields that are frequently updated. Refer to this page for details.

the way it should be

While I was running the stats above, i tried the same query on a different box with the same table and disk that i had recently restored. Running the same query takes nearly 1/20th of the time, and the iostat (with -m) shows:

avg-cpu: %user %nice %system %iowait %steal %idle
0.50 5.30 25.40 27.00 0.00 41.80

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 47.50 0.00 378.64 0.00 40.62 0.00 219.69 1.68 4.30 2.01 76.01

Now that’s more like it!

We manage nearly double the reads/s, and each read is pulling 219.69 segments = 109kb of data. Our effective throughput is over 20 times what it was before.

the lesson

While painful for large tables, this is an example of why frequent OPTIMIZE statements, especially against myisam tables, are a very good idea. Not only do they defragment your tables, improving performance on any queries involving table scans, they reduce the risk of table corruption by reducing the number of pointers between split records.