【MySQL运维】MySQL 5.7配置文件详细优化示例

TangLu 3368 2020-09-25

一、作用域相关配置

[mysqld]:服务端选项作用域,大部分配置都在该作用域下

[mysql]:mysql客户端作用域,该作用域下的配置仅对mysql这个命令客户端生效

[client]:其他客户端的作用域。该作用域下的配置对mysqladmin、myqsldump等客户端命令都生效。由于不是所有客户端都有相同的配置,可能会因为多余的配置导致启动报错

[mysqldump]:mysqldump客户端作用域

[mysqladmin]:mysqladmin客户端作用域

[mysqld-5.7]:对指定版本的服务端生效


、客户端相关配置

prompt:自定义提示符,方便查看当前所操作数据库,如prompt= "[\\u@linuxe][\\d]>"
tee:将操作记录写到日志中作为审计,如tee= "/data/dblog/tee.log"  #
no-auto-rehash:禁止自动读取元数据,虽然无法再进行命令补齐,但是可以降低负载。如果在使用mysql命令连接服务卡在提示符就是元数据被锁导致,用mysql -A选项也可以启用该功能

default-character-set:客户端字符集设置,建议和服务端一致,如default-character-set=utf8mb4


三、服务端相关配置

1、数据库基本信息

user:指定数据库服务的用户,如user=mysql

basedirMySQL安装路径,如basedir=/usr/local/mysql
datadirMySQL数据文件存放路径,如datadir=/data/mysql/data
port:MySQL实例端口,如port=3306
socket:MySQL sock文件路径,如socket=/tmp/mysql.sock 

character-set-server:字符集设置,如character-set-server=utf8mb4

skip-character-set-client-handshake:强制客户端字符集和服务端一致
default-storage-engine:默认存储引擎,如default-storage-engine=INNODB
skip-name-resolve:建议开启,避免把IP反解析成域名(比如127.0.0.1被解析成localhost),这样使用IP来做权限控制更方便
server_id:主从复制会用到,每个节点ID不能相同,可以避免双主架构带来的循环复制问题

tmpdir:临时表路径,如tmpdir=/data/mysqltmp/mysql3306

max_connect_errors:单个客户端连接失败的最大次数,密码错误或者网络问题产生的连接失败都会计数,超过该次数就会被服务永久拒绝连接,直到使用mysqladmin flush-hosts才会恢复

max_user_connections:每一个用户所能产生的最大连接数,默认为0不限制。设置该值后,可以避免某个程序连接池配置不合理导致大量连接被占用

back_log:如果客户端连接数上限后,允许多少个客户端进入一个队列排队

max_allowed_packet:MySQL与客户端建立连接后能传输的数据包限制,如果一个包含大批量数据更新的SQL超过了该参数的限制就会失败。最大可配置为1G

time_zone:显示指定时区,默认为SYSTEM,即系统的操作时区

performance_schema:显式启用performance_schema数据库,该选项在5.7开始默认是启用的

lower_case_table_names:建议设置为1,表示不对表名进行大小写区分

init_connect:设置每个会话连接后自动执行的命令,比如SET NAMES或者SET AUTOCOMMIT


2、数据库内存信息(尽可能的为数据库分配更多的内存,减少与磁盘的IO)

max_connections:客户端最大连接数,建议参考show status like '%Threads_running%'的值。该值不要调得太大,实际内存计算是max_connections*(sort_buffer_size+read_buffer_size+read_rnd_buffer_size+join_buffer_size+binlog_cache_size+thread_stack)每个客户端要单独申请内存空间

sort_buffer_size:排序语句生成的结果会临时存放到内存空间中,默认256K,如果查询结果超过这个大小则会在硬盘生成临时表进行存放,调大后可以提升order by语句的效率。经过Percona测试发现盲目调大会起到反效果,通常Sort_merge_passes/(Sort_scan+Sort_ range)>2说明sort_buffer_size设置偏小,建议调整为4-8M

show global status like '%sort%'

read_buffer_size:顺序读缓冲区大小,默认128K,建议调整为4-8M
read_rnd_buffer_size:随机读缓冲区大小,默认256K,建议调整为4-8M
join_buffer_size默认256K,建议调整为4-8M
binlog_cache_size:当事务产生的binlog超过该参数值就会使用临时文件来存放binlog cache,默认32K,适当调大可以加快binlog写入效率

