MySQL 5.5.49 大内存优化配置文件优化详解

数据运维 2023-04-26 大猫 手机阅读

一、配置文件说明 my-small.cnf my-medium.cnf my-large.cnf my-huge.cnf my-innodb-heavy-4G.cnf 二、详解 my-innodb-heavy-4G.cnf 三、配置文件优化 注：环境说明，CentO5.5 x86_64+MySQL-5.5.32 相关软件下载：http://y

                        <p>一、配置文件说明</p>

my-small.cnf my-medium.cnf my-large.cnf my-huge.cnf my-innodb-heavy-4G.cnf 

二、详解 my-innodb-heavy-4G.cnf三、配置文件优化 注：环境说明，CentO5.5 x86_64+MySQL-5.5.32 相关软件下载：http://yunpan.cn/QtaCuLHLRKzRq

一、配置文件说明

Mysql-5.5.49是Mysql5.5系列中最后一个版本，也是最后一个有配置文件的版本，为什么这么说呢，用过5.6的博友都知道，在mysql5.6中已经不提供配置文件选择，只有一个默认的配置文件，好了，我们今天说的是5.5.49这个版，就不和大家说5.6了，下面我们来具体说一下，mysql5.5.49中，提供可选的几个配置文件， my-small.cnf my-medium.cnf my-large.cnf my-huge.cnf my-innodb-heavy-4G.cnf

下面我们就来分别的看一下^_^……1.my-small.cnf

[root@mysql support-files]# vim my-small.cnf

  1. Example MySQL config file for small systems.
  2. This is for a system with little memory (<= 64M) where MySQL is only used
  3. from time to time and it's important that the mysqld daemon
  4. doesn't use much resources.

这是my-small.cnf配置文件中开头的简介，它说明了，这个配置文件是在内存小于等于64M时使用的，小型数据库系统，目的是不占更多的系统资源！2.my-medium.cnf

[root@mysql support-files]# vim my-medium.cnf

  1. Example MySQL config file for medium systems.
  2. This is for a system with little memory (32M - 64M) where MySQL plays
  3. an important part, or systems up to 128M where MySQL is used together with
  4. other programs (such as a web server)

这个配置文件是中型数据系统使用，内存在128M左右！3.my-large.cnf

[root@mysql support-files]# vim my-large.cnf

  1. Example MySQL config file for large systems.
  2. This is for a large system with memory = 512M where the system runs mainly
  3. MySQL.

这个配置文件是大型数据库系统使用，内存在512M左右！4.my-huge.cnf

[root@mysql support-files]# vim my-huge.cnf

  1. Example MySQL config file for very large systems.
  2. This is for a large system with memory of 1G-2G where the system runs mainly
  3. MySQL.

这个配置文件是巨型数据库系统使用，内存在1G-2G左右！5.my-innodb-heavy-4G.cnf

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf #BEGIN CONFIG INFO #DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries #TYPE: SYSTEM #END CONFIG INFO

  1. This is a MySQL example config file for systems with 4GB of memory
  2. running mostly MySQL using InnoDB only tables and performing complex
  3. queries with few connections.

