Mysql queries slowing down

RGuillermo
RGuillermo used Ask the Experts™
on
Hello Experts,
One of our remaining legacy apps uses mysql server
and mysql-connector-odbc-5.3.10-win32

It works just fine and we do not plan to develop a new app until other more
important projects are finished.

The fact is our customer replaced his server for some reason and
it works with vmware.

And now our app has lost a lot of speed since last friday.

I understand the new installation is a mirror of the older server.
But then our app has lost speed around 30% only when making queries.

Question1: mysql-connector-odbc-5.3.10-win32 is this a good ODBC or is it too old?
Question2: What configuration should I request in the VM so the company in charge of hardware can configure properly.
What else should I check given the fact that this app is still very useful and we do not plan to replace i until 2020.

Regards,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AlexSenior Infrastructure Analyst
Commented:
Of course it did, the second you virtualize something off you don't get the full use of the processor, you get time with it. So essentially if you had an 8 physical cores and they have now virtualized it off to 8 virtual cores it's not going to be the same and you will lose performance.

It also depends on the version of ESXi that's been installed

Have they installed the spectre fixes?

It's not really your problem, their best option is to increase the cores. There isn't anything you'll be able to do with this.
Ryan ChongSoftware Team Lead
Commented:
Not really to recommend the use of ODBC drivers and configuration in VM, but I recently facing one of the slowness issues in my own MySQL DB that developed years ago, and found that by implementing proper indexing , it really increased the DB performance a lot. You may review that in your own case to see if there's still room for improvement. Of course, your VM most be configured to have enough resources to handle the DB requests.
Commented:
To answer your first question, the connector should be fine.

Now, I agree with both comments about. You're definitely going to notice an overall speed decrease in a virtualized environment vs. dedicated hardware because that's just the nature of virtual machines. You're gaining a lot of portability at the cost of some resources.

1. You need to make sure that the VM guest has the same or better resource allocation than the original hardware specs.

2. You need to make sure that the VM host is hosting the guest correctly. For example, you WOULD want to use a bare-metal OS like ESXi. You would NOT want to use VMWare Workstation hosted on another OS like Windows. The latter would mean that the host is incurring tons of overhead that is ultimately passed to the guest (the guest doesn't know about it - to the guest VM, it just seems like 100% CPU is less powerful than it used to be) and is storing the guest files on a hard drive that could likely see fragmentation (which would have a huge impact on I/O).

3. If the MySQL server is now being separated from its client (e.g. perhaps before the VM switchover, the web server and MySQL were both on the same machine, and now they are on separate VMs), then the slowdown could be due to transfer. A lot of rookie developers will use SELECT * for everything and because there's virtually no transfer overhead when the web server and DB are on the same machine, they don't realize the impact that SELECT * queries can have. Then, when the web server and DB server separate, suddenly all that data has to be transmitted over a much slower connection.

4. The slowdown could be in the connection. You should make sure MySQL isn't doing name lookups on incoming connections or make sure it has access to speedy DNS services (the former option is better, though). If there's any trouble doing lookups, it can introduce a small delay into every new connection (every new page hit). So if the FIRST query seems slow and then the other queries seem fast, then that's what you should look at.

5. Indexing is very important, as Ryan pointed out. I talk about indexing a bit in my "speed up MySQL" article:
https://www.experts-exchange.com/articles/1250/3-Ways-to-Speed-Up-MySQL.html

6. Make sure you turn on your slow query log and analyze any results that show up.
RGuillermoProject Manager

Author

Commented:
Very helpful advice from all experts!!
Thank you so much !!
We were able to discuss arguments properly with our customer...
We improved resources of the VM recovering some speed...
And finally we agreed to have our Mysql on the real server...
And everybody happy specially the users...
Without your help I couldn't have discussed, and I learnt a lot..
Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial