天天看點
傳回

Lua和php讀取MySQL性能比較Lua和php讀取MySQL性能比較

Lua和php讀取MySQL性能比較

php對高并發的優化手段基本上是兩個思路，一個是讓流量盡量限制在上遊的層面，讓流量不要一下子湧入後端服務，第二點是讀寫分離，針對讀請求盡量做緩存，寫請求盡量請求入隊列分批透給db。這樣對付一般的高并發是可以抵禦的，但是如果是億萬級别的呢？查了下大廠都是用lua來做比較高并發的資料接口，後來想了下确實應該這樣來解決，因為當請求進入php的生命周期時，io就是它的瓶頸了，因為每個php程序在請求io的時候是同步阻塞的，等待結果傳回才退出腳本。但是lua不同，背靠nginx這大樹，很好的利用了 epoll 的特性，提高了請求的吞吐量

下面用openresty和php做一個壓力測試，來對比一下從mysql取資料的時候兩者的差異

lua連接配接MySQL讀取資料

  • 目錄結構 
./test
├── conf
│   └── nginx.conf
├── controller
│   ├── mysqlTest.lua
├── log
│   └── error.log
└── lualib
   ├── Response.lua
   ├── connectMysqlUtil.lua
   └── myFunc.lua
  • 具體代碼

nginx.conf

worker_processes  1;        #nginx worker 數量, 這裡隻是開啟一個worker程序來測試，生産環境根據機器的cpu來确定，當然越大能處理的請求越多
error_log /lua-workspace/test/log/error.log;   #指定錯誤日志檔案路徑
events {
    worker_connections 1024;
}
http {
    lua_package_path "/lua-workspace/test/lualib/?.lua;;";  #lua 子產品
    server {
        #監聽端口，若你的6699端口已經被占用，則需要修改
        listen 6699;
	    lua_code_cache off;	#這裡需要注意的是并沒有開啟lua的編譯緩存，如果開啟的話性能會提升一個量級，我單機上測試的是變成了 Requests per second:    3183.87 [#/sec] (mean)
	    
        location /openresty-test/mysqlTest/ {
            default_type text/html;
            #content_by_lua 'ngx.say("in openresty-test")';
	        content_by_lua_file /lua-workspace/test/controller/mysqlTest.lua;
        }
    }
}

connectMysqlUtil.lua - 資料庫連接配接類

local connectMysqlUtil = {}

local MYSQL = require "resty.mysql"
-- connect to mysql;
function connectMysqlUtil.connect()
    local mysql = MYSQL:new()
    mysql:set_timeout(1000)
    local ok, err, errno, sqlstate = mysql:connect{
        host = "127.0.0.1",
        port = 3306,
        database = "crawl",
        user = "root",
        password = "root",
        max_packet_size = 1024 * 1024,
        charset=utf8
    }
    if not ok then
        return nil, err, errno, sqlstate
    end
    return mysql,nil,nil,sqlstate
end

-- 釋放mysql連接配接，放入連接配接池
function connectMysqlUtil.releaseConn(mysqlConn)
    local ok = true
    local db_type = 0
    local err = ""
    if mysqlConn then
        local res, err, errno, sqlstate = mysqlConn:read_result()
        while err == "again" do
            res, err, errno, sqlstate = mysqlConn:read_result()
        end
        -- setkeepalive(maxidletimeout, poolsize)
        local ok, err = mysqlConn:set_keepalive(0, 1000)
        if not ok then
            mysqlConn:close()
            ok = false
            err = "MySQL.Error ( "..(err or "null").." ) "
        end
    end 
    return ok, db_type, err
end


return connectMysqlUtil

Response.lua - 相應類

local Response = {}
local cjson = require "cjson"

function Response.success(data)
    Response.header()
    local json = Response.msg(200, 'success', data)
    ngx.say(json)
end

function Response.error(code, msg)
    Response.header()
    local json = Response.msg(code, msg, {})
    ngx.say(json)
end

