线上 mysql 主库配置文档

服务器配置如下：

Dell R410 XEON 5506 *2 (8核)/ 16GB memory /300G*4 raid 10

注意:需要根据服务器实际配置情况更改相关参数。

线上 mysql 主库配置文档

1. $ cat /etc/my.cnf
2.  
3. [mysqld] 
4. port            = 3306 
5. user = mysql 
6. skip-locking 
7. skip-innodb 
8. skip-name-resolve 
9. key_buffer = 1024M 
10. max_allowed_packet = 16M 
11. max_heap_table_size = 1024M 
12. max_connect_errors = 100 
13. table_cache = 4096 
14. sort_buffer_size = 128M 
15. record_buffer = 4M 
16. read_buffer_size = 8M 
17. read_rnd_buffer_size = 8M 
18. myisam_sort_buffer_size = 16M 
19. thread_cache_size = 2048 
20. query_cache_type = 1 
21. query_cache_size = 1024M 
22. query_cache_limit = 2M 
23. flush_time = 384 
24. back_log = 2048 
25. tmp_table_size = 2048M 
26. character_set_server=utf8 
27. max_connections = 1000 
28. max_user_connections = 1000 
29. interactive_timeout=20000 
30. wait_timeout=20000 
31. thread_concurrency = 8 
32. server-id = 1001 
33. tmpdir          = /tmp/ 

参数说明,来自互联网！

1. 有关请求连接的变量： 有关缓冲区变量： 连接缓冲; 
2.  
3. 有关请求连接的变量： 
4.  
5. max_connections MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量 
6. show variables like '%max_con%'; 
7. +--------------------+-------+ 
8. | Variable_name      | Value | 
9. +--------------------+-------+ 
10. | max_connect_errors | 10    | 
11. | max_connections    | 151   | 
12. +--------------------+-------+ 
13.  
14. 可以通过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小！ 
15. show status like 'conn%'; 
16. +---------------+-------+ 
17. | Variable_name | Value | 
18. +---------------+-------+ 
19. | Connections   | 109   | 
20. +---------------+-------+ 
21.  
22. back_log 是要求MySQL能有的连接数量。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源 
23. show variables like '%back_log'; 
24. +---------------+-------+ 
25. | Variable_name | Value | 
26. +---------------+-------+ 
27. | back_log      | 50    | 
28. +---------------+-------+ 
29.  
30.  
31.  
32. 有关缓冲区变量： 
33. 启动时就需要分配并且总是存在的全局缓冲！ 
34.  
35. key_buffer_size 决定索引处理的速度，尤其是索引读的速度 
36. show variables like 'key_buffer_size'; 
37. +-----------------+-----------+ 
38. | Variable_name   | Value     | 
39. +-----------------+-----------+ 
40. | key_buffer_size | 268435456 | 
41. +-----------------+-----------+ 
42.  
43.  
44. Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好 
45. show status like 'key_read%'; 
46. +-------------------+----------+ 
47. | Variable_name     | Value    | 
48. +-------------------+----------+ 
49. | Key_read_requests | 14648904 | 
50. | Key_reads         | 17686    | 
51. +-------------------+----------+ 
52.  
53. key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。 
54. show status like 'created_tmp_disk_tables'; 
55. +-------------------------+-------+ 
56. | Variable_name           | Value | 
57. +-------------------------+-------+ 
58. | Created_tmp_disk_tables | 0     | 
59. +-------------------------+-------+ 
60.  
61.  
62. query_cache_size 是MySql的查询缓冲大小,MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果. 
63. show variables like 'query_cache_size'; 
64. +------------------+----------+ 
65. | Variable_name    | Value    | 
66. +------------------+----------+ 
67. | query_cache_size | 16777216 | 
68. +------------------+----------+ 
69.  
70. 通过检查状态值’Qcache_%’，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小 
71. show status like 'Qcache%'; 
72. +-------------------------+---------+ 
73. | Variable_name           | Value   | 
74. +-------------------------+---------+ 
75.  
76. | Qcache_hits             | 29605   | 
77.  
78. | Qcache_lowmem_prunes    | 554262  | 
79.  
80. +-------------------------+---------+ 
81.  
82.  
83.  
84. 除了全局缓冲，MySql还会为每个连接发放连接缓冲。 
85. 连接缓冲： 
86. 每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表， 
87.  
88. read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 
89. show variables like 'read_buffer_size'; 
90. +------------------+---------+ 
91. | Variable_name    | Value   | 
92. +------------------+---------+ 
93. | read_buffer_size | 1048576 | 
94. +------------------+---------+ 
95.  
96. sort_buffer_size 是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小. 
97. show variables like 'sort_buffer_size'; 
98. +------------------+---------+ 
99. | Variable_name    | Value   | 
100. +------------------+---------+ 
101. | sort_buffer_size | 1048576 | 
102. +------------------+---------+ 
103.  
104. read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区 
105. show variables like 'read_rnd_buffer_size'; 
106. +----------------------+---------+ 
107. | Variable_name        | Value   | 
108. +----------------------+---------+ 
109. | read_rnd_buffer_size | 4194304 | 
110. +----------------------+---------+ 
111.  
112.  
113. tmp_table_size是MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。 
114. show variables like 'tmp_table_size'; 
115. +----------------+----------+ 
116. | Variable_name  | Value    | 
117. +----------------+----------+ 
118. | tmp_table_size | 16777216 | 
119. +----------------+----------+ 
120.  
121. show variables like '%heap%'; 
122. +---------------------+----------+ 
123. | Variable_name       | Value    | 
124. +---------------------+----------+ 
125. | max_heap_table_size | 16777216 | 
126. +---------------------+----------+ 
127.  
128. table_cache(MySQL 5.0)系统变量 table_cache 改名为 table_open_cache (MySQL 5.1) 
129. 所有线程打开的表的数目，增大该值可以增加mysqld需要的文件描述符的数量。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容 
130. show variables like 'table_open_cache'; 
131. +------------------+-------+ 
132. | Variable_name    | Value | 
133. +------------------+-------+ 
134. | table_open_cache | 256   | 
135. +------------------+-------+ 
136.  
137. 通过检查峰值时间的状态值’Open_tables’和’Opened_tables’，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用’Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。 
138. show status like 'Open%tables'; 
139. +---------------+-------+ 
140. | Variable_name | Value | 
141. +---------------+-------+ 
142. | Open_tables   | 60    | 
143. | Opened_tables | 0     | 
144. +---------------+-------+ 

结束

更多欢迎到此讨论

37275208 (已满)
71921660

本文转自 dongnan 51CTO博客，原文链接：http://blog.51cto.com/dngood/793273