my.cnf(my.ini)重要参数优化配置说明

数据运维 2023-04-28 大白菜程序猿 手机阅读

MyISAM存储引擎 MyISAM存储引擎适用于读多写少，对读性能要求比较高的系统 官方文档：http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html Key_buffer_size，可以设置为内存的30%-40%左右。通过

MyISAM存储引擎

MyISAM存储引擎适用于读多写少，对读性能要求比较高的系统

官方文档：http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

Key_buffer_size，可以设置为内存的30%-40%左右。通过show variables like ‘%key_buffer_size%';

通过 show global status like ‘%key_blocks_unused%' 查看是否还有剩余，如果剩余很多，就不需要再加大key_buffer_size了 如果不用MyISAM，建议设置16m到32m就可以了

Query_cache 如果应用程序有大量读且应用程序级别没有缓存，设置这个会比较有用，但是也别太大，维护开销比较大，mysql反而会变慢，建议32m到512m

Sort_buffer_size当进行复杂查询时候用到，建议8m到16m

Query_cache_size缓存select查询结果，如果有大量相同查询，可以将这个值加大。

Bulk_insert_buffer_size 批量insert时候使用，必须小于key_buffer_size

Read_rnd_buffer_size sql有order by的情况下并且第二次查询时候就会用到，他会记录排序，将直接从内存中读取。

Thread_cache_size cache中保留多少线程重用，如果再设置的值内，线程断开也不会销毁，等待新链接。减少线程创建的开销。

参数官方参考文档：http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam.html

Innodb 存储引擎

Innodb 存储引擎 1

并发线程数:Innodb_thread_concurrency=0[默认]，并不是说没有并发，而是指无限并发，没有并发检查。innodb内部自己控制 取值0到1000

建议：

Cpu数量+磁盘数量 * 2，如果有RAID主备，就不乘2，因为有备份磁盘

Innodb 存储引擎 2

Innodb_io_capacity默认值是200，个人认为是表示磁盘IO的吞吐量，innodb每秒后台进程处理IO操作的数据页上限

Innodb_io_capacity_max默认2000，设置IO上线

源码：在innodb存储引擎层搜索srv_io_capacity（主要在srv0srv.c文件中）

当使用SSD可以再调高一些，直到符合磁盘IO吞吐量即可

Innodb 存储引擎 3

innodb_max_dirty_pages_pct innodb从innodb buffer中刷新脏页的比例15% - 80%

源码：在innodb存储引擎层搜索srv_max_buf_pool_modified_pct（主要在srv0srv.c文件中）

Innodb 存储引擎 4 [重要]

innodb_flush_method（ O_DSYNC 、 O_DIRECT ）

O_DSYNC：InnoDB使用O_SYNC模式打开并更新日志文件，用fsync()函数去更新数据文件。

O_DIRECT：InnoDB使用O_DIRECT模式打开数据文件，用fsync()函数去更新日志和数据文件

在raid设备上，为了避免数据被innodb_buffer和raid多次cache，设置为O_DIRECT方式。也就是说直接打开数据文件，不用打开日志文件了。

源码：在innodb存储引擎层搜索srv_unix_file_flush_method（主要在log0log.c、os0file.c文件中）

Innodb 存储引擎 5 【重要】

innodb_buffer_pool_size

Innodb会遵循lru，在录入数据的时候会根据数据的情况，会加载到innodb_buffer_pool_size 中。如果操作数据的时候就省去了去数据文件中查找，直接从内存中找到了。

一般设置内存的80%左右,但需要考虑数据文件的总量是多大。Buffer_pool_size + 数据量所占容量 + 操作系统所用内存 = 内存大小。尽可能设置多一些。

源码：在innodb存储引擎层搜索srv_buf_pool_size（在srv0srv.c、srv0start.c文件中）。

Innodb 存储引擎 6

innodb_buffer_pool_instances 当有多实例的情况下，需要设置。

源码：在innodb存储引擎层搜索srv_buf_pool_instances（主要集中在的buf0buf.c文件）

Innodb 存储引擎 7

innodb_log_file_size 日志文件大小

innodb_log_buffer_size 日志缓存大小

先写入innodb_log_buffer，buffer写满或事务提交，刷新数据，大事务频繁，增加innodb_log_buffer_size大小，默认16M。

源码：在innodb存储引擎层搜索srv_log_buffer_size（主要在log0log.c文件中）

Innodb 存储引擎 8 【重要】

Innodb_file_per_table

当设置Innodb_file_per_table 为1时为打开状态，也就是设置所有表为独立表空间，一个表一个存储数据文件。同时要设置

