Very slow query for no apparent reason
Hi, I am testing a loaded environment with many accesses to one of the tables (table "A").
The accesses include update and queries.
After a while, i detect that a query performed on another table (table "B") which is of size 1.45 MB(about 1500 rows), is very slow.The query does select on all the table with no where clause.
Table "B" has only a few queries running on it, no locks. So i don't understand why it starts after a while to run real slow.
When i run show full process list i see that the query hangs a long time on
'sending data' phase.
I tested the DB using mysql report. No aparant locks, Innodb Buffer has a lot of available memory.
No problems i can see.
Any idea why after a while table B is very slow. What should be done? is it configuration?
Some infomration which might help:
Create:
CREATE TABLE `accelerators` (
`acc_id` int(20) NOT NULL DEFAULT '0',
`acc_kind` varchar(10) NOT NULL DEFAULT '',
`name` varchar(60) DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`version` varchar(80) DEFAULT NULL,
`model` varchar(30) DEFAULT NULL,
`serial_number` varchar(30) DEFAULT NULL,
`status` int(11) DEFAULT '0',
`central_acc` tinyint(1) DEFAULT '0',
`head_acc` tinyint(1) DEFAULT '0',
`default_gateway` varchar(50) DEFAULT NULL,
`subnet_mask` varchar(50) DEFAULT NULL,
`deployment_type` int(2) DEFAULT '0',
`deployment_size` int(5) DEFAULT NULL,
`timezone` varchar(30) DEFAULT NULL,
`configured_bw` int(11) DEFAULT NULL,
`processor_type` varchar(50) DEFAULT NULL,
`processor_speed` varchar(50) DEFAULT NULL,
`max_flash_memory` int(11) DEFAULT NULL,
`max_ram` int(11) DEFAULT '0',
`ps_redundancy` tinyint(4) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`license_rmon` tinyint(4) DEFAULT '0',
`license_links` int(11) DEFAULT '0',
`license_bandwidth` varchar(30) DEFAULT '0',
`license_ipsec` tinyint(4) NOT NULL DEFAULT '0',
`license_l7qos` tinyint(4) NOT NULL DEFAULT '0',
`license_wafs_fb` tinyint(4) NOT NULL DEFAULT '0',
`license_wafs_fbd` tinyint(4) NOT NULL DEFAULT '0',
`license_tcp_acceleration` tinyint(4) NOT NULL DEFAULT '0',
`license_web_cache` tinyint(4) NOT NULL DEFAULT '0',
`license_qos` tinyint(4) NOT NULL DEFAULT '0',
`license_macc_links` varchar(30) DEFAULT '0',
`rmon_eval` int(11) NOT NULL DEFAULT '0',
`links_eval` int(11) NOT NULL DEFAULT '0',
`bandwidth_eval` int(11) NOT NULL DEFAULT '0',
`global_config_id` int(11) DEFAULT NULL,
`qos_config_id` int(11) DEFAULT NULL,
`global_config_date` datetime DEFAULT '0000-00-00 00:00:00',
`qos_config_date` datetime DEFAULT '0000-00-00 00:00:00',
`download_configuration` tinyint(1) DEFAULT NULL,
`available_seats` tinyint(4) DEFAULT '-1',
`reserved_seats` tinyint(4) DEFAULT '-1',
PRIMARY KEY (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
table status:
'Name','Engine','Version','Row_format','Rows','Avg_row_length','Data_length','Max_data_length','Index_length','Data_free','Auto_increment','Create_time','Update_time','Check_time','Collation','Checksum','Create_options','Comment'
'accelerators','InnoDB','10','Compact','1539','1011','1556480','0','0','149946368','[NULL]','2010-11-25 09:35:07','[NULL]','[NULL]','latin1_swedish_ci','[NULL]','',''
explain select:
'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra'
'1','SIMPLE','accelerators','ALL','[NULL]','[NULL]','[NULL]','[NULL]','1539',''
buffers:
#
'Variable_name','Value'
'bulk_insert_buffer_size','8388608'
'innodb_buffer_pool_size','734003200'
'innodb_log_buffer_size','1048576'
'join_buffer_size','1048576'
'key_buffer_size','2097152'
'myisam_sort_buffer_size','8388608'
'net_buffer_length','16384'
'preload_buffer_size','32768'
'read_buffer_size','1048576'
'read_rnd_buffer_size','262144'
'sort_buffer_size','2097152'
'sql_buffer_result','OFF'
I am using MySql 5.1.53 (or 5.1.22).
thanks,
Edited 3 time(s). Last edit at 12/02/2010 08:32AM by Michal Singer.