thread_stack:每个线程连接时申请的内存空间,默认256K
key_buffer_size:默认64M
tmp_table_size:内存临时表的最大值,默认64M。临时表常常用于存放group by所需要的统计数据,方便后面对这些数据进行排序。
如果内存临时表超过该参数限额,则开始生成基于磁盘的临时表,影响性能。通过show status like '%tmp%'后观察tmp_disk_tables的数量,如果数值增长很快说明tmp_table_size设置得太小了。调大该值可以提高GROUP BY语句和联合查询的速度。需注意该参数还受到max_heap_table_size的影响,如max_heap_table_size设置得比tmp_table_size小,那max_heap_table_size的值才是最大的内存临时表的上限

max_heap_table_size:用户可以创建的内存临时表大小


3、数据库超时配置

interactive_timeout交互模式下会话超时时间,比如mysql客户端,单位为秒,建议600-1800秒之间
wait_timeout应用会话连接超时时间,比如jdbc,单位为秒,默认为8小时。应用程序在连接成功后如果没有后续操作,则连接处于Sleep空闲状态,建议600-1800秒之间

rpl_stop_slave_timeout:停止从库时的超时时间,如果超时会报错,避免从库回滚大事务一直卡住。如果从库存在大事务建议先直接停止mysql,增加索引后再stop slave

innodb_lock_wait_timeout:InnoDB行锁等待超时时间,比如事务A对某行数据进行修改但未提交,此时事务B也修改该行数据时就会被锁,直到超时。该值默认50,建议5-20秒

lock_wait_timeout:元数据锁超时时间,默认是1年,设置30秒足够

max_execution_time=60000:控制每个语句执行的最长时间,避免单个语句意外执行太长时间,比如一些框架会默认把SQL使用begin\commit框起来,或者代码错误把SELECT语句放入到事务中。该配置项单位是毫秒


4、数据库日志信息

· errorlog

log_error:错误日志路径,如/data/mysql/logs/error.log


· slowlog
slow_query_log慢日志开关,on代表打开

slow_query_log_file:慢日志路径,如/data/mysql/logs/slow.log,不指定的话默认在数据库文件目录下,名为hostname-slow.log
long_query_time慢日志标准,达到这个标准的才会被记录慢日志,单位为秒,设为0代表记录所有查询

log_queries_not_using_indexes=1:将没有使用索引的语句都记录到慢日志中,即便没有达到阈值
log_throttle_queries_not_using_indexes=60:和上面选项配合使用,如果没有使用索引的语句执行频繁,那每分钟最多只记录60次
log-slow-admin-statements:记录由ALTER TABLE等语句引发的慢查询


· binlog
log_binbinlog日志路径,如/data/mysql/logs/master-bin

log_bin_index:binlog索引文件路径,如/data/mysql/logs/master-bin.index
binlog_formatbinlog日志格式,推荐row

binlog_rows_query_log_events:将所执行的原始sql语句记录到日志中,更便于分析

expire_logs_daysbinlog过期天数
max_binlog_size单个binlog文件大小
binlog_cache_sizebinlog缓存配置,和性能优化相关

log_timestamps:默认为UTC时间,这样查看日志会存在时区问题,建议修改为SYSTEM,和系统时间一致

master_info_repository:建议设置为table,将binlog状态信息存放在mysql.slave_master_info表中


· relaylog

relay-log:relaylog日志路径与文件名,如/data/mysql/logs/relay-bin

relay-log-purge:是否自动清理已经应用完的relaylog,1为自动清理  

relay_log_info_repository:建议设置为table,将中继日志状态存放在mysql.slave_relay_log_info表中

relay_log_recovery:建议为1,开启中继日志恢复模式,可以保证数据一致性。如果从库宕机导致当前中继日志损坏的话,就放弃该日志并重新从master上获取


· Redo Log

innodb_log_file_size:redo log日志大小,默认是48M,SSD硬盘建议配置到4G。如果设置太小会导致数据库经常flush日志到磁盘影响性能,配置越大写操作的效率越高,但是崩溃恢复时间会稍长一点

innodb_log_buffer_size:redo log缓存区域大小,刷新缓存的几种情况:master thread每秒刷新buffer、buffer写满时刷新、事务提交时刷新、服务重启时刷新,建议32-64M

innodb_log_files_in_group:redo log数量,默认2,最多支持4个,redolog日志命名为ib_logfile0~ib_logfileN


