MySQL性能指标及计算方法

2020-07-25  本文已影响0人  程序员面试吧

绝大多数MySQL性能指标可以通过以下两种方式获取:

(1)mysqladmin

使用mysqladmin extended-status命令获得的MySQL的性能指标,默认为累计值。如果想了解当前状态,需要进行差值计算;加上参数 --relative(-r),就可以看到各个指标的差值,配合参数--sleep(-i)就可以指定刷新的频率。

image image

(2)Show global status

可以列出MySQL服务器运行各种状态值,累计值。

image

mysqladmin extended-status命令及show global status得到的指标项特别多。实际应用中,重点关注以下性能指标:

tps/qps

tps: Transactions Per Second,每秒事务数;

qps: Queries Per Second每秒查询数;

通常有两种方法计算tps/qps:

方法1:

基于 com_commit、com_rollback 计算tps,基于 questions 计算qps。

TPS = Com_commit/s + Com_rollback/s

其中,

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n20" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Com_commit /s= mysqladmin extended-status --relative --sleep=1|grep -w Com_commit
Com_rollback/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_rollback
QPS 是指MySQL Server 每秒执行的Query总量,通过Questions (客户的查询数目)状态值每秒内的变化量来近似表示,所以有:
QPS = mysqladmin extended-status --relative --sleep=1|grep -w Questions</pre>

仿照上面的方法还可以得到,mysql每秒select、insert、update、delete的次数等,如:

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n22" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Com_select/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_select
Com_select/s:平均每秒select语句执行次数
Com_insert/s:平均每秒insert语句执行次数
Com_update/s:平均每秒update语句执行次数
Com_delete/s:平均每秒delete语句执行次数</pre>

方法2:

基于com_%计算tps ,qps

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n25" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">tps= Com_insert/s + Com_update/s + Com_delete/s
qps=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s</pre>

线程状态

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n27" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">* threads_running:
当前正处于激活状态的线程个数
​
* threads_connected:
当前连接的线程的个数
​
### 流量状态
* Bytes_received/s:
平均每秒从所有客户端接收到的字节数,单位KB
​
* Bytes_sent/s:
平均每秒发送给所有客户端的字节数,单位KB</pre>

innodb文件读写次数

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n29" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">* innodb_data_reads:
innodb平均每秒从文件中读取的次数
​
* innodb_data_writes:
innodb平均每秒从文件中写入的次数
​
* innodb_data_fsyncs:
innodb平均每秒进行fsync()操作的次数</pre>

innodb读写量

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n31" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">* innodb_data_read:
innodb平均每秒钟读取的数据量,单位为KB
​
* innodb_data_written:
innodb平均每秒钟写入的数据量,单位为KB</pre>

innodb缓冲池状态

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n33" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">* innodb_buffer_pool_reads: 
平均每秒从物理磁盘读取页的次数 
​
* innodb_buffer_pool_read_requests: 
​
平均每秒从innodb缓冲池的读次数(逻辑读请求数)
* innodb_buffer_pool_write_requests: 
​
平均每秒向innodb缓冲池的写次数
* innodb_buffer_pool_pages_dirty: 
​
平均每秒innodb缓存池中脏页的数目
* innodb_buffer_pool_pages_flushed: 
​
* innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
innodb缓冲池的读命中率
​
* Innodb_buffer_usage =  ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100
Innodb缓冲池的利用率</pre>

innodb日志

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n35" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">innodb_os_log_fsyncs: 
平均每秒向日志文件完成的fsync()写数量
​
innodb_os_log_written: 
平均每秒写入日志文件的字节数
​
innodb_log_writes: 
平均每秒向日志文件的物理写次数
​
innodb_log_write_requests: 
平均每秒日志写请求数</pre>

》》点击免费领取: ECS运维指南之Linux系统诊断
https://apprhKAAI3V6603.h5.xeknow.com/st/9RaP1B351

innodb行

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n38" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">innodb_rows_deleted: 
平均每秒从innodb表删除的行数
​
innodb_rows_inserted: 
平均每秒从innodb表插入的行数
​
innodb_rows_read: 
平均每秒从innodb表读取的行数
​
innodb_rows_updated: 
平均每秒从innodb表更新的行数
​
innodb_row_lock_waits:  
一行锁定必须等待的时间数
​
innodb_row_lock_time: 
行锁定花费的总时间,单位毫秒
​
innodb_row_lock_time_avg: 
行锁定的平均时间,单位毫秒</pre>

MyISAM读写次数

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n40" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">key_read_requests: 
MyISAM平均每秒钟从缓冲池中的读取次数
​
Key_write_requests: 
MyISAM平均每秒钟从缓冲池中的写入次数
​
key_reads : 
MyISAM平均每秒钟从硬盘上读取的次数
​
key_writes : 
MyISAM平均每秒钟从硬盘上写入的次数</pre>

MyISAM缓冲池

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n42" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">MyISAM平均每秒key buffer利用率
Key_usage_ratio =Key_blocks_used/(Key_blocks_used+Key_blocks_unused)*100
​
MyISAM平均每秒key buffer读命中率
Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100
​
MyISAM平均每秒key buffer写命中率
Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100</pre>

临时表

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n44" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">Created_tmp_disk_tables: 
服务器执行语句时在硬盘上自动创建的临时表的数量
​
Created_tmp_tables: 
服务器执行语句时自动创建的内存中的临时表的数量
​
Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%,如果Created_tmp_tables值比较大,可能是排序句子过多或者连接句子不够优化</pre>

其他

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="bash" cid="n46" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">slow_queries: 
执行时间超过long_query_time秒的查询的个数(重要)
​
sort_rows: 
已经排序的行数
​
open_files: 
打开的文件的数目
​
open_tables: 
当前打开的表的数量
​
select_scan: 
对第一个表进行完全扫描的联接的数量</pre>

此外,还有一些性能指标不能通过mysqladmin extended-status或show global status直接得到,但是十分重要。

response time: 响应时间

Percona提供了tcprstat工具统计响应时间,此功能默认是关闭的,可以通过设置参数query_response_time_stats=1打开这个功能。

有两种方法查看响应时间:

(1)通过命令SHOW QUERY_RESPONSE_TIME查看响应时间统计;

(2)通过INFORMATION_SCHEMA里面的表QUERY_RESPONSE_TIME来查看。

Slave delay: 备库延迟

可以在slave节点上执行show slave status\G命令,Seconds_Behind_Master项的值即为slave当前的延时量,单位秒。

image
》》点击免费领取:【阿里云】深入浅出Kubernetes项目实战手册(超详细127页)
https://apprhKAAI3V6603.h5.xeknow.com/st/9W8SJYDIn
上一篇下一篇

猜你喜欢

热点阅读