Re: slow queries and high % of wait I/O
Posted by:
Rick James
Date: January 19, 2009 11:37PM
Defragmentation probably won't help much, nor for long. InnoDB is block-oriented, so it tends to move toward fragmentation.
"Around 25" implies upwards of 25 disk hits. (1/3 may be cached; a few may rows may happen to be in the same block.)
| innodb_flush_log_at_trx_commit | 1 | -- If you have typical disk system, this limits you to about 100 transactions per second. Extra secure, but extra slow. Look up the docs; try it at 0 or 2.
Rows-examined == rows returned -> good index on that table.
How often does History change? If enough less frequently than the SELECT *, it might to use the Query Cache to cache at least that one select.
Do you use all the fields when you do SELECT * FROM history? If not, you could improve cachability by vertical partitioning and putting the unused fields in another table. (This won't keep you from hitting the disk, but should calm it down some.)
Compressing the three big VARCHARs would shrink the data some. (Again, some but not enough I/O savings.)
Unfortunately, there is no simple way to "cluster" the data around device_id, which would make the SELECT _much_ more efficient in disk access. What comes to mind is the compound key that works in MyISAM but not InnoDB: "PRIMARY KEY (device_id, id)", where id is AUTO_INCREMENT but it starts from 1 for each distinct device_id. You could do that programatically (with some effort -- can't use AUTO_INCREMENT; you have to SELECT MAX(id) WHERE device_id = ?, do the INSERT, make sure the transaction did not fail due to a collision, etc.) But then all the rows for a given device_id would (usually) be in a single block, not 'around 25', like it is now.
My performance mantra is "count the disk hits".