Innodb_open_files（同时打开文件数）。因为每个表对应一个数据文件，所以需要设置同时打开文件的数量以保证查询多表的情况，并且想要把某个表移出的别的磁盘时共享表空间是无法迁移的，因为所有表都使用着共享表空间。

默认是所有表都放到共享空间中。也就是OFF

innodb 存储引擎9

Innodb_flush_log_at_trx_commit 核心参数：

0：每秒将log buffer的内容写事务日志并且刷新到磁盘

1：每个事务提交后，将log buffer的内容写事务日志并写入数据磁盘

2：每个事务提交，将log buffer内容写事务日志，但是不进行数据刷盘

Sync_binlog

双一致模式： innodb_flush_log_at_trx_commit=1；sync_binlog=1；这样的主从数据是一致的，不会丢数据。

官方参数描述地址： http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

系统参数优化

NUMA（双实例下，可以把每一个实例放到numa单独控制的节点下面）

在os层numa关闭时，打开bios层的numa会影响性能，QPS会下降15-30%左右；

在bios层面numa关闭是，无论os层面的numa是否打开，都不会影响性能。

系统优化 jemalloc

网卡优化：RPS+RFS

malloc

1)、下载jemalloc源码包 wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2 tar -xjf jemalloc-3.6.0.tar.bz2

2)、编译安装 cd jemalloc-3.6.0; ./configure;make &make install

3)、配置MySQL

[mysqld_safe] malloc-lib=$PATH/libjemalloc.so

4)、参考文档：http://blog.chinaunix.net/uid-29957450-id-4547818.html

my.cnf配置文件参考

  1. 以下选项会被MySQL客户端应用读取。
  2. 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。
  3. 如果你想你自己的MySQL应用程序获取这些值。
  4. 需要在MySQL客户端库初始化的时候指定这些选项。 [client] port = 3306 socket = /usr/local/mysql/mysql.sock
  5. MySQL 服务端 [mysqld] #默认存储引擎INNODB default-storage-engine=INNODB #GROUP_CONCAT长度 group_concat_max_len =99999 #端口号 port = 3306 #socket位置 socket = /usr/local/mysql/mysql.sock #pid写入文件位置 pid-file = /usr/local/mysql/mysqld.pid #数据库文件位置 datadir = /home/data/mysql/data user = mysql #SQL模式具体查阅相关资料 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #当外部锁定（external-locking）起作用时，每个进程若要访问数据表, #则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁, #因此在单服务器环境下external locking会让MySQL性能下降。 #所以在很多Linux发行版的源中，MySQL配置文件中默认使用了skip-external-locking来避免external locking。 skip-external-locking #跳过DNS反向解析 skip-name-resolve #关闭TIMESTAMP类型默认值 explicit_defaults_for_timestamp

#不受client字符集影响，保证sever端字符集 skip-character-set-client-handshake #初始连接字符集UTF8 init-connect='SET NAMES utf8' #默认数据库字符集 character-set-server=utf8

#查询缓存0,1,2，分别代表了off、on、demand query_cache_type = 1 #单位秒,握手时间超过connect_timeout,连接请求将会被拒绝 connect_timeout = 20 #设置在多少秒没收到主库传来的Binary Logs events之后,从库认为网络超时,Slave IO线程会重新连接主库。 #该参数的默认值是3600s ，然而时间太久会造成数据库延迟或者主备库直接的链接异常不能及时发现。 #将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。一般线上设置为5s slave_net_timeout = 30

#这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的， #但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项， #这样它的从服务器C才能获得它的二进制日志进行同步操作 log-slave-updates=1 #用于slave服务器，io线程会把server id与自己相同的event写入日志，与log-slave-updates选项冲突 replicate-same-server-id=0

  1. 对了生成唯一的server_id我想到了，大家ip地址唯一，比如10.112.87.91 ，就直接去掉点号在后面加上编号01或者02或者03（加上2位数编号是怕一台物理机 沧岸嗍道,主从复制需要server-id来标识的
  2. 用10112879101做server_id就可以了。 server_id=10112879101
  3. 打开二进制日志功能.
  4. 在复制(replication)配置中,作为MASTER主服务器必须打开此项
  5. 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志 log-bin =/home/data/mysql/binlog/mysql-bin.log #relay-log日志 relay-log=mysql-relay-bin #master-info-repository以及relay-log-info-repository打开以启用崩溃安全的二进制日志/从服务器功能（在事务表而不是平面文件中存储信息） master-info-repository=TABLE relay-log-info-repository=TABLE

