|
近期在做平台压测，在分析压测结果时，发现很多MySQL监控指标不太熟悉，也了解了一些，本篇笔记记录一下
Server Status Variable
MySQL服务端维护了很多状态值，用以监控MySQL的运行状态，官方参考文档引用链接
这里记录下常用的一些状态量
Com_xxx
官方解释
The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively.
大意：Com_xxx记录了xxx语句被执行的数量。比如，Com_delete和Com_update分别表示Delete语句和Update语句执行的数量
All Com_stmt_xxx variables are increased even if a prepared statement argument is unknown or an error occurred during execution. In other words, their values correspond to the number of requests issued, not to the number of requests successfully completed.
大意：Com_stmt_xxx 记录一个语句被发起的数量，而不是被成功执行的数量。就是说，一个请求来了，尽管没执行成功，也+1。看起来Com_xxx就是成功执行的数量咯？？？
Queries, Questions
Queries: The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
大意：服务端执行语句的数量（自上一次启动），语句包括服务端存储过程中隐藏执行的语句（within stored programs？？？），但是不包括PING和STATISTICS语句的数量
Questions: The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
大意：同样是服务端执行语句的数量（自上一次启动），但是它仅记录由客户端发送给服务端的语句，即不包含服务端存储过程中内部执行的语句，除了PING、STATISTICS之外，也不包括PREPARE、CLOSE、RESET等语句
Threads_connected, Threads_running
Threads_connected: The number of currently open connections
大意：当前开启的连接数。这应该也说明，MySQL server新起一个连接，就是新建一个线程。
Threads_running: The number of threads that are not sleeping.
大意：正在执行的线程数。也就是并发度。这个参数很重要，其上限一般由MySQL的CPU核数决定，即便线程数再多，如果只是单核CPU，并发度只能是1
Uptime, Uptime_since_flush_status
Uptime: The number of seconds that the server has been up.
大意：服务器启动后的秒数
Uptime_since_flush_status: The number of seconds since the most recent FLUSH STATUS statement.
大意：最近一次执行FLUSH STATUS后的秒数。FLUSH STATUS命令会重置很多统计数值。
QPS Vs TPS
QPS的统计方式似乎不太统一，目前看到的几种统计方式如下：
-
Questions Per Second:
- questions = show global status where variable_name=‘Questions’;
- uptime = show global status like ‘Uptime’;
- qps=questions/uptime
使用服务器启动到此刻的语句执行总数 / 服务存活总时间，可见，这个只比较平均，峰值会被削弱 -
Com_select Per Second： 仅计算Select语句每秒执行时间
相对于上述比较平均的计算方式，还有一种计算是通过两次时间内执行命令的差值，比两次时间的差值，可以更精确的统计峰值QPS
TPS的统计也存在版本差异
-
基于Com_commit, Com_rollback计算TPS
TPS = (Com_commit + Com_rollback) / uptime -
基于Com_insert, Com_update, Com_delete计算TPS
个人感觉前一种更科学，因为一个事务中可能存在多个update, delete, insert等
Sessions Vs Connections
连接和会话的区别？日常中，我们说连接比较多，但在看MySQL文档时又提到会话session的概念。googel了一下，StackOverflow上有一个高赞回答搬过来引用连接：
A session is just a result of a successful connection. Any MySQL client requires some connection settings to establish a connection and after the connection has been established it acquires a connection id (thread id) and some context which is called session.
大意：会话是连接的上下文，连接是客户端和服务端的通道；当客户端和服务端的连接建立成功之后，客户端会获得一个连接id，即线程id，还有上下文环境，即会话
|