Same MySQL query is so slower on ubuntu than Windows

Question

I have a big select in MySQL 5.7.33. In ubuntu (real server) 20.04 it takes 600s for the answer but 1.2s on Windows about the same on the VM Ubuntu 20.04.

The MySQL configuration for all the os are the same.

Do you have any idea to resolve this problem?

Thanks.

Edited Part:

Explain Select ...

Slow:

Fast:

Answer 1

This is certainly a common issue when doing queries on a VM with large datasets that are not already in the buffer. One of the problems that can get in the way is the file system translation that needs to take place. The Ubuntu VM is likely using an Ext4 file system, which sits inside a container on an NTFS partition. This adds a bit of overhead when working with strenuous disk operations.

If the VM needs to be as performant as possible, one suggestion would be to create an Ext4 partition on the computer's storage device (SSD or HDD), and mount it within the VM. The database files would be moved to that bare-metal file system and the main Ubuntu installation would remain in the VM's file system.

Doing this will reduce the overhead when working with large read and write operations by a noticeable percentage, though there will always be a little penalty when working with VMs rather than a bare metal installation.

Answer 2

For 99% I bet it's a CPU TOPOLOGY (both HW and VM may have the same) vs OS LICENSING.

While LINUX KERNEL utilizes all CPU's the WINDOWS limit those number to valid license type. e.g. for W10 PRO AND STANDARD EDITION it is a 2.

So in short - your COMPUTING POWER is limited on WINDOWS OS while ON LINUX KERNEL IT MIGHT BE ONLY A GOVERNOR to tune the speed of OP's.

Other thing to consider is the utilization of raw or shared mem under those OSes or setting for hyperthreading memory interleaving etc. Generally all other OS implementation that tries to utilize all those "advanced hw speedup techs" to run OPs.

I have encountered such a situation myself - running a 64 core HP server on a VM with a badly copied topology under unlicensed Windows 10 queries results took very long time (about 5 minutes) when normally under full licensed (64cores) Windows server it took 10 seconds. it all depends on the operating software (use of MT, HT, governors, thread locking etc).

Answer 3

The tables should be reindexed to solve the problem

ANALYZE TABLE tablename;