5、双1参数

innodb_flush_log_at_trx_commitredolog刷盘策略,建议为1。0是每秒刷新一次redo buffer到磁盘,当服务崩溃可能丢失1秒数据;1是每次事务提交时都立即刷新到磁盘;2是每次事务提交都立即刷新到os cache中,随后刷新到磁盘,机器断电才会丢失1秒数据

sync_binlog:binlog刷盘策略,建议为1。当值为0代表事务提交时仅将Binlog信息写入OS Cache,但是由系统自己控制刷盘时机,如果系统宕机则丢失缓存中的所有数据;当值为1时代表每个事务提交时同步把Binlog刷新到磁盘,当数据库或操作系统宕机恢复后,由于Binlog中缺少的任何事务都是处于准备阶段,所以MySQL会回滚这些事务,保证无数据丢失,数据库安全性最高,但是性能损耗也最大;当值为N时代表每N次事务提交才会刷盘,如果操作系统在这个时候宕机,数据库可能会丢失N个事务


6、主从复制相关配置

log_slave_updates:从库是否记录binlog,不承载高可用的从库可以关闭节约IO性能,如果需要级联复制的场景可以开启。如果从库只配置了log_bin而没有开启该选项,则binlog为空

slave_parallel_workers:该值为0代表单线程复制,大于1时表示启用多线程复制。为了提升从库复制效率通常和CPU核数一致,该变量动态设置后需要重启复制线程才会生效。并行复制开启后通过processlist看到状态为system lock的system user用户就是并行复制的线程

slave_parallel-type:并行复制方式,5.7之前是基于DATABASE级别的粒度进行并行复制,并行效率很差。而5.7开始采用LOGICAL_LOCK实现了GroupCommit并行回放,同一个Group内的事务可以并行执行。在binlog中通过last_committed的值来标记事务所属的组,如果两个事务last_committed值一致,这两个事务就属于同一个组

slave_preserve_commit_order保证从库回放事务的顺序与主库提交的顺序完全一致。比如主库执行了A、B、C 3个事务,且last_commited相同,从库满足并行回放的条件。但是如果slave_preserve_commit_order没有开启的话,则顺序可能是乱的。该选项动态开启时需要先停止SQL线程,并且在8.0.19以前从库必须同时开启log_bin和log_slave_updates才能进行配置

binlog_group_commit_sync_delay:并行复制时延迟多少微妙后才会调用fsync

binlog_group_commit_sync_no_delay_count多线程复制时累计多少次以后就调用fsync

transaction_write_set_extraction(该配置在5.7.22开始支持):建议配置为XXHASH64

binlog_transaction_dependency_tracking(该配置在5.7.22开始支持):默认为COMMIT_ORDER,但是基于COMMIT_ORDER的并行复制只有在主库有压力的情况下才可能会形成一组,这样从库并行效率并不会高。建议修改为WRITESET,可以在ORDER_COMMIT的基础上尽可能的降低last commit以获取更好的并行度(即便在主库串行执行的事务在从库也能并行应用)


7、InnoDB信息

· InnoDB性能

innodb_buffer_pool_sizeMySQL最重要的性能参数,该变量用于设置数据和索引的缓存区大小,使用缓存区种的数据就可以避免与磁盘产生大量交互,从而提升效率,该变量建议设置为系统最大内存的70%(注意该变量的值必须是innodb_buffer_pool_instance * innodb_buffer_pool_chunk_size(默认128M,不能动态调整)的倍数)。5.7开始支持动态配置,但是在线修改的话值只能为字节格式,配置文件可以使用M、G单位。通过SHOW GLOBAL STATUS LIKE '%innodb%'命令查看MySQL剩余buffer,如果Innodb_buffer_pool_pages_free值很小或为0说明buffer_pool已经用光了,而Innodb_buffer_pool_wait_free不为0,说明产生过对应次数的等待内存释放行为。这2种情况都需要增加innodb_buffer_pool_size的值。

innodb_buffer_pool_instances将buffer_pool_size平均划分多个区域,提升并发性能,建议和CPU核数一致

innodb_flush_method=O_DIRECT默认为fsync,代表刷数据到磁盘的时候会先申请系统级别的缓存,这样性能虽然会更好但是会申请额外的内存。由于MySQL除了innodb_buffer_pool_size占用内存,每个连接还会单独申请内存空间,再加上fsync申请的内存,很容易导致OOM。改为O_DIRECT会跨过系统缓存直接写到磁盘。

