Re: Very slow query for no apparent reason
Hi,
Before answering the questions, please take under consideration this input:
1. The more open tables i have at a current moment - the more it takes the query longer to run. If stats_sit is loaded, the accelerators query takes a long time otherwise it takes less than a seconds to execute. Is there a configuration i can check in my.ini to improve this specific indication (for example table_cache)?
Can it be related because there is a foreign key between them, might that cause the impact one has on the other?
2. I have a problematic query which has no index which takes a long time which might cause load on the stats_sit table:
Select * from stats_sit where matrixID = 882014 and agg_time + 60 <= end_time limit 0, 5000. I am not sure which index to use here??? is index on matrixId enough or should i add agg_time???
Now, for the other questions u asked:
***************************************
the reason i use a lot of connections ("clients") is because i get otherwise:
"too many connections" error, since i reach the limit. I use 1000 connections.
Should i try to decrease this?
localhost:2676','fresh','Query','338','Sending data','select acc_id, timezone from accelerators'
Did you mean to be fetching the entire table?[Michal] yes. Though, if this causes problems, i can add support for caching for this. Though, this query is supposed to run once an hour.
[ code ]
Table: alerts
Create Table: CREATE TABLE `alerts` (
`alert_id` int(11) NOT NULL DEFAULT '0',
`alert_prop_id` int(11) NOT NULL DEFAULT '0',
`alert_severity` int(11) NOT NULL DEFAULT '0',
`alert_description` varchar(200) DEFAULT NULL,
`alert_first_occurrence_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`alert_last_occurrence_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`alert_last_occurrence_acc_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`time_occurs_msec` int(11) DEFAULT '0',
`alert_first_occurrence_date_acc_tz` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`alert_last_occurrence_date_acc_tz` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`acc_id` int(20) DEFAULT '0',
`link_id` int(11) DEFAULT '-1',
`coll_branch_id` int(11) DEFAULT '-1',
`alert_status` int(11) NOT NULL DEFAULT '0',
`log_treatment` text,
`count` int(11) DEFAULT NULL,
PRIMARY KEY (`alert_id`),
KEY `link_id` (`link_id`),
KEY `acc_id` (`acc_id`,`alert_prop_id`,`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
Table: stats_sit
Create Table: CREATE TABLE `stats_sit` (
`accID` int(20) NOT NULL DEFAULT '0',
`linkID` int(20) NOT NULL DEFAULT '0',
`appID` int(20) NOT NULL DEFAULT '0',
`start_time` int(20) NOT NULL DEFAULT '0',
`end_time` int(20) NOT NULL DEFAULT '0',
`matrixID` int(11) NOT NULL DEFAULT '0',
`slot` int(11) DEFAULT '0',
`len` int(11) DEFAULT '0',
`id` int(11) NOT NULL DEFAULT '0',
`agg_time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `accID` (`accID`,`linkID`,`appID`),
CONSTRAINT `0_2633` FOREIGN KEY (`accID`) REFERENCES `accelerators` (`acc_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Table: links_backups
Create Table: CREATE TABLE `links_backups` (
`link_id` int(11) NOT NULL DEFAULT '0',
`active_link_id` int(11) NOT NULL DEFAULT '0',
`acc_source_id` int(11) NOT NULL DEFAULT '0',
`acc_destination_id` int(11) NOT NULL DEFAULT '0',
`link_number` int(11) DEFAULT '0',
`link_type` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`use_default_parameters` int(11) DEFAULT NULL,
`status` tinyint(4) DEFAULT '0',
`acceleration` tinyint(4) DEFAULT '0',
`encapsulation` int(1) DEFAULT '0',
`tcp_acceleration` tinyint(4) DEFAULT '0',
`checksum` tinyint(4) DEFAULT '0',
`outbound_bandwidth_value_type` varchar(16) DEFAULT 'absolute',
`outbound_bandwidth_size` int(20) DEFAULT '0',
`inbound_bandwidth_value_type` varchar(16) DEFAULT 'absolute',
`inbound_bandwidth_size` int(20) DEFAULT '0',
`inbound_bandwidth` tinyint(4) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`mtu` int(11) DEFAULT '0',
`header_compression` tinyint(4) DEFAULT '0',
`src` tinyint(4) DEFAULT '0',
`ttl` tinyint(4) DEFAULT '0',
`tos` int(11) DEFAULT '0',
`tos_value` int(11) DEFAULT '0',
`code_value` int(11) DEFAULT '0',
`code_value_ip_precedence` int(11) DEFAULT '0',
`tos_select` int(11) DEFAULT '0',
`large_cash_size` tinyint(4) DEFAULT '0',
`fragmentation_status` tinyint(4) DEFAULT '0',
`fragmentation_size` int(11) DEFAULT '0',
`aggregation_status` tinyint(4) DEFAULT '0',
`aggregation_size` int(11) DEFAULT '0',
`wanId` int(11) DEFAULT '0',
`max_qlen_low` int(11) DEFAULT '0',
`max_qlen_meduim` int(11) DEFAULT '0',
`max_qlen_high` int(11) DEFAULT '0',
`max_qlen_real_time` int(11) DEFAULT '0',
`max_qlen_passthrough` int(11) DEFAULT '0',
`max_qlen_discard` int(11) DEFAULT '0',
`obsolete_low` int(11) DEFAULT '0',
`obsolete_meduim` int(11) DEFAULT '0',
`obsolete_high` int(11) DEFAULT '0',
`obsolete_real_time` int(11) DEFAULT '0',
`obsolete_passthrough` int(11) DEFAULT '0',
`obsolete_discard` int(11) DEFAULT '0',
`weights_low` int(11) DEFAULT '0',
`weights_meduim` int(11) DEFAULT '0',
`weights_high` int(11) DEFAULT '0',
`weights_real_time` int(11) DEFAULT '0',
`weights_passthrough` int(11) DEFAULT '0',
`weights_discard` int(11) DEFAULT '0',
`creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`deletion_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`monitored_applications` varchar(255) DEFAULT NULL,
`poa_citrix` varchar(100) DEFAULT NULL,
`poa_default` varchar(100) DEFAULT NULL,
`poa_custom1` varchar(100) DEFAULT NULL,
`poa_custom2` varchar(100) DEFAULT NULL,
`ipsec_enable` tinyint(4) DEFAULT NULL,
`ipsec_policy_name` varchar(100) DEFAULT NULL,
`is_local_nat_ip_enabled` tinyint(4) DEFAULT '0',
`ipsec_local_nat_ip` varchar(100) DEFAULT NULL,
`is_remote_nat_ip_enabled` tinyint(4) DEFAULT '0',
`ipsec_remote_nat_ip` varchar(100) DEFAULT NULL,
`udpEncapSrcPort` int(11) DEFAULT '0',
`udpEncapDstPort` int(11) DEFAULT '0',
`system_encapsulation` int(1) DEFAULT '0',
`udpSysEncapSrcPort` int(11) DEFAULT '0',
`udpSysEncapDstPort` int(11) DEFAULT '0',
`portsPreservation` tinyint(4) DEFAULT '0',
`metric` int(11) DEFAULT '11',
PRIMARY KEY (`link_id`),
KEY `acc_destination_id` (`acc_destination_id`),
KEY `acc_source_id` (`acc_source_id`,`acc_destination_id`,`status`),
KEY `0_2624` (`active_link_id`),
CONSTRAINT `0_2623` FOREIGN KEY (`acc_source_id`) REFERENCES `accelerators` (`acc_id`) ON DELETE CASCADE,
CONSTRAINT `0_2624` FOREIGN KEY (`active_link_id`) REFERENCES `links` (`link_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
********************** 1. row ***************************
Table: stats_applications
Create Table: CREATE TABLE `stats_applications` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(50) NOT NULL DEFAULT '',
`active` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[ / code ]
Thanks, Michal
Edited 2 time(s). Last edit at 12/09/2010 06:42AM by Michal Singer.