#不写入binlog二进制日志中的数据库 binlog-ignore-db=mysql # No sync databases binlog-ignore-db=test # No sync databases binlog-ignore-db=information_schema # No sync databases binlog-ignore-db=performance_schema # No sync databases

#写入binlog二进制日志中数据库 binlog-do-db=business_db binlog-do-db=user_db binlog-do-db=plocc_system

#15滚动清理binlog expire-logs-days=15 max_binlog_size = 1073741824 # Bin logs size ( 1G )

#使binlog在每1000次binlog写入后与硬盘同步 sync_binlog = 1000

#指定只复制哪个库的数据 replicate-do-db=business_db replicate-do-db=user_db replicate-do-db=plocc_system

#开启事件调度器Event Scheduler event_scheduler=1 #MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。 #如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源， #该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源 back_log = 500

#整个数据库最大连接（用户）数 max_connections = 6000 #一个用户的最大连接数 max_user_connection=3000

  1. 每个客户端连接最大的错误允许数量,如果达到了此限制.

  2. 这个客户端将会被MySQL服务阻止直到执行了”FLUSH HOSTS” 或者服务重启

  3. 非法的密码以及其他在链接时的错误会增加此值.

  4. 查看 “Aborted_connects” 状态来获取全局计数器 max_connect_errors = 1844674407370954751 #表描述符缓存大小，可减少文件打开/关闭次数 table_open_cache = 2048

  5. 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)

  6. 每个连接独立的大小.大小动态增加 max_allowed_packet = 64M

  7. 在一个事务中binlog为了记录SQL状态所持有的cache大小

  8. 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.

  9. 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中

  10. 如果事务比此值大, 会使用磁盘上的临时文件来替代.

  11. 此缓冲在每个连接的事务第一次更新状态时被创建 binlog_cache_size = 1M

  12. 独立的内存表所允许的最大容量.

  13. 此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源. max_heap_table_size = 1342177280

  14. 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序

  15. 如果排序后的数据无法放入排序缓冲,

  16. 一个用来替代的基于磁盘的合并分类会被使用

  17. 查看 “Sort_merge_passes” 状态变量.

  18. 在排序发生时由每个线程分配 sort_buffer_size = 8M

  19. 此缓冲被使用来优化全联合(full JOINs 不带索引的联合).

  20. 类似的联合在极大多数情况下有非常糟糕的性能表现,

  21. 但是将此值设大能够减轻性能影响.

  22. 通过 “Select_full_join” 状态变量查看全联合的数量

  23. 当全联合发生时,在每个线程中分配 join_buffer_size = 8M

  24. 我们在cache中保留多少线程用于重用

  25. 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,

  26. 则客户端线程被放入cache中.

  27. 这可以在你需要大量新连接的时候极大的减少线程创建的开销

  28. (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.) thread_cache_size = 128

  29. 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.

  30. 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris).

  31. 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值 thread_concurrency = 8

  32. 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果.

  33. 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表.

  34. 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高.

  35. 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同,

  36. 查询缓冲也许引起性能下降而不是性能提升. query_cache_size = 64M

  37. 只有小于此设定值的结果才会被缓冲

  38. 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖 query_cache_limit = 2M

  39. 被全文检索索引的最小的字长.

  40. 你也许希望减少它,如果你需要搜索更短字的时候.

  41. 注意在你修改此值之后,

  42. 你需要重建你的 FULLTEXT 索引 ft_min_word_len = 4

  43. 线程使用的堆大小. 此容量的内存在每次连接时被预留.

  44. MySQL 本身常不会需要超过64K的内存

  45. 如果你使用你自己的需要大量堆的UDF函数

  46. 或者你的操作系统对于某些操作需要更多的堆,

  47. 你也许需要将其设置的更高一点. thread_stack = 192K

  48. 设定默认的事务隔离级别.可用的级别如下:

  49. READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE transaction_isolation = READ-COMMITTED

  50. 内部(内存中)临时表的最大大小

  51. 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表.

  52. 此限制是针对单个表的,而不是总和. tmp_table_size = 1342177280

#binlog日志类型--混合型 binlog_format=mixed #开启慢查询日志 slow_query_log #文件格式 log_output = FILE

  1. 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.
  2. 不要在这里使用”0″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别). long_query_time = 0.5 #慢查询日志位置 slow_query_log_file=/usr/local/mysql/mysqld_slow.log #指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

