mysql 监控

mysql 需要掌握的地方还是很多,必须由点及面的逐个掌握,然后才能到下一个阶段。

mysql 监控

一、查看mysql锁相关命令

1、通过INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 获取事务与锁的信息

1)查看哪些事务正在执行

命令:SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

trx_state表示事务的状态

mysql 监控

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G*************************** 1. row ***************************trx_id: 4391trx_state: LOCK WAIT  //事务状态,LOCK WAIT表示等待锁trx_started: 2019-09-27 11:39:09trx_requested_lock_id: 4391:57:962:104trx_wait_started: 2019-09-27 11:39:09trx_weight: 2trx_mysql_thread_id: 9//线程IDtrx_query: select * from employees where birth_date = '1953-09-02'for update

trx_operation_state: starting index read

trx_tables_in_use: 1trx_tables_locked: 1trx_lock_structs: 2trx_lock_memory_bytes: 1136trx_rows_locked: 1trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0*************************** 2. row ***************************trx_id: 4390trx_state: RUNNING    //事务状态,RUNNING表示正在运行的事务,如果一直运行中,有可能是block了trx_started: 2019-09-27 11:38:41trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 65trx_mysql_thread_id: 20//线程IDtrx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0trx_tables_locked: 1trx_lock_structs: 65trx_lock_memory_bytes: 8400trx_rows_locked: 127trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 02 rows in set (0.00 sec)

2)查看当前锁定的事务

命令:SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

mysql 监控

mysql> select * from information_schema.INNODB_LOCKS\G*************************** 1. row ***************************lock_id: 17778:82:3:6 --当前锁ID

lock_trx_id: 17778 --该锁对应的事务ID

lock_mode: X -- 锁类型,排它锁X

lock_type: RECORD --锁范围,记录锁:record lock,其他锁范围:间隙锁:gap lock,或者next-key lock(记录锁+间隙锁)

lock_table: `test`.`trx_fee`

lock_index: PRIMARY --加载在哪个索引上的锁

lock_space: 82lock_page: 3lock_rec: 6lock_data: 4*************************** 2. row ***************************lock_id: 17773:82:3:6lock_trx_id: 17773lock_mode: X

lock_type: RECORD

lock_table: `test`.`trx_fee`

lock_index: PRIMARY

lock_space: 82lock_page: 3lock_rec: 6lock_data: 4

3)查看当前正在等待锁的事务

命令:SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

mysql> select * from information_schema.INNODB_LOCK_WAITS\G

*************************** 1. row ***************************

requesting_trx_id: 17778 --请求锁的事务ID(等待方)

requested_lock_id: 17778:82:3:6 -- 请求锁ID

blocking_trx_id: 17773 -- 阻塞该锁的事务ID(当前持有方,待释放)

blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

2、查询锁争用情况

1)查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析表锁争夺情况。

mysql> show status like 'table%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Table_locks_immediate | 6165 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 0 |

| Table_open_cache_misses | 0 |

| Table_open_cache_overflows | 0 |

+----------------------------+-------+

变量解释:

table_lock_waited 表示不能马上获得锁的数量

table_lock_immediate表示马上获得锁的数量

2)查看记录锁使用情况

mysql> show status like 'innodb_row_lock_%';

+-------------------------------+-------+

| Variable_name | Value |

+-------------------------------+-------+

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 1896 |

| Innodb_row_lock_time_avg | 6 |

| Innodb_row_lock_time_max | 193 |

| Innodb_row_lock_waits | 274 |

+-------------------------------+-------+

变量解释:

Innodb_row_lock_current_waits:当前等待锁的数量

Innodb_row_lock_time:系统启动到现在,锁定的总时间长度

Innodb_row_lock_time_avg:每次平均锁定的时间

Innodb_row_lock_time_max:最长一次锁定时间

Innodb_row_lock_waits:系统启动到现在总共锁定的次数

二、查看MySQL的运行状态

我们需要了解MySQL的服务器状态信息,比如查看当前MySQL启动后的运行时间,MySQL的客户端会话连接数,MySQL服务器执行的慢查询数,MySQL执行了多少SELECT/UPDATE/DELETE/INSERT语句等统计信息。查看MySQL运行状态,优化MySQL运行效率,就需要使用show status命令。

1、线程缓存命中率

1)查看当前连接数

命令:SHOW STATUS LIKE 'Thread_%';

Thread_cached:被缓存的线程的个数

Thread_running:处于激活状态的线程的个数

Thread_connected:当前连接的线程的个数

Thread_created:总共被创建的线程的个数

Thread Cache Hits 

Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;

Connections = SHOW GLOBAL STATUS LIKE 'Connections';

TCH = (1 - ( Threads_created / Connections ) ) * 100

如果TCH数小于90%,说明命中率不高,创建连接耗费了不少时间,可以考虑加大 thread_cache_size 变量值来增大Thread_cached的数量。

