@gy-ban
2017-07-02T08:18:46.000000Z
字数 2797
阅读 621
mysql
最近发现有台数据库服务器不定时的会自动重启，查看日志发现是配置的参数不当导致内存使用超过服务器最大内存。
下面就对mysql配置参数调优总结下
内存配置调优的原则：
确定MySQL能使用的内存的绝对上限(比如是物理内存的多少)
确定MySQL会为每个连接使用多少内存，比如排序缓冲区和临时表(要充分估计最大连接数，如sort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_size)
确定操作系统需要多少内存来很好的运行自身，包括机器上其他程序
假设以上工作都完成，就可以把剩下的内存分配给MySQL的缓存，如InnoDB的缓存池
innodb_buffer_pool_size： InnoDB单个缓存池大小
作用：用于缓存索引和数据，所以设置过大或过小都不好，设置过大，不仅造成资源的浪费，还会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率，设置过小，会影响MySQL的性能。最大最好不要超过MySQL数据量和索引量的总的大小(要考虑后续数据的增长)。
推荐值：MySQL官方推荐其值为MySQL所在服务器物理内存的50%-80%，但我们应该根据具体情况来设定
计算公式：innodb缓存池总大小 <= 物理内存 - 连接数 * 每个连接分配的内存 - 系统程序内存
innodb_buffer_pool_instances: InnoDB缓存池的数量
key_buffer_size: MyISAM存储引擎索引缓存池大小
作用：用于缓存MyISAM索引，数据是根据操作系统来决定是否存储(根据索引量来决定，通过计算索引的大小来决定其大小)
推荐值：和MyISAM的索引量大小匹配即可
sort_buffer_size： 排序缓存池大小
作用：是MySql执行排序使用的缓冲大小
推荐值：根据服务器的最大连接数(非专业DBA请采用默认值)、以及排序的场景和服务器配置来决定(针对单个连接)
join_buffer_size： 表连接缓存池大小
作用：当我们的join是ALL,index,rang或者Index_merge的时候使用的缓冲区(针对单个连接)
read_buffer_size： 读入缓存池大小
作用：是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区
read_rnd_buffer_size： 随机读入缓存池大小
作用：是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。
thread_cache_size: 缓存的线程数量
作用：保存与当前线程无关的线程，以供新连接使用
推荐值：观察threads_created这个变量，如果其值小于10，则不用改变当前的配置；然后再观察threads_connected这个变量，如果其值在100-200之间，可以将缓存的线程数量设置为100，如果它在500-700之间，设置为200即可
table_open_cache: 打开表的缓存数
作用：为打开的表设置缓存，如果之后打开同名的表，可以从缓存中直接取出，不用直接再打开
推荐值：不断观察open_tables这个状态值来决定其大小。
table_definition_cache： 表定义缓存数
作用：为表的结构定义建立缓存,存放的表的结构定义信息
推荐值：根据表的数据来决定(最大为表的数量，当然最好是常用的表)
MySQL为了较高的性能，并不是每次操作都写入磁盘，也是写入事务日志，再根据相关的配置项，定时从事务日志中获取内容再写入到磁盘。
innodb_log_file_size: 单个事务日志的大小
innodb_flush_log_at_trx_commit: 从事务日志中把数据刷新到磁盘的规则
选项值：
0表示每秒进行一次log写入cache,并flush log到磁盘
1表示每次事务提交执行log写入到cache,并flush log到磁盘
2每秒事务提交，执行log数据写入到cache,每秒执行一次flush log到磁盘
推荐值：2
innodb_flush_method: 设置InnoDB同步IO的方式
推荐值：O_DIRECT
innodb_file_per_table: 将每个数据库的表的数据存储为一个单独的文件
推荐值：1
innodb_doublewrite ： 是否启用双写缓存
推荐值：1
open_files_limit： 最大打开文件数
推荐值：观察open_files来决定其值是否修改
innodb_read_io_threads: 读IO线程数
推荐值：可以与服务器CPU核数匹配
innodb_write_io_threads: 写IO线程数
推荐值：可以与服务器CPU核数匹配
sync_binlog: 是否同步二进制日志(在启用主从复制的服务器上)
推荐值：在主从复制的服务器的主服务器建议开启
tmp_table_size: 内部临时表的大小(为每个线程分配的大小)
作用：在查询时使用到内存临时表时，受该参数影响
推荐值：根据观察created_tmp_tables和created_tmp_disk_tables的大小来决定，如果created_tmp_disk_tables/created_tmp_tables的比例小于0.25时，则已经很好了，不需要修改，如果超过这个比例，则可以修改。这个最大值不能超过max_heap_table_size
max_heap_table_size： 内存表大小(为每个线程分配的大小)
expire_logs_day: 二进制日志保存的天数
read_only: 是否只读
推荐值：在从服务器上建议开启该选项
sql_mode: SQL模式
作用：限制SQL的执行模式
推荐值：生产环境建议为严格模式(STRICT_TRANS_TABLES)
max-allowed-packet: 设置网络传输中一次消息传输量的最大值，取值范围为1MB~1GB,必须设置为1024的倍数
max-connect-errors: 每个主机在连接请求异常中断的最大次数，当超过该次数，则禁止host的连接请求，直到服务器重启或flush hosts命令清空该host的相关信息
skip-name-resolve: 启动mysql禁用DNS主机名查找