innodb_thread_concurrency:并发查询数(非并发连接),默认为0不限制。如果为了避免并发太多导致CPU跑满可以设置一个合理值。由于锁等待产生的并发并不会带来CPU开销,所以这些并发不会占用这个连接名额,避免锁等待而让数据库不可用

innodb_strict_mode:开启严格模式,可以避免非空字段插入null等情况

innodb_stats_on_metadata:建议为off关闭动态统计被触发

innodb_io_capacity:脏页正常刷新频率,单位为页,默认200即代表每次刷新200页(每个页默认16K)。如果磁盘速度快就可以调大,建议设置为磁盘IOPS的一半或更高,SSD的话通常可以达到1W+,可以通过fio来测试磁盘IO

innodb_io_capacity_max:脏页最大刷新频率,建议和磁盘IOPS一样

innodb_flush_neighbors:默认为1代表刷脏页时会把邻近脏页一起刷到磁盘。对于机械硬盘能够减少磁盘寻道的开销,提升性能;但是对于SSD提升很小,建议为0关闭
innodb_file_per_table是否开启独立表空间,5.6开始默认打开,通常也建议打开。开启后每个表数据都会单独存放在一个以表命名后缀为.ibd文件中分散IO提升性能,也便于表的维护,否则所有表数据会全部存放在ibdata1文件中,即便删除了某个表,这个文件空间也不会释放,只是将表空间被标记为可复用

innodb_buffer_pool_dump_at_shutdown:ON代表每次停机会dump出buffer pool中的数据

innodb_buffer_pool_filename:dump出的数据文件名,如ib_buffer_pool

innodb_buffer_pool_load_at_startup:ON代表启动服务时加载dump文件到内存进行缓存,提升性能

table_open_cache:指定打开表的缓存区大小,也可以理解为允许缓存客户端打开多少张表。这个配置需要结合open_tables(当前打开的表数量)与opened_tables(服务启动以来总共打开的表数量)两个状态值来调整。如果open_tables的值接近table_open_cache代表缓存区已经使用完,如果此刻opened_tables又在不断增加,说明mysql还在不断打开表。这个使用缓存是没有起到作用的,所以需要调大缓存区的值,但是不能大于系统文件描述符。

innodb_large_prefix是否允许单列的索引长度超过767字节

innodb_old_blocks_pct:MySQL为避免LRU算法把冷数据放到头部,所以将LRU链表拆分为热、冷数据两个部分,优先对冷数据进行淘汰。该参数控制了冷热数据比例,默认37代表冷数据占37%

innodb_old_blocks_time:控制冷数据转为热数据的策略,默认1000毫秒,代表数据页被加载到缓存页N毫秒后如果再次被访问,就挪动到热数据区域表头部。这个思想也可以用在Redis上,可以每天统计出来哪些商品被访问的次数最多,然后通过定时作业把这些热门商品预加载到Redis里,第二天对热门商品的访问就会优先走Redis缓存了


· Undo Log

innodb_undo_tablespaces:undolog独立表空间个数,默认为0表示不为undolog设置独立表空间,而是记录到ibdata文件中。如果设置为4则代表创建命名为undo001~undo004的undolog。该配置在mysql初始化后就无法进行修改

innodb_max_undo_log_size:单个回滚日志最大占用空间大小,默认为10M
innodb_undo_log_truncate当undolog超过innodb_max_undo_log_size阀值时对undolog空间进行收缩到10M


四、my.cnf配置文件示例


[client]
port = 3310
socket = /tmp/mysql3310.sock

[mysql]
no-auto-rehash
prompt = "\\u@\\d \\R:\\m> "
default-character-set = utf8mb4

[mysqld]
### General ###
user = mysql
port = 3310
basedir = /usr/local/mysql
datadir = /data/mysql3310/data
socket = /tmp/mysql3310.sock
tmpdir = /data/mysql3310/tmp/
character_set_server                = utf8mb4
default-storage-engine                = InnoDB
default-time-zone               = "+8:00"
server-id = 30241
transaction_isolation                = READ-COMMITTED
max_connect_errors         = 500
#max_user_connections                            = 100
back_log = 1024
open_files_limit        = 65535
max_allowed_packet        = 512M
init_connect        ='set names utf8mb4'
group_concat_max_len                = 10240
table_open_cache           = 8192
table_definition_cache                = 8192
log_timestamps = system
performance_schema = ON
lower_case_table_names = 1
explicit_defaults_for_timestamp = 0
sql_mode="NO_ENGINE_SUBSTITUTION"
skip-character-set-client-handshake = 1
skip-name-resolve
skip-ssl
skip-external-locking
#skip-grant-tables

