默认值才是王道：记一次MySQL服务器参数调优之旅

最新推荐文章于 2022-10-17 18:36:07 发布
最新推荐文章于 2022-10-17 18:36:07 发布
阅读量561 收藏
点赞数 1
分类专栏： # MySQL / MariaDB 文章标签： MySQL 参数调优 服务器调优 基准测试 my.cnf
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sweeper_freedoman/article/details/78942844
版权

【前言】

     最近入职一家新的公司维护MySQL，最开始的工作是配置调优。在研究了我们的业务和前任留下的配置文件后，做了若干基准测试，基于测试结果，对数据库做了一些简单优化。这里整理了部分自己写的测试报告分享一下。

 

【过程】

1.环境            
        ①Slave        
    64位CentOS服务器：        
    物理机2核4G；        
    MySQL2G内存池。        
        ②Master        
    64位CentOS服务器：        
    物理机8核32G；        
    MySQL4G内存池。        
            
2.工具            
    ①sysbench（https://github.com/akopytov/sysbench）：数据库和服务器系统基准测试工具。        
    ②tpcc-mysql(https://github.com/Percona-Lab/tpcc-mysql)：数据库复杂事务处理能力TPC-C指标测试工具。        
            
3.目标            
    MySQL性能优化。        
            
4.方式            
    对比法：先以原配置进行测试并记录测试结果；再修改部分数据库配置参数重新执行测试。每个项测试结束皆清除缓存并重启MySQL数据库，消除交叉影响。        
            
5.测试过程            
    ①随机读写测试(sysbench):        
    对一张100W条记录的表执行随机读写测试。        
    ②复杂事务处理能力(tpcc-mysql)：        
    对一个虚拟仓库销售供应商公司的数据库模型执行复杂事务处理能力测试。        
            
6.测试结果            
    本次测试基于原配置文件(当前线上在用版本)主要做了以下测试，具体的每份测试报告可以查看群里面的共享文件夹：        
    ①修改部分基础参数至MySQL默认值；        
    ②修改部分基础参数低于MySQL默认值；        
    ③修改MySQL事务隔离级别(基于原配置文件)；        
    ④修改InnoDB事务日志缓冲刷新机制(基于原配置文件)；        
    ⑤修改基于数据安全和高可用的部分参数；        
    ⑥综合修改。        
    测试结果如下(基于Slave测试数据)。        
            
        >>>>>>>>吞吐量
        
    如图所示，从最左边天蓝色到右边草绿色分别为原配置文件、修改部分基础参数至MySQL默认值、修改部分基础参数低于MySQL默认值、修改MySQL事务隔离级别(基于原配置文件)、        
    修改InnoDB事务日志缓冲刷新机制、修改基于数据安全和高可用的部分参数以及综合修改调整的测试结果：左边为事务总量(Throughput)；右边为其相应的TPS。以此可以简单看出：        
    ①MySQL基于数据安全和高可用的部分参数对吞吐能力有极大影响，若干倍级别，数据安全和读写性能是一对绝对的矛盾体；        
    ②事务隔离级别到了5.7版本对MySQL的性能已经几乎没有多大影响；        
    ③MySQL各种参数的默认值在大多数情况下都是最优解或接近最优解；        
    ④对当前线上在用配置文件进行一些非核心参数的微调，性能还是能有一定的上升，测试中TPS可以从590提高到680。        
            
        >>>>>>>>复杂事务处理能力
       
    复杂事务处理能力的测试情况基本上与吞吐量数据一致。        
    
    在处理复杂事务时MySQL两种隔离级别的表现。        
            
7.综合修改测试数据            
        >>>>>>>>Slave        

        >>>>>>>>Master        

            
8.测试结论配置意见            
    当前配置的主要问题：        
    ①部分基础参数值设置过大，造成系统资源消耗，如一些buffer/cache size的大小；        
    ②部分基础参数值设置不合理，影响数据库性能，如缓冲池实例数量；        
    ③数据库安全、数据安全和MySQL高可用的一些参数基本上都设置了比较低的级别，有事件丢失等潜在风险。        
            
    修改建议(之后会为Slave和Master配置文件升级分别写一个Shell脚本)：        
    ①部分参数根据测试结果可以考虑切回默认值；        
    ②部分参数根据物理机服务器配置做一些适当调整；        
    ③修改或增加一些对数据库性能基本上没有直接影响但保护数据库安全的配置；        
    ④数据安全和高可用配置的修改，对性能影响较大，可以暂时维持现状，其中复制库slave只是用于BI读库复制的潜在问题可以忽略，但是master有丢失事件的潜在风险，这是当前数据安全的最大隐患。        

【小结】

     本次调优最终主要是将若干参数的现有配置调回默认值或官方推荐的修改值，可以看出经过微调Master和Slave的性能都有所提升，服务器的硬件压力也有所减轻。以下是主要修改列表。

 

→ 基础设置：
参考MySQL内存计算器（http://www.mysqlcalculator.com/）。
	①.max_connections
客户端最大连接数。
MySQL默认151；线上配置1024。
考虑到实际连接并发数，先尝试降到500。
	②.max_user_connections
账户最大连接数，其实受全局变量“max_connections”控制。
MySQL默认0表示没有限制；线上配置1000。
建议切回默认值。
	③.wait_timeout
非交互式客户端连接系统回收之前的空闲等待时间（秒）。
MySQL默认28800（非Windows系统）；线上配置8640000。
建议先切回默认值。
	④.interactive_timeout
交互式客户端连接系统回收之前的空闲等待时间（秒）。
MySQL默认28800；线上配置8640000。
建议切回默认值。
	⑤.max_heap_table_size
MEMORY表最大长度。该参数和“tmp_table_size”共同起作用（取两者中的最小值为实际中的上限值）。
MySQL默认16MB；线上配置64MB。
建议切回默认值。
	⑥.tmp_table_size
临时表最大长度。该参数和“max_heap_table_size”共同起作用（取两者中的最小值为实际中的上限值）。
MySQL默认16MB；线上配置64MB。
建议切回默认值。
	⑦binlog_cache_size
事务执行过程中的二进制日志缓冲，每个客户端连接在执行事务时都会分配一块“binlog_cache_size”大小的内存。
MySQL默认32KB；线上配置2MB。
建议切回默认值。

→ 存储引擎
对MySQL默认存储引擎InnoDB若干设置的修改。
	①.innodb_buffer_pool_instances
InnoDB存储引擎内存池实例。
MySQL默认8个（innodb_buffer_pool_size < 1GB时默认1个）；线上配置8个。
MySQL官方推荐每个内存池实例应该分配1GB的内存，所以建议“innodb_buffer_pool_size”设置几G，
这个值就设置几个。【该系统参数不支持动态修改，所以修改在MySQL重启后才能启用。】
	②.transaction_isolation
事务隔离级别。MySQL默认是“REPEATABLE-READ”消除了“幻读”；Oracle和MS SQL Server默认是“READ-COMMITTED”。
线上配置READ-COMMITTED。
建议切回默认值。
	③.innodb_purge_threads
Undo回收线程数。
MySQL默认4（5.7.7版本以后）表示开启4个后台线程回收Undo；线上配置1。
维持现值。
	④.innodb_purge_batch_size
Purge线程一次批处理的Undo页数量。
MySQL默认300；线上配置32。
建议切回默认值。该参数只是用来测试和调优innodb_purge_threads用，MySQL官方不建议修改其默认值。
	⑤.innodb_page_cleaners
脏页刷新线程数。
MySQL默认4（5.7.7版本以后）；线上采用默认值。
建议将该值设置为“innodb_buffer_pool_instances”参数的设置值。
	⑥.innodb_log_buffer_size
InnoDB事务日志缓冲大小。
MySQL默认16MB（5.7.5版本以后）；线上配置64MB。
建议切回默认值。InnoDB事务日志刷新回磁盘的频率非常高（Master线程秒刷；或者配置事务提交时触发刷新），OLTP事务默认值足以支撑。
	⑦.innodb_change_buffer_max_size
Change Buffer可以使用InnoDB内存池的最大百分比。
MySQL默认25（25%）；线上采用默认值。
建议提升到35试试，线上写事务比较频繁。
	⑧.innodb_file_format
InnoDB表空间文件格式。
MySQL默认“Barracuda”（5.7.6版本以后）；线上采用默认值。
建议从配置文件里面去掉该配置行，该参数为一个版本升级用过渡参数，即将废弃。

→ 访问安全：
	①max_connect_errors
连接错误超过该次数设定后主机将被禁止继续访问数据库（FLUSH HOSTS命令解除限制）。
MySQL默认100；线上配置10000。
基于数据库安全考虑建议该参数切回默认值。

→ 数据安全和高可用配置（为了性能考虑暂时未上线）：
	①.innodb_flush_log_at_trx_commit
InnoDB事务日志缓冲刷新回磁盘触发选项。
MySQL默认1，表示事务每提交一次就刷新回磁盘，以确保完整的ACID；线上配置2，表示事务每提交一次刷新到操作系统（OS文件系统也有可能缓冲）。
文件缓冲（并不能保证写入磁盘文件）。
建议切回默认值，避免宕机故障丢失线上事务。
	②.sync_binlog
二进制日志缓冲刷新回磁盘触发选项。
MySQL默认1（5.7.6版本以后）表示同步磁盘写；线上配置0表示非同步写。
建议切回默认值，保证复制架构的高可用。

 

     因为服务器物理机和数据库实例比较多，手动修改配置文件也很容易出现误操作。最后写了一个小Shell脚本，然后每次停机维护的时候修改几台配置，慢慢迭代。

 

# !/bin/bash
# Author    : 蛙鳜鸡鹳狸猿
# create_ts : 2017年 12月 28日 星期四 09:47:09 CST
# program   : MySQL Master Config File Upgrade
# crontab   : o(>﹏<)o


### header parameters ceil ###
cnf_file=/etc/my.cnf
bak_file=/etc/my.cnf.$(date +%Y%m%d%H%M%S)
### header parameters floor ###


### function block ceil ###
# set backups
function setbackups(){
    cp ${cnf_file} ${bak_file}
    if [ $? == 0 ]; then
        echo -e "\n\(^o^)/ ${cnf_file} backup to ${bak_file} \(^o^)/\n"
        return 0
    else
        echo -e "\no(>﹏<)o my.cnf backup failed o(>﹏<)o\n"
        exit 1
    fi
}

# set threads
function setthreads(){
    cpus=$(lscpu | sed -ne '4p' | awk '{ print $2 }') && declare -i threads=${cpus}-1
    if [ $? == 0 ]; then
        echo -e "\n\(^o^)/ threads sets ${threads} \(^o^)/\n"
        return ${threads}
    else
        echo -e "\no(>﹏<)o threads sets failed o(>﹏<)o\n"
        exit 1
    fi
}

# set setbuffers
function setbuffers(){
    buffers=$(cat ${cnf_file} | grep ^innodb_buffer_pool_size | tr -cd "[[:digit:]]\n")
    if [ $? == 0 ]; then
        echo -e "\n\(^o^)/ buffers sets ${buffers} \(^o^)/\n"
        return ${buffers}
    else
        echo -e "\no(>﹏<)o buffers sets failed o(>﹏<)o\n"
        exit 1
    fi
}
### function block floor ###


######## main block ceil ########
# get threads
setthreads
thread=$?
# get buffers
setbuffers
buffer=$?
# set backup
setbackups
# upgrade block
sed -i '1, $s/^wait_timeout.*$/wait_timeout = 28800/g' ${cnf_file}
sed -i '1, $s/interactive_timeout.*$/interactive_timeout = 28800/g' ${cnf_file}
sed -i '1, $s/^max_connections.*$/max_connections = 500/g' ${cnf_file}
sed -i '1, $s/^max_user_connections.*$/max_user_connections = 0/g' ${cnf_file}
sed -i '1, $s/^tmp_table_size.*$/tmp_table_size = 16M/g' ${cnf_file}
sed -i '1, $s/^max_heap_table_size.*$/max_heap_table_size = 16M/g' ${cnf_file}
sed -i '1, $s/^innodb_log_buffer_size.*$/innodb_log_buffer_size = 16M/g' ${cnf_file}
sed -i '1, $s/^innodb_purge_batch_size.*$/innodb_purge_batch_size = 300/g' ${cnf_file}
sed -i '1, $s/^max_connect_errors.*$/max_connect_errors = 100/g' ${cnf_file}
sed -i '1, $s/^transaction_isolation.*$/transaction_isolation = REPEATABLE-READ/g' ${cnf_file}
sed -i '1, $s/^default-storage-engine.*$/default_storage_engine = InnoDB/g' ${cnf_file}
sed -i '1, $s/^character-set-server.*$/character_set_server = utf8/g' ${cnf_file}
sed -i '1, $s/^slave-net-timeout.*$/slave_net_timeout = 10/g' ${cnf_file}
sed -i '1, $s/^open-files-limit.*$/open_files_limit = 28192/g' ${cnf_file}
sed -i '1, $s/^skip-external-locking/skip_external_locking/g' ${cnf_file}
sed -i '1, $s/^skip-name-resolve/skip_name_resolve/g' ${cnf_file}
sed -i '1, $s/^no-auto-rehash/disable-auto-rehash/g' ${cnf_file}
sed -i '1, $s/^innodb_file_format.*$//g' ${cnf_file}
sed -i "1, \$s/^innodb_buffer_pool_instances.*\$/innodb_buffer_pool_instances = ${buffer}/g" ${cnf_file}
sed -i '99i innodb_change_buffer_max_size = 35' ${cnf_file}
sed -i "94i innodb_page_cleaners = ${buffer}" ${cnf_file}
sed -i "1, \$s/^innodb_purge_threads.*\$/innodb_purge_threads = ${thread}/g" ${cnf_file}
######## main block floor ########

 

蛙鳜鸡鹳狸猿
关注
专栏目录
mysql性能调优参数
01-16
MySQL性能调优参数 MySQL 是一个广泛使用的开源关系型数据库管理系统，在实际应用中，如何提高 MySQL 的性能是一个关键的问题。本文将总结 MySQL 性能调优参数，涵盖 Innodb 相关参数、连接参数、缓存参数、日志...
MySQL性能优化/调优：默认配置的修改
qq_31776303的博客
09-14 304
原文地址：http://blog.chinaunix.net/uid-366408-id-116506.html 在这里罗列一下这些配置， 每次新装MySQL的时候， 最好根据实际需要调整一下这些配置： max_connections 　　最大并发连接数。当MySQL的并发连接达到这个设定值时，新的连接将会被拒绝。当发现MySQL有能力处理更多的并发的时候， 建议调大这个值，相应给服
MySQL字段值默认方式添加和非默认添加效率对比
m0_47087822的博客
01-14 704
快速通道测试内容表结构测试代码POJO对比【1】（除主键外所有字段均自定义插入）插入100条数据（1713ms）插入1000条数据(3964ms)插入10000条数据(19153ms)插入50000条数据(84008ms)插入100000条数据(277664ms)对比【2】（'age'和'sex'字段使用数据库默认的方式）插入100条数据(1731ms)插入1000条数据(3768ms)插入10000条数据(19713ms)插入50000条数据(86576ms)插入100000条数据(298735ms)结果
7、MySQL默认值（DEFAULT）
热门推荐
you can you up up up的博客
10-11 1万+
默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。 例如，员工信息表中，部门位置在北京的较多，那么部门位置就可以默认为“北京”，系统就会自动为这个字段赋值为“北京”。 默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。 在创建表时设置默认值约束 创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下： &lt
mysql 设置默认值_MySQL参数设置01
weixin_39732491的博客
12-04 1058
innodb_buffer_pool_sizeInnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%.脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buf...
通过查看mysql 配置参数、状态来优化你的mysql
weixin_34082854的博客
01-18 471
mysql的监控方法大致分为两类：1.连接到mysql数据库内部，使用show status，show variables，flush status 来查看mysql的各种性能指标。2. 直接使用mysqladmin查看其性能指标，例如：UserParameter=mysql.uptime,mysqladmin -uroot status|cut -f2 -d":"|cut ...
php定义一个参数带有默认值的函数实例分析
10-24
1. 只有在参数经常被省略的情况下，才为函数参数设置默认值。 2. 默认值应该是逻辑上合适的值，以便在不传递参数时，函数可以执行预期的任务。 3. 函数内部不应该假设参数会使用默认值，应该总是检查参数的实际值，...
MySQL数据库：单条数据记录的插入一.pptx
05-29
如果列清单和VALUES清单都为空，则INSERT会创建一行，每个列都设置成默认值。; 插入语句最常用格式： INSERT INTO 表名(列名,...) VALUES (表达式, ...) ;【例】 向表Book中插入如下的一行数据： ? INSERT into ...
走向Erlang网络编程性能调优之旅
03-04
Erlang网络编程性能调优之旅 Erlang是一种功能强大的编程语言，擅长于开发高性能的网络应用程序。在这篇文章中，我们将探索Erlang网络编程性能调优的旅程，从开始的简单连接压力测试到最后的高性能网络服务器的实现...
ES6知识点整理之函数对象参数默认值及其解构应用示例
10-17
首先，函数参数默认值允许我们在定义函数时为参数指定一个默认值，如果在调用函数时没有传递该参数，那么就会使用这个默认值。例如： ```javascript function logMessage(message = 'Hello, World!') { console....
mysql参数优化
最新发布
weixin_39735909的博客
10-17 4024
mysql参数设置
MySQL调优之服务器参数设置
shw12357的专栏
09-24 408
服务器参数设置 general datadir=/var/lib/mysql 数据文件存放的目录 socket=/var/lib/mysql/mysql.sock mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接 pid_file=/var/lib/mysql/mysql.pid 存储mysql的pid port=3306 mysql服务的端口号 default_storage_engi
mysql 设置默认值_MySql 性能优化（my.ini）
weixin_39939276的博客
11-21 711
查看MySQL参数，可以使用show variables和show status命令查看，前者查看服务器静态参数，即在数据库启动后不会动态更改的值，比如缓冲区、字符集等。后者查看服务器的动态运行状态信息，即数据库运行期间动态变化的信息，比如锁，当前连接数等。1.max_connections = 3000 MySQL允许最大的进程连接数，个人觉得没必要加大此值 . 2 .table_cache...
MySQL调优之服务器参数优化实践
小小默：进无止境
03-01 3014
**关联博文：** [MySQL中常见的优化策略详解](https://blog.csdn.net/J080624/article/details/53199410) 接上文[MySQL中常见的优化策略详解](https://blog.csdn.net/J080624/article/details/53199410)后，我们继续学习MySQL自身配置的优化。本文是基于Innodb引擎。 目前来说，InnoDB是为MySQL处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库
MySQL对默认值的使用
sdxushuxun的专栏
01-03 3361
在实际使用中，通常会对某些字段设置默认值，比如0或者1等等，MySQL在什么情况下会采用默认值呢？ 经过测试当insert into语句中不对该字段操作，即insert没有该字段时才会使用默认值填充， 当插入值为null 或者“”时都不会是默认值，而是认为null “”也是一个值插入
mysql输入参数初始值_mysql使用default来设置字段的默认值
weixin_34711401的博客
01-19 1300
mysql创建表时，我们可以使用default来设置表字段的默认值,这样当向表中插入或添加数据时，如果没有为此字段设置任何值，则使用default默认值来填充该字段的值在使用create table创建表的时候，为字段设置默认值，如下：mysql> CREATE TABLE Test(State char(2) NOT NULL DEFAULT "KY");Query OK, 0 rows ...
python 提取固定列名数据_python获取数据库列名
weixin_39806288的博客
12-01 6091
{"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航，为用户的数据库应用系统进行性能和风险评估，参与配合进行数据压测演练，提供数据库优化方面专业建议，在业务高峰期与用户共同保障数据库系统平...
MySQL性能优化：关键参数设置与服务器调整
3. **key_buffer_size**：这是一个非常重要的参数，它决定了MyISAM表的索引缓存大小。增大此值可以显著提升索引查询速度，但也要注意不要设置过大，以免消耗过多内存并引发系统性能下降。 4. **max_connections**：...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值