Mysql性能优化(一) - 性能检测与瓶颈分析
## 概述
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';
```