slow queries and high % of wait I/O
We have a simple database with some InnoDB tables. No fancy queries are executed. Recently we have been having delays in SQL execution in peak hours. We see a number of slow queries and high % of wait I/O ( we use a Linux - RAID-5 dedicated DB server)
Table where the highest percentage (99.999) of slow queries are logged is below:
Schema:
*******
create table if not exists history (
id int unsigned not null auto_increment,
device_id varchar(100) not null,
url varchar(512) not null,
title varchar(255) null,
count int not null,
last_visited_at datetime not null,
favicon_url varchar(512) null,
primary key (id),
index index1(device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
Query that is logged repeatedly ( every 1 sec at peak hr):
*********************************************************
Select * from history where device_id='xxxxxxx'
Production server History table status Output:
*************************** ******************
Name: history
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 19880596
Avg_row_length: 332
Data_length: 6610747392
Max_data_length: 0
Index_length: 706215936
Data_free: 0
Auto_increment: 80657419
Create_time: 2008-05-10 07:22:05
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 32768 kB
1 row in set (0.97 sec)
Innodb_buffer_pool_size: 2GB
**********************
Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests at peak hour hovers around 0.035..
From MySQL documentation, I gather that the ratio of buffer_pool_reads to buffer_pool_readrequests should be less than 0.01.
My Questions:
a. Since the ratio of reads to readrequests at peak hour is around 0.035, can I assume that the "Innodb_buffer_pool_size" is the issue?
b. In our application, the ratio of Selects to Updates is 6:1. there are very few Inserts or deletes that occur. I tried to simulate the same problem in my test lab by looking at Innodb_rows_read and Innodb_rows_updated values from the production system and created a script to do it. However, I am unable to reproduce the problem with the same setup. ( the only thing different is that I did a MYSQLDUMP from the production system and loaded it in my test lab box). Is it really possible to reproduce the problem and if so how?
c. How can I compute the buffer_pool_size I will need for my application, if infact that is the issue?
I am a Newbie, so please bear with me if I havent provided sufficient information. If you need that info, I can get it. Thanks in advance for any help.