Mysql query execution time is varying in different environments

Hi Experts,

One query I am executing in two different environments.
Dev1 and Dev2.
Dev1 Environment query is taking 0.05 seconds
Dev2 Environment query is taking 2 seconds

Table creation syntax is same in both the environments.
Can some one suggest me how to identify the issue.

Thanks,
LVL 2
srikoteshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
How are you measuring it?  And what do you mean by 'environment'?  If it is two different computers then it could simply be that one is slower than the other.  Or has a slower network connection.  Or is simply busier.
srikoteshAuthor Commented:
different environments means one is local environment and another one is production environment.
these two databases running in two different servers.
How to identify network is slow or busy?
so there is no problem with query correct?
because same query I am executing
local environment ----->0.02 secs it is taking
other environment ----->2seconds
Dave BaldwinFixer of ProblemsCommented:
First, if you are connecting thru 'localhost', that is a special connection that does not actually go thru all of the network software and as a result, it is usually quicker.  A remote connection to the production server goes thru the network software on both machines including the firewalls which takes more time than the 'localhost' connection.

There is no obvious problem with the query because of the difference in the environments.  If you go to the production machine, you would probably see a similar time thru 'localhost' on that machine.  It might be somewhat slower because it might be busier.  But you would have to run the query on the production server itself to compare with the 'localhost' results.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

srikoteshAuthor Commented:
I am not connecting with local host.
I am connecting thru server ip address.
Steve BinkCommented:
Run "EXPLAIN" for the query on both servers.  Is there any difference in their execution plan?
srikoteshAuthor Commented:
explain select testtotask0_.id as col_0_0_, task1_.task_status as col_1_0_, taskaction2_.success as col_2_0_, taskaction2_.action_status as col_3_0_, taskaction2_.scheduled_time as col_4_0_  from test_to_task testtotask0_  left outer join task task1_ on testtotask0_.task_id =task1_.task_id  inner join task_action taskaction2_ on testtotask0_.task_id=taskaction2_.task_id where testtotask0_.test_id=6 order by testtotask0_.id desc limit 1;
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys   | key     | key_len | ref                        | rows   | Extra                           |
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
|  1 | SIMPLE      | taskaction2_ | ALL    | NULL            | NULL    | NULL    | NULL                       | 350714 | Using temporary; Using filesort |
|  1 | SIMPLE      | testtotask0_ | ref    | test_id,task_id | task_id | 8       | stages.taskaction2_.task_id |      1 | Using where                     |
|  1 | SIMPLE      | task1_       | eq_ref | PRIMARY         | PRIMARY | 8       | stages.testtotask0_.task_id |      1 |                                 |
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
3 rows in set (0.00 sec)



Lab:

explain select testtotask0_.id as col_0_0_, task1_.task_status as col_1_0_, taskaction2_.success as col_2_0_, taskaction2_.action_status as col_3_0_, taskaction2_.scheduled_time as col_4_0_ from test_to_task testtotask0_ left outer join task task1_ on testtotask0_.task_id =task1_.task_id  inner join task_action taskaction2_ on testtotask0_.task_id=taskaction2_.task_id where testtotask0_.test_id=6 order by testtotask0_.id desc limit 1;
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
| id | select_type | table        | type   | possible_keys         | key              | key_len | ref                             | rows | Extra                       |
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
|  1 | SIMPLE      | testtotask0_ | ref    | test_id,task_id       | test_id          | 4       | const                           | 1725 | Using where; Using filesort |
|  1 | SIMPLE      | task1_       | eq_ref | PRIMARY,task_id_index | PRIMARY          | 8       | stagees.testtotask0_.task_id |    1 |                             |
|  1 | SIMPLE      | taskaction2_ | ref    | task_id_20101029      | task_id_20101029 | 8       | stages.testtotask0_.task_id |    1 |                             |
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
3 rows in set (0.01 sec)
Julian HansenCommented:
3 rows in set (0.00 sec)
3 rows in set (0.01 sec) 

Open in new window

Looks like network - server response is the same.
srikoteshAuthor Commented:
I can see the difference in no of rows
one environment it is 1750,other one have 350000

in one environment I have added index on primary key manually by using
alter command add unique index.recently I have added this change.
Steve BinkCommented:
Can you post "SHOW CREATE TABLE" for each of these tables, in each environment?

The EXPLAIN results are showing a few differences:

1) taskaction2_ is not using an index in the first set.  That results in 350k rows being examined vs the 1-row const comparison in the second set.

2) task1_ is using a different ref...?

3) testtotask0_ is selecting a different key between the two sets.  

What differences are there between the data sets and structures?
srikoteshAuthor Commented:
I will post it soon
srikoteshAuthor Commented:
Please find show create table details:

