Re: Very high disk utilization by mysql server during reads.
mysql> show create table table1\G
*************************** 1. row ***************************
Table: table1
Create Table: CREATE TABLE `table1` (
`field1` bigint(20) NOT NULL DEFAULT '0',
`field2` mediumint(9) NOT NULL DEFAULT '0',
`field3` varchar(36) NOT NULL DEFAULT '',
`field4` varchar(255) DEFAULT NULL,
`field5` varchar(35) DEFAULT NULL,
`field6` varchar(255) DEFAULT NULL,
`field7` varchar(255) DEFAULT NULL,
`field8` blob,
`field9` varchar(255) DEFAULT NULL,
`field10` varchar(255) DEFAULT NULL,
PRIMARY KEY (`field1`,`field2`,`field3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql>
mysql> explain select field4,field5,field6,field7,field8,field9,field10 from table1 where field1=1234 and field2=9;
+----+-------------+---------------+------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | table1 | ref | PRIMARY | PRIMARY | 11 | const,const | 434 | |
+----+-------------+---------------+------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.04 sec)
mysql>
mysql> SHOW TABLE STATUS LIKE 'table1'\G
*************************** 1. row ***************************
Name: table1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 50125045
Avg_row_length: 1211
Data_length: 60713493288
Max_data_length: 281474976710655
Index_length: 3141316608
Data_free: 2577892
Auto_increment: NULL
Create_time: 2012-06-28 16:43:19
Update_time: 2012-07-03 10:06:02
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
I have 32G of RAM. There are few other Innodb tables also in the database and this is the only MyIsam table. I have assigned 24G of RAM to innodb buffer pool. I am using mysql 5.5.25 version.
Thanks for the reply Aftab.