node.js MySQL性能 [英] node.js MySQL performance
问题描述
我比较node.js和PHP在写入性能到MySQL数据库。我使用Apache Benchmark,linux Mint在虚拟机,最新的mysql-server(5.5.43)和驱动程序的MySQL与node.js 从这里。
我使用的代码是
server.js
var http = require('http');
var mysql = require('mysql');
var server = http.createServer(function(req,res){
var connection = mysql.createConnection({
host:'localhost',
user: 'root',
password:'root',
database:'testDB'
});
connection.connect();
connection。 query(INSERT INTO Persons(LastName,FirstName,Address,City)VALUES('Futterkiste','Alfreds','Obere Str。57','Berlin'),function(err,rows,fields){
if(!err)
console.log('解决方案是:',rows);
else
console.log });
connection.end();
res.writeHead(200,{'Content-Type':'text / plain'});
res.end('Hello World');
});
server.listen(1337,'127.0.0.1');
console.log('Server running at http://127.0.0.1:1337/');
index.php
$ b b
$ servername =localhost;
$ username =root;
$ password =root;
$ dbname =testDB;
try {
$ conn = new PDO(mysql:host = $ servername; dbname = $ dbname,$ username,$ password);
//将PDO错误模式设置为异常
$ conn-> setAttribute(PDO :: ATTR_ERRMODE,PDO :: ERRMODE_EXCEPTION);
$ sql =INSERT INTO Persons(LastName,FirstName,Address,City)VALUES('Futterkiste','Alfreds','Obere Str。57','Berlin');
//使用exec(),因为没有返回结果
$ conn-> exec($ sql);
echo新记录已成功创建;
}
catch(PDOException $ e)
{
echo $ sql。 < br> 。 $ e-> getMessage();
}
$ conn = null;
echoHello world;
?>
使用index.php文件的Apache2服务器的Apache Benchmark
ab -n 1000 -c 100 http:// localhost /
PHP MySQL写性能
并发级别:100
测试所需时间:1.328秒
完成请求:1000
失败的请求:0
总传输:230000字节
HTML传输:43000字节
每秒请求数:752.99 [#/ sec] b $ b每个请求的时间:132.804 [ms](平均值)
每个请求的时间:1.328 [ms](意味着所有并发请求)
传输速率:169.13 [Kbytes / $ b
Apache Benchmark到server.js文件中的node.js服务器
ab -n 1000 -c 100 http:// localhost:1337 /
node.js MySQL写性能
并发级别:100
测试所需时间: 3.896秒
完成请求:1000
失败的请求:0
总传输:113000字节
HTML传输:12000字节
每秒请求数:256.68 [#/ sec] (平均值)
每个请求的时间:389.585 [ms](平均值)
每个请求的时间:3.896 [ms](意味着所有并发请求)
传输速率:28.33 [ ]收到
我的印象是,node.js在I / O数据库操作中胜过PHP。所以它使我惊讶,无论我尝试(也试过SELECT * FROM Persons)的SQL语句node.js变得更糟。
不仅如此,当cuncurrency级别为100时,节点在控制台中记录大量的执行查询时出错消息,并且只有约500个请求得到写入数据库。是mysql节点驱动程序只是那么坏,或我在做一些非常错误的事情吗?非常感谢您的帮助:)
谢谢
解决方案首先你没有完成server.js代码。有一个错误与太多的连接打开到DB。要解决这个问题我使用connectionPool。
现在,Apache + PHP + MySQL(XAMP)的结果作为参考:
/ p>
并发级别:100
/ pre>
测试时间:7.476秒
完成请求:1000
失败的请求:0
总传输:230000字节
HTML传输:42000字节
每秒请求数:133.77 [#/ sec](平均值)
每个请求的时间:747.557 [ms ](平均值)
每个请求的时间:7.476 [ms](意味着所有并发请求)
传输速率:30.05 [Kbytes / sec]接收
现在等于修复server.js的机会
var http = require('http');
var mysql = require('mysql');
var connection = mysql.createPool({
connectionLimit:10,
host:'localhost',
user:'test',
password:'test'
database:'testDB'
});
var server = http.createServer(function(req,res){
connection.query(INSERT INTO Persons(LastName,FirstName,Address,City)VALUES('Futterkiste','Alfreds',' Obes str。57','Berlin'),function(err,rows,fields){
if(!err)
console.log $ b else {
console.log('执行查询时出错');
}
res.writeHead(200,{'Content-Type':'text / plain'}) ;
res.end('Hello World');
});
});
server.listen(1337,'127.0.0.1');
server.on('close',function(){
connection.end();
})
console.log('Server running at http://127.0。 0.1:1337 /');
Node + MySQL的结果:
并发级别:100
测试所需时间:7.289秒
完成请求:1000
失败的请求:0
传输的总数:112000字节
HTML转移:11000字节
每秒请求数:137.19 [#/ sec](平均值)
每个请求的时间:728.899 [ms](平均值)
每个请求的时间:7.289 [ms](平均,所有并发请求)
传输速率:15.01 [Kbytes / sec]接收
$ b b正如你可以看到的结果非常接近。但这是一个节点进程针对11 Apache工作。如果我将集群添加到方程会发生什么?以下是修改后的代码:
var http = require('http');
var mysql = require('mysql');
var cluster = require('cluster');
if(cluster.isMaster){
cluster.fork();
cluster.fork();
cluster.fork();
cluster.fork();
} else {
var connection = mysql.createPool({
connectionLimit:10,
host:'localhost',
user:'test',
password:'test',
database:'testDB'
});
var server = http.createServer(function(req,res){
connection.query(INSERT INTO Persons(LastName,FirstName,Address,City)VALUES('Futterkiste','Alfreds' Obes str。57','Berlin'),function(err,rows,fields){
if(!err)
console.log $ b else {
console.log('执行查询时出错');
}
res.writeHead(200,{'Content-Type':'text / plain'}) ;
res.end('Hello World');
});
});
server.listen(1337,'127.0.0.1');
server.on('close',function(){
connection.end();
})
console.log('Server running at http://127.0。 0.1:1337 / worker:'+ cluster.worker.id);
}
四个节点工人结果:
并发级别:100
测试所需时间:2.782秒
完成请求:1000
失败的请求:0
:112000字节
HTML转移:11000字节
每秒请求数:359.48 [#/ sec](平均值)
每个请求的时间:278.179 [ms](平均值)
请求:2.782 [ms](意味着所有并发请求)
传输速率:39.32 [Kbytes / sec]接收
为了好奇,我为10个工人的节点添加结果:
并发级别:100
测试所需时间:2.647秒
完成请求:1000
失败的请求:0
总传输:112000字节
HTML转移:11000字节
每秒请求数:377.84 [#/ sec](平均值)
每个请求的时间:264.665 [ms](平均值)
每个请求的时间:2.647 [ms](意味着所有并发请求)
传输速率: 41.33 [Kbytes / sec]收到
我的笔记本电脑是Core2Duo T6600,Ubuntu 14.04.3,php 5.5。 9,节点0.10.37,mysql 5.5.44I am comparing node.js versus PHP in terms of write performance to MySQL database. I am using Apache Benchmark, linux Mint in virtual machine, newest mysql-server(5.5.43) and driver for MySQL with node.js from here. The code I used is
server.js
var http = require('http'); var mysql = require('mysql'); var server = http.createServer(function (req, res) { var connection = mysql.createConnection({ host : 'localhost', user : 'root', password : 'root', database : 'testDB' }); connection.connect(); connection.query("INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Futterkiste', 'Alfreds', 'Obere Str. 57', 'Berlin')", function(err, rows, fields) { if (!err) console.log('The solution is: ', rows); else console.log('Error while performing Query.'); }); connection.end(); res.writeHead(200, {'Content-Type': 'text/plain'}); res.end('Hello World'); }); server.listen(1337, '127.0.0.1'); console.log('Server running at http://127.0.0.1:1337/');
index.php
$servername = "localhost"; $username = "root"; $password = "root"; $dbname = "testDB"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Futterkiste', 'Alfreds', 'Obere Str. 57', 'Berlin')"; // use exec() because no results are returned $conn->exec($sql); echo "New record created successfully"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; echo "Hello world"; ?>
Apache Benchmark to Apache2 server with index.php file
ab -n 1000 -c 100 http://localhost/
PHP MySQL write performance
Concurrency Level: 100 Time taken for tests: 1.328 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 230000 bytes HTML transferred: 43000 bytes Requests per second: 752.99 [#/sec] (mean) Time per request: 132.804 [ms] (mean) Time per request: 1.328 [ms] (mean, across all concurrent requests) Transfer rate: 169.13 [Kbytes/sec] received
Apache Benchmark to node.js server in server.js file
ab -n 1000 -c 100 http://localhost:1337/
node.js MySQL write performance
Concurrency Level: 100 Time taken for tests: 3.896 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 113000 bytes HTML transferred: 12000 bytes Requests per second: 256.68 [#/sec] (mean) Time per request: 389.585 [ms] (mean) Time per request: 3.896 [ms] (mean, across all concurrent requests) Transfer rate: 28.33 [Kbytes/sec] received
I was under the impression that node.js outperforms PHP in I/O database operations. So it surprises me that no matter which SQL statement I try (also tried SELECT * FROM Persons) node.js turns out worse.
Not only that but also when cuncurrency level is 100, node logs a ton of 'Error while performing Query.' messages to the console and only ~500 out of 1000 requests get written to the database. Are mysql node drivers just that bad or I am doing something very wrong here? I would really appreciate your help :)
Thank you
解决方案First of all you didn't finish server.js code. There is a bug with too many connections opened to DB. To fix this I used connectionPool. And second of all Apache use workers to run many copies of same script in parallel.
Now the result for Apache + PHP + MySQL (XAMP) as a reference point:
Concurrency Level: 100 Time taken for tests: 7.476 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 230000 bytes HTML transferred: 42000 bytes Requests per second: 133.77 [#/sec] (mean) Time per request: 747.557 [ms] (mean) Time per request: 7.476 [ms] (mean, across all concurrent requests) Transfer rate: 30.05 [Kbytes/sec] received
Now to equal the chances I fixed server.js
var http = require('http'); var mysql = require('mysql'); var connection = mysql.createPool({ connectionLimit: 10, host : 'localhost', user : 'test', password : 'test', database : 'testDB' }); var server = http.createServer(function (req, res) { connection.query("INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Futterkiste', 'Alfreds', 'Obere Str. 57', 'Berlin')", function(err, rows, fields) { if (!err) console.log('The solution is: ', rows); else { console.log('Error while performing Query.'); } res.writeHead(200, {'Content-Type': 'text/plain'}); res.end('Hello World'); }); }); server.listen(1337, '127.0.0.1'); server.on('close', function() { connection.end(); }) console.log('Server running at http://127.0.0.1:1337/');
And results of Node + MySQL:
Concurrency Level: 100 Time taken for tests: 7.289 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 112000 bytes HTML transferred: 11000 bytes Requests per second: 137.19 [#/sec] (mean) Time per request: 728.899 [ms] (mean) Time per request: 7.289 [ms] (mean, across all concurrent requests) Transfer rate: 15.01 [Kbytes/sec] received
As you can see the results are very close. But this is one node process against 11 Apache workers. What happens if I add clusters to the equation? Here is the modified code:
var http = require('http'); var mysql = require('mysql'); var cluster = require('cluster'); if (cluster.isMaster) { cluster.fork(); cluster.fork(); cluster.fork(); cluster.fork(); } else { var connection = mysql.createPool({ connectionLimit: 10, host : 'localhost', user : 'test', password : 'test', database : 'testDB' }); var server = http.createServer(function (req, res) { connection.query("INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Futterkiste', 'Alfreds', 'Obere Str. 57', 'Berlin')", function(err, rows, fields) { if (!err) console.log('The solution is: ', rows); else { console.log('Error while performing Query.'); } res.writeHead(200, {'Content-Type': 'text/plain'}); res.end('Hello World'); }); }); server.listen(1337, '127.0.0.1'); server.on('close', function() { connection.end(); }) console.log('Server running at http://127.0.0.1:1337/ worker:' + cluster.worker.id); }
Four node workers results:
Concurrency Level: 100 Time taken for tests: 2.782 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 112000 bytes HTML transferred: 11000 bytes Requests per second: 359.48 [#/sec] (mean) Time per request: 278.179 [ms] (mean) Time per request: 2.782 [ms] (mean, across all concurrent requests) Transfer rate: 39.32 [Kbytes/sec] received
For curiosity I add results for node with 10 workers:
Concurrency Level: 100 Time taken for tests: 2.647 seconds Complete requests: 1000 Failed requests: 0 Total transferred: 112000 bytes HTML transferred: 11000 bytes Requests per second: 377.84 [#/sec] (mean) Time per request: 264.665 [ms] (mean) Time per request: 2.647 [ms] (mean, across all concurrent requests) Transfer rate: 41.33 [Kbytes/sec] received
My laptop is Core2Duo T6600, Ubuntu 14.04.3, php 5.5.9, node 0.10.37, mysql 5.5.44
这篇关于node.js MySQL性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!