MySQL系统配置

MySQL系统配置

内存相关

• sort_buffer_size 定义了每个线程排序缓存区的大小，当索引无法满足需要的顺序信息时会用到，执行计划中出现using filesort，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）(innodb_sort_buffer_size和 myisam_sort_buffer_size和 sort_buffer_size)，可以根据sort_scan、sort_rows、sort_range、sort_merge_passes状态来检查执行了多少个排序操作。也不能设置的过大，在高并发的情况下使用过大的值，可能会导致内存耗尽

  通过show global status like 'sort%'来查看排序情况。

  sort_merge_passes的步骤如下：mysql先会尝试在内存中进行排序，使用的内存大小sort_buffer_size决定，如果该值不够大则把所有的记录读到内存中，mysql会把每次在内存中排序的结果存到临时文件中，等mysql找到所有记录后，再把临时文件中的记录做一次排序，此时会增加sort_merge_passes的值

• join_buffer_size 定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，当索引无法满足连接条件时，会用到这个缓冲区，执行计划中如果有using join buffer，可以通过检查select_scan状态(表示第一张表执行完整扫描的连接数量)和select_full_range_join状态(表示使用范围搜索的连接数量)来判断是否需要设置更大的值

• read_buffer_size 用于表的顺序扫描，表示每个线程分配的缓冲区大小，在进行全表扫描时，mysql会按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，提高效率，其必须是4k的倍数

• read_rnd_buffer_size MySQL每个线程的随机读缓冲区大小，MySQL有查询按任意顺序读取行需要时会为其分配内存，进行排序查询时，利用该缓冲区来暂存读取的数据，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值

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

• innodb_buffer_pool_size 定义了Innodb所使用缓存池的大小，存储数据和索引页，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中，这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。物理内存的60%~70%，典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)

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

• innodb_buffer_pool_instances 缓冲池实例个数

• key_buffer_size MyISAM为索引键设置缓冲区大小，使用时才真正分配，索引缓冲区所有线程共享，增加索引缓冲区可以得到更好处理的索引，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间

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

  通过 show global status like 'key_read%'查看key_buffer_size的使用情况

  未命中缓存的概率 key_cache_miss_rate = Key_reads / Key_read_requests * 100%，该值在0.1%以下都很好，如果在0.01%以下，则可适当减少key_buffer_size

• record_buffer：每个进行顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值

• table_cache_size: 缓存表的数量，对InnoDB效果不大，主要用于MyISAM

• table_cache：类似于thread_cache _size，但用来缓存表文件，同时打开表的个数，对InnoDB效果不大，主要用于MyISAM

• max_heap_table_size: 内存临时表最大大小

• tmp_table_size 临时表大小，检查Created_tmp_disk_tables状态来看是否需要增大该配置

  临时表的大小取得是max_heap_table_size和tmp_table_size的最小值

  通过show global status like 'created_tmp%'查询临时表情况，Created_tmp_disk_tables表示在磁盘上创建临时表；Created_tmp_files表示创建的临时文件数

  Created_tmp_disk_tables / Created_tmp_tables*100% <=25%就可以

• innodb_file_per_table: 每个表一个文件，InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table = OFF） 或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间

• max_length_for_sort_data: 用于排序数据的最大长度，可以影响MySQL选择哪个排序算法

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

  通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小，可以使用query_cache_type来启用或禁用查询缓存

  • Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片，使用flush query cache 会对缓存中的碎片进行整理
  • Qcache_free_memory 缓存中的空闲内存
  • Qcache_hits 命中次数
  • Qcache_inserts 每次插入一个查询时就会增大
  • Qcache_lowmem_prunes 多少条query因为内存不足而被清除出query cache
  • Qcache_not_cached 不适合进行缓存的查询数量
  • Qcache_queries_in_cache 当前缓存的查询数量
  • Qcache_total_blocks 缓存中块的数量

• query_cache_limit 超过此大小的查询将不缓存

• query_cache_min_res_unit 缓存块的最小值

• query_cache_type 缓存类型

• query_cache_wlock_invalidate 当有其他客户端在对MyISAM表进行写操作时，读操作要等write lock释放资源后在查询还是允许直接从query cache中读取结果(FALSE 表示允许从query cache中读取结果)

• Innodb_buffer_pool_read_ahead 预读的次数

• Innodb_buffer_pool_read_ahead_evicted 预读的页，但是没有被读取就从缓冲池中被替换的页的数量，用来判断预读的效率

• Innodb_buffer_pool_read_requests 从缓冲池中读取页的次数

• Innodb_buffer_pool_reads 从物理磁盘读取页的次数

• Innodb_data_read 总共读入的字节数

• Innodb_data_reads 发起读取请求的次数

  缓冲池命中率=Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads)

• innodb_old_blocks_pct: 控制进入缓冲区sublist of old blocks区域的数量，默认是37，占整个缓冲池比例的3/8

• innodb_old_blocks_time:在进行访问sublist of old blocks 区域过程里的数据块时，并不是马上就移动到sublist of new blocks区域，而是会先让数据停留在sublist of old blocks区域innodb_old_blocks_time(微秒)，然后再移动到sublist of new blocks区域，这样就保证了sublist of new blocks区域里的数据不会马上被踢出

