mysql服务器参数优化

一、调整MySQL配置参数的说明

  • 调整MySQL服务器参数,性能并不能得到数量级的提升
  • 要慎重调整MySQL服务器参数,在有经验的DBA的指导下调整,否则,不合理的调整,可能会造成MySQL的安全性问题,甚至造成MySQL服务的异常
  • 有些参数,需要重启MySQL才能起作用,因此要慎重对待。另外要确定MySQL配置文件的正确位置
  • 对调整后的参数要进行观察和监控,分析其是否真正起到了优化的作用

二、配置文件及配置项说明

1、确定配置文件位置
  1. /usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 'Default options'

结果如下:

这是查找配置文件的优先级说明

2、配置项说明

对于MySQL配置项,有的配置项是对于一个线程而言的(即为每个线程分配的资源,包括sort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_size是为每个连接线程分配的内存大小),因此要充分估计MySQL的负载。

三、MySQL配置参数调优

1、内存配置相关参数

内存配置调优的原则:

  • 确定MySQL能使用的内存的绝对上限(比如是物理内存的多少)
  • 确定MySQL会为每个连接使用多少内存,比如排序缓冲区和临时表(要充分估计最大连接数,如sort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_size)
  • 确定操作系统需要多少内存来很好的运行自身,包括机器上其他程序
  • 假设以上工作都完成,就可以把剩下的内存分配给MySQL的缓存,如InnoDB的缓存池
(1)缓存池配置
  • innodb_buffer_pool_size:InnoDB单个缓存池大小

    • 作用:用于缓存索引和数据,所以设置过大或过小都不好,设置过大,不仅造成资源的浪费,还会导致system的swap空间被占用,导致操作系统变慢,从而减低sql查询的效率,设置过小,会影响MySQL的性能。最大最好不要超过MySQL数据量和索引量的总的大小(要考虑后续数据的增长)。

    • 推荐值:MySQL官方推荐其值为MySQL所在服务器物理内存的50%-80%,但我们应该根据具体情况来设定

    • 计算公式:innodb缓存池总大小 <= 物理内存 - 连接数 * 每个连接分配的内存 - 系统程序内存

    -物理内存即服务器的总内存大小

    - 系统程序内存,包括运行操作系统正常运行的内存,以及系统上其他程序正常运行需要的内存

    - 每个连接分配的内存,如排序缓存池sort_buffer_size、连接缓存池join_buffer_size,读入缓存池read_buffer_size,随机读入缓存池read_rnd_buffer_size

  • innodb_buffer_pool_instances:InnoDB缓存池的数量

  • key_buffer_size:MyISAM存储引擎索引缓存池大小

    • 作用:用于缓存MyISAM索引,数据是根据操作系统来决定是否存储(根据索引量来决定,通过计算索引的大小来决定其大小)

    • 推荐值:和MyISAM的索引量大小匹配即可

    • 获取方式:查看MyISAM表的所有索引的数量

  1. select sum(index_length) from information_schema.tables where engine="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的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。

(2)线程缓存
  • thread_cache_size:缓存的线程数量
    • 作用:保存与当前线程无关的线程,以供新连接使用
    • 推荐值:观察threads_created这个变量,如果其值小于10,则不用改变当前的配置;然后再观察threads_connected这个变量,如果其值在100-200之间,可以将缓存的线程数量设置为100,如果它在500-700之间,设置为200即可
(3)表缓存
  • table_open_cache:打开表的缓存数

    • 作用:为打开的表设置缓存,如果之后打开同名的表,可以从缓存中直接取出,不用直接再打开

    • 推荐值:不断观察open_tables这个状态值来决定其大小。

  • table_definition_cache:表定义缓存数

    • 作用:为表的结构定义建立缓存,存放的表的结构定义信息

    • 推荐值:根据表的数据来决定(最大为表的数量,当然最好是常用的表)

2、IO配置相关参数

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:内存表大小(为每个线程分配的大小)

3、安全配置相关参数
  • expire_logs_day:二进制日志保存的天数

  • read_only:是否只读

    • 推荐值:在从服务器上建议开启该选项
  • sql_mode:SQL模式

    • 作用:限制SQL的执行模式
    • 推荐值:生产环境建议为严格模式(STRICT_TRANS_TABLES)
4、其他参数
  • max-allowed-packet:设置网络传输中一次消息传输量的最大值,取值范围为1MB~1GB,必须设置为1024的倍数

  • max-connect-errors:每个主机在连接请求异常中断的最大次数,当超过该次数,则禁止host的连接请求,直到服务器重启或flush hosts命令清空该host的相关信息

  • skip-name-resolve:启动mysql禁用DNS主机名查找

四、修改MySQL配置参数的方法:

1、在命令行下使用set global 参数名 = 参数值,这种在mysql重启后失效。

如:

  1. set global innodb_buffer_pool_size = 10240000
2、修改mysql配置文件my.cnf,然后重启MySQL服务

将配置项写入到配置文件中。(注意配置文件的正确位置)

五、第三方Mysql配置工具

地址:https://tools.percona.com

