How to objectively test the performance of node.js and PHP in mysql database queries?

Question

PHP

Script on node to count execution time from 1st to last asynchronous DB connection:

// начало выполнения скрипта
var start = new Date();
// счетчик обращений к БД
var ConnCountGlobal = 0;
// Число обращений к БД
var iLength         = 100;

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '', 
  database : 'epr'
});

for (var i = 0; i < iLength; i++) {
  connection.query(
    'SELECT * FROM dreg_document', 
    function(error, result, fields){
      
      // для последнего запроса вычисляем таймер
      if (ConnCountGlobal == iLength-1){
      
        var dateObj   = new Date();
        var currTime  = dateObj.getTime();
      
        console.log(' node.js - %d мс', (currTime-start.getTime()) / 1000);
      }

      ConnCountGlobal++;
    }
  );
}
connection.end();

PHP script

$mtime = microtime();        //Считываем текущее время
$mtime = explode(" ", $mtime);    //Разделяем секунды и миллисекунды

// Составляем одно число из секунд и миллисекунд
// и записываем стартовое время в переменную
$tstart = $mtime[1] + $mtime[0];

define('DB_NAME', 'epr'); 
define('DB_USER', 'root'); 
define('DB_HOST', 'localhost'); 
define('DB_PASSWORD', '');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die ("Could not connect to MySQL");

$charset = 'utf8';
$connection_method = 'SET CHARACTER SET';

@mysql_query("{$connection_method} {$charset}", $link);

mysql_select_db (DB_NAME) or die ("Could not select database!");

  for ($i = 0; $i < 1000; $i++) {
    $result = mysql_query('SELECT * FROM dreg_document');
  }

// Делаем все то же самое, чтобы получить текущее время
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$totaltime = ($mtime - $tstart); //Вычисляем разницу	
  
  print 'php - ' . $totaltime ;

Answer 1

Like we do in php. You write lines, perform in order. Made one to the end, further. How are we with Nodezhs. You write lines and also in order, but not in the same order as in php. For example, we have 2 calls to the database. They will also go in order, but almost simultaneously, since immediately after the first call to the database, it will turn a second time without waiting for an answer. He will already be waiting for a response in the callback function to the call.
PHP is a calm type that calmly performs tasks and does not get confused. Nodejs is a career amphetaminer who, at any free time (for example, while waiting for a response from the same base), will perform his other duties.

Answer 2

The node code in the example is incorrect in relation to the task:

for (var i = 0; i < 100000; i++) {
  connection.query(
    'SELECT * FROM dreg_document', 
    function(error, result, fields){}
  );
}

It's just that 100,000 asynchronous requests are called in a synchronous loop WITHOUT waiting for their completion. They just threw a request 100,000 times and finished the program by counting the time. The author of the question does not understand how node.js works and therefore misinterprets the result. If you wait for each response to a query to the SQL server, the result will be completely different.

Answer 3

for ($i = 0; $i < 1000; $i++) {
    $result = mysql_query('SELECT * FROM dreg_document');
  }

Pointless and ruthless. Have you heard about query caching? So that's just it here and will work)
And yet, have you heard about asynchrony? So, NodeJS is asynchronous, including with requests to the database.

Answer 4

thinking out loud:
PHP: mysql_query makes a query and buffers the result. in mind.
If you do this 1000 times, then each time the buffer is saved. One hopes that it empties after each line, but it's not 100%.
NodeJS: maybe it doesn't, it's worth adding reading these lines to a variable.

Answer 5

and which is constantly updated

Haha. mysql_* has been de facto unsupported for over 10 years since PHP5.0.0 and mysqli.
Compare with mysqli, then you can already think of something.

Answer 6

$mtime = microtime();        //Считываем текущее время
$mtime = explode(" ", $mtime);    //Разделяем секунды и миллисекунды

// Составляем одно число из секунд и миллисекунд
// и записываем стартовое время в переменную
$tstart = $mtime[1] + $mtime[0];

=
$tstart = microtime(true);

Answer 7

As already mentioned above, the node is asynchronous, so it turned out faster. But that doesn't mean it's faster. It's just that you didn't take this feature into account when writing the test. How the test works in php:
loop - (send request - wait for result) - display running time; how your node test works - loop - (send request) - display the time of sending all requests - start getting results. That is, function(error, result, fields){} does not fall into the measurement.

Answer 8

PHP executes code synchronously, i.e. one request after another.
Node.JS executes the request asynchronously, in this example in parallel.

for (var i = 0; i < 100000; i++) {
  connection.query(
    'SELECT * FROM dreg_document', 
    function(error, result, fields){}
  );
}

I don't recommend that you use unrestricted selections.

Answer 9

As for PHP... A bit of an unfair test... You checked the execution of the request on the V8 platform... As I understand it, the server on the node was out of place for you.. Therefore, it would be logical to compare the sample from PHP through the interpreter in the console and not through the browser and HTTP server Apache, Njinx ..
As mentioned above, both technologies are completely different (asynchronous and synchronous) .. But if you want to really compare, then disable caches and all possible add-ons, Apache and nginx do not apply to PHP in any way ...
You added a pretty solid overhead to PHP