西宁网站建设君博推荐,致远oa系统,wordpress无法访问站点,网站敏感关键词.txt本文主要基于MySQL 5.7版本的数据库环境#xff0c;总结my.cnf文件中核心参数的配置使用#xff0c;让更多的人对MySQL技术体系有更全面、更专业的深度了解。一、客户端核心参数1、port端口号#xff0c;默认33062、socketSocket文件地址#xff0c;默认以.sock为文件名称后…本文主要基于MySQL 5.7版本的数据库环境总结my.cnf文件中核心参数的配置使用让更多的人对MySQL技术体系有更全面、更专业的深度了解。一、客户端核心参数1、port端口号默认33062、socketSocket文件地址默认以.sock为文件名称后缀用于UNIX套接字通信。二、服务端全局区(SGA)缓冲区参数1、innodb_buffer_poolInnoDB缓冲池位于主内存缓存被访问过的表数据、索引文件、插入缓冲、数据字典等推荐大小设置为物理内存的50%-80%2、innodb_buffer_pool_instance将缓冲池进一步划分为互相隔离的内存区域更好的支持并发读写减少内存争用现象默认值为1仅当innodb_buffer_pool参数配置大于1024MB有效通过命令show engine innodb status查看每个内存区域的内存使用情况3、innodb_old_blocks_time和innodb_old_blocks_pctInnoDB缓冲池innodb_buffer_pool内部由LRU链表管理LRU链表进一步分为old pages list和young pages listold pages list存放长时间未被访问的数据页young pages list存放最新、最近被访问的数据页当超过innodb_old_blocks_time参数设置的时间时就会移动到old pages list默认是1000ms。innodb_old_blocks_pct参数决定old pages list在LRU链表及innodb_buffer_pool中占比存在大表扫描或者执行mysqldump备份操作时可能会剔除热数据增加I/O压力因此要适当减少innodb_old_blocks_pct参数值4、innodb_log_buffer_size和innodb_log_waitsinnodb_log_buffer_size日志缓冲区的大小取值范围16MB-64MBinnodb_log_waits等待日志缓冲刷出的次数通过命令show global status like %innodb_log_waits%查看如果发现innodb_log_waits值大于0并且持续增长可以考虑增大innodb_log_buffer_size5、innodb_log_buffer事务在内存中的缓冲区的大小即redo log buffer6、query_cache_size和query_cache_typequery_cache_siz静态数据的缓存区大小默认关闭建议关闭保证数据库的TPS数据仓库场景可能会选择开启实例启动前设置query_cache_size0和query_cache_typeoff才算真正关闭三、服务端程序缓冲区(PGA)参数1、sort_buffer_size用于SQL语句在内存中的临时排序2、join_buffer_size表连接使用用于BKA3、read_buffer_sizeMyISAM存储引擎的表顺序扫描的缓存4、read_rnd_buffer_size随机读缓冲区大小用于mrr特性5、tmp_table_size和max_heap_table_sizetmp_table_sizeSQL语句在排序或者分组时没有用到索引会使用临时表空间max_heap_table_size管理heap、memory存储引擎表建议tmp_table_size和max_heap_table_siz大小配置一致三、服务端刷盘参数内存中的数据需要根据刷新机制决定在某一时刻如何刷新到磁盘刷新机制涉及redo log buffer、data buffer、binlog cache等内存缓冲区read thread、write thread、redo log thread、change buffer thread四大I/O线程及master thread后台主线程。1、innodb_flush_log_at_trx_commit控制由内存redo log buffer写入磁盘relo log、和执行刷盘操作的频率可选参数值(实践根据业务场景选择)0:redo log thread每隔1s会执行一次刷盘策略此种情况每次事务提交不会触发redo log thread将日志缓冲中的数据写入redo log性能最好但是安全性最低MySQL进程崩溃将会导致丢失1s的数据1:每次事务提交时都会触发redo log thread将日志缓冲中的数据写入redo log并且执行刷盘操作以便保证数据确实已经写入磁盘安全性最高但是性能最差2:每次事务提交时都会触发redo log thread将日志缓冲中的数据写入redo log但是不会同时执行刷盘操作另外两个决定redo log buffer刷到磁盘的条件master thread每秒进行刷新redo log buffer使用超过一半时触发刷新2、sync_binlog控制由内存binlog cache刷新到磁盘binglog的频率参数值为正整数0:每次事务提交后不主动做fsync之类的磁盘同步指令刷新binlog cache让FileSystem自行决定同步时机或者binlog cache满才同步到磁盘1:每次事务提交后主动做刷新binlog cache到磁盘n:每进行n次事务提交后主动做fsync之类的磁盘同步指令刷新binlog cache到磁盘3、数据库的双一模式innodb_flush_log_at_trx_commit 1sync_binlog 14、innodb_max_dirty_pages_pct控制脏页的刷新频率在buffer pool中dirty page所占的百分比达到阈值会触发page cleaner thrad执行脏页刷新默认值是75%建议设置为25%-50%之间保证数据库整体的TPS5、innodb_flush_method控制InnoDB数据文件、redo log文件的打开刷写模式可选参数值O_SYNC:O_DSYNC:O_DIRECT:数据文件直接从MySQL InnoDB Buffer写入到磁盘不用通过OS buffer四、服务端并发量参数1、innodb_max_thread_concurrencyInnoDB内核最大并发线程数默认值为0(含义不受限制)2、innodb_open_filesInnoDB可同时打开的.ibd文件个数参数值规则10默认值300建议调整为65535提高并发数3、max_connections和innodb_thread_concurrencymax_connections控制MySQL数据库最大的连接数默认值151高并发场景中经常遇到的too many connections异常报错即为此参数配置过低或者集群需要水平扩容innodb_thread_concurrency控制InnoDB内部并发数参数值为正整数(默认值为0)0:不受限制n:并发数为n建议设置为服务器逻辑CPU核数的2倍根据实际情况由小及大逐步调整验证五、服务端事务参数1、transaction_isolation事务隔离级别可选参数值(默认值REPEATABLE-READ)READ-UNCOMMITTED读未提交READ-COMMITTED读已提交REPEATABLE-READ可重复读SERIALIZABLE序列化六、服务端影响I/O性能的参数1、innodb_log_file_sizeredo log日志的大小需要合理设置如果设置偏小将会导致redo log切换频繁产生无用的I/O消耗影响数据库性能如果设置偏大将会导致实例宕机恢复需要较长时间2、innodb_log_files_in_groupredo log文件组中的日志文件的数量默认情况下至少需要2个。3、innodb_stats_on_metadatainformation_schema中的表某些监控程序读取innodb_stats_on_metadata表可通过命令set global innodb_stats_on_metadata0关闭七、服务端慢查询参数1、show_query_log慢查询日志开关可选参数值1:开启状态推荐配置2、long_query_time慢查询的时间阈值单位为秒超过阈值就会记录到慢查询日志文件3、log_queries_not_using_indexs运行的SQL没有使用索引是否记录到慢查询日志文件可选参数值on记录推荐配置set global set log_queries_not_using_indexsonoff不记录八、服务端二进制文件参数1、expire_logs_daysbinlog文件的过期时间单位为天2、binlog_format二进制日志文件的内容格式可选参数值statement记录的是一条SQL语句row此模式较为安全不会出现跨库复制丢失数据的情况一般中间件采集数据解析也是这种模式记录行变更前的样子及变更后的内容mixed混合模式九、服务端其它参数1、innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown快速加载实例宕机前内存中的热数据减少磁盘I/O压力可选参数值(默认0)0关闭状态1启用状态在实例关闭时将热数据的元数据信息保存到innodb_buffer_pool_filename参数指定的文件(默认ib_buffer_pool)在实例启动时从innodb_buffer_pool_filenam参数指定的文件(默认ib_buffer_pool)快速加载回内存2、innodb_data_file_path指定系统表空间文件的路径和ibdata1文件的大小默认大小是10MB3、interactive_timeout和wait_timeoutinteractive_timeout是服务器关闭交互式连接前等待活动的时间默认值28800s即8小时iwait_timeout是服务器关闭非交互式连接前等待活动的时间默认值28800s即8小时两个参数需要一起调整并且保持一致默认值时间过长建议调整为300s-600s之间4、server-id主从环境中当前MySQL在主从结构中的唯一标识5、lower_case_table_names表名称是否区分大小写可选参数值0:区分大小写1:不区分大小写实际存储使用小写6、innodb_fast_shutdown和innodb_force_recovertyinnodb_fast_shutdown控制表的InnoDB存储引擎在关闭时的行为可选参数值0默认值需要执行purge all、merge change buffer、flush dirty pages操作此方式是最慢的关闭方式但是restart时最快1不需要执行purge all、merge change buffer等操作但是需要执行flush dirty pages操作2不完成purge all、merge change buffer等操作也不刷新脏页到磁盘只是将日志写入日志文件因此不会丢数据但是重新启动会进行recovery操作7、innodb_force_recoverty控制表的InnoDB存储引擎在恢复时的行为可选参数值0:默认值当需要恢复时执行所有操作1:忽略检查到的corrupt页2:阻止主线程的运行3:不执行事务回滚操作4:不执行插入缓冲的合并操作5:不查看撤销日志InnoDB存储引擎会将未提交的事务视为已提交6:不执行前滚操作7、innodb_status_output和innodb_status_output_locks数据库监控信息记录到error log建议关闭防止错误日志增长过快造成磁盘空间使用紧张8、innodb_io_capacityInnoDB后台进程最大的I/O性能指标影响刷新脏页和插入缓冲的数量默认值为200可根据实际使用的磁盘类型选择调整9、auto_increment_increment和auto_increment_offsetauto_increment_increment表示自增长字段每次递增的量默认值为1auto_increment_offset表示自增长字段从哪个值开始默认值为1欢迎订阅个人公众号浏览更多MySQL技术体系知识。
