We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

MySql Linux vs Windows: bad results for Windows but why?

2,584 Views
Last Modified: 2017-05-16
Hello,

This is the second case with the same scenario:

-the customer uses MySql on Linux for more than 5 years without any problem
-they buy a brand new Pc (about 3 times as fast as the Linux computer), intall Windows Server on it and MySql
-migrate all the data
-the software which uses the MySql database will be about 10 times slower

How can it be that on a much faster computer the MySql is so much slower?
Is it about the differences between the operation systems or the MySql  on Windows needs some special parameters?
(by the way the mysql.ini had the same settings on both).

Thank you very much.
Comment
Watch Question

arnoldEE Topic Advisor, IT Consultant
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
OS overhead and disk access as well as these days the Windows firewall.
Unfortunately you are not providing how the prior setup worked versus the new.

Network, 1GB?
Hard drive type. You shoukd increase the amount of memory you allocated to MySQL such that more data will be in memory than read from the disk.

Specs on the original system versus the current.

Any consideration using the new system with Linux (LAMP)

Db schema ? Innodb?

Author

Commented:
Hello,

-the network is 1 Gb for both
-Linux has HDD, Widows SSD
-InnoDb schema

I don't know the exact server specs : it's at customer side.

If we cannot speed it up we will return to Linux again.
Brian TaoOwner
CERTIFIED EXPERT
Top Expert 2014

Commented:
-migrate all the data
How was the migration done? Are indexes rebuilt?
Database Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
arnoldEE Topic Advisor, IT Consultant
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
To Tomas's point, to be clearer, migrate means copy mysql variable settings, working parameters while leaving datadir etc alone.
The parameter changes can be made dynamically by connecting to mysql and issuing the
show variables #will list the current running settings
show variables where Variable_name like '%tmp%' # will return the settings for the various settings that include tmp in the name
set global <variable_name>=new_value
will dynamically update/change what resources are available to mysql
To make the changes permanent you have to add/adjust those settings in the my.cnf file.

use task manager, resource monitor to see whether the mysql spends inordinate amount of time waiting for disk I/O.....
If so, allocating more memory by adjusting the size of memory related variables.....
Try the tunner, but make sure the mysql service ran a significant amount of time to have the operating statistics on which the tuner will make suggestions, recommendations.

Author

Commented:
"A well known mysqltuner script in Linux is know available for Windows. "

I downloaded it, thank you but it stops at about 30% and although it doesn't freeze it doesn't do anything either. :-(
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi!

Make sure that your system meets the requirements for the windows mysqltuner that are listed
at the webpage.
Did you transfer the my.cnf config file from Linux to the Windows Server ?

Regards,
    Tomas Helgi
arnoldEE Topic Advisor, IT Consultant
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Does your new setup include PHP in IIS? If so, another option is to get phpmyadmin that will give you similar suggestion.
the tunner needs to have activeperl installed if I am not mistaken.  IT does require some processing so patience is important.
i.e. the longer your mysql has been running, the more preceise/detailed the suggestions can be.

Another option while you are working out those, is to enable long query log...
Enable feature dynamically by issuing the following in a  mysql session
set global slow_launch_time=5; #this will set the marker at 5 seconds.
set global slow_query_log_file="Path_to_file usually should be where your mysql server/file is installed D:\program files\mysql\mysql server .....\"; # the location must be writeable by mysql services... on windows it runs as local account, so should not be an issue.
set global slow_query_log='ON'; #enable the log file
set global log_slow_queries='ON";

once enabled, queries meeting the criteria will be added to the file.

Using those queries, and running them with
explain <query>;
mysql will provide the execution plan from which you can see what/where most of the time is consumed.

Your querys should be using indexed columns in the where clauses. if you use like indexes/.... the indexes if exist might ......

could you post the show create table for all your tables?
Do you have triggers, etc...

please post the output from running the
show variables where variable_name like '%tmp%';
to see the resource allocation to temp...

Allowing mysql to consume more system memory should improve the performance while you use the tunner or phpmyadmin or other tuning .....

use set global as those settings are dynamically applied and will rever on service restart unless and until these modifications are added into the my.cnf or my.ini file depending on the system, windows uses my.ini if not mistaken.

Author

Commented:
Did you transfer the my.cnf config file from Linux to the Windows Server ?
Yes I did. By the way yesterday I made an experiment: I installed mysql on two "twin" computers: one has Linux, the other Windows.
Same database, same MySql, same Pc parameters.
A simple Sql like

select * from information_schema.triggers where
information_schema.triggers.trigger_schema = DATABASE()

was more than twice as fast on Linux. (there were only 3 triggers in the database :-).




another option is to get phpmyadmin that will give you similar suggestion.

could you post the show create table for all your tables?

Open in new window


Sorry it is at customer's side and I need to go to abroad for two weeks today... :-(
arnoldEE Topic Advisor, IT Consultant
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
OS overhead is significantly higher on Windows vs Linux which is usually set at non-graphical..
One option on Windows is to configure the system to ptioritize CPU/memory for applications/programs on a server OS. Desktop defaults to programs.

The other as mentioned is to allocate more memory to tmp related variables.

Twice as fast means little without a context. A response on Linux of 1second and 2 seconds on Windows .......

Presumably your query was run within MySQL command window on Windows versus via a GUI workbench tool.
Time response deals with how fast data can be pulled, the rendering/display is .....
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.