0%

MySQL系统配置

MySQL系统配置

内存相关

  • sort_buffer_size 定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存)(innodb_sort_buffer_sizemyisam_sort_buffer_sizesort_buffer_size)
  • join_buffer_size 定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲
  • read_buffer_size 定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数
  • read_rnd_buffer_size MySQL的随机读缓冲区大小,MySQL有查询按任意顺序读取行需要时会为其分配内存,进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值

上述四个参数配置是按照每个线程进行分配的,所分配的内存还要乘以线程数

  • innodb_buffer_pool_size 定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中

  • key_buffer_size 定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间

    即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表

  • record_buffer:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,可能想要增加该值
  • thread_cache_size:保存当前没有与连接关联但是准备为后面新的连接服务的线程,可以快速响应连接的线程请求而无需创建新的
  • table_cache:类似于thread_cache _size,但用来缓存表文件,对InnoDB效果不大,主要用于MyISAM

IO相关

  • innodb_additional_mem_pool_size:InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL会记录Warning信息到数据库的错误日志中,这时就需要该调整这个参数大小,小于2G内存的机器,推荐20M;32G内存以上推荐为100M

  • innodb_log_buffer_size:InnoDB存储引擎的事务日志所使用的缓冲区,一般来说不建议超过32MB

  • innodb_log_file_size : 事务日志文件大小,100M以下

  • innodb_log_files_in_group :事务日志个数,一般2-3个

  • innodb_flush_log_at_trx_commit : 事务日志刷新策略 ,其值如下:

    0:每秒进行一次 log 写入 cache,并 flush log 到磁盘,性能最高

    1:在每次事务提交执行 log 写入 cache,并 flush log 到磁盘,最安全

    2:每次事务提交,执行 log 数据写到 cache,每秒执行一次 flush log 到磁盘,折中

  • innodb_max_dirty_pages_pct: 默认是75,表示达到75%的时候刷写内存脏页到磁盘

  • max_connections 控制允许的MySQL实例的最大连接数,上限值是16384

  • max_user_connection:每个数据库用户的最大连接数,默认为0无上限,最好设一个合理上限

  • back_log:back_log值可以指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。可以从默认的50升至500

  • wait_timeout:数据库连接闲置时间,闲置连接会占用内存资源。可以从默认的8小时减到半小时

  • thread_concurrency:并发线程数,设为CPU核数的两倍

  • skip_name_resolve:禁止对外部连接进行DNS解析,消除DNS解析时间,但需要所有远程主机用IP访问

  • query_cache_size:查询缓存,缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们数据变化非常频繁的情况下,使用Query Cache可能得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大。可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小

  • tmp_table_size 临时表大小