Re: Mysql 5.5 High I/O Wait
First, some critique of the schema. (Some of the critique applies to the high I/O.)
* There should be a PRIMARY KEY to explicitly identify each row.
* Don't have an "array" of columns:
`pict1` longblob,
`pict2` longblob,
...
`pict12` longblob,
Instead have another table, JOINed to this table on the PRIMARY KEY of `track`.
* Generally it is better to use numeric fields for numbers:
`latitude` varchar(20) DEFAULT NULL,
`longitude` varchar(20) DEFAULT NULL,
* Use NOT NULL wherever appropriate. (Surely something is non-optional in this table?)
* Use a single DATETIME or TIMESTAMP field, not two fields:
`pdate` date DEFAULT NULL,
`ptime` time DEFAULT NULL,
* Use NULL or '', not both:
where longitude != ''
`latitude` varchar(20) DEFAULT NULL,
(unless you really need two non-values)
* Don't do this unless you really need all the fields:
select *
* != is rarely optimizable. Hence you WHERE clause probably focused on
customer = '49081'
and could not make use of the other three parts. See
EXPLAIN SELECT ...
to see which KEY it used, and how much of that key it used. I suspect key_len is about 23, implying that only `customer` was used.
* How many rows have
customer = '49081'
I suspect it is 8653, based on the slowlog.
What is the avg_row_len (See SHOW TABLE STATUS `track`.)
Because of the BLOBs and LONGTEXT, I suspect the avg_row_len is large. (We are finally getting to an important point...)
So, starting over, and putting together the pieces:
* 8653 rows fetched.
* InnoDB without PRIMARY KEY, but secondary key is useful. This allows it to scan 8653+1 rows of the index (not I/O intensive; maybe 50 blocks to fetch).
* But, since customer is not at the beginning of the PK, it is upwards of 8653 _random_ disk hits to get the rows.
* Furthermore, you are asking for all the fields ("select *"). BLOBs and TEXTs are (usually) stored in blocks separate from the row. So, that means another 8653 (maybe lots more) blocks to be fetched.
* On ordinary disks, a Rule of Thumb is that you can do 100 I/Os per second. So assuming only 50+8653+1+8653 blocks to fetch, that would be about 174 seconds. You were lucky; some blocks were probably cached (and/or some of my assumptions were wrong), since it took only
Query_time: 20.691085
If you fetch the same data for the _same_ customer twice in a row, it will probably be much faster the second time -- this is where the buffer_pool caching comes into play.
If you have lots of users asking about lots of _different_ customers, each will be pounding on their variant of 50+8653+1+8653 blocks that may or may not be cached. When all the active customers are cached, things run smoothly.
> MAXCLIENTS IN APACHE = 128
Probably implies that you are allowing 128 users to initiate SELECTs like this one all at the "same" time. MySQL is happy doing several, but 128 will probably lead to all 128 slowing down because of sharing resources (CPU, I/O, mutexes, locks, etc, etc).
So, another "fix" for your problem: Decrease MaxClients to, say, 30. This won't fully cure it, but it will keep it from being so severe when it does happen. (It's why metering lights on freeway ramps actually speed up traffic.)
(I'll look at the STATUS and VARIABLES now.)