Mysql性能优化(一) - 性能检测与瓶颈分析

2014-10-29· 2636 次浏览
## 概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU饱和一般发生在数据装入内存或从磁盘上读取数据时候。 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候瓶颈就会出现在网络上,我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 ## 性能检测命令 ### show status 显示状态信息,参考:[Mysql show status命令详解](https://baike.xsoftlab.net/view/216.html) ### show processlist 查看当前SQL执行,包括执行状态、是否锁表等,参考:[Mysql show processlist命令详解](https://baike.xsoftlab.net/view/215.html) ### show variables 显示系统变量,参考:[Mysql show variables命令详解](https://baike.xsoftlab.net/view/218.html) ## 瓶颈分析命令 ### 获取mysql用户下的进程总数 ```bash ps -ef &##124; awk '{print $1}' &##124; grep "mysql" &##124; grep -v "grep" &##124; wc -l ``` ### 主机性能状态 ```bash # uptime 13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00 ``` ### CPU使用率 ```bash # top 或 # vmstat ``` ### 磁盘IO量 ```bash # vmstat 或 # iostat ``` ### swap进出量\[内存\] ```bash # free -m ``` ### 数据库性能状态 #### QPS(每秒Query量) QPS = Questions(or Queries) / seconds ```sql mysql > show /* global */ status like 'Question'; ``` #### TPS(每秒事务量) TPS = (Com\_commit + Com\_rollback) / seconds ```sql mysql > show status like 'Com_commit'; mysql > show status like 'Com_rollback'; ``` #### key Buffer 命中率 key\_buffer\_read\_hits = (1-key\_reads / key\_read\_requests) * 100% key\_buffer\_write\_hits = (1-key\_writes / key\_write\_requests) * 100% ```sql mysql> show status like 'Key%'; ``` #### InnoDB Buffer命中率 innodb\_buffer\_read\_hits = (1 - innodb\_buffer\_pool\_reads / innodb\_buffer\_pool\_read\_requests) * 100% ```sql mysql> show status like 'innodb_buffer_pool_read%'; ``` #### Query Cache命中率 Query\_cache\_hits = (Qcahce\_hits / (Qcache\_hits + Qcache_inserts )) * 100%; ```sql mysql> show status like 'Qcache%'; ``` #### Table Cache状态量 ```sql mysql> show status like 'open%'; ``` #### Thread Cache 命中率 Thread\_cache\_hits = (1 - Threads_created / connections ) * 100% ```sql mysql> show status like 'Thread%'; mysql> show status like 'Connections'; ``` #### 锁定状态 ```sql mysql> show status like '%lock%'; ``` #### 复制延时量 ```sql mysql > show slave status ``` #### Tmp Table 状况(临时表状况) ```sql mysql > show status like 'Create_tmp%'; ``` #### Binlog Cache 使用状况 ```sql mysql > show status like 'Binlog_cache%'; ``` #### Innodb\_log\_waits 量 ```sql mysql > show status like 'innodb_log_waits'; ```