Lab:
 CREATE TABLE `test_to_task` (
  `id` int(10) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `test_id` int(9) NOT NULL,
  `incident_id` bigint(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_index` (`id`),
  KEY `test_id` (`test_id`),
  KEY `task_id` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=426972 DEFAULT CHARSET=latin1 |


Az:
CREATE TABLE `test_to_task` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `test_id` int(9) NOT NULL,
  `incident_id` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_id` (`test_id`),
  KEY `task_id` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=462791 DEFAULT CHARSET=latin1 |

Lab:
CREATE TABLE `task` (
  `task_id` bigint(19) NOT NULL auto_increment,
  `message` text NOT NULL,
  `task_status` varchar(30) NOT NULL,
  `src_to_customer_id` bigint(20) NOT NULL,
  `pushed_on` datetime NOT NULL,
  `script_output` text NOT NULL,
  PRIMARY KEY  (`task_id`),
  UNIQUE KEY `task_id_index` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=436007 DEFAULT CHARSET=latin1 |

Az:
CREATE TABLE `task` (
  `task_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `task_status` varchar(30) NOT NULL,
  `src_to_customer_id` bigint(20) NOT NULL,
  `pushed_on` datetime NOT NULL,
  `script_output` text NOT NULL,
  PRIMARY KEY (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=472848 DEFAULT CHARSET=latin1 |




Lab:
CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime default NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) default NULL,
  `vault_ref_id` varchar(100) default NULL,
  `script_output_arguments` text,
  PRIMARY KEY  (`task_action_id`),
  UNIQUE KEY `taskaction_id_index` (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=439153 DEFAULT CHARSET=latin1 |


Az:

CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime DEFAULT NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) DEFAULT NULL,
  `vault_ref_id` varchar(100) DEFAULT NULL,
  `script_output_arguments` text,
  PRIMARY KEY (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=476495 DEFAULT CHARSET=latin1 |
Steve BinkCommented:
The main problem I see is that one of your environments is not using the task_id_20101029 index.  Why that index is being ignored is probably the key to the performance difference.

Check https://dev.mysql.com/doc/refman/5.5/en/explain-extended.html for your next steps, and post the results here.  I'm focusing on the environment not using the index, but please run this through both.
srikoteshAuthor Commented:
Hi

In Lab environment recently i have added index for primary key .
same thing i am not applied to another environment.
i came to know that no need to add index for primary key. so thats why i am not applied index for primary key on another environment.

apart from this any place i have ignored task_id_20101029 index.
Please let me know i will add it or i will utilize it effectively.
srikoteshAuthor Commented:
please verify explain extended query.
i have attached text file.
extend.txt
Steve BinkCommented:
The "az" environment is still showing no possible keys for taskaction2_.  Resolve that difference, then check the performance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
K,I will check and let you know.
Thanks
srikoteshAuthor Commented:
Hi SteveBink,

Yes u r correct index is ignoring in task_action table.
how do i reassign the index to the same column.
is it possible reindex it?
I am not sure how to add index again on the same column.
i have tried to add index

alter table task_action add index task_id_index(task_id);

but still it is ignoring index,i can ableto see possible key showing as null and cordinality value as null.

Can u suggest me what to do.
Steve BinkCommented:
On further examination, I think the root problem is the index on another table - testtotask0_.  In the 'az' environment, the index task_id is being used, while lab is selecting the index test_id.  Since your where clause is keyed to test_id, that second index makes much more sense.  

What is the cardinality of the test_id column in both tables?  See here for information on assessing cardinality.
srikoteshAuthor Commented:
test_id having cardinality value in both the tables.

Mean while i have made change in  query.
splitten into two parts.

only task_action table only causing the issue.
select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;

when i execute only this query it is taking more time.
Earlies as i said cardinality value is zero for task_id ,
Steve BinkCommented:
Run that new SELECT query through EXPLAIN in both environments and post the results.  Also, please post the SHOW CREATE TABLE for both tables.
srikoteshAuthor Commented:
AZ:
====

mysql> select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----------+---------------+---------------------+
| col_0_0_ | col_1_0_      | col_2_0_            |
+----------+---------------+---------------------+
|        0 | TASK-ACT-COMP | 2010-07-21 09:00:00 |
+----------+---------------+---------------------+
1 row in set (0.79 sec)

mysql> explain select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | taskaction0_ | ALL  | NULL          | NULL | NULL    | NULL | 350714 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime DEFAULT NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) DEFAULT NULL,
  `vault_ref_id` varchar(100) DEFAULT NULL,
  `script_output_arguments` text,
  PRIMARY KEY (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=476495 DEFAULT CHARSET=latin1 |


LAB:
====
mysql> select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----------+---------------+---------------------+
| col_0_0_ | col_1_0_      | col_2_0_            |
+----------+---------------+---------------------+
|        0 | TASK-ACT-COMP | 2010-07-21 09:00:00 |
+----------+---------------+---------------------+
1 row in set (0.00 sec)

mysql> explain select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | taskaction0_ | ref  | task_id_20101029 | task_id_20101029 | 8       | const |    1 |       |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

 CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime default NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) default NULL,
  `vault_ref_id` varchar(100) default NULL,
  `script_output_arguments` text,
  PRIMARY KEY  (`task_action_id`),
  UNIQUE KEY `taskaction_id_index` (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=439169 DEFAULT CHARSET=latin1 |
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.