这个配置文件主要作用是,支持4G内存，支持InnoDB引擎，支持事务（ACID）等特性所使用！ 说明：ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）！6.总结 从上面的说明我们可以出，基本是通过内存大小来选择mysql的配置文件的，那有博友会说了，现在的服务器动不动就是32G内存或者64G内存，甚至更大的内存，你那个配置文件最大只支持4G内存是不是有点小了，确认会有这样的问题，从mysql5.6以后，为了更大的发挥mysql的性能，已经去除了配置文件选择，只有一个默认的配置文件，里面只有一些基本配置，所有设置管理员都可以根据自己实际的需求进行自行设置，好了说了这么多，我们就来说一说，在企业的用的最多的my-innodb-heavy-4G.cnf配置文件！二、详解 my-innodb-heavy-4G.cnf 1.详细说明 注：下面是my-innodb-heavy-4G.cnf默认配置我没有做任何修改，下面我们就来详细的说一说！

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf #BEGIN CONFIG INFO #DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries #TYPE: SYSTEM #END CONFIG INFO

  2. This is a MySQL example config file for systems with 4GB of memory

  3. running mostly MySQL using InnoDB only tables and performing complex

  4. queries with few connections.

  6. MySQL programs look for option files in a set of

  7. locations which depend on the deployment platform.

  8. You can copy this option file to one of those

  9. locations. For information about these locations, see:

  10. http://dev.mysql.com/doc/mysql/en/option-files.html

  12. In this file, you can use all long options that a program supports.

  13. If you want to know which options a program supports, run the program

  14. with the "--help" option.

  16. More detailed information about the individual options can also be

  17. found in the manual.

  19. The following options will be read by MySQL client applications.

  20. Note that only client applications shipped by MySQL are guaranteed

  21. to read this section. If you want your own MySQL client program to

  22. honor these values, you need to specify it as an option during the

  23. MySQL client library initialization.

    1. 以下选项会被MySQL客户端应用读取, 注意只有MySQL附带的客户端应用程序保证可以读取这段内容,如果你想你自己的MySQL应用程序获取这些值,需要在MySQL客户端库初始化的时候指定这些选项 [client] #password = [your_password] #mysql客户端连接mysql时的密码 port = 3306 #mysql客户端连接时的默认端口 socket = /tmp/mysql.sock #与mysql服务器本地通信所使用的socket文件路径

  25. Application-specific options follow here

  27. The MySQL server

  28. [mysqld]

  29. generic configuration options #一般配置选项 port = 3306 #mysql服务器监听的默认端口 socket = /tmp/mysql.sock #socket本地通信文件路径

  30. back_log is the number of connections the operating system can keep in

  31. the listen queue, before the MySQL connection manager thread has

  32. processed them. If you have a very high connection rate and experience

  33. "connection refused" errors, you might need to increase this value.

  34. Check your OS documentation for the maximum value of this parameter.

  35. Attempting to set back_log higher than your operating system limit

  36. will have no effect.

  37. back_log 是操作系统在监听队列中所能保持的连接数,

  38. 队列保存了在MySQL连接管理器线程处理之前的连接.

  39. 如果你有非常高的连接率并且出现“connection refused”报错,

  40. 你就应该增加此处的值.

  41. 检查你的操作系统能打开文件数来获取这个变量的最大值.

  42. 如果将back_log设定到比你操作系统限制更高的值,将会没有效果 back_log = 50

  43. Don't listen on a TCP/IP port at all. This can be a security

  44. enhancement, if all processes that need to connect to mysqld run

  45. on the same host. All interaction with mysqld must be made via Unix

  46. sockets or named pipes.

  47. Note that using this option without enabling named pipes on Windows

  48. (via the "enable-named-pipe" option) will render mysqld useless!

  49. 不在TCP/IP端口上进行监听.

  50. 如果所有的进程都是在同一台服务器连接到本地的mysqld,

  51. 这样设置将是增强安全的方法

  52. 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的.

  53. 注意在windows下如果没有打开命名管道选项而只是用此项

  54. (通过 “enable-named-pipe” 选项) 将会导致mysql服务没有任何作用! #skip-networking #默认是没有开启的

  55. The maximum amount of concurrent sessions the MySQL server will

  56. allow. One of these connections will be reserved for a user with

  57. SUPER privileges to allow the administrator to login even if the

  58. connection limit has been reached.

  59. MySQL 服务器所允许的同时会话数的上限

  60. 其中一个连接将被SUPER权限保留作为管理员登录.

  61. 即便已经达到了连接数的上限. max_connections = 100

  62. Maximum amount of errors allowed per host. If this limit is reached,

  63. the host will be blocked from connecting to the MySQL server until

  64. "FLUSH HOSTS" has been run or the server was restarted. Invalid

  65. passwords and other errors during the connect phase result in

  66. increasing this value. See the "Aborted_connects" status variable for

  67. global counter.

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

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

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

  71. 查看 “Aborted_connects” 状态来获取全局计数器. max_connect_errors = 10

  72. The number of open tables for all threads. Increasing this value

  73. increases the number of file descriptors that mysqld requires.

  74. Therefore you have to make sure to set the amount of open files

  75. allowed to at least 4096 in the variable "open-files-limit" in

  76. section [mysqld_safe]

  77. 所有线程所打开表的数量.

  78. 增加此值就增加了mysqld所需要的文件描述符的数量

  79. 这样你需要确认在[mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少2048 table_open_cache = 2048

  80. Enable external file level locking. Enabled file locking will have a

  81. negative impact on performance, so only use it in case you have

  82. multiple database instances running on the same files (note some

  83. restrictions still apply!) or if you use other software relying on

  84. locking MyISAM tables on file level.

  85. 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响

  86. 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)

  87. 或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表 #external-locking #默认是没有开启的

  88. The maximum size of a query packet the server can handle as well as

  89. maximum query size server can process (Important when working with

  90. large BLOBs). enlarged dynamically, for each connection.

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

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

  93. The size of the cache to hold the SQL statements for the binary log

  94. during a transaction. If you often use big, multi-statement

  95. transactions you can increase this value to get more performance. All

  96. statements from transactions are buffered in the binary log cache and

  97. are being written to the binary log at once after the COMMIT. If the

  98. transaction is larger than this value, temporary file on disk is used

  99. instead. This buffer is allocated per connection on first update

  100. statement in transaction

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

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

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

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

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

  106. Maximum allowed size for a single HEAP (in memory) table. This option

  107. is a protection against the accidential creation of a very large HEAP

  108. table which could otherwise use up all memory resources.

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

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

  111. Size of the buffer used for doing full table scans.

  112. Allocated per thread, if a full scan is needed. #MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分#配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，#并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 read_buffer_size = 2M

  113. When reading rows in sorted order after a sort, the rows are read

  114. through this buffer to avoid disk seeks. You can improve ORDER BY

  115. performance a lot, if set this to a high value.

  116. Allocated per thread, when needed. #是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需#要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 read_rnd_buffer_size = 16M

  117. Sort buffer is used to perform sorts for some ORDER BY and GROUP BY

  118. queries. If sorted data does not fit into the sort buffer, a disk

  119. based merge sort is used instead - See the "Sort_merge_passes"

  120. status variable. Allocated per thread if sort is needed.

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

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

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

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

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

  126. This buffer is used for the optimization of full JOINs (JOINs without

  127. indexes). Such JOINs are very bad for performance in most cases

  128. anyway, but setting this variable to a large value reduces the

  129. performance impact. See the "Select_full_join" status variable for a

  130. count of full JOINs. Allocated per thread if full join is found

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

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

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

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

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

  136. How many threads we should keep in a cache for reuse. When a client

  137. disconnects, the client's threads are put in the cache if there aren't

  138. more than thread_cache_size threads from before. This greatly reduces

  139. the amount of thread creations needed if you have a lot of new

  140. connections. (Normally this doesn't give a notable performance

  141. improvement if you have a good thread implementation.)

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

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

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

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

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

  147. This permits the application to give the threads system a hint for the

  148. desired number of threads that should be run at the same time. This

  149. value only makes sense on systems that support the thread_concurrency()

  150. function call (Sun Solaris, for example).

  151. You should try [number of CPUs]*(2..4) for thread_concurrency

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

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

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

  155. Query cache is used to cache SELECT results and later return them

  156. without actual executing the same query once again. Having the query

  157. cache enabled may result in significant speed improvements, if your

  158. have a lot of identical queries and rarely changing tables. See the

  159. "Qcache_lowmem_prunes" status variable to check if the current value

  160. is high enough for your load.

  161. Note: In case your tables change very often or if your queries are

  162. textually different every time, the query cache may result in a

  163. slowdown instead of a performance improvement.

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

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

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

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

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

  169. Only cache result sets that are smaller than this limit. This is to

  170. protect the query cache of a very large result set overwriting all

  171. other query results.

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

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

  174. Minimum word length to be indexed by the full text search index.

  175. You might wish to decrease it if you need to search for shorter words.

  176. Note that you need to rebuild your FULLTEXT index, after you have

  177. modified this value.

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

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

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

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

  182. If your system supports the memlock() function call, you might want to

  183. enable this option while running MySQL to keep it locked in memory and

  184. to avoid potential swapping out in case of high memory pressure. Good

  185. for performance.

  186. 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out

  187. 此选项对于性能有益 #memlock

  188. Table type which is used by default when creating new tables, if not

  189. specified differently during the CREATE TABLE statement.

  190. 当创建新表时作为默认使用的表类型,

  191. 如果在创建表示没有特别执行表类型,将会使用此值 default-storage-engine = MYISAM

  192. Thread stack size to use. This amount of memory is always reserved at

  193. connection time. MySQL itself usually needs no more than 64K of

  194. memory, while if you use your own stack hungry UDF functions or your

  195. OS requires more stack for some operations, you might need to set this

  196. to a higher value.

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

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

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

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

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

  202. Set the default transaction isolation level. Levels available are:

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

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

  205. Maximum size for internal (in-memory) temporary tables. If a table

  206. grows larger than this value, it is automatically converted to disk

  207. based table This limitation is for a single table. There can be many

  208. of them.

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

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

  211. 此限制是针对单个表的,而不是总和. tmp_table_size = 64M

  212. Enable binary logging. This is required for acting as a MASTER in a

  213. replication configuration. You also need the binary log if you need

  214. the ability to do point in time recovery from your latest backup.

  215. 打开二进制日志功能.

  216. 在复制(replication)配置中,作为MASTER主服务器必须打开此项

  217. 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志. log-bin=mysql-bin

  218. binary logging format - mixed recommended #设定记录二进制日志的格式，有三种格式，基于语句 statement、 基于行 row、 混合方式 mixed binlog_format=mixed

  219. If you're using replication with chained slaves (A->B->C), you need to

  220. enable this option on server B. It enables logging of updates done by

  221. the slave thread into the slave's binary log.

  222. 如果你在使用链式从服务器结构的复制模式 (A->B->C),

  223. 你需要在服务器B上打开此项.

  224. 此选项打开在从线程上重做过的更新的日志,

  225. 并将其写入从服务器的二进制日志. #log_slave_updates

  226. Enable the full query log. Every query (even ones with incorrect

  227. syntax) that the server receives will be logged. This is useful for

  228. debugging, it is usually disabled in production use.

  229. 打开查询日志. 所有的由服务器接收到的查询 (甚至对于一个错误语法的查询)

  230. 都会被记录下来. 这对于调试非常有用, 在生产环境中常常关闭此项. #log #默认是没有开启的，会影响服务器性能

  231. Print warnings to the error log file. If you have any problem with

  232. MySQL you should enable logging of warnings and examine the error log

  233. for possible explanations.

  234. 将警告打印输出到错误log文件. 如果你对于MySQL有任何问题

  235. 你应该打开警告log并且仔细审查错误日志,查出可能的原因. #log_warnings

  236. Log slow queries. Slow queries are queries which take more than the

  237. amount of time defined in "long_query_time" or which do not use

  238. indexes well, if log_short_format is not enabled. It is normally good idea

  239. to have this turned on if you frequently add new queries to the

  240. system.

  241. 记录慢速查询. 慢速查询是指消耗了比 “long_query_time” 定义的更多时间的查询.

  242. 如果 log_long_format 被打开,那些没有使用索引的查询也会被记录.

  243. 如果你经常增加新查询到已有的系统内的话. 一般来说这是一个好主意 slow_query_log

  244. All queries taking more than this amount of time (in seconds) will be

  245. trated as slow. Do not use "1" as a value here, as this will result in

  246. even very fast queries being logged from time to time (as MySQL

  247. currently measures time with second accuracy only).

  248. 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.

  249. 不要在这里使用”1″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别). long_query_time = 2

  250. Replication related settings # 主从复制相关的设置

  251. Unique server identification number between 1 and 2^32-1. This value

  252. is required for both master and slave hosts. It defaults to 1 if

  253. "master-host" is not set, but will MySQL will not function as a master

  254. if it is omitted.

  255. 唯一的服务辨识号,数值位于 1 到 2^32-1之间.

  256. 此值在master和slave上都需要设置.

  257. 如果 “master-host” 没有被设置,则默认为1, 但是如果忽略此选项,MySQL不会作为master生效. server-id = 1

  258. Replication Slave (comment out master section to use this) #复制的Slave (去掉master段的注释来使其生效)

  260. To configure this host as a replication slave, you can choose between

  261. two methods : #为了配置此主机作为复制的slave服务器,你可以选择两种方法:

  263. 1) Use the CHANGE MASTER TO command (fully described in our manual) -

  264. the syntax is: #使用 CHANGE MASTER TO 命令 (在我们的手册中有完整描述) -

  265. 语法如下:

  267. CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

  268. MASTER_USER=, MASTER_PASSWORD= ;

  270. where you replace , , by quoted strings and

  271. by the master's port number (3306 by default).

  272. 你需要替换掉 , , 等被尖括号包围的字段以及使用master的端口号替换 (默认3306).

  273. Example: 案例

  275. CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

  276. MASTER_USER='joe', MASTER_PASSWORD='secret';

  278. OR 或者

  280. 2) Set the variables below. However, in case you choose this method, then

  281. start replication for the first time (even unsuccessfully, for example

  282. if you mistyped the password in master-password and the slave fails to

  283. connect), the slave will create a master.info file, and any later

  284. changes in this file to the variable values below will be ignored and

  285. overridden by the content of the master.info file, unless you shutdown

  286. the slave server, delete master.info and restart the slaver server.

  287. For that reason, you may want to leave the lines below untouched

  288. (commented) and instead use CHANGE MASTER TO (see above)

  289. #设置以下的变量. 不论如何, 在你选择这种方法的情况下, 然后第一次启动复制(甚至不成功的情况下,

  290. 例如如果你输入错密码在master-password字段并且slave无法连接),

  291. slave会创建一个 master.info 文件,并且之后任何对于包含在此文件内的参数的变化都会被忽略

  292. 并且由 master.info 文件内的内容覆盖, 除非你关闭slave服务, 删除 master.info 并且重启slave 服务.

  293. 由于这个原因,你也许不想碰一下的配置(注释掉的) 并且使用 CHANGE MASTER TO (查看上面) 来代替

  294. required unique id between 2 and 2^32 - 1

  295. (and different from the master)

  296. defaults to 2 if master-host is set

  297. but will not function as a slave if omitted

  298. 所需要的唯一id号位于 2 和 2^32 – 1之间

  299. (并且和master不同)

  300. 如果master-host被设置了.则默认值是2

  301. 但是如果省略,则不会生效 #server-id = 2

  303. The replication master for this slave – required

  304. 复制结构中的master – 必须 #master-host =

  306. The username the slave will use for authentication when connecting

  307. to the master – required

  308. 当连接到master上时slave所用来认证的用户名 – 必须 #master-user =

  310. The password the slave will authenticate with when connecting to

  311. the master – required

  312. 当连接到master上时slave所用来认证的密码 – 必须 #master-password =

  314. The port the master is listening on.

  315. optional - defaults to 3306

  316. master监听的端口.

  317. 可选 – 默认是3306 #master-port =

  318. Make the slave read-only. Only users with the SUPER privilege and the

  319. replication slave thread will be able to modify data on it. You can

  320. use this to ensure that no applications will accidently modify data on

  321. the slave instead of the master

  322. 使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据.

  323. 你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据 #read_only # MyISAM Specific options # MyISAM 相关选项

  324. Size of the Key Buffer, used to cache index blocks for MyISAM tables.

  325. Do not set it larger than 30% of your available memory, as some memory

  326. is also required by the OS to cache rows. Even if you're not using

  327. MyISAM tables, you should still set it to 8-64M as it will also be

  328. used for internal temporary disk tables.

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

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

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

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

  333. MyISAM uses special tree-like cache to make bulk inserts (that is,

  334. INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA

  335. INFILE) faster. This variable limits the size of the cache tree in

  336. bytes per thread. Setting it to 0 will disable this optimisation. Do

  337. not set it larger than "key_buffer_size" for optimal performance.

  338. This buffer is allocated when a bulk insert is detected.

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

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

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

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

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

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

  345. This buffer is allocated when MySQL needs to rebuild the index in

  346. REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE

  347. into an empty table. It is allocated per thread so be careful with

  348. large settings.

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

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

  351. The maximum size of the temporary file MySQL is allowed to use while

  352. recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.

  353. If the file-size would be bigger than this, the index will be created

  354. through the key cache (which is slower).

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

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

  357. If a table has more than one index, MyISAM can use more than one

  358. thread to repair them by sorting in parallel. This makes sense if you

  359. have multiple CPUs and plenty of memory.

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

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

  362. Automatically check and repair not properly closed MyISAM tables.

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

  364. INNODB Specific options

  365. INNODB 相关选项

  366. Use this option if you have a MySQL server with InnoDB support enabled

  367. but you do not plan to use it. This will save memory and disk space

  368. and speed up some things.

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

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

  371. Additional memory pool that is used by InnoDB to store metadata

  372. information. If InnoDB requires more memory for this purpose it will

  373. start to allocate it from the OS. As this is fast enough on most

  374. recent operating systems, you normally do not need to change this

  375. value. SHOW INNODB STATUS will display the current amount used.

  376. 附加的内存池被InnoDB用来保存 metadata 信息

  377. 如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存.

  378. 由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值.

  379. SHOW INNODB STATUS 命令会显示当先使用的数量. innodb_additional_mem_pool_size = 16M

  380. InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

  381. row data. The bigger you set this the less disk I/O is needed to

  382. access data in tables. On a dedicated database server you may set this

  383. parameter up to 80% of the machine physical memory size. Do not set it

  384. too large, though, because competition of the physical memory may

  385. cause paging in the operating system. Note that on 32bit systems you

  386. might be limited to 2-3.5G of user level memory per process, so do not

  387. set it too high.

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

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

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

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

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

  393. 所以不要设置的太高. innodb_buffer_pool_size = 2G

  394. InnoDB stores data in one or more data files forming the tablespace.

  395. If you have a single logical drive for your data, a single

  396. autoextending file would be good enough. In other cases, a single file

  397. per device is often a good choice. You can configure InnoDB to use raw

  398. disk partitions as well - please refer to the manual for more info

  399. about this.

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

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

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

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

  404. Set this option if you would like the InnoDB tablespace files to be

  405. stored in another location. By default this is the MySQL datadir.

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

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

  408. Number of IO threads to use for async IO operations. This value is

  409. hardcoded to 8 on Unix, but on Windows disk I/O may benefit from a

  410. larger number.

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

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

  413. If you run into InnoDB tablespace corruption, setting this to a nonzero

  414. value will likely help you to dump your tables. Start from value 1 and

  415. increase it until you're able to dump the table successfully.

  416. 如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.

  417. 从1开始并且增加此值知道你能够成功的导出表. #innodb_force_recovery=1

  418. Number of threads allowed inside the InnoDB kernel. The optimal value

  419. depends highly on the application, hardware as well as the OS

  420. scheduler properties. A too high value may lead to thread thrashing.

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

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

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

  424. If set to 1, InnoDB will flush (fsync) the transaction logs to the

  425. disk at each commit, which offers full ACID behavior. If you are

  426. willing to compromise this safety, and you are running small

  427. transactions, you may set this to 0 or 2 to reduce disk I/O to the

  428. logs. Value 0 means that the log is only written to the log file and

  429. the log file flushed to disk approximately once per second. Value 2

  430. means the log is written to the log file at each commit, but the log

  431. file is only flushed to disk approximately once per second.

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

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

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

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

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

  437. Speed up InnoDB shutdown. This will disable InnoDB to do a full purge

  438. and insert buffer merge on shutdown. It may increase shutdown time a

  439. lot, but InnoDB will have to do it on the next startup instead.

  440. 加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.

  441. 这可能极大增加关机时间, 但是取而代之的是InnoDB可能在下次启动时做这些操作. #innodb_fast_shutdown

  442. The size of the buffer InnoDB uses for buffering log data. As soon as

  443. it is full, InnoDB will have to flush it to disk. As it is flushed

  444. once per second anyway, it does not make sense to have it very large

  445. (even with long transactions).

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

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

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

  449. Size of each log file in a log group. You should set the combined size

  450. of log files to about 25%-100% of your buffer pool size to avoid

  451. unneeded buffer pool flush activity on log file overwrite. However,

  452. note that a larger logfile size will increase the time needed for the

  453. recovery process.

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

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

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

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

  458. Total number of files in the log group. A value of 2-3 is usually good

  459. enough.

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

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

  462. Location of the InnoDB log files. Default is the MySQL datadir. You

  463. may wish to point it to a dedicated hard drive or a RAID1 volume for

  464. improved performance

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

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

  467. Maximum allowed percentage of dirty pages in the InnoDB buffer pool.

  468. If it is reached, InnoDB will start flushing them out agressively to

  469. not run out of clean pages at all. This is a soft limit, not

  470. guaranteed to be held.

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

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

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

  474. The flush method InnoDB will use for Log. The tablespace always uses

  475. doublewrite flush logic. The default value is "fdatasync", another

  476. option is "O_DSYNC".

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

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

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

  480. How long an InnoDB transaction should wait for a lock to be granted

  481. before being rolled back. InnoDB automatically detects transaction

  482. deadlocks in its own lock table and rolls back the transaction. If you

  483. use the LOCK TABLES command, or other transaction-safe storage engines

  484. than InnoDB in the same transaction, then a deadlock may arise which

  485. InnoDB cannot notice. In cases like this the timeout is useful to

  486. resolve the situation.

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

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

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

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

  491. 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout = 120 [mysqldump]

  492. Do not buffer the whole result set in memory before writing it to

  493. file. Required for dumping very large tables

  494. 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项 quick max_allowed_packet = 16M [mysql] no-auto-rehash

  495. Only allow UPDATEs and DELETEs that use keys.

  496. 仅仅允许使用键值的 UPDATEs 和 DELETEs . #safe-updates [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe]

  497. Increase the amount of open files allowed per process. Warning: Make

  498. sure you have set the global system limit high enough! The high value

  499. is required for a large number of opened tables

  500. 增加每个进程的可打开文件数量.

  501. 警告: 确认你已经将全系统限制设定的足够高!

  502. 打开大量表需要将此值设大 open-files-limit = 8192

