Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server












1















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:



enter image description here



And this is the my.ini file contents on that location:



enter image description here



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









share|improve this question
















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 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
















1















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:



enter image description here



And this is the my.ini file contents on that location:



enter image description here



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









share|improve this question
















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 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














1












1








1








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:



enter image description here



And this is the my.ini file contents on that location:



enter image description here



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









share|improve this question
















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:



enter image description here



And this is the my.ini file contents on that location:



enter image description here



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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Apr 22 '16 at 10:50









Paul White

53.1k14283457




53.1k14283457










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 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



















  • 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

















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










2 Answers
2






active

oldest

votes


















0














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.






share|improve this answer
























  • I have updated my question.

    – Zakir_SZH
    Apr 21 '16 at 15:29





















0














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.






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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.






    share|improve this answer
























    • I have updated my question.

      – Zakir_SZH
      Apr 21 '16 at 15:29


















    0














    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.






    share|improve this answer
























    • I have updated my question.

      – Zakir_SZH
      Apr 21 '16 at 15:29
















    0












    0








    0







    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.






    share|improve this answer













    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.







    share|improve this answer












    share|improve this answer



    share|improve this answer










    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





















    • 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















    0














    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.






    share|improve this answer




























      0














      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.






      share|improve this answer


























        0












        0








        0







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Apr 25 '16 at 15:40









        James PulleyJames Pulley

        32524




        32524






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh