MySQL最佳化效能my.cnf詳解

標籤：bin   單位   完整   使用者   開始   變數   最佳化   讀取   位元組   

提供一個MySQL 5.6版本適合在1GB記憶體VPS上的my.cnf設定檔（點擊這裡下載檔案）：

1. [client]
2. port=3306
3. socket=/tmp/mysql.sock
4.  
5. [mysqld]
6. port=3306
7. socket=/tmp/mysql.sock
8.  
9. basedir=/usr/local/mysql
10. datadir=/data/mysql
11. pid-file=/data/mysql/mysql.pid
12. user=mysql
13. bind-address=0.0.0.0
14. server-id=1#表示是原生序號為1,一般來講就是master的意思
15.  
16. skip-name-resolve
17. #禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，
18. #則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求
19.  
20. #skip-networking
21.  
22. back_log=600
23. #MySQL能有的串連數量。當主要MySQL線程在一個很短時間內得到非常多的串連請求，這就起作用，
24. #然後主線程花些時間(儘管很短)檢查串連並且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。
25. #如果期望在一個短時間內有很多串連，你需要增加它。也就是說，如果MySQL的串連資料達到max_connections時，新來的請求將會被存在堆棧中，
26. #以等待某一串連釋放資源，該堆棧的數量即back_log，如果等待串連的數量超過back_log，將不被授予串連資源。
27. #另外，這值（back_log）限於您的作業系統對到來的TCP/IP串連的偵聽隊列的大小。
28. #你的作業系統在這個隊列大小上有它自己的限制（可以檢查你的OS文檔找出這個變數的最大值），試圖設定back_log高於你的作業系統的限制將是無效的。
29.  
30. max_connections=1000
31. #MySQL的最大串連數，如果伺服器的並發串連請求量比較大，建議調高此值，以增加並行串連數量，當然這建立在機器能支撐的情況下，因為如果串連數越多，介於MySQL會為每個串連提供串連緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。可以過‘conn%‘萬用字元查看目前狀態的串連數量，以定奪該值的大小。
32.  
33. max_connect_errors=6000
34. #對於同一主機，如果有超出該參數值個數的中斷錯誤串連，則該主機將被禁止串連。如需對該主機進行解禁，執行：FLUSHHOST。
35.  
36. open_files_limit=65535
37. #MySQL開啟的檔案描述符限制，預設最小1024;當open_files_limit沒有被配置的時候，比較max_connections*5和ulimit-n的值，哪個大用哪個，
38. #當open_file_limit被配置的時候，比較open_files_limit和max_connections*5的值，哪個大用哪個。
39.  
40. table_open_cache=128
41. #MySQL每開啟一個表，都會讀入一些資料到table_open_cache緩衝中，當MySQL在這個緩衝中找不到相應資訊時，才會去磁碟上讀取。預設值64
42. #假定系統有200個並發串連，則需將此參數設定為200*N(N為每個串連所需的檔案描述符數目)；
43. #當把table_open_cache設定為很大時，如果系統處理不了那麼多檔案描述符，那麼就會出現用戶端失效，串連不上
44.  
45. max_allowed_packet=4M
46. #接受的資料包大小；增加該變數的值十分安全，這是因為僅當需要時才會分配額外記憶體。例如，僅當你發出長查詢或MySQLd必須返回大的結果行時MySQLd才會分配更多記憶體。
47. #該變數之所以取較小預設值是一種預防措施，以捕獲用戶端和伺服器之間的錯誤資訊包，並確保不會因偶然使用大的資訊包而導致記憶體溢出。
48.  
49. binlog_cache_size=1M
50. #一個事務，在沒有提交的時候，產生的日誌，記錄到Cache中；等到事務提交需要提交的時候，則把日誌持久化到磁碟。預設binlog_cache_size大小32K
51.  
52. max_heap_table_size=8M
53. #定義了使用者可以建立的記憶體表(memorytable)的大小。這個值用來計算記憶體表的最大行數值。這個變數支援動態改變
54.  
55. tmp_table_size=16M
56. #MySQL的heap（堆積）表緩衝大小。所有聯合在一個DML指令內完成，並且大多數聯合甚至可以不用暫存資料表即可以完成。
57. #大多數暫存資料表是基於記憶體的(HEAP)表。具有大的記錄長度的暫存資料表(所有列的長度的和)或包含BLOB列的表格儲存體在硬碟上。
58. #如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。還可以通過設定tmp_table_size選項來增加暫存資料表的大小。也就是說，如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯結查詢速度的效果
59.  
60. read_buffer_size=2M
61. #MySQL讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。
62. #如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能
63.  
64. read_rnd_buffer_size=8M
65. #MySQL的隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，
66. #MySQL會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大
67.  
68. sort_buffer_size=8M
69. #MySQL執行排序使用的緩衝大小。如果想要增加ORDERBY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。
70. #如果不能，可以嘗試增加sort_buffer_size變數的大小
71.  
72. join_buffer_size=8M
73. #聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享
74.  
75. thread_cache_size=8
76. #這個值（預設8）表示可以重新利用儲存在緩衝中線程的數量，當中斷連線時如果緩衝中還有空間，那麼用戶端的線程將被放到緩衝中，
77. #如果線程重新被請求，那麼請求將從緩衝中讀取,如果緩衝中是空的或者是新的請求，那麼這個線程將被重新建立,如果有很多新的線程，
78. #增加這個值可以改善系統效能.通過比較Connections和Threads_created狀態的變數，可以看到這個變數的作用。(–>表示要調整的值)
79. #根據實體記憶體設定規則如下：
80. #1G—>8
81. #2G—>16
82. #3G—>32
83. #大於3G—>64
84.  
85. query_cache_size=8M
86. #MySQL的查詢緩衝大小（從4.0.1開始，MySQL提供了查詢緩衝機制）使用查詢緩衝，MySQL將SELECT語句和查詢結果存放在緩衝區中，
87. #今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。根據MySQL使用者手冊，使用查詢緩衝最多可以達到238%的效率。
88. #通過檢查狀態值‘Qcache_%‘，可以知道query_cache_size設定是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，
89. #如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，
90. #這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝
91.  
92. query_cache_limit=2M
93. #指定單個查詢能夠使用的緩衝區大小，預設1M
94.  
95. key_buffer_size=4M
96. #指定用於索引的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，
97. #系統將開始換頁並且真的變慢了。對於記憶體在4GB左右的伺服器該參數可設定為384M或512M。通過檢查狀態值Key_read_requests和Key_reads，
98. #可以知道key_buffer_size設定是否合理。比例key_reads/key_read_requests應該儘可能的低，
99. #至少是1:100，1:1000更好(上述狀態值可以使用SHOWSTATUSLIKE‘key_read%‘獲得)。注意：該參數值設定的過大反而會是伺服器整體效率降低
100.  
101. ft_min_word_len=4
102. #分詞詞彙最小長度，預設4
103.  
104. transaction_isolation=REPEATABLE-READ
105. #MySQL支援4種交易隔離等級，他們分別是：
106. #READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE.
107. #如沒有指定，MySQL預設採用的是REPEATABLE-READ，ORACLE預設的是READ-COMMITTED
108.  
109. log_bin=mysql-bin
110. binlog_format=mixed
111. expire_logs_days=30#超過30天的binlog刪除
112.  
113. log_error=/data/mysql/mysql-error.log#錯誤記錄檔路徑
114. slow_query_log=1
115. long_query_time=1#慢查詢時間超過1秒則為慢查詢
116. slow_query_log_file=/data/mysql/mysql-slow.log
117.  
118. performance_schema=0
119. explicit_defaults_for_timestamp
120.  
121. #lower_case_table_names=1#不區分大小寫
122.  
123. skip-external-locking#MySQL選項以避免外部鎖定。該選項預設開啟
124.  
125. default-storage-engine=InnoDB#預設儲存引擎
126.  
127. innodb_file_per_table=1
128. #InnoDB為獨立資料表空間模式，每個資料庫的每個表都會產生一個資料空間
129. #獨立資料表空間優點：
130. #1．每個表都有自已獨立的資料表空間。
131. #2．每個表的資料和索引都會存在自已的資料表空間中。
132. #3．可以實現單表在不同的資料庫中移動。
133. #4．空間可以回收（除droptable操作處，表空不能自已回收）
134. #缺點：
135. #單表增加過大，如超過100G
136. #結論：
137. #共用資料表空間在Insert操作上少有優勢。其它都沒獨立資料表空間表現好。當啟用獨立資料表空間時，請合理調整：innodb_open_files
138.  
139. innodb_open_files=500
140. #限制Innodb能開啟的表的資料，如果庫裡的表特別多的情況，請增加這個。這個值預設是300
141.  
142. innodb_buffer_pool_size=64M
143. #InnoDB使用一個緩衝池來儲存索引和未經處理資料,不像MyISAM.
144. #這裡你設定越大,你在存取表裡面資料時所需要的磁碟I/O越少.
145. #在一個獨立使用的資料庫伺服器上,你可以設定這個變數到伺服器實體記憶體大小的80%
146. #不要設定過大,否則,由於實體記憶體的競爭可能導致作業系統的換頁顛簸.
147. #注意在32位系統上你每個進程可能被限制在2-3.5G使用者層面記憶體限制,
148. #所以不要設定的太高.
149.  
150. innodb_write_io_threads=4
151. innodb_read_io_threads=4
152. #innodb使用後台線程處理資料頁上的讀寫I/O(輸入輸出)請求,根據你的CPU核心數來更改,預設是4
153. #注:這兩個參數不支援動態改變,需要把該參數加入到my.cnf裡，修改完後重啟MySQL服務,允許值的範圍從1-64
154.  
155. innodb_thread_concurrency=0
156. #預設設定為0,表示不限制並發數，這裡推薦設定為0，更好去發揮CPU多核處理能力，提高並發量
157.  
158. innodb_purge_threads=1
159. #InnoDB中的清除操作是一類定期回收無用資料的操作。在之前的幾個版本中，清除操作是主線程的一部分，這意味著運行時它可能會堵塞其它的資料庫操作。
160. #從MySQL5.5.X版本開始，該操作運行於獨立的線程中,並支援更多的並發數。使用者可通過設定innodb_purge_threads配置參數來選擇清除操作是否使用單
161. #獨線程,預設情況下參數設定為0(不使用單獨線程),設定為1時表示使用單獨的清除線程。建議為1
162.  
163. innodb_flush_log_at_trx_commit=2
164. #0：如果innodb_flush_log_at_trx_commit的值為0,logbuffer每秒就會被刷寫記錄檔到磁碟，提交事務的時候不做任何操作（執行是由mysql的masterthread線程來執行的。
165. #主線程中每秒會將重做日誌緩衝寫入磁碟的重做記錄檔(REDOLOG)中。不論事務是否已經提交）預設的記錄檔是ib_logfile0,ib_logfile1
166. #1：當設為預設值1的時候，每次提交事務的時候，都會將logbuffer刷寫到日誌。
167. #2：如果設為2,每次提交事務都會寫日誌，但並不會執行刷的操作。每秒定時會刷到記錄檔。要注意的是，並不能保證100%每秒一定都會刷到磁碟，這要取決於進程的調度。
168. #每次事務提交的時候將資料寫入交易記錄，而這裡的寫入僅是調用了檔案系統的寫入操作，而檔案系統是有緩衝的，所以這個寫入並不能保證資料已經寫入到物理磁碟
169. #預設值1是為了保證完整的ACID。當然，你可以將這個配置項設為1以外的值來換取更高的效能，但是在系統崩潰的時候，你將會丟失1秒的資料。
170. #設為0的話，mysqld進程崩潰的時候，就會丟失最後1秒的事務。設為2,只有在作業系統崩潰或者斷電的時候才會丟失最後1秒的資料。InnoDB在做恢複的時候會忽略這個值。
171. #總結
172. #設為1當然是最安全的，但效能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對資料一致性和完整性要求不高，完全可以設為2，如果只最求效能，例如高並發寫的Log Service器，設為0來獲得更高效能
173.  
174. innodb_log_buffer_size=2M
175. #此參數確定些記錄檔所用的記憶體大小，以M為單位。緩衝區更大能提高效能，但意外的故障將會遺失資料。MySQL開發人員建議設定為1－8M之間
176.  
177. innodb_log_file_size=32M
178. #此參數確定資料記錄檔的大小，更大的設定可以提高效能，但也會增加恢複故障資料庫所需的時間
179.  
180. innodb_log_files_in_group=3
181. #為提高效能，MySQL可以以迴圈方式將記錄檔寫到多個檔案。推薦設定為3
182.  
183. innodb_max_dirty_pages_pct=90
184. #innodb主線程重新整理緩衝池中的資料，使髒資料比例小於90%
185.  
186. innodb_lock_wait_timeout=120
187. #InnoDB事務在被復原之前可以等待一個鎖定的逾時秒數。InnoDB在它自己的鎖定表中自動檢測事務死結並且復原事務。InnoDB用LOCKTABLES語句注意到鎖定設定。預設值是50秒
188.  
189. bulk_insert_buffer_size=8M
190. #批量插入緩衝大小，這個參數是針對MyISAM儲存引擎來說的。適用於在一次性插入100-1000+條記錄時，提高效率。預設值是8M。可以針對資料量的大小，翻倍增加。
191.  
192. myisam_sort_buffer_size=8M
193. #MyISAM設定恢複表之時使用的緩衝區的尺寸，當在REPAIRTABLE或用CREATEINDEX建立索引或ALTERTABLE過程中排序MyISAM索引分配的緩衝區
194.  
195. myisam_max_sort_file_size=10G
196. #如果臨時檔案會變得超過索引，不要使用快速排序索引方法來建立一個索引。注釋：這個參數以位元組的形式給出
197.  
198. myisam_repair_threads=1
199. #如果該值大於1，在Repairbysorting過程中並行建立MyISAM表索引(每個索引在自己的線程內)
200.  
201. interactive_timeout=28800
202. #伺服器關閉互動式串連前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的用戶端。預設值：28800秒（8小時）
203.  
204. wait_timeout=28800
205. #伺服器關閉非互動串連之前等待活動的秒數。線上程啟動時，根據全域wait_timeout值或全域interactive_timeout值初始化會話wait_timeout值，
206. #取決於用戶端類型(由mysql_real_connect()的串連選項CLIENT_INTERACTIVE定義)。參數預設值：28800秒（8小時）
207. #MySQL伺服器所支援的最大串連數是有上限的，因為每個串連的建立都會消耗記憶體，因此我們希望用戶端在串連到MySQLServer處理完相應的操作後，
208. #應該中斷連線並釋放佔用的記憶體。如果你的MySQLServer有大量的閑置串連，他們不僅會白白消耗記憶體，而且如果串連一直在累加而不斷開，
209. #最終肯定會達到MySQLServer的串連上限數，這會報‘toomanyconnections‘的錯誤。對於wait_timeout的值設定，應該根據系統的運行情況來判斷。
210. #在系統運行一段時間後，可以通過showprocesslist命令查看當前系統的串連狀態，如果發現有大量的sleep狀態的串連進程，則說明該參數設定的過大，
211. #可以進行適當的調整小些。要同時設定interactive_timeout和wait_timeout才會生效。
212.  
213. [mysqldump]
214. quick
215. max_allowed_packet=16M#伺服器發送和接受的最大包長度
216.  
217. [myisamchk]
218. key_buffer_size=8M
219. sort_buffer_size=8M
220. read_buffer=4M
221. write_buffer=4M

 更多內容：http://www.wangzhanjianshegs.com/

MySQL最佳化效能my.cnf詳解