mysql计算实时的QPS/TPS

版本:mysql 5.7

需要开启show_compatibility_56

mysql> set global show_compatibility_56=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

 

 

 

QPS:

select VARIABLE_VALUE,now() from information_schema.GLOBAL_STATUS where VARIABLE_NAME in ('Queries');

查询两次得到的差值除以时间间隔

 

TPS:

select
  sum(`VARIABLE_VALUE`),
  now()
from
  information_schema.GLOBAL_STATUS
where
  VARIABLE_NAME in (
    'COM_COMMIT',
    'COM_ROLLBACK',
    'COM_INSERT',
    'COM_DELETE',
    'COM_UPDATE'
  )

 

python 计算QPS

# -*- coding: utf-8 -*-

import pymysql
import smtplib
import datetime
import time

##mysql 5.7
mysql_server="192.168.1.22"
user_name="root"
password= "testtest"
db_name="performance_schema"
gl_port=3306
offset = 100

def get_query_value():
    sqltext = "select VARIABLE_VALUE as a from performance_schema.global_status where VARIABLE_NAME in ('Queries')"
    ##print(sqltext)
    db = pymysql.connect(mysql_server,user_name, password, db_name,port=gl_port)
    cursor = db.cursor()
    try:
        cursor.execute(sqltext)
        results = cursor.fetchone()
    except Exception as e:
        print(e)
    db.close()
    q_value = results[0]
    return q_value

if __name__ == '__main__':
    prev_ops = 0
    while True:
        ops = int(get_query_value())
        qps = ops - prev_ops
        prev_ops = ops
        print("QPS: "+ str(qps))
        time.sleep(1)

 

TPS

# -*- coding: utf-8 -*-

import pymysql
import smtplib
import datetime
import time

##mysql 5.7
mysql_server="192.168.1.22"
user_name="root"
password= "testtest"
db_name="information_schema"
gl_port=3306
offset = 100

def get_tps_value():
    sqltext = "select sum(VARIABLE_VALUE) as a from information_schema.global_status where VARIABLE_NAME in ('COM_COMMIT','COM_ROLLBACK','COM_INSERT','COM_DELETE','COM_UPDATE')"
    ##print(sqltext)
    db = pymysql.connect(mysql_server,user_name, password, db_name,port=gl_port)
    cursor = db.cursor()
    try:
        cursor.execute(sqltext)
        results = cursor.fetchone()
    except Exception as e:
        print(e)
    db.close()
    q_value = results[0]
    return q_value



if __name__ == '__main__':
    prev_ops = 0
    while True:
        ops = int(get_tps_value())
        qps = ops - prev_ops
        prev_ops = ops
        print("TPS: "+ str(qps))
        time.sleep(1)