function Response.header() 
    ngx.header.content_type="application/json"
    ngx.header['Content-Type']="text/html;charset=UTF-8"
    ngx.header["X-Server-By"] = 'server by surjur'
    ngx.header["Server"] = 'nginx'
end

function Response.msg(code, msg, data) 
    local j = {
        code = code,
        msg = msg,
        data = data
    }
    local json = cjson.encode(j)
    return json
end

return Response

mysqlTest.lua - 測試mysql

local connectMysqlUtil = require("connectMysqlUtil")
local Response = require "Response"

local mysql, err = connectMysqlUtil.connect()
if not mysql then
    Response.error(-1001, err)
else
    local sql = "select * from rule order by rule_id asc limit 10"
    local res, err, errno, sqlstate = mysql:query(sql)
    -- ngx.log(ngx.ERR, "get_reused_times: "..mysql:get_reused_times())
    if not res then
        Response.error(-1001, "query failed!")
    else
        Response.success(res)
    end
    connectMysqlUtil.releaseConn(mysql)
    ngx.exit(ngx.HTTP_OK)
    return
end

啟動 openresty

openresty -c /lua-workspace/openresty/openresty-test/conf/nginx.conf

ab 測試

ab -c 100 -n 1000 http://127.0.0.1:6699/openresty-test/mysqlTest/

結果

This is ApacheBench, Version 2.3 <$Revision: 1826891 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests


Server Software:        nginx
Server Hostname:        127.0.0.1
Server Port:            6699

Document Path:          /openresty-test/mysqlTest/
Document Length:        4844 bytes

Concurrency Level:      100
Time taken for tests:   2.226 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      5026000 bytes
HTML transferred:       4844000 bytes
Requests per second:    449.26 [#/sec] (mean)
Time per request:       222.590 [ms] (mean)
Time per request:       2.226 [ms] (mean, across all concurrent requests)
Transfer rate:          2205.04 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.9      0       5
Processing:    10  215  29.1    219     289
Waiting:        6  215  29.1    219     289
Total:         10  216  28.7    219     289

Percentage of the requests served within a certain time (ms)
  50%    219
  66%    226
  75%    232
  80%    236
  90%    248
  95%    258
  98%    272
  99%    281
 100%    289 (longest request)

可以看到每秒的并發請求 Requests per second: 449.26 [#/sec] (mean)，還有所有請求的相應時間統計 100% 289 (longest request)

下面用php的接口來擷取同樣的 crawl 庫的 rule 表 的資料

<?php

/**
 * 首頁
 * @author benzhan
 */
class DefaultController extends BaseController {
    function actionTest() {
        $objRule = new TableHelper('rule', 'crawl');
        return $objRule->getAll([], ["_limit" => 10]);
    }
}

ab 測試

ab -c 100 -n 1000 http://cjms.funkstyle.com/test

結果

This is ApacheBench, Version 2.3 <$Revision: 1826891 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking cjms.funkstyle.com (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests


Server Software:        Apache/2.4.33
Server Hostname:        cjms.funkstyle.com
Server Port:            80

Document Path:          /test
Document Length:        132 bytes

Concurrency Level:      100
Time taken for tests:   3.444 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      686000 bytes
HTML transferred:       132000 bytes
Requests per second:    290.35 [#/sec] (mean)
Time per request:       344.406 [ms] (mean)
Time per request:       3.444 [ms] (mean, across all concurrent requests)
Transfer rate:          194.52 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.6      0       3
Processing:    49  317 120.3    319    2888
Waiting:       49  317 120.2    319    2888
Total:         53  318 120.0    319    2888

Percentage of the requests served within a certain time (ms)
  50%    319
  66%    332
  75%    343
  80%    348
  90%    362
  95%    382
  98%    415
  99%    446
 100%   2888 (longest request)

顯而易見lua請求MySQL比單純php請求MySQL的 qps 高出了許多，而且請求時間也比減少了近一倍

php學習
上一篇: php5.3 使用namespace後性能比較及類、函數、靜态方法的性能比較
下一篇: 快速排序、歸并排序、堆排序三種算法性能比較

繼續閱讀