• 5
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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.

      • 2
    • 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.
      • 1
    • Well in any event server-1 s/b performing better for sure. What is the disk queue length like on server-1?
    • 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?

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.

  • 1
Reply Report

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.

  • 0
Reply Report

Trending Tags