mysql面试（八）管理及监控类问题

性能类指标

功能类指标

QPS

• show global status like "Com%"
• Sum(Com_XXX)
• show global status like "Queries"
• QOS=(Queries2-Queries1)/ 时间间隔

TPS

• show global status where Variable_name in ("com_insert","com_delete","com_update")
• TC≈com_insert+com_delete+com_update
• TPS≈(TC2-TC1)/(time2-time1)

数据库并发数

• show global status like "threads_running"

数据库连接数

• show global status like "threads_connected"
• 报警阈值 threads_connected/max_connection>0.8

Innodb 缓存命中率

• (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests*100%
• Innodb_buffer_pool_read_requests: 从缓冲池读取的次数
• Innodb_buffer_pool_reads: 从物理磁盘读取的次数

数据库可用性

• 周期性连接数据库服务器并执行 select @@version;
• Nysqladmin -uxxx -pxxx -hxxx ping

阻塞

• < MySQL5.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

• = MySQL5.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

死锁

• show engine innodb status

• pt-deadlock-logger u=xx,p=xxxx,h=127.0.0.1 --create-dest-table --dest u=xx,p=xxx,h=127.0.0.1,D=crn,t=deadlock

• set global innodb_print_all_deadlocks=on

监控慢查询

• 通过慢查询日志监控
• 通过 information_schema.PROCESSLIST表实时监控

监控主从延时

• show slave status (Seconds_Behind_Master)
• pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval=1
• pt-heartbeat --user=xx --password=xxx -h slave --create-table --database crn --monitor --daemonize --log /tmp/slave_lag.log

监控主从状态

• show slave status (Slave_IO_Running,Slave_SQL_Running,Last_Errno,Last_Error)

欢迎扫描下方二维码，持续关注：

互联网工程师（id:phpstcn），我们一起学习，一起进步