mysql监控与调优_mysql学习笔记之监控与优化

# 11 监控与优化

## 11.1 监控指标


### 11.1.1 QPS

```mysql

mysql> show global status like 'Com%';

mysql> show global status like 'Queries';

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| Queries | 1983766 |

+---------------+---------+

1 row in set (0.00 sec)

```

QPS = ( Queries 2- Queries 1 ) / 间隔时间

```mysql

mysql> show global status where variable_name in ('Queries','uptime');

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| Queries | 1983768 |

| Uptime | 1364443 |

+---------------+---------+

2 rows in set (0.00 sec)

```

### 11.1.2 TPS

```mysql

mysql> show global status where variable_name in ('com_insert','com_update','com_delete','uptime');

+---------------+---------+

| Variable_name | Value |

+---------------+---------+

| Com_delete | 23676 |

| Com_insert | 793072 |

| Com_update | 259586 |

| Uptime | 1364651 |

+---------------+---------+

4 rows in set (0.00 sec)

```

Transaction_sum= Com_delete+ Com_insert+ Com_update

TPS = (Transaction_sum 2 - Transaction_sum 1 ) / (time 2 - time 1)

### 11.1.3 并发数

```mysql

mysql> show global status like 'Threads_running';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| Threads_running | 2 |

+-----------------+-------+

1 row in set (0.01 sec)

```

### 11.1.4 连接数

```mysql

# 最大连接数

mysql> show global status like 'max_used_connections%';

+---------------------------+---------------------+

| Variable_name | Value |

+---------------------------+---------------------+

| Max_used_connections | 22 |

| Max_used_connections_time | 2019-09-04 13:49:52 |

+---------------------------+---------------------+

2 rows in set (0.00 sec)

# 当前连接数

mysql> show global status like 'threads_connected';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_connected | 2 |

+-------------------+-------+

1 row in set (0.01 sec)

```

### 11.1.5 缓存命中率

```mysql

##从缓存中读取的次数

mysql> show global status like 'innodb_buffer_pool_read_requests';

+----------------------------------+----------+

| Variable_name | Value |

+----------------------------------+----------+

| Innodb_buffer_pool_read_requests | 16217299 |

+----------------------------------+----------+

1 row in set (0.00 sec)

##从物理磁盘读取的次数

mysql> show global status like 'innodb_buffer_pool_reads';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| Innodb_buffer_pool_reads | 2067 |

+--------------------------+-------+

1 row in set (0.00 sec)

```

缓存命中率 = ( innodb_buffer_pool_read_requests - innodb_buffer_pool_reads ) /

innodb_buffer_pool_reads * 100%

### 11.1.6 服务可用性

1. 周期连接，执行查询：select @@version; 或 select user();

2. mysqladmin -uroot -pxxx -hxxxx ping

### 11.1.7 阻塞

```mysql

##< mysql 5.7

SELECT b.trx_mysql_thread_id as '被阻塞的线程',

b.trx_query as '被阻塞的SQL',

c.trx_mysql_thread_id as '阻塞线程',

c.trx_query as '阻塞SQL',

UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'

FROM information_schema.INNODB_LOCK_WAITS a

JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_id

JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_id

WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>30

##> mysql 5.7

SELECT waiting_pid AS '被阻塞的线程',

waiting_query AS '被阻塞的SQL',

blocking_pid AS '阻塞线程',

blocking_query AS '阻塞SQL',

wait_age AS '阻塞时间',

sql_kill_blocking_query AS '建议操作'

FROM sys.innodb_lock_waits

WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

```

### 11.1.8 死锁

```shell

##pt工具

pt-deadlock-logger u=admin, p=123456, h=127.0.0.1 \

--create-dest-table \

--dest u=admin,p=123456, h=127.0.0.1.D=dba,t=deadlock

##全局参数，日志监控

mysql> set persist innodb_print_all_deadlocks=on;

```

### 11.1.9 慢查询

1. 监控慢查询日志

2. 通过information_shcema.processlist表实时监控

### 11.1.10 主从延迟

1. show slave status\G

2. pt-heartbeat

```shell

##主库

pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval=1

##从库

pt-hearbeat --user=xx --password=xx -h slave --database xxx

--monitor --daemonize --log /tmp/slave_lag.log

```

### 11.1.11 主从状态

1. show slave status\G

IO/SQL 两个线程状态(yes or no)

## 11.2 负载问题


## 11.3 优化