1. 监控与优化

1.1 监控指标

1.1.1 QPS

mysql>showglobalstatuslike'Com%';mysql>showglobalstatuslike'Queries';+---------------+---------+|Variable_name|Value|+---------------+---------+|Queries|1983766|+---------------+---------+1rowinset(0.00sec)

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

mysql>showglobalstatuswherevariable_namein('Queries','uptime');+---------------+---------+|Variable_name|Value|+---------------+---------+|Queries|1983768||Uptime|1364443|+---------------+---------+2rowsinset(0.00sec)

1.1.2 TPS

mysql>showglobalstatuswherevariable_namein('com_insert','com_update','com_delete','uptime');+---------------+---------+|Variable_name|Value|+---------------+---------+|Com_delete|23676||Com_insert|793072||Com_update|259586||Uptime|1364651|+---------------+---------+4rowsinset(0.00sec)

Transaction_sum= Com_delete+ Com_insert+ Com_update

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

1.1.3 并发数

mysql>showglobalstatuslike'Threads_running';+-----------------+-------+|Variable_name|Value|+-----------------+-------+|Threads_running|2|+-----------------+-------+1rowinset(0.01sec)

1.1.4 连接数

#最大连接数mysql>showglobalstatuslike'max_used_connections%';+---------------------------+---------------------+|Variable_name|Value|+---------------------------+---------------------+|Max_used_connections|22||Max_used_connections_time|2019-09-0413:49:52|+---------------------------+---------------------+2rowsinset(0.00sec)#当前连接数mysql>showglobalstatuslike'threads_connected';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|Threads_connected|2|+-------------------+-------+1rowinset(0.01sec)

1.1.5 缓存命中率

##从缓存中读取的次数mysql>showglobalstatuslike'innodb_buffer_pool_read_requests';+----------------------------------+----------+|Variable_name|Value|+----------------------------------+----------+|Innodb_buffer_pool_read_requests|16217299|+----------------------------------+----------+1rowinset(0.00sec)##从物理磁盘读取的次数mysql>showglobalstatuslike'innodb_buffer_pool_reads';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Innodb_buffer_pool_reads|2067|+--------------------------+-------+1rowinset(0.00sec)

缓存命中率 = ( 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 阻塞

##<mysql5.7SELECTb.trx_mysql_thread_idas'被阻塞的线程',b.trx_queryas'被阻塞的SQL',c.trx_mysql_thread_idas'阻塞线程',c.trx_queryas'阻塞SQL',UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))as'阻塞时间'FROMinformation_schema.INNODB_LOCK_WAITSaJOINinformation_schema.INNODB_TRXbONa.requesting_trx_id=b.trx_idJOINinformation_schema.INNODB_TRXcONa.blocking_trx_id=c.trx_idWHERE(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>30##>mysql5.7SELECTwaiting_pidAS'被阻塞的线程',waiting_queryAS'被阻塞的SQL',blocking_pidAS'阻塞线程',blocking_queryAS'阻塞SQL',wait_ageAS'阻塞时间',sql_kill_blocking_queryAS'建议操作'FROMsys.innodb_lock_waitsWHERE(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

1.1.8 死锁

##pt工具pt-deadlock-loggeru=admin,p=123456,h=127.0.0.1\--create-dest-table\--destu=admin,p=123456,h=127.0.0.1.D=dba,t=deadlock##全局参数，日志监控mysql>setpersistinnodb_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-hmaster--create-table--databasexxx--update--daemonize--interval=1##从库pt-hearbeat--user=xx--password=xx-hslave--databasexxx--monitor--daemonize--log/tmp/slave_lag.log

1.1.11 主从状态

show slave status\G

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

1.2 负载问题

1.3 优化