WEB程序效率问题都是在数据库IO方面,究竟数据库对web程序效率影响有多大?
操作系统:centos
测试机IP:172.16.8.30
请求机IP:172.16.8.14
测试语言:PHP
测试数据库:mysql
test表内数据:17200890条
连接方式:PDO
压力测试工具:webbench
程序代码:
<?php
header('Content-Type:text/html; charset=utf-8');
try{
$mysql = (int)$_GET['mysql'];
if($mysql){
$pdo = new PDO(
'mysql:host=127.0.0.1;dbname=test',
'root','*******',
array(
PDO::ATTR_TIMEOUT => 1,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
)
);
$sql ="SELECT COUNT(id) FROM myisam";
$rs = $pdo->query($sql);
echo 'mysql执行完了';
}else{
$sql ="SELECT COUNT(id) FROM myisam";
echo '不执行mysql';
}
}catch(Exception $e){
$info ="####################################################";
$info .="\n时间:".date("Y-m-d H:i:s");
$info .="\n错误信息:".$e->getMessage().' 错误行号:'.$e->getLine();
$info .="\n####################################################\n\n";
echo $info;
}
webbench命令:
/usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1 //链接mysql
/usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=0 //不链接mysql
测试结果:
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=0
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=0
1000 clients, running 30 sec.
Speed=254144 pages/min, 609995 bytes/sec.
Requests: 127072 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=0
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=0
1000 clients, running 30 sec.
Speed=212818 pages/min, 510813 bytes/sec.
Requests: 106409 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=0
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=0
1000 clients, running 30 sec.
Speed=289674 pages/min, 695434 bytes/sec.
Requests: 144837 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=0
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=0
1000 clients, running 30 sec.
Speed=251946 pages/min, 605405 bytes/sec.
Requests: 125973 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=1
1000 clients, running 30 sec.
Speed=116806 pages/min, 287787 bytes/sec.
Requests: 58403 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=1
1000 clients, running 30 sec.
Speed=103078 pages/min, 269159 bytes/sec.
Requests: 51539 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=1
1000 clients, running 30 sec.
Speed=110758 pages/min, 274232 bytes/sec.
Requests: 55379 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=1
1000 clients, running 30 sec.
Speed=24846 pages/min, 87412 bytes/sec.
Requests: 12423 susceed, 0 failed.
[root@localhost local]# /usr/local/webbench/webbench -c 1000 -t 30 http://172.16.8.30/mysqltest.php?mysql=1
Webbench - Simple Web Benchmark 1.5
Copyright (c) Radim Kolar 1997-2004, GPL Open Source Software.
Benchmarking: GET http://172.16.8.30/mysqltest.php?mysql=1
1000 clients, running 30 sec.
Speed=129234 pages/min, 318405 bytes/sec.
Requests: 64617 susceed, 0 failed.
结论:并发下,不链接mysql的性能比链接mysql高一倍左右