Mac 检查mysql命令

转载

mob64ca1418736f 2024-12-01 20:42:53

文章标签 Mac 检查mysql命令 mysql 数据库 MySQL 连接数 文章分类 MySQL 数据库

一、正在执行的线程查看

二、查看最大链接数

三、事务相关

四、日志刷新导致IO过高

五、临时表创建过多导致IO过高

六、有频繁的全表扫描的sql

七、大事务写Binlog导致实例I/O高

八、DDL语句导致实例I/O高

一、正在执行的线程查看

show processlist;

+----+------+--------------------+-----------+---------+------+-------------+------------------+
| Id | User | Host               | db        | Command | Time | State       | Info             |
+----+------+--------------------+-----------+---------+------+-------------+------------------+
| 36 | root | 172.16.100.19:7954 | tpcc_test | Sleep   |  456 |             | NULL             |
| 37 | root | 172.16.100.19:7969 | tpcc_test | Sleep   |  456 |             | NULL             |
| 42 | root | localhost          | NULL      | Query   |    0 | System lock | show processlist |
| 43 | root | 10.0.102.204:49224 | employees | Sleep   |   12 |             | NULL             |
+----+------+--------------------+-----------+---------+------+-------------+------------------+
4 rows in set (0.00 sec)

ID：连接标识。这个值和INFORMATION_SCHEMA.PROCESSLIST表的ID列，以及PERFORMANCE_SCHEMA中的threads中的process_id值是相同的。
time：线程已经在当前状态的时间。

kill线程

与MySQL服务器每个链接都在一个单独的线程中运行。可以使用如下语句杀死一个线程。

kill [connection| query] processlist_id connection: 与kill processlist_id相同；中断连接正在执行的任何语句之后，中断连接。 query：中断连接正在执行的语句，但是保持本身的连接。

注意：

这里需要注意的就是如果出现大量的sleep进程的话，并且时间很长的话，这种都是链接的客户端在使用完链接没有close造成的。这里是需要调整的属性 wait_timeout 就是 sleep 连接最大存活时间，默认是 28800 s，换算成小时就是 8 小时，相当于今天上班以来所有建立过而未关闭的连接都不会被清理。

这里说明一下，下面的时间单位是秒。

执行命令：

show global variables like '%wait_timeout';


set global wait_timeout=250;

实用小sql(20221009)

-- 按照客户端IP分组，看哪个客户端的连接数最多
select client_ip, count(client_ip) as client_num
from (select substring_index(host, ':', 1) as client_ip
      from `information_schema`.processlist) as connect_info
group by client_ip
order by client_num desc;

-- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select *
from `information_schema`.processlist
where Command != 'Sleep'
order by Time desc;

-- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';')
from `information_schema`.processlist
where Command != 'Sleep'
  and Time > 300
order by Time desc;

二、查看最大链接数

show variables like '%max_connection%'; 查看最大连接数
set global max_connections=1000;        重新设置最大连接数

mysql> show status like  'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 32    |
| Threads_connected | 10    |
| Threads_created   | 50    |
| Threads_rejected  | 0     |
| Threads_running   | 1     |
+-------------------+-------+
5 rows in set (0.00 sec)

Threads_connected ：这个数值指的是打开的连接数.

Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值.

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

三、事务相关

# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Lock wait timeout exceeded：后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。

Dead Lock：两个事务互相等待对方释放相同资源的锁，从而造成的死循环，就会引发这个异常。

还有一个要注意的是innodb_lock_wait_timeout与lock_wait_timeout也是不一样的。

innodb_lock_wait_timeout：innodb的dml操作的行级锁的等待时间

lock_wait_timeout：数据结构ddl操作的锁的等待时间

那么如何查看innodb_lock_wait_timeout的具体值：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'

ps. 注意global的修改对当前线程是不生效的，只有建立新的连接才生效。

mysql官方文档，其他的一堆命令

四、日志刷新导致IO过高

可能存在MySQL在日志在每次事务提交时，都会将其写入并刷新到磁盘，造成磁盘IO的高占用。如果所在磁盘是机械磁盘的话，可能io会更高。

通过在MySQL命令行运行以下命令：

show variables like 'sync_binlog';

可以看到：sync_binlog 的值为1。

该值意味着：启用在提交事务之前将二进制日志同步到磁盘。这是最安全的设置，但是会造成磁盘的较高占用。

show variables like 'innodb_flush_log_at_trx_commit';

可以看到：innodb_flush_log_at_trx_commit 的值为1。

该值意味着：日志会在每次事务提交时写入并刷新到磁盘。

Mac 检查mysql命令_MySQL

对于设置 0 和 2，不能 100% 保证每秒一次刷新。

注意：这种解决办法是在牺牲数据库安全的前提下，提高磁盘的性能！！！更改配置可能会带来更高的数据丢失风险！！！

五、临时表创建过多导致IO过高

ps：临时表创建过多也会导致内存的使用过高哦！

命令查看：

mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 22    |
| Created_tmp_tables      | 8     |
+-------------------------+-------+

多执行几次，如果发现tmp_files和tmp_disk_tables的值在增长，证明在大量的创建临时文件及磁盘临时表，则会引起磁盘IO过高。

常见的情况会导致频繁建立临时表

1. UNION查询;

2. insert into select ...from ...

3. ORDER BY和GROUP BY的子句不一样时;

4.数据表中包含blob/text列

六、有频繁的全表扫描的sql导致IO过高

查看sql的全表扫描次数：

show global status like '%Select_scan%';

频繁的全表扫描也会引起数据库的io过高。

七、大事务写Binlog导致实例I/O高

现象事务只有在提交时才会写Binlog文件，如果存在大事务，例如一条Delete语句删除大量的行，可能会产生几十GB的Binlog文件，Binlog文件刷新到磁盘时，会造成很高的I/O吞吐。
解决方案建议尽量将事务拆分，避免大事务和降低刷新磁盘频率。

八、DDL语句导致实例I/O高

现象 DDL语句可能会重建表空间，期间会扫描全表数据、创建索引排序、刷新新表产生的脏页，这些都会导致大量的I/O吞吐。另外一种场景是删除大表造成的I/O抖动。

九、 MySQL活跃线程数高

活跃线程数或活跃连接数是衡量MySQL负载状态的关键指标，通常来说一个比较健康的实例活跃连接数应该低于10，高规格和高QPS的实例活跃连接数可能20、30，如果出现几百、上千的活跃连接数，说明出现了SQL堆积和响应变慢，严重时会导致实例停止响应，无法继续处理SQL请求。

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 31    |
| Threads_connected | 239   |
| Threads_created   | 2914  |
| Threads_running   | 4     |
+-------------------+-------+

排查慢SQL堆积问题

首先通过show processlist;命令查看是否有慢SQL。如果有很多扫描行数太多的SQL，容易导致活跃连接数升高。

排查表缓存（Table Cache）问题