2)线程缓存大小 thread_cache_size

当客户端断开连接之后,MySQL会把线程缓存起来以响应下一个请求而不是直接销毁(前提是缓存数未达上限)

查看thread_cache_size的值

show global variables like 'thread_cache_size';

设置thread_cache_size大小:

如果是短连接,可以适当设大一点。因为短连接往往需要不停地创建和销毁。如果设大一点,重新创建和销毁的数量会小一些,对性能提升有较大的作用。

如果是长连接,如果是长连接,可以适当设置小一点。

物理内存设置规则:

可以大致根据物理内存来设置,1G-->8、2G-->16、3G-->32、4G-->64

两种设置方法:

mysql> set global thread_cache_size = 16

编辑/etc/my.cnf  更改/添加 thread_concurrency = 16

2、QPS、TPS 和 RW Ratio

几个重要参数:

Qcache_hits:查询缓存命中次数

SHOW GLOBAL STATUS LIKE 'Qcache_hits';

Com_select:总的查询语句量

SHOW GLOBAL STATUS LIKE 'Com_select';

Com_insert:执行插入语句量

SHOW GLOBAL STATUS LIKE 'Com_insert';

Com_update:执行更新语句量

SHOW GLOBAL STATUS LIKE 'Com_update';

Com_delete:执行删除语句量

SHOW GLOBAL STATUS LIKE 'Com_delete';

Com_replace:执行字符串替换语句量

SHOW GLOBAL STATUS LIKE 'Com_replace';

Uptime:本次启动服务到现在执行的时长

SHOW GLOBAL STATUS LIKE 'Uptime';

1)QPS,每秒查询量

基于Questions计算QPS。Questions表示发送给服务器的查询数量

Questions:

SHOW GLOBAL STATUS LIKE 'Questions';

QPS = Questions / Uptime

基于com_*计算QPS。Com_select表示查询语句执行数量

QPS = Com_select / Uptime

使用Questions进行计算QPS时,人为拉高了计算结果。相对来说,使用Com_select这种计算方式更符合实际一些。

2)TPS,每秒事务量

服务器每秒处理的事务数,如果是InnoDB会显示,没有InnoDB就不会显示。

基于Com_commit和Com_rollback计算。Com_commit表示执行的事务提交次数,Com_rollback表示执行的事务回滚次数

Com_commit:

SHOW GLOBAL STATUS LIKE 'Com_commit';

Com_rollback:

SHOW GLOBAL STATUS LIKE 'Com_rollback';

TPS = (Com_commit + Com_rollback) / Uptime

QPS 和 TPS值一定要实时监控,如果接近架构搭建时的测试峰值,表示系统压力很大

3)R/W Ratio,读写比

Read/Writes Ratio,数据库读写比

R/W = (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

读写比:优化数据库的重要依据,读的多优化读,写的多优化写

3、慢查询、无索引 join查询

1)慢查询

/*查看慢查询时间 */

show variables like "long_query_time";默认10s

/*查看慢查询配置情况 */

show status like "%slow_queries%";

/*查看慢查询日志路径 */

show variables like "%slow%";

=======================================

/*开启慢日志 */

set global slow_query_log = ON;

/*设置慢查询时间 2s */

set global long_query_time = 2;

/*设置慢查询日志路径 */

set global slow_query_log_file='/usr/local/mysql/data/slow.log';

/*设置无索引的查询,设置后没有使用索引的查询也会被认为是慢查询 */

set global log_queries_not_using_indexes = ON;

======================================

Slow queries per minute

Slow_queries:

SHOW GLOBAL STATUS LIKE 'Slow_queries';

SQPM = Slow_queries/(Uptime/60)

S/Q = Slow_queries/Questions

新版本上线时要重点关注慢查询,出现慢查询sql,要尽快优化。

2)无索引join查询

Full_join per minute

Select_full_join:

SHOW GLOBAL STATUS LIKE 'Select_full_join';

FJPM = Select_full_join / (Uptime / 60)

没有使用索引而造成的全表扫描的join,需要优化索引。

4、buffer 和cache设置

Innodb buffer read hits,buffer命中率

Innodb_buffer_pool_reads:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Innodb_buffer_pool_read_requests:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

IFRH = (1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

InnoDB Buffer命中率,目标95%-99%;

Table Cache,表缓存

Open_tables:

SHOW GLOBAL STATUS LIKE 'Open_tables';

Opened_tables:

SHOW GLOBAL STATUS LIKE 'Opened_tables';

table_cache:

SHOW GLOBAL STATUS LIKE 'table_cache';

table_cache应该大于Open_tables小于Opened_tables

排他锁和共享锁区别:

https://blog.csdn.net/DD_ITNAN/article/details/90603887

上一篇:readView


下一篇:MySQL 死锁快速解决方案与死锁处理策略