type
status
date
slug
summary
tags
category
icon
password
1、基本运行情况
查看正在执行的 SQL 线程信息
查看 InnoDB 存储引擎信息
用于显示 InnoDB 存储引擎的状态信息,包括信号量(CPU自旋、OSWAITARRAYINFO)、死锁情况、外键冲突、事务状态、I/O操作和缓冲池等,这些都是数据库性能诊断的关键指标。通过对这些信息的理解,可以帮助优化数据库性能,解决死锁和资源争用问题。
查看内存配置参数
这些变量分别表示InnoDB缓冲池大小、键缓冲区大小和查询缓存大小。如果这些值设置得过高,会导致MySQL占用大量内存。
innodb_buffer_pool_size
:这个参数控制了InnoDB存储引擎使用的内存量。根据系统可用内存和数据库的读写负载情况,适当调整这个值可以减少内存占用。建议将其设置为系统总内存的50%-80%。
key_buffer_size
:这个参数控制了键缓冲区的大小,主要用于MyISAM存储引擎。如果您的数据库主要使用InnoDB存储引擎,可以将这个值设置为较小的值或完全关闭(设置为0)。
query_cache_size
:查询缓存用于存储SELECT语句和对应的结果。如果您的数据库经常更新数据,查询缓存可能会占用大量内存。在这种情况下,可以关闭查询缓存以减少内存占用。通过将query_cache_size
设置为0来关闭查询缓存。
查看字符集
查看数据库/表大小
查看表最后更新时间
2、事务和锁
确认表是否在被使用
查看当前运行的所有事务
INNODB_TRX
表中主要记录了当前正在执行的事务信息,包括只读事务。首先来看字段和字段所表示的含义:字段名称 | 字段含义 |
TRX_ID | InnoDB存储引擎内部的唯一事务ID |
TRX_WEIGHT | 事务权重(与事务修改的行数和锁定的行数有关),当两个事务执行发生死锁时,InnoDB会选择权重较低的事务进行回滚 |
TRX_STATE | 当前的事务执行状态,包括RUNNING, LOCK WAIT, ROLLING BACK, 以及COMMITTING,LOCK WAIT表示当前事务正等待某个锁的释放 |
TRX_STARTED | 事务开始时间,格式如2000-01-01 14:01:08 |
TRX_REQUESTED_LOCK_ID | 当前事务所等待的锁ID,该字段只有在状态为LOCK WAIT才有值,否则为NULL。可与 INNODB_LOCKS 通过LOCK_ID字段进行关联查询,获取更为详细的锁信息。 |
TRX_WAIT_STARTED | 当前事务等待锁的起始时间,在状态为LOCK WAIT时才有值,否则为NULL。 |
TRX_QUERY | 当前事务正在执行的SQL语句(不是事务所有的执行语句) |
TRX_OPERATION_STATE | 事务的当前操作状态,包括PREPARING, UPDATING, DELETING, COMMITTING以及NULL,该字段在绝大部分情况下均为NULL,捕捉某一事务的瞬间执行状态还是比较困难的(除非是大事务) |
TRX_TABLES_IN_USE | 正在执行的SQL语句所操作的表数量,是一个动态变化值,通常很难观测 |
TRX_TABLES_LOCKED | 当前事务在各个表中添加行锁的表数量 |
TRX_LOCK_STRUCTS | 当前事务持有的锁数量 |
TRX_LOCK_MEMORY_BYTES | 当前事务中锁结构的内存总占用 |
TRX_ROWS_LOCKED | 当前事务锁住的近似数据总行数 |
TRX_ROWS_MODIFIED | 当前事务插入、修改的总行数 |
TRX_CONCURRENCY_TICKETS | 表示当前事务在换出之前所能做的工作之和 |
TRX_ISOLATION_LEVEL | 当前事务隔离级别,包括READ UNCIMMITTED、READ COMMITTED、READ REPEATABLE以及SERIALIZABLE |
TRX_UNIQUE_CHECKS | 当前事务是否开启唯一性检查 |
TRX_FOREIGN_KEY_CHECKS | 当前事务是否开启外键检查 |
TRX_LAST_FOREIGN_KEY_ERROR | 当前事务执行时最后发生的外键错误 |
TRX_ADAPTIVE_HASH_LATCHED | 当前事务是否锁定了自适应哈希索引 |
在这20多个字段中,较为重要的包括事务ID,事务执行状态,事务等待锁的起始时间,事务锁定的近似总行数。
查看当前出现的锁
- 在 MySQL 5.5 以上、5.7.14以下的版本中,用户可以通过
information_schema
下的INNODB_TRX
、INNODB_LOCKS
以及INNODB_LOCK_WAITS
这三张表简单地监控并分析可能存在的锁问题。
- 在 MySQL 8.0 版本中,则需要使用
performance_schema
下的data_locks
以及data_lock_waits
获取相关的锁以及锁等待信息。
- MySQL版本在 5.7.14 到 8.0 之间的用户,只能通过其它手段间接的获取上述信息。
以 MySQL 8.0 版本的
performance_schema.data_locks
为例字段名称 | 字段含义 |
ENGINE | 申请或持有锁的存储引擎类型 |
ENGINE_LOCK_ID | 存储引擎内部的锁ID,该值会发生动态变化,外部系统不应该依赖该值 |
ENGINE_TRANSACTION_ID | 持有锁的事务ID,与INNODB_TRX中的TRX_ID对应 |
THREAD_ID | 持有锁的线程ID |
EVENT_ID | 事件ID,该字段将于下方进行详细描述 |
OBJECT_SCHEMA | 锁所在的schema(database) |
OBJECT_NAME | 锁所在的表名称 |
PARTITION_NAME | 锁所在分片名称 |
SUBPARTITION_NAME | 锁所在的子分片名称 |
INDEX_NAME | 被添加锁的索引名称 |
OBJECT_INSTANCE_BEGIN | 锁的内存空间起始地址 |
LOCK_TYPE | 锁类型,包含TABLE和RECORD |
LOCK_MODE | 锁的模式,包括S,X,IS,IX,AUTO_INC以及UNKNOWN |
LOCK_STATUS | 锁的状态,InnoDB引擎中包括GRANTED(已添加)和WAITING(等待中) |
LOCK_DATA | 锁覆盖的范围,该字段将于下方详细描述 |
查看由于锁占用导致等待的表
data_lock_waits
相比于INNODB_TRX
以及data_locks
而言则要更复杂一些,该表实际上是一个ManyToMany的关系表,记录了data_locks
中锁之间的等待以及依赖关系,同时也记录了锁所对应的事务/会话信息。字段名称 | 字段含义 |
ENGINE | 存储引擎类型 |
REQUESTING_ENGINE_LOCK_ID | 存储引擎内锁ID,对应于data_locks表中的ENGINE_LOCK_ID |
REQUESTING_ENGINE_TRANSACTION_ID | 存储引擎内事务ID |
REQUESTING_THREAD_ID | 线程ID |
REQUESTING_EVENT_ID | 事件ID |
REQUESTING_OBJECT_INSTANCE_BEGIN | 锁的内存空间起始地址 |
BLOCKING_ENGINE_LOCK_ID | 等待释放的锁ID |
BLOCKING_ENGINE_TRANSACTION_ID | 等待结束的事务ID |
BLOCKING_THREAD_ID | 等待结束的线程ID |
BLOCKING_EVENT_ID | 等待结束的事件ID |
BLOCKING_OBJECT_INSTANCE_BEGIN | 等待结束的锁的内存空间起始地址 |
![notion image](https://minsonlee.github.io/images/article/mysql-data-lock/mysql8.0-data-lock-waits.png?t=3cfec5c0-ade5-4bcf-878a-db1d6f1b6435)
查看事务等待锁的超时时间
当一个事务的等待时间超过该值后,就对这个事务进行回滚,于是锁就释放了,另一个事务就可以继续执行了。在 InnoDB 中,参数
innodb_lock_wait_timeout
是用来设置超时时间的,默认值时 50 秒。开启主动死锁检测
主动死锁检测在发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数
innodb_deadlock_detect
设置为 on,表示开启这个逻辑,默认就开启。3、Mysql日志
查看慢日志参数
- slow_query_log:慢日志开关,默认为OFF关闭
- slow_query_log_file:慢日志保存文件;
- long_query_time:慢日志判断阀值,执行时长超过此值的sql都将记录入慢日志文件;
- slow_launch_time:此值不是慢日志的判断阀值,注意区分,好像是废弃的;
启用慢日志
方法一:全局变量设置,只在当前会话生效。
方法二:配置文件设置,然后重启Mysql服务
修改配置文件
my.cnf
,在 [mysqld]
的下方加入查看慢日志记录
执行超过时长阀值(默认10s)的sql语句
此时查看
slow_query_log_file
文件,多出如下记录:在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具
mysqldumpslow
。使用 mysqldumpslow --help
来查看用法常见用法
- Author:mcbilla
- URL:http://mcbilla.com/article/10985c7d-7c1d-80bf-b6fb-e8a40f91ba99
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts