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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.