MySQL Forums
Forum List  »  Performance

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".

Options: ReplyQuote

Written By
January 19, 2009 05:36PM
Re: slow queries and high % of wait I/O
January 19, 2009 11:37PM
January 21, 2009 01:22AM
January 20, 2009 01:28AM
January 20, 2009 08:41PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.