mysql中的监控与优化过程是怎样的
MySQL中的监控与优化过程是怎样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
专业从事网站设计、网站建设,高端网站制作设计,成都小程序开发,网站推广的成都做网站的公司。优秀技术团队竭力真诚服务，采用HTML5+CSS3前端渲染技术，成都响应式网站建设公司，让网站在手机、平板、PC、微信下都能呈现。建站过程建立专项小组，与您实时在线互动，随时提供解决方案，畅聊想法和感受。
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 服务可用性
周期连接，执行查询：select @ @version; 或 select user();
mysqladmin -uroot -pxxx -hxxxx ping
1.1.7 阻塞
##< 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
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 慢查询
监控慢查询日志
通过information_shcema.processlist表实时监控
1.1.10 主从延迟
show slave status\G
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 主从状态
show slave status\G
IO/SQL 两个线程状态（yes or no)
1.2 负载问题
1.3 优化
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。
当前标题：mysql中的监控与优化过程是怎样的
本文地址：http://shouzuofang.com/article/ghdpss.html