• expire_log_days : 在指定天数后清理旧的二进制日志，如果开启了二进制日志，应该打开这个选项，否则会使得服务器的空间耗尽

• thread_stack : 表示每个线程的堆栈大小

线程相关

• thread_cache_size：Thread cache中存放的连接线程数，缓存线程是保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的，查看Threads_created状态变量来设置thread_cache_size

  通过 show global status like 'Thread%' 查询线程的创建情况，如果Threads_created过大，说明mysql服务器一直在创建线程，可以适当增大thread_cache_size

• innodb_thread_concurrency: 设置一次有多少线程进入内核，0表示不限制，控制并发

• thread_concurrency：并发线程数，设为CPU核数的两倍

IO相关

• innodb_log_file_size : 事务redo日志文件大小，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_os_log_fsyncs状态变量存储fsync()到日志文件的操作次数；innodb_os_log_pending_fsyncs包含挂起的fsync()写次数；innodb_log_writes包含写入次数；innodb_os_log_pending_writes包含挂起的写次数

• innodb_log_buffer_size：事务日志缓冲区的大小，把日志文件接入磁盘的缓冲区大小，为尚未执行的事务分配的缓存(redo日志)，InnoDB存储引擎的事务日志所使用的缓冲区，在写事务日志时，为了提高性能，先将信息写入innodb log buffer中，当满足innodb_flush_log_at_trx_commit参数设置的条件(或缓冲区写满)时，再将日志写到文件中，如果事务中包含有二进制大对象或者大文本字段的话，缓存会很快填满并触发额外的I/O操作，可以通过看Innodb_log_waits状态变量(因缓冲区太小而导致的IO等待次数)，如果该变量不为0，说明innodb_log_buffer_size配置该上调了

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

• innodb_flush_method :设置如何与文件系统进行交互，数据和日志写入硬盘的方式，两种方式O_DSYNC 和O_DIRECT

• open_files_limit: 打开文件的数目限制，注意操作系统的ulimit也会限制，ulimit默认是1024

• innodb_io_capacity : 调整刷新脏页的数量，默认是200，取决于硬盘的IOPS

主从配置

• log_bin: 开启二进制日志
• binlog_direct_non_transactional_updates: 指定何时非事务表更新应该写入二进制日志
• read_only : 备库只接受从主库传输的变更，不允许使用sql来进行修改数据库
• skip_slave_start : 阻止mysql自动启动复制
• slave_net_timeout : 备库与主库连接失败需要重连等待的时间

网络连接

• max_connections 控制允许的MySQL实例的最大连接数，上限值是16384，如果经常看到‘Too many connections’错误，可以适当的调高该值

  通过 show global status like 'Max_used_connections'查看最大使用的连接数

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

• net_buffer_length :网络包传输中，传输消息之前的net buffer初始化大小，当每次消息都很大时，MySQL总是需要多次申请扩展该缓存区的大小，默认16K

• max_allowed_packet: 在网络传输中，一次传输消息结果集的最大容量，相当于net_buffer_length配置的上限，避免超长的SQL执行

• max_connect_errors : 每个客户端连接的最大错误允许数，超过该次数，客户端会被列入黑名单，导致无法连接，直到执行 FLUSH HOSTS 或服务重启

• back_log：在MySQL的连接请求等待队列中允许存放的最大连接请求数。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在队列中，以等待某一连接释放资源，该队列的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500

• connect_timeout: 服务器和客户端交换授权包时连接超时，默认10s

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

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

• optimizer_switch: 设置优化器中哪个高级索引合并功能被开启

• default_storage_engine: 默认的存储引擎

• sql_mode： 支持的各种sql模式

• innodb_strict_mode:一个专门为InnoDB插件提供的服务器SQL模式级别

工具相关

• slow_query_log: 是否记录慢查询日志
• slow_query_log_file: 慢查询日志文件名
• long_query_time: 正常查询最大的执行时间，超过该时间为慢查询
• general_log: 是否记录所有数据库查询日志
• general_log_file: 所用的日志文件名
• log_output: 执行慢查询日志和全面日志的输出位置，有FILE/TABLE/NONE三种选项
• profiling: 是否分析每个线程的语句

动态参数和静态参数

MySQL数据库中的参数分为动态参数和静态参数，动态参数可以在MySQL实例运行中进行更改，静态参数在整个实例生命周期中不可修改

-- 修改动态参数值
set [global | session] name=value
set [@@global. | @@session.] name=value

-- 如果想要设置成默认值
set [global | session] name=DEFAULT

静态参数只能在配置文件中修改然后重新启动mysql

配置检测

可以使用tuning-primer.sh工具来进行检测当前配置

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
# 执行权限
chmod 755 tuning-primer.sh

使用

Would you like to provide a different socket?: [y/N] n //是否使用其他的socket

Do you have your login handy ? [y/N] : y //是否手动输入用户名和密码，当然你可以在my.cnf里面设置

User: root

Password:

Would you like me to create a ~/.my.cnf file for you? [y/N] : n //是不是要帮你在创建一个my.cnf