Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server
One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.
On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.
On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:
And this is the my.ini file contents on that location:
Is there anything in my.ini that I can change to boost the speed?
Here are the outputs from explain as requested:
Dedicated server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2648 Using where
Local Server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2837 Using where
mysql performance
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.
On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.
On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:
And this is the my.ini file contents on that location:
Is there anything in my.ini that I can change to boost the speed?
Here are the outputs from explain as requested:
Dedicated server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2648 Using where
Local Server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2837 Using where
mysql performance
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
We really need to see the query. And theSHOW CREATE TABLE. It may be something as simple as a missing composite index.
– Rick James
Apr 24 '16 at 21:47
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50
add a comment |
One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.
On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.
On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:
And this is the my.ini file contents on that location:
Is there anything in my.ini that I can change to boost the speed?
Here are the outputs from explain as requested:
Dedicated server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2648 Using where
Local Server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2837 Using where
mysql performance
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) server. Both databases, tables, engine, columns, indexes all are same/identical on both servers.
On my development server (local) the query takes only milliseconds (less than a second) where on my dedicated server it takes 4-5 seconds.
On my dedicated Server I noticed in Task Manger that msqld is running by a command line which defined my.ini file:
And this is the my.ini file contents on that location:
Is there anything in my.ini that I can change to boost the speed?
Here are the outputs from explain as requested:
Dedicated server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2648 Using where
Local Server
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PSCThana ALL ThanaID,UpazillaID NULL NULL NULL 509 Using where
1 SIMPLE PSC15ALL ref STD_ROLL,THANA THANA 18 siamit.PSCThana.ThanaID 2837 Using where
mysql performance
mysql performance
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
edited Apr 22 '16 at 10:50
Paul White♦
53.1k14283457
53.1k14283457
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
asked Apr 21 '16 at 14:27
Zakir_SZHZakir_SZH
62
62
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
We really need to see the query. And theSHOW CREATE TABLE. It may be something as simple as a missing composite index.
– Rick James
Apr 24 '16 at 21:47
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50
add a comment |
We really need to see the query. And theSHOW CREATE TABLE. It may be something as simple as a missing composite index.
– Rick James
Apr 24 '16 at 21:47
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50
We really need to see the query. And the
SHOW CREATE TABLE. It may be something as simple as a missing composite index.– Rick James
Apr 24 '16 at 21:47
We really need to see the query. And the
SHOW CREATE TABLE. It may be something as simple as a missing composite index.– Rick James
Apr 24 '16 at 21:47
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50
add a comment |
2 Answers
2
active
oldest
votes
There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
add a comment |
On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of the amount of bytes having a zero time travel path across your local bus versus a complex network path. There are other items which can come into play, such as running on physical OS/Hardware on your dev instance verus running in a VM with hypervisor arbitrated access to CPU/DISK/RAM/Network which can cause deviations as well.
Have you considered that perhaps one of your index creation scripts has failed to run which could then be resulting in a table scan for data? Or perhaps you tested on a small data set which would up being pinned in cache but in prod you don't have that luxury and an index is called for.
Start looking at the changes between the two environments in terms of configuration, communication and load. One or a combination of these is likely the key to a difference.
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f136089%2fmysql-query-take-longer-to-prcoess-on-my-dedicated-server-than-my-local-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
add a comment |
There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
add a comment |
There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
answered Apr 21 '16 at 14:38
James RhoatJames Rhoat
1,1692724
1,1692724
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
add a comment |
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
I have updated my question.
– Zakir_SZH
Apr 21 '16 at 15:29
add a comment |
On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of the amount of bytes having a zero time travel path across your local bus versus a complex network path. There are other items which can come into play, such as running on physical OS/Hardware on your dev instance verus running in a VM with hypervisor arbitrated access to CPU/DISK/RAM/Network which can cause deviations as well.
Have you considered that perhaps one of your index creation scripts has failed to run which could then be resulting in a table scan for data? Or perhaps you tested on a small data set which would up being pinned in cache but in prod you don't have that luxury and an index is called for.
Start looking at the changes between the two environments in terms of configuration, communication and load. One or a combination of these is likely the key to a difference.
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
add a comment |
On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of the amount of bytes having a zero time travel path across your local bus versus a complex network path. There are other items which can come into play, such as running on physical OS/Hardware on your dev instance verus running in a VM with hypervisor arbitrated access to CPU/DISK/RAM/Network which can cause deviations as well.
Have you considered that perhaps one of your index creation scripts has failed to run which could then be resulting in a table scan for data? Or perhaps you tested on a small data set which would up being pinned in cache but in prod you don't have that luxury and an index is called for.
Start looking at the changes between the two environments in terms of configuration, communication and load. One or a combination of these is likely the key to a difference.
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
add a comment |
On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of the amount of bytes having a zero time travel path across your local bus versus a complex network path. There are other items which can come into play, such as running on physical OS/Hardware on your dev instance verus running in a VM with hypervisor arbitrated access to CPU/DISK/RAM/Network which can cause deviations as well.
Have you considered that perhaps one of your index creation scripts has failed to run which could then be resulting in a table scan for data? Or perhaps you tested on a small data set which would up being pinned in cache but in prod you don't have that luxury and an index is called for.
Start looking at the changes between the two environments in terms of configuration, communication and load. One or a combination of these is likely the key to a difference.
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
On your production box you likely have to compete with other queries that you do not have to compete for resources with on your development instance. Also, if your development instance is running on your localhost, which also generates the query, versus the introduction of a network link (Busy, slow, error prone, WAN distant) then you will have an issue of the amount of bytes having a zero time travel path across your local bus versus a complex network path. There are other items which can come into play, such as running on physical OS/Hardware on your dev instance verus running in a VM with hypervisor arbitrated access to CPU/DISK/RAM/Network which can cause deviations as well.
Have you considered that perhaps one of your index creation scripts has failed to run which could then be resulting in a table scan for data? Or perhaps you tested on a small data set which would up being pinned in cache but in prod you don't have that luxury and an index is called for.
Start looking at the changes between the two environments in terms of configuration, communication and load. One or a combination of these is likely the key to a difference.
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
answered Apr 25 '16 at 15:40
James PulleyJames Pulley
32524
32524
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f136089%2fmysql-query-take-longer-to-prcoess-on-my-dedicated-server-than-my-local-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
We really need to see the query. And the
SHOW CREATE TABLE. It may be something as simple as a missing composite index.– Rick James
Apr 24 '16 at 21:47
For example, MyISAM tables involve table locks, which are probably more common on the production server because of more writes. How much RAM do you have?
– Rick James
Apr 24 '16 at 21:50