mysql常用运维命令

mysql常用运维命令

引擎状态

SHOW ENGINE INNODB STATUS;

SEMAPHORES信号量优化

引擎状态输出结果SEMAPHORES解读

reservation为数据库线程向操作系统申请的量，signal为释放的量。输出例

OS WAIT ARRAY INFO: reservation count 96852
OS WAIT ARRAY INFO: signal count 82683

修改操作系统信号量

# 查看操作系统当前信号量
cat /proc/sys/kernel/sem

• 输出结果例：32000 1024000000 500 32000
• 输出结果解读：每个信号量集允许的最大信号量数量；系统范围内允许的最大信号量数；单次系统调用中可以操作的最大信号量数量；系统范围内允许的最大信号量集数量

在数据库中信号量申请值过高说明可能有大量并发，如果超出第一列的值，可以尝试修改/etc/sysctl.conf参数kernel.sem调高上限，例kernel.sem = 64000 1024000000 500 64000
修改后执行sysctl -p应用更改。如果出现sysctl: setting key "kernel.sem": Numerical result out of range提示说明值设定超出系统支持的范围，可以尝试逐步调小

连接

查看当前所有连接

-- 查看当前所有连接
SHOW FULL PROCESSLIST;

说明：

• 关注State状态列，是否有锁。如果大量状态是waiting for handler commit检查磁盘是否占满
• 关注Time耗时列，是否有慢查询
• 关注Command列，如果存在大量Sleep且对应Time列持续很长，说明存在大量空闲连接会占用cpu。建议在客户端应用层修改连接池配置，或在服务端数据库调整wait_timeout和interactive_timeout参数关闭。

查看最大连接数

-- 查看数据库设置的最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 临时修改最大连接数
-- set global max_connections=200;

-- 查看历史连接记录
-- 关注Max_used_connections项，历史最大连接数，可根据该值调整配置
-- 关注Max_used_connections_time项，历史最大连接数的时间
show status like '%connection%';

查看慢查询

开启慢查询统计可能会导致cpu占用升高

show variables like '%slow_query_log%'

锁

-- 查看当前锁统计记录
show status like 'innodb_row_lock%'; 

-- 查看当前锁线程
SELECT * FROM performance_schema.data_locks;
-- 查看当前锁等待线程
SELECT * FROM performance_schema.data_lock_waits;

-- 查看当前连接和sql。根据死锁的thread_id线程id，执行kill -9 <pid> 杀死对应的proccesslist_id进程id
SELECT b.processlist_id,a.thread_id,a.sql_text FROM
performance_schema.events_statements_current a, performance_schema.threads b
WHERE a.thread_id=b.thread_id;

配置

查看缓存配置

mysql 8.0以后已经废弃。开启可能会导致cpu占用升高

show variables like'%query_cache%';
-- 关闭缓存
-- set session uery_cache_type=off;

问题和排查思路

数据库cpu高占用排查

# 找出mysql 的进程号
ps -ef | grep mysql

# 根据进程号，找出占用CPU靠前的线程号
top -H -p <mysqld进程id>

-- 查看高cpu占用线程对应sql
SELECT
    a. USER,
    a. HOST,
    a.db,
    b.thread_os_id,
    b.thread_id,
    a.id processlist_id,
    a.command,
    a.time,
    a.state,
    a.info
FROM
    information_schema.PROCESSLIST a,
    performance_schema.threads b
WHERE
    a.id = b.processlist_id
AND b.thread_os_id = <线程id>;