MySQL Forums
Forum List  »  Performance

slow queries and high % of wait I/O
Posted by: jawahar Muthukrishnan
Date: January 19, 2009 01:58PM

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:

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

Options: ReplyQuote

Written By
slow queries and high % of wait I/O
January 19, 2009 01:58PM
January 19, 2009 05:36PM
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.