使用 mysql 池对 node.js(集群)的性能进行基准测试

问题描述

Edit(2): 现在使用 db-mysql 和 generic-pool 模块.错误率大幅下降，徘徊在 13%，但吞吐量仍然在 100 req/sec 左右.

Edit(1): 在有人建议 ORDER BY RAND() 会导致 MySQL 变慢后，我从查询中删除了该子句.Node.js 现在徘徊在 100 请求/秒左右，但服务器仍然报告连接错误:连接过多".

Node.js 或 Lighttpd 与 PHP?

您可能看到过许多 node.js 的Hello World"基准测试……但是hello world"测试，即使是每个请求延迟 2 秒的测试，甚至都没有接近实际生产使用情况.我还使用 node.js 执行了Hello World"测试的这些变体，看到吞吐量约为 800 req/sec，错误率为 0.01%.但是，我决定进行一些更现实的测试.

也许我的测试不完整，很可能 node.js 或我的测试代码真的有问题，所以如果你是 node.js 专家，请帮助我写一些更好的测试.我的结果发表在下面.我使用 Apache JMeter 进行测试.

测试用例和系统规范

测试非常简单.对用户数的 mysql 查询是随机排序的.检索并显示第一个用户的用户名.mysql 数据库连接是通过 unix 套接字连接的.操作系统是 FreeBSD 8+.8GB 内存.英特尔至强四核 2.x Ghz 处理器.在我遇到 node.js 之前，我稍微调整了 Lighttpd 配置.

Apache JMeter 设置

线程数(用户):5000 我相信这是并发连接数

加速时间(以秒为单位):1

循环次数:10 这是每个用户的请求数

Apache JMeter 最终结果

结果结论

Node.js 太糟糕了，我不得不提前停止测试.[已修复已完全测试]

Node.js 在服务器上报告连接错误:连接过多".[已修正]

大多数时候，Lighttpd 的吞吐量约为 1200 请求/秒.

然而，node.js 的吞吐量约为 29 req/sec.[已修复现在是 100req/sec]

这是我用于 node.js 的代码(使用 MySQL 池)

var cluster = require('cluster'), http = require('http'), mysql = require('db-mysql'), generic_pool = require('generic-pool');var pool = generic_pool.Pool({名称:'mysql'，最大:10，创建:函数(回调){新的 mysql.Database({套接字:/tmp/mysql.sock"，用户:'根'，密码:'密码'，数据库:'v3edb2011'}).connect(function(err, server) {回调(错误，这个)；});},销毁:函数(数据库){db.disconnect();}});var server = http.createServer(function(request, response) {response.writeHead(200, {"Content-Type": "text/html"});pool.acquire(function(err, db) {如果(错误){return response.end("连接错误:" + err);}db.query('SELECT * FROM tb_users').execute(function(err, rows, columns) {pool.release(db);如果(错误){return response.end("查询错误:" + err);}response.write(rows.length + ' ROWS found using node.js<br/>');response.end(rows[0]["username"]);});});});集群(服务器).set('工人', 5).听(8080)；

这是我用于 PHP (Lighttpd + FastCGI) 的代码

query('SELECT * FROM tb_users ORDER BY RAND()');如果($结果){echo ($result->num_rows).'使用 Lighttpd + PHP (FastCGI) 找到的 ROWS<br/>';$row = $result->fetch_assoc();回声 $row['用户名'];} 别的 {echo '错误:数据库查询';}} 别的 {echo '错误:数据库连接';}?>

这是一个糟糕的基准比较.在 node.js 中，您选择整个表并将其放入一个数组中.在 php 中，您只解析第一行.所以你的表越大，节点看起来就越慢.如果您让 php 使用 mysqli_fetch_all，那将是类似的比较.虽然 db-mysql 应该很快，但它的功能不是很全，并且缺乏将其进行公平比较的能力.使用不同的 node.js 模块，如 node-mysql-libmysqlclient 应该允许您只处理第一行.

Edit(2): 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.

Edit(1): 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'.

Node.js or Lighttpd with PHP?

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.

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.

Test Case and System Specs

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.

Apache JMeter Settings

Number of threads (users) : 5000 I believe this is the number of concurrent connections

Ramp up period (in seconds) : 1

Loop Count : 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

Result Conclusions

Node.js was so bad i had to stop the test early. [Fixed Tested completely]

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

Most of the time, 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 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';
  }
?>

This is a bad benchmark comparison. In node.js your selecting the whole table and putting it in an array. In php your only parsing the first row. So the bigger your table is the slower node will look. If you made php use mysqli_fetch_all it would be a similar comparison. While db-mysql is supposed to be fast it's not very full featured and lacks the ability to make this a fair comparison. Using a different node.js module like node-mysql-libmysqlclient should allow you to only process the first row.

这篇关于使用 mysql 池对 node.js(集群)的性能进行基准测试:Lighttpd + PHP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！