最佳实践 -MySQL-MySQL实例参数调优参考

对于云数据库MySQL版的实例，您可以通过控制台上修改一些参数。对于某些重要参数而言，不恰当的参数值会导致实例性能问题或应用报错，所以本文将介绍一些重要参数的优化建议以减少您在设置参数时的疑虑。

back_log

• 
  默认值：3000
• 
  修改完后是否需要重启：是
• 
  作用：MySQL每处理一个连接请求时都会创建一个新线程与之对应。在主线程创建新线程期间，如果前端应用有大量的短连接请求到达数据库，MySQL会限制这些新的连接进入请求队列，由参数back_log控制。如果等待的连接数量超过back_log的值，则将不会接受新的连接请求，所以如果需要MySQL能够处理大量的短连接，需要提高此参数的大小。
• 
  现象：如果参数过小，应用可能出现如下错误。
  1. SQLSTATE [HY000 ] [ 2002 ] Connection timed out ;
• 
  修改建议：提高此参数值的大小。

• 
  默认值：1
• 
  修改完后是否需要重启：是
• 
  作用：在MySQL 5.1.22后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，用于控制自增主键的锁机制。该参数可以设置的值为0、1、2，RDS默认的参数值为1，表示InnoDB使用轻量级别的mutex锁来获取自增锁，替代最原始的表级锁。但是在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，则可能导致应用在并发导入数据时出现死锁。
• 
  现象：在load data（包括INSERT … SELECT和REPLACE … SELECT）场景下若使用自增表锁，在并发导入数据时出现如下死锁。
  1.   RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx .xx trx id xxx lock_mode X insert intention waiting . TABLE LOCK table xxx .xxx trx id xxxx lock mode AUTO -INC waiting ；
• 
  修改建议：建议将该参数值改为2，表示所有情况插入都使用轻量级别的mutex锁（只针对row模式），这样就可以避免auto_inc的死锁，同时在INSERT … SELECT的场景下性能会有很大提升。

  
  说明：当该参数值为2时，binlog的格式需要被设置为row。

• 
  默认值：3145728
• 
  修改完后是否需要重启：否
• 
  作用：该参数用于控制MySQL query cache的内存大小。如果MySQL开启query cache，在执行每一个query的时候会先锁住query cache，然后判断是否存在于query cache中，如果存在则直接返回结果，如果不存在，则再进行引擎查询等操作。同时，insert、update和delete这样的操作都会将query cahce失效掉，这种失效还包括结构或者索引的任何变化。但是cache失效的维护代价较高，会给MySQL带来较大的压力。所以，当数据库不会频繁更新时，query cache是很有用的，但如果写入操作非常频繁并集中在某几张表上，那么query cache lock的锁机制就会造成很频繁的锁冲突，对于这一张表的写和读会互相等待query cache lock解锁，从而导致select的查询效率下降。
• 
  现象：数据库中有大量的连接状态为checking query cache for query、Waiting for query cache lock、storing result in query cache。
• 
  修改建议：RDS默认是关闭query cache功能的，如果您的实例打开了query cache，当出现上述情况后可以关闭query cache。

• 
  默认值：60
• 
  修改完后是否需要重启：否
• 
  作用：等待将一个block发送给客户端的超时时间。
• 
  现象：若参数设置过小，可能会导致客户端出现错误the last packet successfully received from the server was milliseconds ago或the last packet sent successfully to the server was milliseconds ago。
• 
  修改建议：该参数在RDS中默认设置为60秒，一般在网络条件比较差时或者客户端处理每个block耗时较长时，由于net_write_timeout设置过小导致的连接中断很容易发生，建议增加该参数的大小。

• 
  默认值：2097152
• 
  修改完后是否需要重启：否
• 
  作用：该参数用于决定内部内存临时表的最大值，每个线程都要分配，实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表。优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。
• 
  现象：如果复杂的SQL语句中包含了group by、distinct等不能通过索引进行优化而使用了临时表，则会导致SQL执行时间加长。
• 
  修改建议：如果应用中有很多group by、distinct等语句，同时数据库有足够的内存，可以增大tmp_table_size（max_heap_table_size）的值，以此来提升查询性能。

• 
  默认值：10737418240
• 
  修改完后是否需要重启：否
• 
  作用：用于控制MySQL能够使用的临时文件的大小。
• 
  现象：如果临时文件超出loose_rds_max_tmp_disk_space的取值，则会导致应用出现如下错误。
  1. The table ‘/home /mysql /dataxxx /tmp /#sql_2db3_1 ’ is full
• 
  修改建议：首先，需要分析一下导致临时文件增加的SQL语句是否能够通过索引或者其它方式进行优化。其次，如果确定实例的空间足够，则可以提升此参数的值，以保证SQL能够正常执行。

• 
  默认值：0
• 
  修改完后是否需要重启：是
• 
  作用：用于控制TokuDB引擎能够使用的buffer内存大小，比如innodb_buffer_pool_size设置为1000MB，tokudb_buffer_pool_ratio设置为50（代表50%），那么TokuDB引擎的表能够使用的buffer内存大小则为500MB。
• 
  修改建议：如果RDS中使用TokuDB引擎，建议调大该参数，以此来提升TokuDB引擎表的访问性能。

• 
  默认值：0
• 
  修改完后是否需要重启：否
• 
  作用：用于控制查询在MySQL的最长执行时间。如果超过该参数设置的时间，查询将会自动失败，默认是不限制。
• 
  现象：若查询时间超过了该参数的值，则会出现如下错误。
  1. ERROR 3006 (HY000 ): Query execution was interrupted , max_statement_time exceeded
• 
  修改建议：如果您想要控制数据库中SQL的执行时间，则可以开启该参数，单位是毫秒。

• 
  默认值：50000
• 
  修改完后是否需要重启：否
• 
  作用：用于控制MySQL并发的查询数目，比如将rds_threads_running_high_watermark的值设置为100，则允许MySQL同时进行的并发查询为100个，超过限制数量的查询将会被拒绝掉。该参数与rds_threads_running_ctl_mode配合使用（默认值为select）。
• 
  修改建议：该参数常常在秒杀或者大并发的场景下使用，对数据库具有较好的保护作用。