MySQL在Linux环境下的性能调优

📊 MySQL性能基准测试：知己知彼

建立性能基线的关键指标

# 核心性能指标监控脚本
#!/bin/bash
echo "=== MySQL Performance Baseline ==="
mysql -e "SHOW GLOBAL STATUS LIKE 'Questions';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Uptime';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';"

QPS计算公式：QPS = (Questions - 上次Questions值) / 时间间隔

性能测试工具组合拳

1. sysbench - 全方位压测之王

# 准备测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua \--mysql-host=localhost \--mysql-user=test \--mysql-password=password \--mysql-db=testdb \--tables=10 \--table-size=1000000 \prepare# 执行混合读写测试
sysbench /usr/share/sysbench/oltp_read_write.lua \--mysql-host=localhost \--mysql-user=test \--mysql-password=password \--mysql-db=testdb \--tables=10 \--table-size=1000000 \--threads=16 \--time=300 \--report-interval=10 \run

2. mysqlslap - MySQL官方利器

# 模拟100个并发用户，执行1000次查询
mysqlslap --user=root --password=password \--host=localhost \--concurrency=100 \--iterations=1000 \--create-schema=testdb \--query="SELECT * FROM users WHERE id = FLOOR(RAND() * 100000);" \--verbose

⚙️ Linux系统层面优化：基础决定上层建筑

内核参数调优黄金配置

# /etc/sysctl.conf 优化配置
# 网络层优化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 5000
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0# 内存管理优化
vm.swappiness = 1          # 尽量避免使用swap
vm.dirty_ratio = 15        # 脏页占用内存比例
vm.dirty_background_ratio = 5# 文件系统优化
fs.file-max = 1000000      # 系统最大文件句柄数

磁盘I/O优化策略

1. 文件系统选择与挂载参数

# ext4文件系统最佳实践
mount -o noatime,data=writeback,barrier=0,nobh /dev/sdb1 /var/lib/mysql# XFS文件系统（推荐用于大数据量）
mount -o noatime,attr2,inode64,noquota /dev/sdb1 /var/lib/mysql

2. I/O调度器优化

# 对于SSD，使用deadline调度器
echo deadline > /sys/block/sdb/queue/scheduler# 对于机械硬盘，使用cfq调度器
echo cfq > /sys/block/sda/queue/scheduler

内存分配策略

# 计算合理的buffer pool大小
# 规则：物理内存的70-80%分配给InnoDB
total_mem=$(free -m | awk 'NR==2{print $2}')
buffer_pool_size=$((total_mem * 75 / 100))
echo "建议InnoDB buffer pool大小: ${buffer_pool_size}M"

🔧 MySQL配置文件深度调优

my.cnf黄金配置模板

[mysqld]
# 基础配置
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql
tmpdir = /tmp# 连接配置
max_connections = 1000
max_connect_errors = 10000
connect_timeout = 60
wait_timeout = 28800
interactive_timeout = 28800# InnoDB引擎优化 - 核心重点
innodb_buffer_pool_size = 6G           # 内存的75%
innodb_buffer_pool_instances = 8       # CPU核心数
innodb_log_file_size = 1G              # 日志文件大小
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2     # 性能优先设置
innodb_flush_method = O_DIRECT         # 避免双重缓冲
innodb_file_per_table = 1
innodb_io_capacity = 2000              # SSD设置更高
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0# 查询缓存（5.7及以下版本）
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M# 临时表优化
tmp_table_size = 256M
max_heap_table_size = 256M# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1# 二进制日志
log-bin = mysql-bin
binlog_format = ROW
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 7# 复制优化
relay_log_recovery = 1
slave_skip_errors = 1062,1053,1146

动态参数调优技巧

-- 运行时调整关键参数
SETGLOBAL innodb_buffer_pool_size =8*1024*1024*1024;  -- 8GB
SETGLOBAL max_connections =2000;
SETGLOBAL innodb_io_capacity =3000;
SETGLOBAL query_cache_size =512*1024*1024;  -- 512MB-- 查看当前配置
SHOW VARIABLES LIKE'innodb_buffer_pool_size';
SHOW VARIABLES LIKE'max_connections';

