mysql的性能瓶颈_Mysql性能优化(一) - 性能检测与瓶颈分析

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';

```