mysql中的监控与优化过程是怎样的

mysql中的监控与优化过程是怎样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

1. 监控与优化

1.1 监控指标

1.1.1 QPS

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> show global status where variable_name in ('Queries','uptime');+---------------+---------+| Variable_name | Value   |+---------------+---------+| Queries       | 1983768 || Uptime        | 1364443 |+---------------+---------+2 rows in set (0.00 sec)

1.1.2 TPS

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)

1.1.3 并发数

mysql> show global status like 'Threads_running';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| Threads_running | 2     |+-----------------+-------+1 row in set (0.01 sec)

1.1.4 连接数

# 最大连接数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)

1.1.5 缓存命中率

##从缓存中读取的次数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%

1.1.6 服务可用性

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

2. mysqladmin -uroot -pxxx -hxxxx ping

1.1.7 阻塞

##< mysql 5.7SELECT 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_idJOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_idWHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>30##> mysql 5.7SELECT 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_waitsWHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

1.1.8 死锁

##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表实时监控

1.1.10 主从延迟

1. show slave status\G

2. pt-heartbeat

##主库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

1.1.11 主从状态

1. show slave status\G

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

1.2 负载问题

1.3 优化

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。