MySQL Forums
Forum List  »  Performance

Very slow query for no apparent reason
Posted by: Michal Singer
Date: December 02, 2010 06:45AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow query for no apparent reason
4023
December 02, 2010 06:45AM


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.