使用 locust 對 mysql 語句進行壓測

使用locust對mysql語句進行壓測

一、目標說明

1、自定義封裝協議對mysql語句進行壓測
2、此處使用第三方庫 pymysql

二、實現原理

1、先簡單對pymysql進行簡單封裝（若使用原生方法太多，不方便使用）
2、參照 官方原文 https://docs.locust.io/en/stable/testing-other-systems.html
3、三步走：1、構建協議客戶端=》2、依賴客戶端對協議方法進行裝飾和資料統計 =》3、整改協議繼承user類
4、給類的所有方法加裝飾器不理解參照：https://blog.csdn.net/weixin_36179862/article/details/102829018

三、程式碼邏輯及演示

3.1 對pymysql進行個性化簡單封裝（mysql.py）：

import pymysql
import traceback
from utils.logger import log  #可使用系統的logging
import sys
import time
#import stopit


class MySql():
    def __init__(self):

        self.conn = None  # 一個連結物件
        self.cur = None  # 連結中的遊標
    def __getattr__(self, name):
        pass
        # magic method dispatcher
        #log.info("開始呼叫 mysql __getattr__：{}".format(name))


    def close_conn(self):
        # 連結未關閉,主動關閉
        try:
            if self.conn != None:
                self.conn.close()
                self.conn = None
        except:
            # 防止之前的 連結未關閉
            pass

    def creat_conn(self, host, user, pwd, dbname, port=3306, close=1, isnew=0):
        """
        新建一個mysql連結conn
        c= self.conn
        c.ping()  # 採用連線物件的ping()函式檢測連線狀態
        print('connect-%d ok' % 1)
        :param host:
        :param user:
        :param pwd:
        :param dbname:
        :param port:
        :return:
        :close=1;預設強制關閉此物件之前已經開啟的連結物件和遊標
        :isnew=1:強制建立一個新的連結 ，0不新建連線

        """
        #print(host, user, pwd, dbname, port,close)
        # 已存在連結，且不需要新建，就返回已有的conn
        if self.conn != None and isnew == 0:
            return self.conn

        self.close_conn()

        try:

            self.conn = pymysql.connect(host=host, user=user, passwd=pwd, db=dbname, port=port, charset='utf8')
            self.cur = self.conn.cursor()  # 並建立取一個遊標
        except  Exception as e:
            #print(host,user,pwd,dbname,port)
            log.error("mysql連結資料庫失敗：\n" + str(traceback.print_exc()))
            raise Exception("mysql連結資料庫失敗1：\n" ,traceback.print_exc())
            # logger.error(str(*sys.exc_info()))
        return self.conn


    #@stopit.threading_timeoutable()
    def query(self, sql,close_cursor=1,is_except=0,iscommit=0):
        """
        :param sql: sql語句
        :param close_cursor: 失敗1關閉遊標，0失敗不關閉關閉遊標
        :param  is_except :是否丟擲異常，1為丟擲異常(測試用例中會報失敗)，0為返回異常值
        :return:
        """
        try:
            #log.info("utils.mysql準備執行的sql：\n{}".format(sql))
            self.cur.execute(sql)
            if iscommit == 1:
                #log.info("準備提交：")
                self.conn.commit()
            data = self.cur.fetchall()
            #log.info(sql,"mysql data:{}".format(data))
            return data
        except  Exception as e:
           # log.warning("utils.mysql準備執行的sql：\n{}".format(sql))
            message=str(e)
            if close_cursor ==1:
                self.cur.close()  # 關閉遊標
            log.warning("utils_sql失敗:{}\n{}!!!{}".format(sql,e, traceback.print_exc()))
            if is_except ==1:
                raise Exception(message[-300:])

            return ('sql失敗{}\n{}'.format(sql,message[-300:]),)


            # self.conn.close()  # 釋放資料庫資源


# 示列化一個物件
mysql = MySql()

3.2 依賴mysql.py 進行封裝locust指令碼（mysql_locust.py）：

import time
from locust import User, env, task, between
from utils.mysql import MySql  #上面的 mysql.py檔案的 mysql物件
from utils.logger import log #此處可使用 系統logging

#log.set_logpath("/mysql/locust/")

