title: Benchmarking Performance of node.js (cluster) with mysql pools : Lighttpd + PHP?
body: <p><strong>Edit(2):</strong> Now using db-mysql with generic-pool module. The error rate has dropped significantly and hovers at 13% but the throughput is still around 100 req/sec.</p>
  
  <p><strong>Edit(1):</strong> After someone suggesting that ORDER BY RAND() would cause MySQL to be slow, I had removed that clause from the query. Node.js now hovers around 100 req/sec but still the server reports 'CONNECTION error: Too many connections'.</p>
  
  <h2>Node.js or Lighttpd with PHP?</h2>
  
  <p>You probably saw many "Hello World" benchmarking of node.js... but "hello world" tests, even those that were delayed by 2 seconds per request, are not even close to real world production usage. I also performed those variations of "Hello World" tests using node.js and saw throughput of about 800 req/sec with 0.01% error rate. However, I decided to some tests that were a bit more realistic.</p>
  
  <p>Maybe my tests are not complete, most likely something is REALLY wrong about node.js or my test code and so if your a node.js expert, please do help me write some better tests. My results are published below. I used Apache JMeter to do the testing.</p>
  
  <h2>Test Case and System Specs</h2>
  
  <p>The test is pretty simple. A mysql query for number of users is ordered randomly. The first user's username is retrieved and displayed. The mysql database connection is through a unix socket. The OS is FreeBSD 8+. 8GB of RAM. Intel Xeon Quad Core 2.x Ghz processor. I tuned the Lighttpd configurations a bit before i even came across node.js.</p>
  
  <h2>Apache JMeter Settings</h2>
  
  <p>Number of threads (users) : 5000 <em>I believe this is the number of concurrent connections</em></p>
  
  <p>Ramp up period (in seconds) : 1</p>
  
  <p>Loop Count : 10 <em>This is the number of requests per user</em></p>
  
  <h2>Apache JMeter End Results</h2>
  
  <pre>
  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
  </pre>
  
  <h2>Result Conclusions</h2>
  
  <p>Node.js was so bad i had to stop the test early. [<strong>Fixed</strong> Tested completely]</p>
  
  <p>Node.js reports "CONNECTION error: Too many connections" on the server. [<strong>Fixed</strong>]</p>
  
  <p>Most of the time, Lighttpd had a throughput of about 1200 req/sec.</p>
  
  <p>However, node.js had a throughput of about 29 req/sec. [<strong>Fixed</strong> Now at 100req/sec]</p>
  
  <h2>This is the code i used for node.js (Using MySQL pools)</h2>
  
  <pre><code>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&lt;br /&gt;');
              response.end(rows[0]["username"]);
          });
      });   
  });
  
  cluster(server)
    .set('workers', 5)
    .listen(8080);
  </code></pre>
  
  <h2>This this is the code i used for PHP (Lighttpd + FastCGI)</h2>
  
  <pre><code>&lt;?php
    $conn = new mysqli('localhost', 'root', 'password', 'v3edb2011');
    if($conn) {
      $result = $conn-&gt;query('SELECT * FROM tb_users ORDER BY RAND()');
      if($result) {
        echo ($result-&gt;num_rows).' ROWS found using Lighttpd + PHP (FastCGI)&lt;br /&gt;';
        $row = $result-&gt;fetch_assoc();
        echo $row['username'];
      } else {
        echo 'Error : DB Query';
      }
    } else {
      echo 'Error : DB Connection';
    }
  ?&gt;
  </code></pre>