三、配置文件优化（根据实际情况优化） 说明，上文中我对my-innodb-heavy-4G.cnf中默认的所有选项进行了说明，下面我就根据我们公司的实际情况进行优化！1.服务器的运行环境 硬件服务器：Dell R710，双至强E5620 CPU、16G内存、6*500G硬盘 操作系统：CentOS5.5 X86_64 系统 Mysql版本：MySQL 5.5.32 适用于：日IP 100-200W ，日PV 200-500W 的站点2.具体优化配置如下

[client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8 #设置客户端的字符编码 [mysqld]

  1. generic configuration options port = 3306 socket = /tmp/mysql.sock # char set character-set-server = utf8 #设置服务器端的字符编码

# network back_log = 512 #skip-networking #默认没有开启 max_connections = 3000 max_connect_errors = 30 table_open_cache = 4096 #external-locking #默认没有开启 max_allowed_packet = 32M max_heap_table_size = 128M

  1. global cache read_buffer_size = 8M read_rnd_buffer_size = 64M sort_buffer_size = 16M join_buffer_size = 16M

  2. thread thread_cache_size = 16 thread_concurrency = 8 thread_stack = 512K

  3. query cache query_cache_size = 128M query_cache_limit = 4M

  4. index ft_min_word_len = 8

#memlock #默认没有开启 default-storage-engine = INNODB transaction_isolation = REPEATABLE-READ

  1. tmp table tmp_table_size = 64M

  2. bin log log-bin=mysql-bin binlog_cache_size = 4M binlog_format=mixed #log_slave_updates #默认没有开启 #log #默认没有开启，此处是查询日志，开启会影响服务器性能 log_warnings #开启警告日志

  3. slow query log slow_query_log long_query_time = 10

  4. Replication related settings server-id = 1 #server-id = 2 #master-host = #master-user = #master-password = #master-port = #read_only # MyISAM Specific options key_buffer_size = 128M bulk_insert_buffer_size = 256M myisam_sort_buffer_size = 256M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover

  5. INNODB Specific options #skip-innodb #默认没有开启 innodb_additional_mem_pool_size = 64M innodb_buffer_pool_size = 6G #注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, 所以不要设置的太高. innodb_data_file_path = ibdata1:10M:autoextend #innodb_data_home_dir = innodb_write_io_threads = 8 innodb_read_io_threads = 8 #innodb_force_recovery=1 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 #说明：innodb_flush_log_at_trx_commit = 2 如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。 #innodb_fast_shutdown innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 #innodb_log_group_home_dir innodb_max_dirty_pages_pct = 90 #innodb_flush_method=O_DSYNC innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 2048M sort_buffer_size = 2048M read_buffer = 32M write_buffer = 32M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 10240

3.总结 MySQL 配置文件的优化是根据线上环境的实际需要进行优化，不能随便没有根据的进行优化，写这篇博文就是给博友们一些参考！ 4.MySQL状态查看的常用命令

mysql> show status; #显示状态信息 mysql> show variables; #显示系统变量 mysql> show engines; #查看所有引擎 mysql> show engine innodb status; #显示InnoDB存储引擎的状态

本文出自 “Share your knowledge …” 博客

经过每日运维小编的测试，这样的效率很不错，直接拿来就可以使用。