MySQL调优参数配置
MySQL服务器硬件优化
- 硬盘：mysql 对磁盘的要求比较高，包括随机读写的带宽和IOPS和顺序读写的带宽和IOPS，可以通过使用高转速磁盘、商业FC存储、固态硬盘等方式提高IOPS及读写带宽；
- 内存：mysql 服务器内存越高，可加载的热点索引数据越多，可提供给操作线程的内存越多。Mysql 读写操作越快；
- CPU： mysql正常的查询 对CPU要求比较低，如果磁盘和内存不足CPU配置过高更容易引起磁盘吞吐量下降严重导致性能过低，所以硬件优化首先优化硬盘和内存，只有硬盘和内存无瓶颈后增加CPU才会使mysql性能更高如果有大量的慢查询则很容易将CPU跑满，所以CPU如果过高应首先检查慢查询优化慢查询，如慢查询优化完成应首先检查是否由于磁盘IO过高引起的CPU过高。
内存优化-数据索引页共享内存
- innodb_buffer_pool_size
- 作用：pool-size可以缓存索引和行数据，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置
到电脑物理内存的75-80%- 调优参考计算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)
- innodb_buffer_pool_instances
- 作用：innodb_buffer_pool_instances的值主要用于将innodb buffer pool进行划分，通过划分innodbbuffer pool为多个实例，可以提高并发能力，并且减少了不同线程读写造成的缓冲页。每一页从其中一个buffer pool中使用hash函数随机的读取和写入。每个buffer pool管理和维护各自的信息，包括free lists、flush lists、LRUs等
- 调优参考计算方法：
在innodb_buffer_pool_size设置小于32G时控制在6-12左右。
在innodb_buffer_pool_size设置比较大的情况下（32G以上），可以将innodb_buffer_pool_instances的值设置为8-16，保证一个pool 8G以上
内存优化-查询缓存
- query_cache_size
查询缓存大小，MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。
查询缓存会跟踪查询中涉及的每个表，如果这写表发生变化，那么和这个表相关的所有缓存都将失效。
但是随着服务器功能的强大，查询缓存也可能成为整个服务器的资源竞争单点，query_cache_size =1才会开启。
通过命令：show status like '%Qcache%';查看查询缓存使用状态值:
- Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过
多了，可能在一定的时间进行整理。整理碎片期间，查询缓存无法被访问，可能导致服务器僵死一段时间，所以查询缓存不宜太大。 - Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
- Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理
想。 - Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询
处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的， 这很正常。 - Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
- Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
- Qcache_queries_in_cache:当前缓存中缓存的查询数量。
- Qcache_total_blocks:当前缓存的block数量。
内存优化-临时表内存大小
- tmp_table_size
- 通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表
- 首先在优化sql的时候就应该尽量避免临时表如果必须使用临时表 且同时执行大量sql 生成大量临时表时适当增加tmp_table_size；如果生成的临时表数据量大于tmp_table_size则会将临时表存储于磁盘而不是内存
- MySQL中的 max_heap_table_size参数也会影响到临时表的内存缓存大小；max_heap_table_size 是MEMORY内存引擎的表大小，因为临时表也是属于内存表所以也会受此参数的限制 所以如果要增加 tmp_table_size 的大小也需要同时增加max_heap_table_size 的大小；
- 可以通过Created_tmp_disk_tables 和 Created_tmp_tables 状态来分析是否需要增加 tmp_table_size
- 查看状态
show global status like 'Created_tmp_disk_tables';
show global status like 'Created_tmp_tables';
Created_tmp_disk_tables : 磁盘临时表的数量
Created_tmp_tables : 内存临时表的数量
内存优化-线程内存优化
- read_buffer_size
是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，一般根据内存大小1-4M即可
- join_buffer_size
应用经常会出现一些两表（或多表）join的操作需求，MySQL在完成某些join需求的时候（all row join/all index/scan join）为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作当join buffer 太小，MySQL不会将该buffer存入磁盘文件而是先将join buffer中的结果与需求join的表进行操作，然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中，如此往复，这势必会造成被驱动表需要被多次读取，成倍增加IO访问，降低效率，一般根据内存大小及sql表关联的多少设置1-4M即可
- binlog_cache_size
一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘，设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache不是全局的，当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache 。设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件，根据内存和事务大小设置为1-4M
- sort_buffer_size
可以考虑增加sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作,不能通过查询或者索引优化的
内存优化-最大使用内存计算
- MySQL最大使用内存
select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@query_cache_size + @@tmp_table_size + @@max_connections*(@@read_buffer_size +
@@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack) )/1024/1024/1024 as GB;
- MySQL最大使用内存（不包含连接占用内存）
select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@query_cache_size + @@tmp_table_size )/1024/1024/1024 as GB;
- MySQL单个连接最大使用内存
select (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size +
@@thread_stack)/1024/1024/1024 as GB;
MySQL Redo Log写入优化
- innodb_flush_log_at_trx_commit
- 1：事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
- 0：事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
- 2：每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。
OS数据即使不刷盘对上层系统所有应用也是认为是在磁盘上的，只是非持久化状态。如果关机会不会丢数据？断电会不会
丢数据？
其实值为2和0的时候，它们的差距并不太大，但2却比0要安全的多。它们都是每秒从osbuffer刷到磁盘，它们之间的时间差体现在log buffer刷到os buffer上。因为将log buffer中的日志刷新到os buffer只是内存数据的转移，并没有太大的开销，所以每次提交和每秒刷入差距并不大。可以测试插入更多的数据来比较，插入100W行数据的情况。从结果可见，值为2和0的时候差距并不大，但值为1的性能却差太多。
- innodb_log_buffer_size
写入redo log文件时内存缓冲,在业务高峰运行期间如果 Innodb_log_waits 值为0或接近0， innodb_log_buffer_size 可能太大,可以减少，日志缓冲区大小,一般不用设置太大。
- innodb_log_file_size =256-512M
- innodb_log_files_in_group=2-8
当你提交事务，然后InnoDB确认你的提交，更改准备写入到实际的数据文件。
现在你认为它们会被马上写入到硬盘的数据文件，事实上不是这样的。为什么？因为这样做效率非常低。反而，更改仅仅被写入到事务日志(因为是顺序写，速度会很快，称为重做日志记录)，而更改的记录仍然在日志中 ， InnoDB缓冲池的脏页，过一定的时间才刷新到硬盘；
一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容,设置的太大重启恢复时时间比较长，太小会影响效率。
MySQL bin log写入优化
- binlog_cache_size
一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘，设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache不是全局的，当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache 。设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件，根据内存和事务大小设置为1-4M
- sync_binlog
根据同步完整性要求可设置为具体数值如：15
这个参数直接影响mysql同步的性能和完整性
- sync_binlog=0
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。- sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
- Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失
MySQL线程优化
- innodb_purge_threads
回收线程数：
- delete-mark的记录最后会被purge线程回收，Purge会检测记录上是否有其他事物在引用undo，如果没有就可以删除。
- innodb_purge_threads (5.6以后)，可以设置的大一些，回收的速度会快一些。innodb_purge_threads = 4
- innodb_read_io_threads=8
- innodb_write_io_threads=8
以上读写线程数根据读和写的比例以及CPU线程数，设置为2-10左右
浙公网安备 33010602011771号