MySQL参数

常用的一些调整：1-11

　　1、sql_mode   //根据业务需要进行调整，由开发人员定，我们需要将sql_mode列的各个值列出来让开发人员去确定，参照官方文档。

　　2、autocommit    //是否隐式提交，这个需要开发的去确认

　　3、character_set_server=utf8mb4    //字符集也需要开发人员去定，互联网企业一般字符集设置为utf8

　　4、transaction_isolation = READ-COMMITTED  //默认隔离级别可重复读会存在MVCC和gap锁，对并发相比已提交读会产生影响

　　5、explicit_defaults_for_timestamp = 1      //为1表示关闭时间戳默认属性

　　　　[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

　　　　对于timestap列，会有很多的默认属性，这些默认属性在新的版本中已经被废弃，建议使用explicit_defaults_for_timestamp关闭默认属性。

　　6、max_allowed_packet = 16777216   //允许最大包的大小，如果表中有大列或一次插入大SQL，可以提高性能：

　　　　比如对于sql：

　　　　insert into values(),(),.;  //这个sql可能很大

　　　　insert into values；表中带有blob字段

　　7、event_scheduler = 1            //允许建立event，event周期性执行

　　8、强密码策略validate_password_policy=STRONG

　　　　validate-password=FORCE_PLUS_PERMANENT

　　9、loose_innodb_numa_interleave=1 //数据库层面关闭numa，尽量在MySQL数据库服务器在BIOS层面关闭NUMA

　　10、log_timestamps=system  //系统报错的时候，以os系统当前时间为准记录错误时间

　　11、show_compatibility_56=on       //在新版本中兼容旧版本的information_schema中的数据字典信息

 

连接相关的参数：12-18

　　12、interactive_timeout = 1800   //限制一些空闲连接

　　　　Wait_timeout = 1800    //连接上来长时间不做任何操作就剔除，控制空闲连接的超时时间

　　13、lock_wait_timeout = 1800 //表锁的超时，可以适当的减小，降低锁冲突的发生

　　14、skip_name_resolve = 1          //关闭反向解析

　　15、max_connections = 1500           //最大连接数一般不要超过2000

　　16、 table_open_cache=9000 //所有线程打开表的数量

　　　　需要适当调整，最大连接数*表关联涉及到的表的数量，最大连接数*6基本就够了

　　　　监控 Opened_tables 参数的值，如果这个值很大，说明table_open_cache设置小了。

　　17、max_connect_errors = 1000000 //最大连接错误数，防止暴力破解

　　　　对于生产用户，账号被lock以后，整个生产全部停止监控最大错误连接数：| Connection_errors_max_connections | 0 |

　　18、Thread_cache_size（线程缓存数量）

　　　　对于频繁的连接建立和断开的情况下，防止线程频繁的建立和销毁

　　　　要想办法降低Threads_created

 

 

用户线程工作空间：谨慎调整，针对每一个会话，19-23

　　19、read_buffer_size = 16777216  //只是对于 myisam 表生效，myisam 表不经过共享内存，但是索引经过共享内存。强烈降低这个参数

　　20、read_rnd_buffer_size = 16777216  //针对所有的表都生效，可以调整的大一些，16M 即可对于通过索引批量取数据有意义;可以降低通过索引访问表的时候，物理读的次数批量访问有意义。

　　21、sort_buffer_size = 33554432　　//排序空间尽量调整成16M以上，如果持续产生 Sort_merge_passes，可以在会话级别临时修改排序空间大小：mysql> set @@session.sort_buffer_size=320000000;

　　22、都是针对每一个会话，尽量调整成16M以上

　　　　tmp_table_size = 67108864——临时表空间大小

　　　　join_buffer_size = 134217728——表连接空间，多表关联会用到

　　　　binlog_cache_size=2048000——二进制日志缓存空间

　　　　　　mysql> show global status like '%disk%';

　　　　　　+----------------------------+-------+

　　　　　　| Variable_name | Value |

　　　　　　+----------------------------+-------+

　　　　　　| Binlog_cache_disk_use | 0 | //针对事务 SQL

　　　　　　| Binlog_stmt_cache_disk_use | 0 |       //针对非事务 SQL

　　　　　　| Created_tmp_disk_tables | 2 |  //针对 tmp 和 join

　　　　　　　　产生tmp的两种情况：

　　　　　　　　1、create temporary table

　　　　　　　　2、sql子查询

　　　　　　　　　　　　select ..

　　　　　　　　　　　　　　from

　　　　　　　　　　　　　　　　(select);

　　23、max_length_for_sort_data=1024　　//当达到这个值的时候就使用两次访问表的模式排序，一般调大该值。

 

日志相关参数：redo、binlog、slow、err、undo log 等，24-43

　　24、log_error = error.log　　//记录错误日志

　　25、slow_query_log = 1　　//开启慢查询

　　26、slow_query_log_file = slow.log　　//指定慢查询日志

　　27、log_queries_not_using_indexes = 1　　//没有使用索引的SQL会被记录到慢日志中

　　28、log_slow_admin_statements = 1　　//对于一些管理性的语句，也被记录到慢查询中去

　　29、log_slow_slave_statements = 1　　//从库上需要开启复制，SQL线程执行速度慢也会被抓取

　　30、log_throttle_queries_not_using_indexes = 10　　//每分钟允许全表扫描的SQL记录到慢查询日志的次数，防止slowlog暴增

　　31、log-bin=MySQLserver　　//开启binlog

　　32、binlog_format = ROW　　//可以使用row和mixed模式;row 模式可能会带来一些问题,比如：当主库表中没有主键进行dml时，对从库来说意味着灾难

　　33、expire_logs_days = 90　　//启用自动删除binlog，保留binlog 90天。可能会导致性能的抖动，主要是因为io被占用

　　34、long_query_time = 2　　//慢查询阀值

　　35、min_examined_row_limit = 100　　//访问的行数(Rows_examined)大于100才记录到慢日志中哪个参数真正反映一个SQL的性能？

　　　　# Query_time: 0.000306 Lock_time: 0.000145 Rows_sent: 1 Rows_examined: 1

　　　　哪个参数真正反映数据库的负载？

　　　　mysql> show global status like '%_rows_%';

　　　　+----------------------+-------+

　　　　| Variable_name | Value |

　　　　+----------------------+-------+

　　　　| Innodb_rows_deleted | 0 |

　　　　| Innodb_rows_inserted | 6 |

　　　　| Innodb_rows_read | 22 |

　　　　| Innodb_rows_updated | 1 |

　　　　+----------------------+-------+

　　36、binlog-rows-query-log-events = 1 update set where id=1;    //以注释的方式记录原生 SQL

　　37、log-bin-trust-function-creators = 1　　//在数据库中假设我们开启了 binlog，默认不能建立函数，如果需要建立函数， 就需要打开这个参数值

　　38、log-slave-updates = 1     //这个需要在从库上打开，开启从库的 binlog，用于级联复制

　　39、innodb_undo_logs = 128   //undo段的数量，默认128就可以

　　40、innodb_undo_tablespaces = 3     //生成三个 undo 文件，会把 undo 从 ibdata 中独立出来

　　41、5.7中undo表空间可以主动收缩，不建议使用

　　　　innodb_undo_log_truncate = 1：清空收缩，清空数据压缩空间

　　　　innodb_max_undo_log_size = 20G：超出undo的最大值后就执行上面参数的清空收缩，2G有点小，一般设置成20G以上，这里超出2G后，对比较老的数据进行mark标记，标记可以被清空purge了。

　　　　innodb_purge_rseg_truncate_frequency = 128，purge启动128次以后

　　42、innodb_log_file_size = 17179869184        //redo日志的大小

　　43、innodb_log_files_in_group = 5    //redo日志的组数，必须要调整的参数，至少5组，每组至少2G

 

innodb 相关的参数：44-62

　　44、innodb_page_size = 16384         //innodb页大小，一般就是16k

　　45、innodb_buffer_pool_size = 160G        //innodb 内存缓冲池大小，一般设置成物理内存的50%~80%

　　　　真正影响 IO，如果物理读很高，从参数层面这是重要设置

　　46、innodb_buffer_pool_instances = 16   //一般10G要分配一个pool

　　　　如果 latch、mutex 争用比较厉害的情况下，从参数的角度可以增加池子的数量

　　47、innodb_buffer_pool_load_at_startup = 1    //5.6的新特性，数据库启动时加载热数据

　　48、innodb_buffer_pool_dump_at_shutdown = 1    //保证重启以后，热数据会继续加载和保留在热数据区

　　49、innodb_buffer_pool_dump_pct = 40    //保存热数据的比例，会影响数据库的启动时间

　　50、innodb_flush_neighbors = 0      //关闭刷新邻接页，对于update频繁的系统刷新邻接页会导致页频繁进出内存，造成资源不必要的浪费，对于insert频繁的系统开启这个参数有意义

　　51、innodb_lock_wait_timeout = 5     //行锁的等待事件

　　52、innodb_file_format = Barracuda

　　53、innodb_file_format_max = Barracuda      //文件格式，52-53 这两个参数不要调

　　54、innodb_sort_buffer_size = 67108864  //这个是用来建立 innodb 表索引的时候使用可以增加大小，加快建立索引的速度

　　55、innodb_large_prefix = 1  //允许在列长度大于 767 字节的列上建立索引一般不建议在这么长的列上建立索引

　　56、innodb_print_all_deadlocks = 1     //死锁记录到errlog日志中

　　57、innodb_strict_mode = 1         //将warning当初错误来报

　　58、innodb_file_per_table = 1      //一个表一个文件，这个需要打开

　　59、innodb_stats_persistent_sample_pages = 64    //mysql收集统计信息的时候，收集的页数

　　60、innodb_autoinc_lock_mode = 2     //0,1,2  自增长锁，建议调整成2；保证唯一，但是保证增长和连续

　　61、innodb_max_dirty_pages_pct //如果io能力足够强，也就是使用了闪卡，可以将这个参数调小

　　62、innodb_doublewrite    //可以关闭doublewrite，但是可能会导致数据库出现问题

 

读写线程相关的参数：63-71

　　63、innodb_write_io_threads = 16：   //默认情况下会调成cpu核数

　　64、innodb_read_io_threads = 16 ：   //默认情况下会调成cpu核数

　　65、innodb_lru_scan_depth = 4096     //影响脏数据的写入力度，要保证有写缓存或者使用PCIe闪卡

　　66、innodb_io_capacity = 10000   //每次写的量，如果脏数据占比设置为90%，当前涨到 80%，还在持续的涨， 说明需要调大innodb_io_capacity的值了，让他写的多点。如果每秒写达到80了，每次写200、这时候需要降低 innodb_io_capacity 的值。如果是闪盘，io足够可以调大。

　　67、innodb_io_capacity_max = 20000      //告诉mysql，我的IO能力有多强，会影响MySQL每次写入的数据页的数量假设我们采用的是PCIe闪卡，这个参数可以调大，加大写入的粒度

　　68、innodb_flush_method = O_DIRECT  //对于innodb表在写入的时候，跳过文件系统缓存，如果写到问文件系统缓存，断电的时候数据会丢失，造成数据不一致。

　　69、innodb_purge_threads = 4  //回收undo、deleted数据行

　　70、innodb_page_cleaners

　　71、innodb_thread_concurrency = 64 //允许MySQL并发执行的线程的数量;保护系统不被hang住，一般要求是cpu核数的4倍

 

事务相关参数：72

　　72、双1问题

　　　　sync_binlog=1，影响binlog写入的参数innodb_flush_log_at_trx_commit=1，

　　　　影响redo log写入的参数innodb_support_xa=1，

　　　　当主库开启双一参数时，为了防止主从数据不一致，要把xa参数打开。

　　　　主库上可以开启双一参数，从库上可以采用双0，提升数据库的性能

　　　　开启双一时，最安全，但性能最差。

 

主从、并行复制相关参数：73-75

　　73、默认info信息放在文件中

　　　　master_info_repository = TABLE，将info信息放在表中，可以保护info信息，因为info表被innodb引擎保护

　　　　relay_log_info_repository = TABLE

　　　　relay_log_recovery = 1，自动恢复损坏的relaylog，info文件损坏的话，这个从库基本上被废掉

　　74、slave_skip_errors = ddl_exist_errors

　　　　从库将来可能会出现一些执行错误

　　　　默认情况下，如果出现执行错误，从库的sql线程就会停止很多时候需要手工干预，让从库跳过一些错误，继续执行

　　　　ERROR 1292 (22007): Incorrect datetime value: 'abc' for column 't1' at row 1

　　75、重点调整并行复制，从库调整

　　　　slave-parallel-type = LOGICAL_CLOCK

　　　　这个参数必须要设置成 LOGICAL_CLOCK，否则下面的参数设置不生效

　　　　slave-parallel-workers = 16 #开启16个并行 SQL 线程去应用，减小应用延迟

　　　　slave_preserve_commit_order=1  #这个一定要是1，因为开启了并行SQL线程去应用，1保证事务在 slave 上执行的顺序与 relay log 中的顺序严格一致，只有当“slave-parallel-workers”>0 时有效

　　　　slave_transaction_retries=128  #在SQL线程执行事务发生锁超时时，先尝试着再执行128 次，不至于一被锁住就马上回滚事务。