query_cache_type = 0
关闭mysql查询缓存功能
query_cache_size = 0
用于缓存sql语句查询结果的缓冲区大小,在一次查询之后,会将查询结果缓存,如果涉及到的表未发生变化,在下次查询时,直接在缓存取结果,如果涉及到的表发生变化,将和该表对应的全部缓存失效。在插入或者更新很多的系统,由于>缓存失效带来的影响可能会超过缓存带来的收益,所以可以根据业务特点选择禁用缓存。
tmp_table_size = 32M
临时表大小,默认32M,如果做很多高级groupby操作,适当增加这个值。
innodb_additional_mem_pool_size = 16M
这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小
IO相关参数:
innodb_log_file_size = 256M
控制单个事物日志文件大小,如果业务繁忙,大log文件会带来较好的性能,一般事物文件应当可以记录服务器一个小时左右时间的事物,但是在崩溃恢复时需要更久时间。事物日志是循环使用的,写满一个就会使用下一个。
事物日志并不是直接写入到日志文件中,而是先写入到缓冲区,缓冲区大小由innodb_log_buffer_size控制。
innodb_log_buffer_size = 32M
事务日志所用缓冲区大小,一般情况下,不需要设置非常大,因为一般1秒钟就会进行一次刷新到磁盘的操作,缓冲区一般只要可以保存至少1秒钟的事物就足够了。适当增大可以提高性能,但是一般情况下32M-128M就已经可以了。
innodb_log_files_in_group:
控制事物日志文件的个数,事物日志总大小=innodb_log_file_size * innodb_log_files_in_group
innodb_flush_log_at_trx_commit = 2 # may change to 2 or 0
刷新事物日志的频繁程度,对性能的影响比较大。
0 表示每秒钟进行一次log写入到cache,并刷新cache到磁盘,在事务提交时并不会进行任何操作,在mysql崩溃时,会丢失一秒钟的事务。
1 默认值,表示在每个事物结束后,都会执行log写入cache,并将cache flush到磁盘,这是最安全的设置,保证不会丢失任何已提交的事务,除非操作系统或者磁盘做了伪刷新,这个值性能是最差的。
2 建议值,表示每次事务提交后,将log写入cache,每秒钟将cache刷新到磁盘。
0 与 2 的区别在与,如果mysql进程崩溃,设置为2不会丢失任何数据,只有服务器宕机才会丢失一秒钟的数据。
在myisam中delay_key_write控制缓冲数据什么时候刷到磁盘文件中.
off: 表示关闭延迟键写入,每次写操作后刷新数据到磁盘文件中,最安全也是性能比较差的选项。
on: 只对建表时指定了delay_key_write的选项的表进行延迟刷新。
all: 对所有myisam表都使用延迟键写入。
如果启用了延迟刷新,服务器崩溃时候,有数据没有刷新到磁盘,将会导致myisam索引文件的损坏,则需要使用repair table 对损坏的表进行修复。
innodb_flush_method = O_DIRECT
避免操作系统和innodb对数据的双重缓冲,对linux设置为O_DIRECT
innode_file_per_table = 1
控制innodb如何使用表空间,如果设置这个参数,会为每个表建立一个单独的表空间,否则会将所有表的数据放在系统表空间,强烈建议启用这个参数。
innodb_doublewrite = 1
控制innodb是否使用双写缓存,主要用于避免页没有写完整导致的数据损坏,会对性能有些影响但是提高了数据安全性。
max_connections = 1024
最大连接数,如果同时连接mysql的程序很多,需要对应增加这个参数的值,比如2000,具体值根据应用环境定。
内存相关参数:
sort_buffer_size = 2M
排序缓冲区大小,定义了每个线程使用的排序缓冲区的大小,不是为每个连接都分配这个缓冲区,只有当连接查询需要排序操作时才进行分配。
与connection相关,每个connection 需要使用这个buffer时,一次分配指定缓冲大小的全部内存,不管改排序是否需要这么大的内存,不是越大越好,过大的值在连接过多的时候会耗尽系统资源。
join_buffer_size
join操作用到的buffer的大小,如果一个查询join了多张表,就会为每个join分配一个缓冲区,不宜过大。
read_buffer_size
对myisam(不确定是不是不包含innodb)表进行全表扫描时,读缓冲池的大小,只会在有查询需要时才会一次性分配该参数制定的大小的内存,一定要是4k的倍数。
顺序读读入缓冲区大小,如果顺序读请求频繁,可以增加这个值.
read_rnd_buffer_size
控制索引缓冲区的大小,只有在有查询需要时,才会为该缓存区分配内存,并且分配需要的内存大小而不是参数指定的内存大小(和上面三个不一样?),随机读(查询)缓冲区大小,需要排序大量数据时,增大该值可以避免进行磁盘搜索,提高速度。
以上四个参数都是对线程而言,如果一个数据库线程数很多,以上四个参数设置过大,很有可能造成内存溢出,导致服务器崩溃。
缓冲池大小参数:
innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
对innodb性能影响很大,innodb缓冲不仅要缓冲索引还要缓冲数据,锁,以及其他内部数据结构,同时还使用缓冲池帮助延迟写入以合并多个写入操作,一起顺序写入磁盘,innodb性能严重有依赖缓冲池。但是过大的缓冲池会影响数据库关闭时的速度,在数据库关闭时,需要更多时间将缓冲中的数据刷新到磁盘。
缓冲池是用来在内存中缓存数据和索引的区域,它用来将经常使用的数据缓存在内存,这个值可以设置为50%-70%的内存值,并且innode_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances,mysql官方文档在这里。
总内存 - (每个线程使用的内存数 * 连接数)- 系统保留内存
key_buffer_size = 32M
用于索引的缓冲区大小,增加可以获得更好的索引处理性能。
主要用于myisam存储引擎,myisam存储引擎只会缓存索引,数据的缓存依赖与操作系统。
使用select sum(index_length) from information_schema.tables where engine = "myisam"获取myisam表索引占据的空间大小。
及时数据库中所有表都是使用innodb存储引擎,还是要为这个值制定一部分缓冲空间,因为系统表还在使用myisam存储引擎。
安全相关参数:
expire_log_days:
制定自动清理binlog天数,如果启用了binlog,则应该打开这个选项,让服务器在制定的天数之后自动清理旧的日志。至少覆盖两次全备的天数,如果每天全备,则至少保存7天左右。
max_allowed_packet = 33554432 # 32M
mysql接受的最大包的大小,也会影响用户定义的最大变量的大小。如果主从复制,主从的大小应该一致。
网络中一次传输量的最大值
skip_name_resolve
禁用DNS查找
sysdate_is_now
确保sysdate()函数和now()函数返回的结果一致
read_only
主从复制的从库中启用,禁止没有super权限的用户的数据变更操作,只接受主库中传输过来的数据变更,对保证主从复制数据的一致性很有用。
skip_slave_start
禁用slave自动恢复,阻止mysql在启动后自动进行复制,在服务器崩溃重启后,检查服务器无问题后,再手动启动复制。
sql_mode
设置mysql所使用的sql模式
strict_trans_tables如果给定的数据不能插入到事务型存储引擎中,则会中断当前操作,对非事务存储引擎,该参数无任何影响。
no_engine_subtition 在建表语句中制定的存储引擎不可用的情况下,不会使用默认存储引擎建立表。
no_zero_date 禁止0000年00月00日这样的日期写入到日期字段。
no_zero_in_date 不接受部分日期为0的日期。
only_full_grouy_by 分组查询中,groupby 将所有没有聚合函数的列列出,否则报错。
生产环境不要轻易改动这个值,否则可能造成应用程序出错。
max_connect_errors = 512
如果客户端尝试连接,错误次数超过此限制,则拒绝连接。
thread_cache_size = 128
表示可以重新利用,放在缓存的线程数量,根据服务器内存大小进行调整。
其他参数
sync_binlog
控制MySQL怎么刷新二进制日志到磁盘
0 默认值, 表示MySQL并不会主动刷新日志到磁盘,而是有操作系统决定何时刷新日志到磁盘
其它大于0的值,表示两次刷新到磁盘之间间隔多少次二进制日志的写操作,如果设置为1表示每次写二进制日志都刷新到磁盘,对于主从复制的主库,建议设置为1,这样每次事务之后都会进行磁盘写操作,以保证数据安全 。
tmp_table_size和max_heap_table_size
控制使用memory引擎的内存临时表能使用内存大小,如果内存临时表大小超过了指定的大小,将转换为磁盘临时表,这两个值应当保持一致,并且不要过大。
ref: https://blog.csdn.net/orichisonic/article/details/48026031
http://mysql.rjweb.org/doc.php/memory