mysql计算QPS

首先连接上mysql：

$ mysql -h 192.168.0.x -P3306 -uusername -p123456

进入Mysql之后，查询general_log：

mysql>  SHOW VARIABLES LIKE "general_log%";
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)

如果这个时候general_log是关闭的，可用命令设置为ON：

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

同理也可以设置文件路径：

mysql> SET GLOBAL general_log_file = '/var/run/mysqld/mysqld.log';
Query OK, 0 rows affected (0.06 sec)

打开/var/run/mysqld/mysqld.log文件看到的是如下展示：

Time            Id  Command     Argument
190529 22:26:07 4   Query       REPLACE INTO *
                1   Query       REPLACE INTO *
                4   Query       REPLACE INTO *
                1   Query       REPLACE INTO *

从该文件可以看出来，秒数发生改变的时候，才会打印带日期的日志。那么可用通过该文件两个带日期的行之间的行数差距，即可算出其QPS：

• 筛选带有日期的行，带上行号转存到文件

$ grep "^1905" -n mysqld.log > Line.txt 

• 编写一个Python脚本，计算QPS

#coding=utf-8
import os,sys

if __name__ == '__main__':
        file = open('Line.txt', 'r')
        if file == None:
                print "open file failed"
                sys.exit(1)
        outFile = open("QPS.csv", 'w')
        outFile.write("Time,QPS");
        lastLine = 0
        for line in file:
                params = line.split(' ')
                if len(params) < 2:
                        continue

                time = params[1]
                lineAndDate = params[0]
                subParams = lineAndDate.split(':')
                if len(subParams) < 2:
                        continue
                line = int(subParams[0])
                date = subParams[1]
                fullDate = "%s %s" %(date, time)
                QPS = line - lastLine
                lastLine = line

                outFile.write("\r\n%s,%d"%(fullDate,QPS))
        outFile.close()
        file.close()

然后执行脚本，计算结果导出到QPS.csv：