Lua和php读取MySQL性能比较

骑猪看日落 2022-04-22 09:22 427阅读 0赞

# 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 高出了许多，而且请求时间也比减少了近一倍