# 第一步 構建新協議的客戶端 和 第二步 對類的方法進行裝飾
class MysqlClient(MySql):
    """
    Simple, sample XML RPC client implementation that wraps xmlrpclib.ServerProxy and
    fires locust events on request_success and request_failure, so that all requests
    gets tracked in locust's statistics.
    """

    _locust_environment = None  #設定預設私有的環境變數

    def __getattribute__(self, item):  # __getattribute__
        """通過該方法對所有的方法進行裝飾，能被locust 統計資料
        可參考：官方文件或下面連結
        https://blog.csdn.net/weixin_36179862/article/details/102829018

        """
        func = super().__getattribute__(item)  # __getattr__   __getattribute__
        if str(type(func)) == "<class 'function'>" or str(type(func)) == "<class 'method'>":
            def wrapper(*args, **kwargs):
                if 'locust_name' in kwargs.keys():  # 處理資料統計名字
                    name = kwargs['locust_name']
                    kwargs.pop('locust_name')
                else:
                    name = item

                start_time = time.time()
                try:
                    result = func(*args, **kwargs)
                except Exception as e:
                    total_time = int((time.time() - start_time) * 1000)
                    # 新增請求失敗事件
                    self._locust_environment.events.request_failure.fire(request_type="mysql", name=name,
                                                                         response_time=total_time, exception=e)
                else:
                    total_time = int((time.time() - start_time) * 1000)
                    #新增請求成功事件
                    self._locust_environment.events.request_success.fire(request_type="mysql", name=name,
                                                                         response_time=total_time,
                                                                         response_length=0)
                    # In this example, I've hardcoded response_length=0. If we would want the response length to be
                    # reported correctly in the statistics, we would probably need to hook in at a lower level
                log.info("{} 耗時total_time：{} reslut:\n{}".format(name, total_time, result))
                return result

            return wrapper
        else:
            return func


# 第三步 繼承user類，並初始化客戶端和設定環境變數
class MysqlUser(User):
    """
    This is the abstract User class which should be subclassed. It provides an XML-RPC client
    that can be used to make XML-RPC requests that will be tracked in Locust's statistics.
    """
    abstract = True

    def __init__(self, *args, **kwargs):
        # super(MysqlUser, self).__init__(*args, **kwargs)
        super().__init__(*args, **kwargs)
        self.client = MysqlClient()
        self.client._locust_environment = self.environment


# 第四步 使用繼承了user的類，產生新協議的使用者
class ApiUser(MysqlUser):
    host = "http://10.2.1.95:3318/"
    wait_time = between(0.1, 1)

    def on_start(self):
        log.info("開始登陸：")
        user = 'root'
        pwd = '123456'
        host = '10.2.1.95'
        dbname = 'coreframe'
        self.client.creat_conn(host, user, pwd, dbname, port=3318)

    @task(1)
    def test_mysql(self):
        # log.info("self.client2:", self.client,self.environment)
        sql = """
              select count(*) from om_employee
                """
        name = "select count(*) from LD16090835"
        data = self.client.query(sql, locust_name=name)
        log.info("data:{}".format(data))
        # [log.info(('on_start:', s)) for s in [self.environment.stats.total]]


if __name__ == '__main__':
    import subprocess, os


    path = os.path.dirname(os.path.abspath(__file__))
    file_name = os.path.split(__file__)[-1].split(".")[0]
    default_url = 'http://10.2.1.95:3318/'
    log.info(path, file_name)

    log.info("開始執行wt")  # --no-web -c 2 -r 1 -t 3s
    # locust -f --headless -u 1000 -r 100 --run-time 1h30m --step-load --step-users 300 --step-time 20m
    # subprocess.call(
    #     'locust -f {}/{}.py  -u 1 -r 1 -t 10 -l --csv-full-history'.format(path,file_name, default_url),
    #     shell=True)
    cmd = 'locust -f {}/{}.py  --host={}  --web-host="127.0.0.1" --web-port 8090 --csv CSV_{}'.format(path, file_name,
                                                                                                      default_url,
                                                                                                      file_name)
    log.info("cmd：", cmd)
    dd = subprocess.call(cmd, shell=True)  ##--web-port 8090
    # d=subprocess.check_call('locust -f {}/{}.py  --worker'.format(path,file_name, default_url),shell=True)
    # d1=subprocess.check_call('locust -f {}/{}.py  --worker'.format(path,file_name, default_url),shell=True)
    d2 = subprocess.call(
        'locust -f {}/{}.py  --master --host={}  --web-host="127.0.0.1" --web-port 8090 --csv CSV_{}'.format(path,
                                                                                                             file_name,
                                                                                                             default_url,
                                                                                                             file_name),
        shell=True)

四、展示結果及其他說明