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

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)

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%

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

mysqladmin -uroot -pxxx -hxxxx ping

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

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.log1.1.11 主从状态

show slave status\G

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

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