### Timeout ###
connect_timeout = 20
interactive_timeout = 3600
wait_timeout = 3600
lock_wait_timeout = 300
max_execution_time = 60000
innodb_lock_wait_timeout        = 10
rpl_stop_slave_timeout                  = 180

### Memory ###
innodb_buffer_pool_size = 32G
innodb_log_buffer_size  = 32M
tmp_table_size          = 64M
max_heap_table_size     = 64M
key_buffer_size         = 64M
max_connections         = 500
 read_buffer_size       = 4M
 sort_buffer_size       = 4M
 read_rnd_buffer_size   = 4M
 join_buffer_size       = 4M
 binlog_cache_size      = 16M

### Error Log ###
log_error = /data/mysql3310/log/error.log

### Slow log ###
slow_query_log = 1
slow_query_log_file = /data/mysql3310/log/slowlog/mysql.slow
long_query_time = 1
log_slow_admin_statements = 1
#log_queries_not_using_indexes = 1
#log_throttle_queries_not_using_indexes = 10
#log_slow_slave_statements = 1
#min_examined_row_limit = 100

### Binlog ###
master_info_repository = TABLE
sync_master_info = 1
log_bin = /data/mysql3310/log/binlog/mysql-bin
binlog_format         = row 
binlog_rows_query_log_events    = on
max_binlog_size = 1024M
expire_logs_days       = 10

### Relay Log ###
relay_log_info_repository         = TABLE
relay-log = /data/mysql3310/log/relaylog/relay-bin
relay_log_recovery = 1
relay-log-purge = 1

### Slave ###
# skip_slave_start = 1
# log_slave_updates = 1
# slave_parallel-type        = LOGICAL_CLOCK
# slave_parallel_workers        = 8
# slave_preserve_commit_order                = 1
# read_only = 1
# super_read_only = 1
# slave_net_timeout = 10 

### Gtid ###
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1

### 半同步复制 ###
#plugin_dir  = /usr/lib64/mysql/plugin/
#plugin_load  = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled  = 1
#loose_rpl_semi_sync_slave_enabled  = 1
#loose_rpl_semi_sync_master_timeout  = 5000

### 双1 ###
innodb_flush_log_at_trx_commit  = 1
sync_binlog      = 1

### Innodb ###
innodb_data_home_dir = /data/mysql3310/data
innodb_data_file_path  = ibdata1:1024M:autoextend
innodb_buffer_pool_instances  = 16  
innodb_support_xa = 1
innodb_flush_method    = O_DIRECT
innodb_file_per_table  = 1
innodb_log_file_size  = 4G
innodb_log_files_in_group  = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_read_io_threads  = 16
innodb_write_io_threads  = 16
innodb_purge_threads   = 8
innodb_io_capacity    = 10000
innodb_io_capacity_max    = 20000
innodb_max_dirty_pages_pct   = 75
innodb_flush_neighbors    = 0
innodb_thread_concurrency               = 0
innodb_strict_mode    = 1
innodb_stats_on_metadata        = 0
innodb_print_all_deadlocks  = 1
innodb_autoinc_lock_mode = 2
innodb_sort_buffer_size  = 67108864
innodb_large_prefix = 1
innodb_page_size     = 16384
innodb_open_files = 65535
innodb_rollback_on_timeout = 1

### Undo Log ###
#innodb_max_undo_log_size = 1G  
#innodb_undo_tablespaces = 4  
#innodb_undo_logs    = 128 
#innodb_undo_log_truncate = 1   

[mysqldump]
quick
max_allowed_packet  = 512M

[mysqld_safe]
open-files-limit = 65535



版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【MySQL运维】show语句的使用
下一篇:使用ulimit、limits.conf与/proc/$pid/limits对进程资源进行限制
相关文章

 发表评论

评论列表

2020-11-24 14:19:11

请问 ,咱们站点的广告在哪里, 我给你点点.

2020-11-24 15:23:07

@silent3035 @silent3035:感谢 在网站右侧标签栏下方有2个广告区

微信二维码