Mysql 11配置项

￡神魔★判官ぃ 2022-08-03 15:49 132阅读 0赞

配置文件一般在/etc/my.cnf或者是/etc/mysql/my.cnf

语法：properties的语法，注意单词用-或\_分割比如：  
auto-increment-offset=5

分个类  
按照作用域，可以分为全局的也就是配置在配置文件中的，或者是会话的，就是连接中的  
还可分为动态和静态的。 动态就是在运行期间可以改写的，这个要特别小心,因为有可能造成全局刷新和阻塞  
语法：

SHOW variables like '%sort_buffer_size%';
    //全局的：
    SET GLOBAL sort_buffer_size = <value>;
    SET @@global.sort_buffer_size := <value>;
    // 会话的
    SET @@sort_buffer_size := <value>;
    SET @@ssession.sort_buffer_size := <value>;

对于value还有一个些其他的限制，比如  
SET 不能用1M  
配置文件不能用表达式。 这些需要注意。

### innodb\_buffer\_pool\_size ###

这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。  
Mysql整体可用内存的70%-80%，太大了可能会占用操作系统内存，影响性能。  
可以使用show engine innodb status;展示的

BUFFER POOL AND MEMORY

查看使用情况

### 事务日志 ###

Created with Raphaël 2.1.0写事务日志日志缓冲区事务日志(循环连续写入)

数据被修改，先写日志缓冲区。 缓冲区满|每秒|事务提交， 缓冲区回写事务日志。  
innodb\_log\_buffer\_size 设置缓冲区大小。  
设置原则：2-8M，一般满足一个小时的活动。活动大小：

show global status like '%Innodb_os_log_written%';

查看写入日志增量。 可以隔100计算，算两次差值。 单位为B。  
最佳实践：3M

innodb\_log\_files\_in\_group 几个日志文件。默认2.一般不用改  
innodb\_log\_file\_size 日志文件大小。 又一个比较有用的参数。  
官方建议 innodb\_log\_files\_in\_group \* innodb\_log\_file\_size = innodb\_buffer\_pool\_size。  
太大会使得恢复速度很慢  
另外该值也没有要设置太大，跟checkpoint的频率有关(checkpoint是一个后台线程)，会把事务日志中的数据写到数据文件中。  
可以使用  
show engine innodb status; 命令查看这部分：

LOG

Log sequence number 300 2512389215  
Log flushed up to 300 2512389215  
Last checkpoint at 300 2508134796  
2512389215 - 2508134796 = 4m多。  
有个官方推荐的公式(innodb\_log\_file\_size\*innodb\_log\_files\_in\_group(default 2))\*0.75 = 4M  
因为是测试环境所以没什么感觉，但实际上一般是几G才够用的。

### innodb\_flush\_logs\_at\_trx\_commit ###

0 缓冲写日志每秒一次  
1 默认 最安全，每次提交写日志，且刷新(刷新到持久化存储)。  
2 缓冲写日志每次提交时写，每秒一次刷新

上面缓冲写日志，实际上是从InnoDB的 缓存转移到操作系统的缓存  
如果允许1秒的丢失的话，实际上是可以使用2的

### 并发配置 ###

innodb\_thread\_concurrency 限制一次性可以有多少线程进入内核。  
其值 = CPU数量 \* 磁盘数量 \* 2， 实际上小点效果会更好。

超过的线程会使用两阶段方式，先睡一定的微秒再尝试进入，如果还不能则进入队列。

一单进入内核那就有一定数量的tiket，下次可以直接进入内核。

innodb\_commit\_concurrency变量控制有多少个线程可以同一时间提交。

### max\_connection ###

防止攻击， 但是要设置足够大，免得正常业务被拒绝。

show variables like 'max_connections';
    show global status like 'Max_used_connections';

观察Max\_used\_\_connections的值，能看到是否有时间点超过了最大值。

### thread\_cache\_size ###

缓存线程的值，不用很大一般256就可以了

show global status like 'Threads_connected';
    show global status like 'Threads_created';

如果Threads\_created这个值很大或者一直增长，则需要增到配置了

### table\_cache\_size ###

要足够大，避免需要重复解析打开表定义

show global status like 'Opened_tables';

这个值变化大，则需要增加

### 其他配置 ###

read\_only 在备库使用，强制备库不接受应用端来的变更请求  
slave\_net\_timeout 备库连主库发现失败时间，默认一个小时，一般一分钟或更短。  
innodb\_autoinc\_lock\_mode 自增锁的模式

sort\_buffer\_size 8M  
join\_buffer\_size 两个connection级别的参数，每次新的连接创建，太大可能占用太多内存，一般 2M

query\_cache\_size 查询缓存大小，因为会有锁，命中率等等问题。实际上用处并不是很大。一般32M

transaction\_isolation 默认隔离级别。

key\_buffer\_size 索引缓冲区大小，可以大一点 512M

read\_buffer\_size查询操作缓冲区，排序时，会查一遍该缓冲区，避免磁盘读写，排序多的时候可以设置大一点， 一般4M

### DBCP连接池的一些配置 ###

*  initialSize 启动时创建的初始化连接数量
 *  maxActive 连接池中可同时连接数据库的线程数。 默认8，一般20
 *  maxIdle 最大空闲， 超过的会被回收。默认8，太小，可能造成回收创建太频繁
 *  minIdle 最小空闲，少于会创建，默认0，可以稍微多一点，比如8
 *  maxWait 没有可用连接时的阻塞时间，默认无现长，有可能无限挂起， 超时会抛异常。我们是30000
 *  testOnBorrow=false 取得连接时是否验证，时间太长了有可能时效了如果不验证会为空
 *  testWhileIdle=true 空闲时是否验证，这个要配合验证的sql.
 *  timeBetweenEvictionRunsMillis=300000 多久启动过期清理线程
 *  numTestsPerEvictionRun=10 每次清理几个
 *  minEvictableIdleTimeMillis=300000 多久算是过期的