MySQL最佳安装之参数调整

目的

通过根据服务器目前状况，修改Mysql的变量参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。因此，我将mysql在启动前需要调整的参数做了分类，便于大家理解。

常用的一些调整

sql_mode=''
autocommit= 
character_set_server=
transaction_isolation=  //以上参数需要跟开发人员协商，由开发人员决定


max_allowed_packet= //允许最大包的大小，
event_scheduler=1 //允许建立event，event周期性执行
loose_innodb_numa_interleave=1  //数据库层面关闭 numa，尽量在 MySQL 数据库服务器在 BIOS 层面关闭NUMA

skip_name_resolve = 1 //关闭反向解析

innodb相关参数

innodb_buffer_pool_size=   //一般是物理内存的50%~80%左右
innodb_buffer_pool_instances= //一般10G分配一个pool,在latch锁争用比较厉害的情况下，可以增加池子的数量
innodb_page_size= //数据页的大小，一般是16K
innodb_buffer_pool_dump_at_shutdown = 1  //保证关闭后，热数据会继续加载和保留在热数据区
innodb_buffer_pool_load_at_startup=1 //5.7新特性，数据库启动时加载热数据



innodb_doublewrite=  //是否开启double write
innodb_flush_neighbors = 0  //是否开启是适应hash索引

用户连接线程相关

sort_buffer_size=   //排序空间大小，如果过小，会产生磁盘排序
join_buffer_size=   //表连接空间，多表连接会用到
read_buffer_size=  //只是针对MyISAM表，强列建议降低该参数值
read_rnd_buffer_size=  //针对所有的表都生效，可以调大一点。建议16M即可
binlog_cache_size=  //二进制日志缓存空间，如果过小，会产生binglog_cache_disk_use
tmp_table_size=   //临时表空间的大小，如果过小，会产生磁盘临时文件

连接相关

max_connections = 1500 //最大连接数不要超过2000
max_connect_errors = 1000000  //最大连接错误数，防止暴力破解
thread_cache_size=  //对于频繁额连接和断开的情况下，防止线程频繁的建立和销毁

读写线程相关

innodb_write_io_threads=  //默认情况下会调成CPU核数
innodb_read_io_threads=   //默认情况下会调成CPU 核数
innodb_purge_threads=
innodb_page_cleaners
innodb_thread_concurrency = 64 //允许并发线程的数量

数据文件相关的(redolog、binlog、slowlog、errorlog)

slow_query_log=1 //开启慢查询
log_queries_not_using_indexes = 1
long_query_time=2




log-bin=mysqlbinlog
binlog_format = mixed
expire_logs_days = 90  //建议手动删除binlog
binlog-rows-query-log-events = 1 //记录原生的SQL语句
sql_log_bin=  //在会话级别临时性关闭binlog
sync_binlog=1



innodb_log_file_size=
innodb_log_files_in_group
innodb_flush_log_at_trx_commit=1


log_error = error.log

锁相关

innodb_lock_wait_timeout=   //事务锁的超时时间
lock_wait_timeout=   //表锁的超时时间，可以适当减小，降低锁冲突
innodb_print_all_deadlocks=  //将死锁的信息打印到错误日志中去
innodb_autoinc_lock_mode=   //自增长锁的工作模式，建议是2

undo相关

innodb_undo_logs = 128  //undo段的数量
innodb_undo_tablespaces = 3 //将undo从ibdata中独立出来

主从、并行复制相关

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1，自动恢复损坏的 relaylog
slave_skip_errors = ddl_exist_errors



并行复制：
slave-parallel-type = LOGICAL_CLOCK 
slave-parallel-workers = 16 //开启16个SQL线程并行应用
slave_preserve_commit_order=1  //保证事务在从库上执行顺序跟realylog中一样
slave_transaction_retries=128 //执行事务发生锁超时时，先尝试着再执行 128 次，不至于一
被锁住就马上回滚事务。