#**** MyISAM 相关选项****

  1. 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块.

  2. 不要将其设置大于你可用内存的30%,

  3. 因为一部分内存同样被OS用来缓冲行数据

  4. 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用. key_buffer_size = 32M

  5. 用来做MyISAM表全表扫描的缓冲大小.

  6. 当全表扫描需要时,在对应线程中分配. read_buffer_size = 2M

  7. 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.

  8. 如果你增高此值,可以提高很多ORDER BY的性能.

  9. 当需要时由每个线程分配 read_rnd_buffer_size = 8M

  10. MyISAM 使用特殊的类似树的cache来使得突发插入

  11. (这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA

  12. INFILE) 更快. 此变量限制每个进程中缓冲树的字节数.

  13. 设置为 0 会关闭此优化.

  14. 为了最优化不要将此值设置大于 “key_buffer_size”.

  15. 当突发插入被检测到时此缓冲将被分配. bulk_insert_buffer_size = 16M

  16. 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配.

  17. 这在每个线程中被分配.所以在设置大值时需要小心. myisam_sort_buffer_size = 128M

  18. MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).

  19. 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢) myisam_max_sort_file_size = 1G

  20. 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.

  21. 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择. myisam_repair_threads = 1

  22. 自动检查和修复没有适当关闭的 MyISAM 表. myisam_recover

  23. * INNODB 相关选项 ***

  24. 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,

  25. 使用此选项会节省内存以及磁盘空间,并且加速某些部分 #skip-innodb

  26. #####[关键项]

  27. InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM.

  28. 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.

  29. 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%

  30. 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.

  31. 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,

  32. 所以不要设置的太高. innodb_buffer_pool_size = 700m #1G

  33. InnoDB 将数据保存在一个或者多个数据文件中成为表空间.

  34. 如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.

  35. 其他情况下.每个设备一个文件一般都是个好的选择.

  36. 你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容 innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend

  37. 设置此选项如果你希望InnoDB表空间文件被保存在其他分区.

  38. 默认保存在MySQL的datadir中. #innodb_data_home_dir =

  39. 用来同步IO操作的IO线程的数量. This value is

  40. 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好. innodb_file_io_threads = 4

  41. 在InnoDb核心内的允许线程数量.

  42. 最优值依赖于应用程序,硬件以及操作系统的调度方式.

  43. 过高的值可能导致线程的互斥颠簸. innodb_thread_concurrency = 16

  44. #####[关键项]

  45. 如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,

  46. 这提供了完整的ACID行为.

  47. 如果你愿意对事务安全折衷, 并且你正在运行一个小的事务, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O

  48. 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.

  49. 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.

  51. (说明：如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；

  52. 设置为0性能最高，但如果发生故障，数据可能会有丢失的危险!

  53. 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘，这是很费时的。

  54. 特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，

  55. 它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。

  56. 设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据) innodb_flush_log_at_trx_commit = 2

  57. 用来缓冲日志数据的缓冲区的大小.

  58. 当此值快满时, InnoDB将必须刷新数据到磁盘上.

  59. 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言) innodb_log_buffer_size = 16M

  60. 在日志组中每个日志文件的大小.

  61. 你应该设置日志文件总合大小到你缓冲池大小的25%~100%

  62. 来避免在日志文件覆写上不必要的缓冲池刷新行为.

  63. 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间 innodb_log_file_size = 1024M

  64. 在日志组中的文件总数.

  65. 通常来说2~3是比较好的. innodb_log_files_in_group = 3

  66. InnoDB的日志文件所在位置. 默认是MySQL的datadir.

  67. 你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能 #innodb_log_group_home_dir

  68. 在InnoDB缓冲池中最大允许的脏页面的比例.

  69. 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面.

  70. 这是一个软限制,不被保证绝对执行. innodb_max_dirty_pages_pct = 90

  71. InnoDB用来刷新日志的方法.

  72. 表空间总是使用双重写入刷新方法

  73. 默认值是 “fdatasync”, 另一个是 “O_DSYNC”. innodb_flush_method=O_DSYNC

  74. 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.

  75. InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.

  76. 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎

  77. 那么一个死锁可能发生而InnoDB无法注意到.

  78. 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout = 30

[mysqldump]

  1. 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项 quick

max_allowed_packet = 64M

[mysql] no-auto-rehash

[myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

[mysqlhotcopy] interactive-timeout

[mysqld_safe]

  1. 增加每个进程的可打开文件数量.
  2. 警告: 确认你已经将全系统限制设定的足够高!
  3. 打开大量表需要将此值设大 open-files-limit = 8192 log-error=/usr/local/mysql/mysqld.log pid-file=/usr/local/mysql/mysqld.pid

MYSQL的性能优化不单单是这些内容，还有很多大家可以在我们站上搜索其它MYSQL性能优化方案