内存相关
- innodb_buffer_pool_size 缓冲池,会缓冲索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数字字典信息等
- innodb_buffer_pool_instances 允许多个缓冲池实例,每页根据哈希平均分配到不同缓冲池实例中,减少数据库内部资源竞争,增加数据库并发处理能力
- innodb_old_blocks_pct 确定modpoint位置,默认37,modpoint指新读取到的页放入LRU(最近最少使用算法)列表中的位置,modpoint之后的列表称为old列表,之前的称为new列表
- innodb_old_blocks_time 表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端
- innodb_purge_batch_size 控制每次full purge回收的undo页的数量
- innodb_change_buffer_max_size 控制change buffer最大使用内存数量
IO相关
- MySQL 5.6 开始支持 Multi-Range Read (MRR),减少磁盘随机访问,将随机访问转化为较为顺序的访问,适用于 range, ref, eq_ref 类型的查询
- 查看是否开启 show variables like '%optimizer_switch%' mrr=on,mrr_cost_based=off(off为不判断,总是开启)'
- read_rnd_buffer_size控制键值的缓冲区大小,当大于该值时,执行器对已经缓存的数据根据RowID进行排序,并通过RowID来取得行数据,默认256K
- 5.6 开始支持Index Condition Pushdown(ICP),取出索引的同时,判断是否可以进行条件过滤,过滤后再去获取记录,可以大大减少上层SQL对记录的索取,支持range,ref,eq_ref,ref_or_null类型的查询。
- innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略
- 默认值为1,事务提交时必须调用一次fsync,将日志刷新到磁盘
- 0,事务提交时不写入重做日志,这个操作仅在master thread中完成,master thread每1秒会进行一次重做日志文件的fsync操作
- 2,事务提交时将重做日志写入重做日志文件,但仅写入文件系统的缓存中,不进行fsync操作
- 优缺点,#1数据有保障但是依赖于磁盘的性能;#2在操作系统宕机时会造成事务丢失。#0&#2的设置都会使事务丧失ACID特性
- innodb_purge_batch_size
- innodb_max_purge_lag & innodb_max_purge_lag_delay
- binlog_max_flush_queue_time
- innodb_read_io_threads & innodb_write_io_threads,多核cpu可以通过这两个参数更有效的利用cpu性能
- innodb_io_capacity 可以充分利用固态硬盘带来的高IOPS特性
- innodb_purge_threads 将purge线程从master线程分离出来,提高cpu使用率提升存储引擎性能,innodb1.2之后可以设置多个purge线程
- innodb_flush_neighbors 刷新邻接页,对于高iops的磁盘,建议关闭此特性
系统相关
- innodb_fast_shutdown 0表示完成所有full purge和merge insert buffer,并将所有脏页刷新回磁盘;1表示不需要完成full purge和merge insert buffer,但要刷新脏页;2表示不执行以上所有操作,但将日志全部写入日志文件,下次启动时需要恢复(recovery)
- innodb_force_recovery
SLOW QUERY 查询相关
- long_query_time
- log_slow_queries
- log_queries_not_useing_indexes 开启后,没有用到索引的查询也会记录到slowlog里
- log_throttle_queries_not_using_indexes 5.6.5新增,表示每分钟允许记录到slow log的未使用索引的sql语句次数
- 可以使用mysqldumpslow命令分析slow log
- log_output,有FILE和TABLE两种,动态修改且是全局的
数据安全性相关
- sync_binlog=[N]表示每写缓冲多少次就同步到磁盘,1表示同步写磁盘的方式写二进制日志。默认为0,由操作系统决定同步
- innodb_support_xa=1,可以保证二进制日志和InnoDB存储引擎数据文件的同步
- innodb_log_file_size 指每个重做日志文件的大小,innodb1.2之前必须小于4G,之后扩大到512G
- innodb_log_files_in_group 指定了日志文件组中重做日志文件的数量,默认为2
- innodb_mirrored_log_groups 指定了日志镜像文件组的数量,默认为1
在线DDL相关
- innodb_online_alter_log_max_size Online DDL 原理是在执行创建或删除操作的同时,将INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中,此参数控制缓存大小,默认128M
时间: 2024-11-02 05:49:04