- 1.innodb_buffer_pool_size
- 2.innodb_buffer_pool_instances
- 3.innodbbuffer_pool_load_at(热数据快速加载 )
- 4.innodb_data_file_path
- 5.内存刷新参数
- 6.innodb_threaad_concurrency
- 7.连接关闭等待时间参数
- 8.innodb_flush_method
- 9.LRU链表相关
- 10.transaction_isolation 隔离级别参数
- 11.innodb_open_files
- 12.日志缓冲相关
- 13.innodb_log_files_in_group
- 14.max_connections
- 15.expire_logs_days
- 16.slow_query_log
- 17.log_query_time
- 18.log_queries_not_using_indexes
- 19.server-id
- 20.binlog_format
- 21.lower_case_table_names
- 22.innodb_fast_shutdown
- 23.innodb_force_recovery
- 24.错误日志增长相关
- 25.innodb_io_capacity
- 26.自增长字段相关
在my.cnf中,分为client section和server section两块
其中,clinet section是用于配置mysql客户端的参数
1.innodb_buffer_pool_size
该缓冲池位于主内存中。InnoDB用它来缓存被访问过的表和索引文件。推荐分配值为物理内存的50%~80%,否则容易出现系统内存不够导致swap或oom的现象。mysql5.7之后已支持在线修改
swap:当可用内存大小,低于内核中设置的内存大小值时,linux启动将内存中不常用的内存,暂存到硬盘上,释放出更多可用内存
oom:内存泄漏
2.innodb_buffer_pool_instances
该参数默认值为1,5.6.6之后可以调整为多个,表示InnoDB缓冲区可以被划分为多个环境,也可以理解为把InnoDB_buffer_pool划分为多个实例,可以提高并发性。通过show engine innodb status也可以看到每个instance使用内存的情况。
只有当innodb_buffer_pool大于1GB时,生成的innodb_buffer_pool多实例才生效
3.innodbbuffer_pool_load_at(热数据快速加载 )
- innodb_buffer_pool_load_at_startup
- innodb_buffer_pool_load_at_shutdown
将以上两个参数开启(设置为1),然后在数据库实例关闭时将热数据的元数据信息 dump 出来,保存到 ib_buffer_pool 文件中。接着在实例再次启动时,把元数据信息快速加载回内存。
上述的元数据信息即space number 和 page number的列表信息
4.innodb_data_file_path
该参数可以指定系统表空间文件的路径和ibdata1文件的大小。默认是10MB,建议设为1GB
5.内存刷新参数
- innodb_flush_log_at_trx_commit
- sync_binlog
- innodb_max_dirty_pages_pct
这三个参数是影响redo log、binlog、脏页的刷新参数
内存刷新机制(日志文件)
6.innodb_threaad_concurrency
7.连接关闭等待时间参数
- interactive_timeout
- 服务器关闭交互式连接前等待活动的时间
- 默认是28800(8小时)
- wait_timeout
- 服务器关闭非交互式连接之前等待活动的时间
- 默认是28800(8小时)
这俩个参数值要一致,最好调整为300-600s,避免连接时间过大
8.innodb_flush_method
影响InnoDB数据文件、redo log文件的打开刷写模式,主要有三个值
- O_SYNC
- O_DSYNC
O_DIRECT(推荐,数据文件直接从Mysql InnoDB Buffer写入磁盘,不用经过os buffer)
9.LRU链表相关
innodb_old_blocks_time
- innodb_old_blocks_pct
InnoDB缓冲池内部是由LRU链表管理,而LRU链表分为old pages list和young pages list。old里面存放的是长时间未被访问的数据页,young中存放最新或最近被访问的数据页。当时间超过innodb_old_blocks_time参数值时,yong就会移动到old中,默认为1000ms。
innodb_old_blocks_pct决定old占整个buffer pool的比例。通过减少比例分配,可以保证更多的热数据不会被冲掉
10.transaction_isolation 隔离级别参数
有四个值,分别对应不同的隔离级别:
- read-uncommitted:读未提交,允许脏读
- read-committer:读提交,不允许脏读,但允许不可重复读
- repeatable-read:可重复读,不允许脏读、不可重复读,但允许幻读;是默认值
-
11.innodb_open_files
InnoDB可同时打开的.ibd文件个数,最小值为10,默认值为300,建议设置为65535。
.ibd是表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据。12.日志缓冲相关
innodb_log_buffer_size
- innodb_log_waits
- innodb_log_file_size
innodb_log_buffer_size是日志缓冲大小。当InnoDB改变数据时,会先将改动记录写入到日志缓冲中。如果innodb_log_waits即等待日志缓冲刷出的次数值大于0,且持续增长,就可以增大log buffer大小,取值范围为16MB-64MB。
innodb_log_file_size是指redo log日志的大小,设置过大会导致实例恢复耗时过长,设置过小会导致redo log切换频繁,产生无用的I/O消耗进而影响数据库性能。
13.innodb_log_files_in_group
redo log 文件组中日志文件的数量,默认情况下至少有2个
14.max_connections
mysql最大连接数,默认值151。当连接请求大于默认连接数时,可以采取:
- 增加max_connections值
- 调整InnoDB内部并发数(innodb_thread_concurrency,该参数mysql5.6.6后默认为0,这代表并发数不受限制,建议改为服务器逻辑CPU核数两倍)
- 设置连接池(thread pool),让连接复用。mysql5.7的商业版引入了这一项。
- 有的监控程序会读取information_schema下面的表,触发统计信息收集,导致一些不常用的数据也进入innodb_buffer_pool中,导致耗费时间变成以及池污染。可以考虑关闭 innodb_stats_on_metadata
- set global innodb_stats_on_metadata=0
-
15.expire_logs_days
16.slow_query_log
慢查询日志的开关,该参数等于1代表开启慢查询。(生产环境要开启慢查询日志)
17.log_query_time
慢查询的时间,某条sql语句超过该参数设置的时间,就会记录到慢查询日志中,该参数单位是秒,默认是10秒。
18.log_queries_not_using_indexes
这个参数开启后会记录没有使用索引的sql语句于慢查询日志,生产环境推荐开启
statement—记录每一条会修改数据的sql语句
- row(生产环境采用)—仅记录哪条数据被修改了
-
21.lower_case_table_names
表名是否区分大小的参数。默认值为0,即区分大小写;1代表不区分大小写,以小写存储
22.innodb_fast_shutdown
存储引擎为InnoDB在关闭时的行为,分为:
0—默认值,在关闭时执行purge all、merge change buffer、flush dirty pages操作。这种方式最慢,但是restart最快
- 1—在关闭时只需要执行flush dirty page
2—再关闭时不做上述三个操作,只将日志写入日志文件,虽然不会丢数据,但是下次启动会recovery
23.innodb_force_recovery
影响InnoDB存储引擎恢复时的行为,取值有:
0—默认值,表示当需要恢复时执行所有的恢复操作
- 1—忽略检查到的corrupt页
- 2—阻止主线程的运行,如主线程需要执行full purge操作,会导致crash
- 3—不执行事务回滚操作
- 4—不执行插入缓冲的合并(insert buffer)操作
- 5—不查看撤销日志,会将未提交的事务视为已提交
-
24.错误日志增长相关
innodb_status_output
- innodb_status_output_locks
这两个参数建议关闭,否则会将数据库监控的所有数据记录在error log中,使错误日志增长过快。
25.innodb_io_capacity
InnoDB后台进程最大的I/O性能指标,影响刷新脏页和插入缓冲的数量。默认值为200。
26.自增长字段相关
- auto_increment_increment—默认为1,表示自增长字段每次递增的量
- auto_increment_offset—默认值为1,表示自增长字段从哪个值开始