🔍 SQL查询优化：从根本解决性能问题

慢查询分析神器

1. 慢查询日志分析

# 使用mysqldumpslow分析慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log  # 按查询次数排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  # 按平均时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log  # 按锁定时间排序

2. pt-query-digest深度分析

# 安装Percona Toolkit
yum install percona-toolkit# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_query_analysis.txt

索引优化实战技巧

1. 复合索引设计原则

-- 错误示例：索引顺序不当
CREATE INDEX idx_wrong ON orders (status, create_time, user_id);-- 正确示例：遵循选择性递减原则
CREATE INDEX idx_correct ON orders (user_id, status, create_time);-- 分析索引使用情况
EXPLAIN SELECT*FROM orders 
WHERE user_id =12345AND status ='paid'
ORDERBY create_time DESC LIMIT 10;

2. 覆盖索引应用

-- 创建覆盖索引，避免回表操作
CREATE INDEX idx_covering ON users (email, status, create_time, username);-- 查询直接从索引获取数据
SELECT username FROM users 
WHERE email = 'user@example.com' AND status = 'active';

EXPLAIN执行计划解读

-- 详细执行计划分析
EXPLAIN FORMAT=JSON 
SELECT u.username, o.total_amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' AND o.create_time > '2024-01-01';

关键指标解读：

  • • type: 访问类型，system > const > eq_ref > ref > range > index > ALL

  • • key: 实际使用的索引

  • • rows: 扫描行数，越少越好

  • • Extra: 额外信息，"Using filesort"和"Using temporary"需要优化

🚨 故障诊断实战案例

Case 1: 连接数耗尽故障

故障现象：

ERROR 1040 (HY000): Too many connections

诊断过程：

-- 查看当前连接状态
SHOW PROCESSLIST;
SHOW STATUS LIKE'Threads_connected';
SHOW STATUS LIKE'Max_used_connections';
SHOW VARIABLES LIKE'max_connections';-- 分析连接来源
SELECT host, db, user, info, time
FROM information_schema.processlist 
WHEREtime>300;  -- 查找长时间运行的连接

解决方案：

# 临时增加连接数
mysql -e "SET GLOBAL max_connections = 2000;"# 优化连接池配置（应用层）
# connection_pool_size = 20
# max_idle_time = 300

Case 2: InnoDB锁等待超时

故障现象：

ERROR 1205 (HY000): Lock wait timeout exceeded

诊断脚本：

-- 查看当前锁等待情况
SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNERJOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNERJOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;-- 杀死阻塞事务
KILL 12345;  -- 替换为实际的thread_id

Case 3: 主从复制延迟

监控脚本：

