登录
MySQL参数一键配置脚本: 有效提升数据库性能
2024年12月23日 03:15
我一直是自己租用VPS服务器，然后搭建各种服务，比如博客就是Apache2+MySQL数据库。一般来说就是默认参数，没有去管，不过最近发现MySQL的性能参数都很保守，不能发挥整个服务器的性能。
然后我就网上搜索了一下，根据参数配置建议，用ChatGPT写了以下Python和BASH脚本。只需要在需要优化的服务器上，跑一下该脚本，然后就会显示参数配置，然后直接把参数添加到MySQL数据库配置参数文件上： /etc/mysql/mysql.conf.d/mysqld.cnf
然后运行: service mysql restart 重启MySQL服务器。
运行了几周，发现效果很好，博客反应速度也快了很多，这很大原因是根据了内存增加了MySQL缓存大小。
Python脚本优化MySQL数据库参数
把下面的Python脚本存成 mysql_config.py 然后运行 python3 mysql_config.py
def get_total_ram():
with open('/proc/meminfo', 'r') as f:
for line in f:
if line.startswith("MemTotal:"):
total_ram_kb = int(line.split()[1])
return total_ram_kb * 1024 # 转换为字节（bytes）
return 0 # 如果未找到 MemTotal，则返回 0
def calculate_mysql_settings():
# 获取总内存（以字节为单位）
total_ram = get_total_ram()
# 根据总内存（以字节为单位）计算 MySQL 配置
innodb_buffer_pool_size = int(total_ram * 0.3) # 使用内存的 30%
key_buffer_size = min(total_ram * 20 // 100, 512 * 1024 * 1024) # 使用内存的 20%，最大限制为 512MB
sort_buffer_size = min(total_ram * 25 // 1000, 4 * 1024 * 1024) # 使用内存的 0.25%，最大限制为 4MB
read_rnd_buffer_size = min(total_ram * 625 // 100000, 512 * 1024) # 使用内存的 0.0625%，最大限制为 512KB
tmp_table_size = max_heap_table_size = min(total_ram * 5 // 100, 64 * 1024 * 1024) # 使用内存的 5%，最大限制为 64MB
join_buffer_size = min(total_ram * 2 // 1000, 4 * 1024 * 1024) # 使用内存的 0.2%，最大限制为 4MB
table_open_cache = min(400 + (total_ram // 64), 2000) # 根据内存动态计算，最大限制为 2000
thread_cache_size = min(total_ram * 15 // 1000, 100) # 使用内存的 1.5%，最大限制为 100
innodb_log_buffer_size = min(total_ram * 5 // 100, 16 * 1024 * 1024) # 使用内存的 5%，最大限制为 16MB
# 以字节为单位打印配置
print(f"MySQL 配置（基于总内存 {total_ram / (1024 * 1024):.2f} MB）:")
print("将以下内容添加到 /etc/mysql/mysql.conf.d/mysqld.cnf 的末尾\n")
print(f"innodb_buffer_pool_size = {innodb_buffer_pool_size}")
print(f"key_buffer_size = {key_buffer_size}")
print(f"sort_buffer_size = {sort_buffer_size}")
print(f"read_rnd_buffer_size = {read_rnd_buffer_size}")
print(f"tmp_table_size = {tmp_table_size}")
print(f"max_heap_table_size = {max_heap_table_size}")
print(f"join_buffer_size = {join_buffer_size}")
print(f"table_open_cache = {table_open_cache}")
print(f"thread_cache_size = {thread_cache_size}")
print(f"innodb_log_buffer_size = {innodb_log_buffer_size}")
# 打印自定义设置
print("expire_logs_days = 3")
print("max_binlog_size = 100M")
if __name__ == "__main__":
calculate_mysql_settings()
会打印出类似以下的配置：
innodb_buffer_pool_size = 626468044 key_buffer_size = 417645363 sort_buffer_size = 4194304 read_rnd_buffer_size = 524288 tmp_table_size = 67108864 max_heap_table_size = 67108864 join_buffer_size = 4176453 table_open_cache = 2000 thread_cache_size = 100 innodb_log_buffer_size = 16777216 expire_logs_days = 3 max_binlog_size = 100M
添加到MySQL的配置文件：/etc/mysql/mysql.conf.d/mysqld.cnf 然后重启数据库即可：service mysql restart
BASH脚本优化MySQL数据库参数
以下是完成同样功能的BASH脚本。
#!/bin/bash
# 获取总内存大小（以字节为单位）
get_total_ram() {
# 从 /proc/meminfo 中提取总内存（以 kB 为单位）
total_ram_kb=$(awk '/^MemTotal:/ {print $2}' /proc/meminfo)
if [[ -z "$total_ram_kb" ]]; then
echo 0 # 如果未找到 MemTotal，则返回 0
else
echo $((total_ram_kb * 1024)) # 将 kB 转换为字节
fi
}
# 根据总内存大小计算 MySQL 配置
calculate_mysql_settings() {
# 获取总内存（以字节为单位）
total_ram=$(get_total_ram)
# 计算 MySQL 配置参数
innodb_buffer_pool_size=$((total_ram * 30 / 100)) # 使用内存的 30%
key_buffer_size=$(($((total_ram * 20 / 100)) < $((512 * 1024 * 1024)) ? $((total_ram * 20 / 100)) : $((512 * 1024 * 1024)))) # 使用内存的 20%，最大限制为 512MB
sort_buffer_size=$(($((total_ram * 25 / 1000)) < $((4 * 1024 * 1024)) ? $((total_ram * 25 / 1000)) : $((4 * 1024 * 1024)))) # 使用内存的 0.25%，最大限制为 4MB
read_rnd_buffer_size=$(($((total_ram * 625 / 100000)) < $((512 * 1024)) ? $((total_ram * 625 / 100000)) : $((512 * 1024)))) # 使用内存的 0.0625%，最大限制为 512KB
tmp_table_size=$((total_ram * 5 / 100 < 64 * 1024 * 1024 ? total_ram * 5 / 100 : 64 * 1024 * 1024)) # 使用内存的 5%，最大限制为 64MB
max_heap_table_size=$tmp_table_size # 临时表大小等于最大堆表大小
join_buffer_size=$(($((total_ram * 2 / 1000)) < $((4 * 1024 * 1024)) ? $((total_ram * 2 / 1000)) : $((4 * 1024 * 1024)))) # 使用内存的 0.2%，最大限制为 4MB
table_open_cache=$(($((400 + total_ram / 64)) < 2000 ? $((400 + total_ram / 64)) : 2000)) # 根据内存动态计算，最大限制为 2000
thread_cache_size=$(($((total_ram * 15 / 1000)) < 100 ? $((total_ram * 15 / 1000)) : 100)) # 使用内存的 1.5%，最大限制为 100
innodb_log_buffer_size=$(($((total_ram * 5 / 100)) < $((16 * 1024 * 1024)) ? $((total_ram * 5 / 100)) : $((16 * 1024 * 1024)))) # 使用内存的 5%，最大限制为 16MB
# 打印配置（以字节为单位）
echo "MySQL 配置（基于总内存 $((total_ram / (1024 * 1024))) MB）:"
echo "将以下内容添加到 /etc/mysql/mysql.conf.d/mysqld.cnf 的末尾"
echo
echo "innodb_buffer_pool_size = $innodb_buffer_pool_size"
echo "key_buffer_size = $key_buffer_size"
echo "sort_buffer_size = $sort_buffer_size"
echo "read_rnd_buffer_size = $read_rnd_buffer_size"
echo "tmp_table_size = $tmp_table_size"
echo "max_heap_table_size = $max_heap_table_size"
echo "join_buffer_size = $join_buffer_size"
echo "table_open_cache = $table_open_cache"
echo "thread_cache_size = $thread_cache_size"
echo "innodb_log_buffer_size = $innodb_log_buffer_size"
echo
echo "expire_logs_days = 3" # 日志过期天数设置为 3 天
echo "max_binlog_size = 100M" # 最大二进制日志大小设置为 100M
}
# 主函数调用
calculate_mysql_settings
需要注意的是，我在脚本后面加入了一些我自定义的配置，根据需求自行修改即可。在配置文件里，后面定义的会覆盖前面的，这就是为什么要添加到文件尾的原因。
其中最关键的配置 innodb_buffer_pool_size 我设置为使用当前内存的30%，如果服务器只有数据库/博客这个功能，可以适当的提高比例，比如60%-80%。
英文：Python/Bash Script to Print the Optimized Parameters for MySQL Servers
运维/DevOps
- MySQL参数一键配置脚本: 有效提升数据库性能
- 把 MySQL 中的 MyISAM 表格转换成 InnoDB 的PHP小工具
- 通过 MySQLTuner 来检查数据库配置
- 升级到 PHP 5.5 之后内置 OpCache 加速缓存
- VPS低配置服务器优化的一些方法
- 如何在 Linux 下 列出最耗资源的进程 (BASH 脚本)
- 在LINUX下循环备份的方法
- 更改Wordpress博客全站的评论名称
- 几条Wordpress 博客 有用的 SQL 语句
扫描二维码，分享本文到微信朋友圈
The post MySQL参数一键配置脚本: 有效提升数据库性能 first appeared on 小赖子的英国生活和资讯.
相关文章：
- 步步高学生电脑上 Basic 编程语言 peek 用法示例 步步高学生电脑 是8位FC机的经典之作.它上面的BASIC有三个版本 1.0, 2.0 和 2.1 2.1 版本有个在线帮助,实际上是 help.cmd 1.0 是用 Esc 键退回到 DOS 的,...
- 一张图告诉你北京的雾霾有多严重 一北京的朋友朋友圈发的: 左上为全新口罩;右上为全新口罩本周一到周五每天室外戴20分钟左右;左下为全新口罩今早室外+公交车戴一个半小时;右下为全新口罩今早开车戴一小时左右. 还有这图 空气污染 – 红色的是严重的.中国,尤其是华北地区,是全球最红的地区,没有”之一”. 本文一共 113 个汉字, 你数一下对不对. 一张图告诉你北京的雾霾有多严重. (AMP 移动加速版本) 赞赏我的几个理由. ¥...
- 你给SteemIt中文微信群拖后腿了么? 这年头不缺算法, 就缺数据. 这两天花了很多时间在整API上, 整完之后自己用了一下还觉得真是挺方便的. 今天就突然想看一看自己是否给大家拖后腿了, 于是调用每日中文区微信群排行榜单的API, 刷刷拿着 NodeJs 练手: 1 2 3 4 5 6...
- 穷举算法的应用 – 去除EXCEL文件中的保护 EXCEL 是可以用密码来保护的. 比如 这个EXCEL 就用了密码保护. 打开EXCEL文件 你会注意到 无法编辑 无法查看宏(VBA)的代码. 去除保护很简单 第一步先编辑宏 VBA 把下面的VBA代码拷贝到VBA编辑器里 并按下F5运行 1...
- 谈谈 Utopian 成立公司 就在刚刚 Utopian 的老板 @elear 在 帖子和 discord 上宣布在 意大利成立 Utopian 公司. 可喜可贺! 这开始只是 Steem 上的一个小项目,...
- 步步高多媒体学生电脑 汇编程序设计 – 1 英文同步 90年代后期步步高生产的软驱一号(又称步步高多媒体学生电脑)和98型学生电脑都带了软驱,一按电源件, 都从软盘启动(98型可以从内置的电子盘启动) 步步高提供了直接在学习机上写汇编开发的工具 BASM. BASM 可以用来写 6502 汇编,并可以编译成 CMD 小型可执行程序 不支持 EXC 程序. CMD...
- 按揭贷款(房贷,车贷) 每月还贷计算器 去年给银行借了17万英镑 买了20万7500英镑的房子, 25年还清. 前2年是定率 Fix Rate 的合同 (年利率2.49%). 每个月大概是还 700多英镑. 有很多种还贷的计算方式, 定率/每月固定 是比较常用的. 简单来说就是 每个月交的钱是...
- 舍得给员工培训的公司是好公司 最近出差比较多, 很多人问我都干嘛去. 各种开会, 各种培训. 公司从剑桥一个软件公司请了一个专业的软件专家来做软件工程上的顾问. 我是 R&D 研发经理, 所以很自然的就要和他经常讨论, 然后目标是把当前公司的软件开发流程给完善, 提高, 把以前做的不对的得整对了. 培训的内容很多, 让我觉得以前公司完全就是在瞎搞, 什么...
