2

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.

Dentra Andres
  • 31
  • 1
  • 6
  • did you tried to remove Win2012 UI ? (there is an option to switch from console to UI, and vice versa) – Froggiz Nov 10 '15 at 10:09
  • Is the hardware and system utilization comparable across three servers? – Eddie Dunn Nov 19 '15 at 19:29
  • Eddie, I will add their descriptions in the thread and let you be the judge... They are different but is it enough to justify? And if so, why the difference between methods. Anyway, please refer to the bottom of the thread. Thanks. – Dentra Andres Nov 19 '15 at 22:08
  • Well in any event server-1 s/b performing better for sure. What is the disk queue length like on server-1? – Eddie Dunn Nov 19 '15 at 22:55
  • Server 1 us a virtual machine, and to clarify - the guest is running Windows Server 2012R2 and it is a guest on a VMWare sever - ESXI? There could be some issues with the way that the VM is configured - how many CPUs and GBs of RAM are dedicated to that specific VM? Is that SSD provisioned only for that VM or is it shared with other VMs that may be utilizing high I/O? – TheGrandPackard Nov 19 '15 at 23:51
  • Eddie Dunn, the disk queue is always very low during the test run, usually below 2, never reaching 5 – Dentra Andres Nov 20 '15 at 20:22
  • @TheGrandPackard, yes, it is 2012R2. And yes, it is a guest on a VMWare server - ESXI. This server has access to 4 cores and 16GB RAM. The SSD is share with other 2 VM's but they are barely used at the moment so I can easy say it is dedicated to my server. Nevertheless, I notice in my tests of the three servers that under "Disks" in "Resource Monitor", "disk activity on SERVER-3 reaches up to 10MB/s while on SERVER-1, it never passes 500KB/s. My IT guy suggested placing a dedicating SSD on that server but I can't believe there isn't a better way. Thanks anyway. – Dentra Andres Nov 20 '15 at 20:42
  • @DentraAndres Great information to know. I had an SSD fail in my laptop not too long ago and it was having similar issues - it was very snappy but anything requiring disk I/O was dog slow. It turns out the SSD is bad, but to troubleshoot it I was able to look at the `top` output under linux (it was dual booting) and the processor was using most of its resources waiting for I/O to respond. Take a look at the Resource Monitor in Windows and the queue length for the disk. Compare it to your other servers (and those others VMs on the ESXi box) and you might notice some drive I/O problems. – TheGrandPackard Nov 20 '15 at 20:47
  • @TheGrandPackard Thanks. I also believe it has something to do with the SSD, although I don't think it is the SSD itself (more like some VM setting limiting SSD I/O). The reason for that is that if I test the SSD itself (my IT guy showed me real-time), its performance is as good as the ones in the other servers. The queue length is low as I mentioned, but the one in the other two servers, although higher, stay between 4 and 5, sometimes dipping below 3. – Dentra Andres Nov 23 '15 at 16:18

2 Answers2

1

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.

Marco
  • 1,709
  • 3
  • 17
  • 31
0

After a lot of tests and trying different combinations, I can't say that I found the perfect solution, which would probably include some VM settings which my IT guy could not find. In any case, here are the two things that really made difference in the performance (Keep in mind that this only applies to INNODB):

1 - Use transactions, i.e., turn off autocommit, do all transactions, and commit at the end.

2 - You can get a huge increase in performance by setting innodb-flush-log-at-trx-commit to 0 or 1. Please read the full documentation to understand the consequences of this action.

I hope it will help someone else out there.

Dentra Andres
  • 31
  • 1
  • 6