mysql优化的核心参数

##mysql优化的核心参数

##cpu优化

参数	参数功能	取值范围	经验值
innodb_thread_concurrency	并发执行的线程的数量（同时干活的线程的数量），保护系统不被hang住	0-1000	一般要求是cpu核数的4倍

##内存优化

参数	参数功能	取值范围	经验值
innodb_buffer_pool_size	缓存innodb表和索引数据的内存池大小	<=5.7.4：非动态全局；>=5.7.5：动态全局；64位：默认128M，最小5M，最大2^64-1	一般设置内存的50%80%，根据实际内存大小设置，如果内存不是很大，可以考虑5070%，如果内存很大，可以考虑到70%~80%，如果总是产生Innodb_buffer_pool_wait_free，说明buffer_pool设置过小
tmpdir	存放临时文件和临时表目录，可以设置多个路径用：分隔	全局参数，非动态，更改需要重启数据库	单独挂载，对读写要求很高，放在高性能盘，独立分区
innodb_buffer_pool_instances	buffer_pool被分成多少实例	非动态，全局；	8个或者16个，根据实际buffer pool大小设置，如果实例数量过小，会导致latch争用
innodb_max_dirty_pages_pct	buffer pool中最大脏页占比	百分比	75%~90%，如果io能力足够强，例如使用了闪卡，可以将这个参数调小；该参数设置越小，写入压力越大。
innodb_max_dirty_pages_pct_lwm	预刷新脏页比例，可以有效控制脏页比例达到最大脏页占比	动态，全局；<5.7.4：默认0，范围099；>=5.7.5：默认0，范围099.99	70，控制脏页比率，防止达到脏页最大占比
innodb_io_capacity

##io优化

参数	参数功能	取值范围	经验值
innodb_io_capacity	设定了后台任务执行的io量的上限	动态，全局；64位：默认200，最小100，最大2^64-1	每秒后台进程处理IO数据的上限，一般为IO QPS总能力的75%
innodb_io_capacity_max	在紧急情况下，innodbio容量上限的最大值，2000是初始默认值。	动态，全局；unix-64位：默认是2000，最小100，最大2^32-1	根据innodb_io_capacity的2倍进行设置
innodb_log_files_in_group	redo日志的组数，即logfile的数量	非动态，全局；默认2个，范围2~100	一般设置5组
innodb_log_file_size	每个logfile的大小	<=5.7.10:默认50M，最小1M，最大512G/innodb_log_files_in_group；>=5.7.11：默认50M，最小4M，最大512G/innodb_log_files_in_group	一般设置2G，每20~30分钟切换一次redo比较合适， 案例：业务人员反映晚上9:10-9:15有大量交易放弃，交易失败，让dba看看这个时间段数据库的性能情况，dba发现这个时间段有redo切换，buffer写入明显增加（写入抖动），TPS明显降低（事务提交了降低），怀疑redo文件过小，组数过少，脏数据写入速度慢。 解决方案：增加redo数量和大小，加大脏数据的写入力度，需要重启数据库
innodb_flush_method	采用O_DIRECT方式写入的时候绕过文件系统缓存，直接写入磁盘	非动态，全局；默认NULL，有效值：fsync、O_DSYNC、littlesync、nosync、O_DIRECT、O_DIRECT_NO_FSYNC	=O_DIRECT
innodb_max_dirty_pages_pct	buffer pool中最大脏页占比		75%~90%，如果io能力足够强，例如使用了闪卡，可以将这个参数调小；该参数设置越小，写入压力越大。
innodb_max_dirty_pages_pct_lwm	预刷新脏页比例，可以有效控制脏页比例达到最大脏页占比	动态，全局；<5.7.4：默认0，范围099；>=5.7.5：默认0，范围099.99	70，，当脏块达到70%的时候，触发网磁盘写，控制脏页比率，防止达到脏页最大占比
innodb_flush_neighbors	是否关闭邻接页刷新	0关闭,1不关闭	一般关闭邻接页刷新

##连接的优化

参数	参数功能	取值范围	经验值
max_connections	允许客户端并发连接数量	动态，全局；默认151，最小1，最大10000	1024，一般不要超过2000
max_user_connections	任何一个mysql用户允许最大并发连接数	动态，全局和会话；默认0，范围0~4294967295	256
table_open_cache	打开表缓存，跟表数量没关系1000个连接上来，都需要访问A表，那么会打开1000个表，打开1000个表是指mysql创建1000个这个表的对象，连接直接访问表对象	动态，全局；默认	4096 ，监控opened_tables值，如果很大，说明该值设置小了

thread_cache_size=512
都是短连接进来容易产生短连接风暴
会话层：事务状态、认证会话
连接层：网络连接、包传输
一个用户 对应 一个session 对应 一个connection
connection - thread:操作系统调用
3000个用户进来使用cache的512个线程，用完就放回去，避免创建、销毁线程的开销

---

wait_timeout=120
指的是app应用连接mysql进行操作完毕后，空闲120秒后断开

---

##数据一致性优化

innodb_flush_log_at_trx_commit=1

0，不管有没有提交，每秒钟都写到redo日志里
1，每次提交事务，都会把log buffer的内容写到磁盘里去，对日志文件做到磁盘刷新，安全最好
2，每次提交事务，都写到操作系统缓存，由OS刷新到磁盘，性能最好

sync_binlog=1

0，事务提交后，mysql不做fsync之类的刷盘，由文件系统来决定什么落盘
n，多少次提交，每n次提交持久化磁盘
生产设为1

3.日志写盘过程
image

1)三个update会话，三个线程都会产生的操作日志
2 )commit后提交到公共的cache中，三个进程之间不能相互看到对方的操作内容
3)经过write写入到标准I/O cache中，也就是文件系统句柄，线程缓存
4)如果需要让其他线程看到文件句柄内容，就需要通过flush刷新到全局可见文件系统缓存
5)最后最重的一步是将内存数据sync落盘