Benchmarking performance node.js (cluster) with mysql pools: Lighttpd + PHP?

Question

Edit (2): Now use db-mysql with the generic-pool module. The error rate has decreased significantly and fluctuates by 13%, but the throughput is still around 100 req / sec.

Edit (1): After someone suggested that ORDER BY RAND () would cause MySQL to be slow, I removed this sentence from the query. Node.js now fluctuates around 100 req / sec, but still the server reports an error CONNECTION: Too many connections.

Node.js or Lighttpd with PHP?

You probably saw a lot of Hello World Node.js ... tests, but the hello world tests, even those that were delayed for 2 seconds per request, are not even close to real world use. I also performed these variations of the Hello World tests using Node.js and a pilot throughput of about 800 req / sec with an error rate of 0.01%. However, I decided on some tests that were a bit more realistic.

Maybe my tests are not completed, most likely something is REALLY wrong with Node.js or my test code, so if your Node.js expert, please help me write some better tests. My results are published below. I used Apache JMeter for testing.

Test Case and System Specifications

The test is pretty simple. The mysql query for the number of users is randomly ordered. The first username of the user is retrieved and displayed. The connection to the mysql database is through a unix socket. OS - FreeBSD 8+. 8 GB of RAM. Intel Xeon Quad Core 2.x Ghz processor. I tweaked Lighttpd configurations a bit before I came across Node.js.

Apache JMeter Settings

The number of threads (users): 5000 I believe that this is the number of simultaneous connections

Acceleration Period (in seconds): 1

Number of cycles: 10 This is the number of requests per user

Apache JMeter End Results

  Label |  # Samples |  Average |  Min |  Max |  Std.  Dev.  |  Error% |  Throughput |  KB / sec |  Avg.  Bytes

 HTTP Requests Lighttpd |  49918 |  2060ms |  29ms |  84790ms |  5524 |  19.47% |  583.3 / sec |  211.79 |  371.8

 HTTP Requests Node.js |  13767 |  106569ms |  295ms |  292311ms |  91764 |  78.86% |  44.6 / sec |  79.16 |  1816

Output Results

Node.js was so bad that I had to stop the test earlier. [ Fixed Tested fully]

Node.js reports a "CONNECTION: too many connections" error on the server. [ Fixed ]

In most cases, Lighttpd had a throughput of about 1200 req / sec.

However, Node.js had a throughput of about 29 req / sec. [ Fixed Now at 100req / sec]

This is the code I used for Node.js (using MySQL pools)

var cluster = require('cluster'), http = require('http'), mysql = require('db-mysql'), generic_pool = require('generic-pool'); var pool = generic_pool.Pool({ name: 'mysql', max: 10, create: function(callback) { new mysql.Database({ socket: "/tmp/mysql.sock", user: 'root', password: 'password', database: 'v3edb2011' }).connect(function(err, server) { callback(err, this); }); }, destroy: function(db) { db.disconnect(); } }); var server = http.createServer(function(request, response) { response.writeHead(200, {"Content-Type": "text/html"}); pool.acquire(function(err, db) { if (err) { return response.end("CONNECTION error: " + err); } db.query('SELECT * FROM tb_users').execute(function(err, rows, columns) { pool.release(db); if (err) { return response.end("QUERY ERROR: " + err); } response.write(rows.length + ' ROWS found using node.js<br />'); response.end(rows[0]["username"]); }); }); }); cluster(server) .set('workers', 5) .listen(8080); 

This is the code I used for PHP (Lighttpd + FastCGI)

 <?php $conn = new mysqli('localhost', 'root', 'password', 'v3edb2011'); if($conn) { $result = $conn->query('SELECT * FROM tb_users ORDER BY RAND()'); if($result) { echo ($result->num_rows).' ROWS found using Lighttpd + PHP (FastCGI)<br />'; $row = $result->fetch_assoc(); echo $row['username']; } else { echo 'Error : DB Query'; } } else { echo 'Error : DB Connection'; } ?> 

Answer 1

This is a poor comparative comparison. In node.js, you select the whole table and put it in an array. In php, your only first line parsing. Thus, the larger your table, the slower the node will look. If you do php, use mysqli_fetch_all, it will be a similar comparison. While db-mysql should be fast, it is not very fully shown and does not have the ability to make this a fair comparison. Using another node.js module, such as node -mysql-libmysqlclient, should allow you to process only the first line.

Answer 2

100 connections is the default for the maximum number of MySQL connections.

So, for some reason, your connections are not reused for different requests. You probably already have one request running for each connection.

Perhaps the nodejs MySQL library you are using will not query the same MySQL connection, but try to open another connection and it will fail.

Answer 3

Correct me if I am wrong, but I feel that you are missing something: Node uses one process to process each request (and processes them through events, the same process), and php receives a new process (thread) for each request .

The problem is that one process from Node is bound to one processor core, and PHP gets scaled with all four cores through multithreading. I would say that with the Quad Core 2.x GHz processor PHP will certainly have a significant advantage over Node only due to the possibility of using additional resources.

There is another discussion giving some information on how to scale Node across multiple cores, but this should be done explicitly through coding. Again, correct me if I am wrong, but I do not see such code in the above example.

I'm new to Node myself, but I hope this helps you improve your test :)

Answer 4

Have you enabled APC with PHP?

Can you try to enable persistent connections with PHP? eg

 $conn = new mysqli('p:localhost', 'root', 'password', 'v3edb2011'); 

Answer 5

Don't you use the 10 maximum MySQL connections in Node.js and the 5000 maximum MySQL connections through PHP?

While you run your tests on any of the systems, I would look at MySQL "SHOW THE FULL PROCESS LIST".

Answer 6

One thing to keep in mind is the driver — database performance can be very tied to the particular driver you are using. The most popular mysql driver and the one that is most actively supported is https://github.com/felixge/node-mysql . May get different results.

But if you are stuck in 100 connections, sounds like connections do not close properly. I can add the console.log statement to the kill event pool to make sure that it is actually running.

Answer 7

This is a bad test, it should be a simple “hello world”, since thousands of tests that prove that nodejs is the “hello world” server are faster than all: D