根据提示生成适合自己的一份配置,如:根据本人自身需要生成的配置:

  1. [mysql]
  2. # CLIENT #
  3. # mysql服务器端口号
  4. port = 3306
  5. # mysql socket连接文件(根据自己的需要设置,且/data/mysql要有可写权限)
  6. socket = /data/mysql/mysql.sock
  7. [mysqld]
  8. # GENERAL #
  9. # 服务器标识id(用于主从复制)
  10. server-id = 1
  11. # 操作mysql的系统用户名
  12. user = mysql
  13. # 默认存储引擎
  14. default-storage-engine = InnoDB
  15. # mysql socket连接文件
  16. socket = /data/mysql/mysql.sock
  17. # 记录mysql进程的文件
  18. pid-file = /data/mysql/mysql.pid
  19. # MyISAM #
  20. # 索引缓冲区大小
  21. key-buffer-size= 32M
  22. # 自动检查和修复没有适当关闭的MyISAM表
  23. myisam-recover = FORCE,BACKUP
  24. # MyISAM表发生变化时重新排序所需要的缓冲区大小
  25. myisam-sort-buffer-size= 128M
  26. # 重建索引时所允许的最大临时文件大小
  27. myisam-max-sort-file-size = 10G
  28. # SAFETY #
  29. # 设置网络传输中一次消息传输量的最大值,取值范围为1MB~1GB,必须设置为1024的倍数
  30. max-allowed-packet = 32M
  31. # 每个主机在连接请求异常中断的最大次数,当超过该次数,则禁止host的连接请求,直到服务器重启或flush hosts命令清空该host的相关信息
  32. max-connect-errors = 6000
  33. # 启动mysql禁用DNS主机名查找
  34. skip-name-resolve
  35. # SQL模式(在生产环境要设置为严格模式,可以防止非法数据的插入)
  36. sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
  37. # 系统时间和当前时间一致
  38. sysdate-is-now = 1
  39. # DATA STORAGE #
  40. # 数据文件位置
  41. datadir= /data/mysql/
  42. # BINARY LOGGING #
  43. # 二进制操作日志文件
  44. log-bin= /data/mysql/mysql-bin
  45. # 日志有效时间
  46. expire-logs-days = 14
  47. # 二进制日志是否同步写入磁盘(1表示每次每写入,是最安全的,但也是最慢的)
  48. sync-binlog= 1
  49. # CACHES AND LIMITS #
  50. # 内存临时表大小(超过其大小会写入磁盘)
  51. tmp-table-size = 32M
  52. # 可创建的内存表大小
  53. max-heap-table-size= 32M
  54. # 是否将查询结果放到查询缓存中
  55. query-cache-type = 0
  56. # 查询缓存大小(在写入量大的系统,建议关闭)
  57. query-cache-size = 0
  58. # 单个查询能够使用的缓冲区大小
  59. query-cache-limit = 4M
  60. # mysql最大进程连接数
  61. max-connections= 500
  62. # 服务器线程缓存大小,类似于缓存池,1G内存建议为8,2G内存建议为16,4G以上可以配置更大
  63. thread-cache-size = 300
  64. # 线程并发数,一般设置为核数的2倍,如8核的CPU可以设置为16
  65. thread-concurrency = 16
  66. # 打开文件描述符的限制(至少在大于 table-open-cache*2+InnoDB表的数量)
  67. open-files-limit = 65535
  68. # 表缓存区大小,应该与max_connections设置相关
  69. table-cache= 614
  70. # 表定义信息缓存(从.frm文件中获取的)
  71. table-definition-cache = 1024
  72. # 打开表(文件描述符)的缓存数量
  73. table-open-cache = 1024
  74. # 读入缓冲区大小
  75. read-buffer-size = 1M
  76. # 随机读缓冲区大小
  77. read-rnd-buffer-size = 16M
  78. # 批量插入缓冲区大小
  79. bulk-insert-buffer-size= 64M
  80. # 用于排序的缓冲区大小
  81. sort-buffer-size = 2M
  82. # 表间关联的缓冲区大小
  83. join-buffer-size = 2M
  84. # INNODB #
  85. #innodb刷新日志和数据的模式,有
  86. innodb-flush-method= O_DIRECT
  87. # 为提高性能,可以以循环方式将日志文件写入到多个文件
  88. innodb-log-files-in-group = 2
  89. # 日志缓存区大小
  90. innodb-log-buffer-size = 16M
  91. # innodb日志文件大小
  92. innodb-log-file-size = 256M
  93. # 提交事务后将日志写入磁盘的频率
  94. innodb-flush-log-at-trx-commit = 1
  95. # 每个innodb表使用一个数据文件
  96. innodb-file-per-table = 1
  97. # innodb缓存池大小
  98. innodb-buffer-pool-size= 12G
  99. # innodb文件IO线程数
  100. innodb-file-io-threads = 4
  101. # innodb并发线程数(与CPU核数相关)
  102. innodb-thread-concurrency = 8
  103. # 在MySQL暂时停止响应新请求前,短时间内的多少个请求可以被存在堆栈中
  104. back_log = 600
  105. # 设置事务的隔离级别,可以使用READ-UNCOMMITED 读未提交,READ-COMMITED 读已提交;REPEATABLE-READ 可重复读;SERIALIZABLE 串行
  106. transaction_isolcation = READ-COMMITED
  107. # 表名都小写
  108. lower_case_table_names = 1
  109. # 避免MySQL外部锁定
  110. external-locking = FALSE
  111. # LOGGING #
  112. # 错误日志文件
  113. log-error = /data/mysql/mysql-error.log
  114. # 是否记录未使用索引的查询
  115. log-queries-not-using-indexes = 1
  116. # 是否开启慢查询日志
  117. slow-query-log = 1
  118. # 慢查询日志文件位置
  119. slow-query-log-file= /data/mysql/mysql-slow.log

注:以上配置并不是最优的配置,只是做一个简要的说明,请根据项目需要自行调整