#!/bin/bash
# 主从延迟监控脚本
while true; dodelay=$(mysql -h slave_host -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')if [ "$delay" -gt 60 ]; thenecho "WARNING: Replication delay is ${delay} seconds"# 发送告警fisleep 10
done

优化措施：

-- 从库优化配置
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL binlog_group_commit_sync_delay = 1000;

📈 监控告警体系建设

Prometheus + Grafana监控配置

# prometheus.yml MySQL监控配置
- job_name: 'mysql'static_configs:- targets: ['localhost:9104']scrape_interval: 15smetrics_path: /metrics

关键监控指标：

  • • 连接数使用率：mysql_global_status_threads_connected / mysql_global_variables_max_connections

  • • 缓冲池命中率：(mysql_global_status_innodb_buffer_pool_read_requests - mysql_global_status_innodb_buffer_pool_reads) / mysql_global_status_innodb_buffer_pool_read_requests

  • • 慢查询增长率：rate(mysql_global_status_slow_queries[5m])

自动化告警脚本

#!/bin/bash
# MySQL健康检查脚本
DB_HOST="localhost"
DB_USER="monitor"
DB_PASS="password"check_mysql_health() {# 检查MySQL是否运行if ! mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SELECT 1" > /dev/null 2>&1; thensend_alert "MySQL服务异常"return 1fi# 检查连接数connections=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW STATUS LIKE 'Threads_connected'" | awk 'NR==2{print $2}')if [ $connections -gt 800 ]; thensend_alert "MySQL连接数过高: $connections"fi# 检查主从状态slave_status=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')if [ "$slave_status" != "Yes" ]; thensend_alert "MySQL主从复制异常"fi
}send_alert() {message="$1"# 发送到钉钉/企业微信curl -X POST "webhook_url" -d "{\"text\": \"$message\"}"
}check_mysql_health

🛠️ 高可用架构最佳实践

MHA自动故障切换配置

# MHA Manager配置示例
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mhapassword
ssh_user=root
repl_user=repl
repl_password=replpassword
ping_interval=3
shutdown_script=/usr/local/bin/power_manager
master_ip_failover_script=/usr/local/bin/master_ip_failover[server1]
hostname=mysql-master
port=3306[server2]
hostname=mysql-slave1
port=3306
candidate_master=1[server3]
hostname=mysql-slave2
port=3306

ProxySQL读写分离配置

-- ProxySQL配置读写分离
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(0, '192.168.1.10', 3306, 1000),  -- 主库
(1, '192.168.1.11', 3306, 900),   -- 从库1
(1, '192.168.1.12', 3306, 900);   -- 从库2-- 配置查询规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*', 1, 1),        -- 读请求路由到从库
(2, 1, '^INSERT|UPDATE|DELETE.*', 0, 1);  -- 写请求路由到主库LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

💡 性能调优总结与建议

调优优先级排序

  1. 1. 硬件层面（投入产出比最高）

    • • 使用SSD存储

    • • 增加内存容量

    • • 使用万兆网卡

  2. 2. 操作系统层面

    • • 内核参数优化

    • • 文件系统选择

    • • I/O调度器调整

  3. 3. MySQL配置层面

    • • InnoDB参数调优

    • • 连接池配置

    • • 缓存参数设置

  4. 4. 应用层面

    • • SQL语句优化

    • • 索引设计优化

    • • 业务逻辑优化

避免的常见误区

❌ 误区1：盲目增加连接数上限
✅ 正确做法：分析连接使用模式，优化应用连接池

❌ 误区2：查询缓存设置过大
✅ 正确做法：MySQL 8.0已移除查询缓存，使用Redis替代

❌ 误区3：忽略慢查询日志分析
✅ 正确做法：定期分析慢查询，持续优化SQL

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/bicheng/91040.shtml
繁体地址，请注明出处：http://hk.pswp.cn/bicheng/91040.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

PyQt事件处理机制深度指南：超越信号与槽的底层掌控

PyQt事件处理机制深度指南：超越信号与槽的底层掌控

—— 5大核心策略实战案例，解锁GUI交互的底层密码 🔍 事件与信号槽的本质差异维度事件处理机制信号与槽机制抽象层级操作系统消息的原始封装对事件的高级封装应用场景控件行为定制/底层交互常规业务逻辑绑定执行顺序先于信号槽触发在事件处理完成后触发性…
阅读更多...
10_opencv_分离颜色通道、多通道图像混合

10_opencv_分离颜色通道、多通道图像混合

split() 通道分离 void cv::split(const Mat & src,Mat * mvbegin ) merge() 通道合并 void cv::merge(InputArrayOfArrays mv,OutputArray dst ) Mat::at()方法 Mat::at()方法返回一个引用到指定的数组元素。 注意是引用，相当于两者等价，也就是…
阅读更多...
Kotlin的datetime库

Kotlin的datetime库

kotlinx 是一组不是 Kotlin 标准库一部分，但非常实用的扩展项目集合。其中，kotlinx-datetime 是一个跨平台的 Kotlin 时间日期处理库。 如何在项目中使用该库 Gradle 项目中 在 repositories 块中添加 Maven Central 仓库： repositories {…
阅读更多...
基于模型蒸馏的大模型文案生成最佳实践

基于模型蒸馏的大模型文案生成最佳实践

背景 大语言模型在生成高质量文案方面表现优异，然而其巨大的计算资源消耗和存储需求，使得实际应用尤其是在资源受限场景中的应用充满挑战。企业在寻求高效的文案生成时，常常面临着在性能和资源之间权衡的困境。在这种背景下，模型…
阅读更多...
调用通义千问大模型实现流式对话

调用通义千问大模型实现流式对话

前言 我使用的是硅基流动中通义千问免费的大模型：我的技术栈使用的 Next14.2 全栈框架。 代码结构 需要使用的库： npm i ai openai目录结构： 基础测试页面 test-openai/page.tsx： use client;import { useChat } from ai/react;ex…
阅读更多...
如何搭建Linux环境下的flink本地集群

如何搭建Linux环境下的flink本地集群

第一步，搭建Linux环境 这里我使用的是 WSL2 安装前，先用管理员打开终端，执行以下三条命令，目的是开启安装 WSL2所需要的环境 //开启适用于windows的Linux子系统 dism.exe /online /enable-feature /featurename:Microsoft-Wind…
阅读更多...
算法：链表part02：24. 两两交换链表中的节点 + 19. 删除链表的倒数第 N 个结点 + 面试题 02.07. 链表相交

算法：链表part02：24. 两两交换链表中的节点 + 19. 删除链表的倒数第 N 个结点 + 面试题 02.07. 链表相交

24. 两两交换链表中的节点题目：https://leetcode.cn/problems/swap-nodes-in-pairs/description/ 讲解：https://programmercarl.com/0024.%E4%B8%A4%E4%B8%A4%E4%BA%A4%E6%8D%A2%E9%93%BE%E8%A1%A8%E4%B8%AD%E7%9A%84%E8%8A%82%E7%82%B9.html 复习可以先…
阅读更多...
【Linux学习】（11）进程的概念

【Linux学习】（11）进程的概念

前言在上一章我们知道了什么是进程，并简单了解了PCB。 本文我们将继续深入学习进程概念相关知识点： 学习进程状态，学会创建进程，掌握僵尸进程和孤儿进程，及其形成原因和危害了解进程调度，Linux进程优先级&a…
阅读更多...
UniappDay04

UniappDay04

1.登录模块-小程序快捷登录定义接口，封装 import { http } from /utils/httptype loginParams {code: stringencryptedData: stringiv: string } export const postLoginWxMinAPI (data: loginParams) > {return http({method: POST,url: /login/wxMin,data,})…
阅读更多...
NPM/Yarn完全指南：前端开发的“基石“与“加速器“

NPM/Yarn完全指南：前端开发的“基石“与“加速器“

开篇：当你第一次运行npm install时... "这node_modules文件夹怎么比我的项目代码还大100倍？！" —— 每个前端新手第一次看到node_modules时的反应都出奇地一致。别担心，今天我要带你彻底搞懂这个让项目"膨胀"的"罪魁祸首"，以及如何用NPM/Y…
阅读更多...
vue页面自定义滚动条

vue页面自定义滚动条

效果图实现思路 固定整个灰色滚动条的长度计算可滚动区域占整个可视视图的比例&#xff0c;来确定橙色块的长度监听页面滚动&#xff0c;计算橙色块向右偏移距离 主要代码 template&#xff1a; <div v-show"showBar" ref"barRef" class"scrollbar…
阅读更多...
企业级JWT验证最佳方案：StringUtils.hasText()

企业级JWT验证最佳方案：StringUtils.hasText()

在企业级Java开发中&#xff0c;判断JWT令牌是否有效的最全面且常用的方式是结合以下两种方法&#xff1a; ✅ 推荐方案&#xff1a;StringUtils.hasText(jwt)&#xff08;Spring框架&#xff09; import org.springframework.util.StringUtils;if (!StringUtils.hasText(jwt))…
阅读更多...
灵动画布：快手可灵 AI 推出的多人协作 AI 创意工作台

灵动画布：快手可灵 AI 推出的多人协作 AI 创意工作台

灵动画布&#xff1a;快手可灵 AI 推出的多人协作 AI 创意工作台 来源&#xff1a;Poixe AI 一、什么是灵动画布 灵动画布是快手旗下可灵 AI 于 2025 世界人工智能大会期间发布的全新创意工作台功能。该功能集无限可视化画布空间、多人实时协作及 AI 智能辅助于一体&#xf…
阅读更多...
【Linux篇】进程间通信：进程IPC

【Linux篇】进程间通信：进程IPC

目录 共享内存空间 共享内存是在用户空间还是内核空间&#xff1f;——用户空间 共享内存的生命周期 如何使用共享内存 共享内存的权限 共享内存是进程间通信中&#xff0c;速度最快的方式&#xff1a; 共享内存的缺点&#xff1a; 进程间通信标准&#xff1a; system …
阅读更多...
Kubernetes 存储入门

Kubernetes 存储入门

目录 Volume 的概念 Volume 的类型 通过 emptyDir 共享数据 编写 emptyDir 的 Deployment 文件 部署该 Deployment 查看部署结果 登录 Pod 中的第一个容器 登录 Pod 中的第二个容器查看 /mnt 下的文件 删除此 Pod 使用 HostPath 挂载宿主机文件 编写 Deployment 文件…
阅读更多...
深入理解Redission释放锁过程

深入理解Redission释放锁过程

lock.unlock();调用unlock方法&#xff0c;往下追Override public void unlock() {try {// 1. 执行异步解锁操作并同步等待结果// - 获取当前线程ID作为锁持有者标识// - unlockAsync()触发Lua脚本执行实际解锁// - get()方法阻塞直到异步操作完成get(unlockAsync(Thread.curre…
阅读更多...
四、计算机组成原理——第4章：指令系统

四、计算机组成原理——第4章：指令系统

目录 4.1指令系统 4.1.1指令集体系结构 4.1.2指令的基本格式 1.零地址指令 2.一地址指令 3.二地址指令 4.三地址指令 5.四地址指令 4.1.3定长操作码指令格式 4.1.4扩展操作码指令格式 4.1.5指令的操作类型 1.数据传送 2.算术和逻辑运算 3.移位操作 4.转移操作 …
阅读更多...
RAG面试内容整理-检索器与生成器的解耦架构

RAG面试内容整理-检索器与生成器的解耦架构

在RAG系统中，检索器（Retriever）与生成器（Generator）的解耦架构是实现灵活高效的关键设计。所谓解耦，即将检索相关文档和生成答案两个步骤分开，由不同的模块或模型负责。这种架构带来的直接好处是模块独立优化：我们可以针对检索任务微调或更换检索模型，而不必影响生成模…
阅读更多...
【2026毕业论文鸿蒙系统毕设选题】最新颖的基于HarmonyOS鸿蒙毕业设计选题汇总易过的精品毕设项目分享(建议收藏)✅

【2026毕业论文鸿蒙系统毕设选题】最新颖的基于HarmonyOS鸿蒙毕业设计选题汇总易过的精品毕设项目分享(建议收藏)✅

文章目录前言最新毕设选题&#xff08;建议收藏起来&#xff09;最新颖的鸿蒙毕业设计选题汇总100套易过的精品毕设项目分享毕设作品推荐&#x1f447;&#x1f447;&#x1f447;文未可免费咨询毕设相关问题&#xff0c;点赞留言可送系统源码&#x1f447;&#x1f447;&#…
阅读更多...
超全！Linux 面试 100 题精选解析：网络篇｜16 个 Linux 网络排查与配置必考题详解

超全！Linux 面试 100 题精选解析：网络篇｜16 个 Linux 网络排查与配置必考题详解

网络&#xff0c;是 Linux 系统的神经系统。 一台服务器再强大&#xff0c;没有网络连接也如孤岛。尤其在实际运维与面试场景中&#xff0c;“网络相关的问题”是高频重灾区&#xff0c;比如&#xff1a; IP 配置错乱&#xff0c;连不上公网DNS 无响应&#xff0c;域名解析失败…
阅读更多...
最新文章

Copyright @ 2022~2023