MySQL - Select queries 10x slower on Azure VM vs on-prem VM

[Sridharan Srinivasan]

We have been working on a project to migrate a MySQL database from an on-prem Linux server to a Windows VM on Azure (IaaS). (There's a specific reason because of which we have gone with the IaaS option instead of the Azure MySQL PaaS offering).

After the migration, we see that the queries on the MySQL database are significantly slower (about 10x) on the new server. The VM is configured with 64 CPUs and 256 GB RAM (the on-prem VM had 48 CPUs and 256 GB RAM).

All the tables in the database are using the InnoDB engine. We have read up quite a lot about query slowness with InnoDB tables, and most of it seems to be pointing to the innodb_buffer_pool_size - which I have already configured to 185 GB (roughly 70% of the total RAM). We have also tried making a number of other changes in the my.ini configuration like key_buffer_size = 20MB, innodb_io_capacity = 2000, query_cache_size = 0, query_cache_type = 0, increasing thread_cache_size, innodb_read_io_threads, innodb_write_io_threads, etc. but nothing seems to be helping with the query performance.

We have compared the indexes on both the servers, and they're exactly the same. And at a high level, it doesn't look like the indexes are broken on the Azure VM. Also, we are trying to measure the performance by running MySQL workbench inside the Azure VM, so network bandwidth should not be an issue.

Can anyone suggest any other options that we could try to improve the performance?

A couple of further points.

What we notice is that although some of the queries take 30+ minutes to run, (they seem to be running in the on-prem server in just 5 minutes), the CPU usage on the VM remains very low (less than 10%). Is there some setting like innodb_buffer_pool_size to allocate some amount of CPU to the MySQL Server?

Like I mentioned before, the on-prem VM is Linux-based and the Azure VM is running on Windows - could that be a problem? I can't find any definitive proof that MySQL on Windows will cause such severe performance degradation.