I've been noticing a performance issue in one of my servers running Apache/PHP/MySQL.
So I decided to investigate and test it against other servers.
What did I do?
In three different servers, I created a database (db_sandbox), with a single innodb table (table_sandbox), with 4 columns (id (int), title (varchar 255), description (text), date (timestamp)). NOTE: id is the primary key with autoincrement.
I created a simple PHP routine that includes 15.000 records into this table, in groups of 5.000, using 3 different methods:
- Method 1: 5.000 insert commands, each being processed individually through 5.000 single queries
- Method 2: 5.000 insert commands, separated by semi-colon, in a single call to the DB.
- Method 3: 1 insert command of 5.000 records.
Before anything else, it is obvious that Methods 2 and 3 are the best regarding performance. But this is not the point. In the real word, methods 2 and 3 are just ideal scenarios but rarely occur.
In any case, when I compare the results of each of these, I was surprised. Considering SERVER-1 as my problematic server this is what I got:
- Method 1: SERVER-2 is 165x faster and SERVER-3 is 40x faster;
- Method 2: SERVER-2 is 6x faster and SERVER-3 is 7x faster;
- Method 3: SERVER-2 is 5% slower and SERVER-3 is 1.5x faster;
My conclusion is that there is something in the settings of SERVER-1 that is impacting the running of multiple individual queries.
Does anyone knows what settings they might be?
SERVER-1: This is a virtual machine
- Intel Xeon E5-2630 v3 2.4GHz
- 16GB RAM
- SSD 120GB
- Windows Server 2012 R2
- MySQL 5.6
- VMWare
SERVER-2
- Intel Xeon X3360 2.83GHz
- 4GB RAM
- SSD 250GB
- Windows Server 2003 (ready for retirement)
- MySQL 5.0
SERVER-3
- Intel I7-3770 3.4GHz
- 16GB RAM
- SSD 120GB
- Windows 8.1 Pro
- MySQL 5.6
NOTE: I compared my.ini from SERVER-1 and SERVER-3 and they are virtually identical. SERVER-2, running Mysql 5.0 is very different.
Best Answer
If I read and understand well your question you are comparing those results you obtain in a Windows VM running on ESXi with server2 and 3 that run their OSes on bare metal.
As long as HW stays similar, there is no chance you will see better performance on the VM.
Bare metal db server will always be faster. More write to disks you enqueue, more true this last sentence becomes.
What's slowing down those inserts on Server1 is probably the virtualization layer and there is a huge literature discussing what shouldn't be virtualized where performances are a point.
Also, this is particularly true on ESXi without flash cache hw raid controller.