MySQL Forums
Forum List  »  Performance

Re: Very slow query for no apparent reason
Posted by: Michal Singer
Date: December 09, 2010 03:58AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
5119
December 02, 2010 06:45AM
Re: Very slow query for no apparent reason
1133
December 09, 2010 03:58AM


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.