MySQL Forums :: Performance :: Very slow query for no apparent reason


Advanced Search

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 3904 Michal Singer 12/02/2010 06:45AM
Re: Very slow query for no apparent reason 1045 Rick James 12/03/2010 09:14AM
Re: Very slow query for no apparent reason 837 Michal Singer 12/05/2010 11:35PM
Re: Very slow query for no apparent reason 845 Rick James 12/06/2010 12:42AM
Re: Very slow query for no apparent reason 834 Michal Singer 12/06/2010 06:42AM
Re: Very slow query for no apparent reason 774 Rick James 12/06/2010 11:17AM
Re: Very slow query for no apparent reason 1135 Michal Singer 12/06/2010 11:56PM
Re: Very slow query for no apparent reason 788 Rick James 12/08/2010 02:09AM
Re: Very slow query for no apparent reason 1038 Michal Singer 12/08/2010 02:21AM
Re: Very slow query for no apparent reason 831 Rick James 12/08/2010 10:06AM
Re: Very slow query for no apparent reason 822 Michal Singer 12/09/2010 03:58AM
Re: Very slow query for no apparent reason 777 Rick James 12/09/2010 11:49AM
Re: Very slow query for no apparent reason 830 Michal Singer 12/12/2010 04:03AM
Re: Very slow query for no apparent reason 850 Rick James 12/12/2010 01:28PM
Re: Very slow query for no apparent reason 839 Michal Singer 12/13/2010 02:44AM
Re: Very slow query for no apparent reason 776 Rick James 12/13/2010 09:38AM
Re: Very slow query for no apparent reason 1243 Michal Singer 12/14/2010 01:36AM
Re: Very slow query for no apparent reason 748 Rick James 12/14/2010 08:56AM
Re: Very slow query for no apparent reason 832 Michal Singer 12/18/2010 11:46PM


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.