在做
軟件開發(fā)時，如果選用
MySQL數(shù)據(jù)庫的系統(tǒng)，當需要存儲數(shù)據(jù)量大或數(shù)據(jù)操作很頻繁時，MySQL對于性能的影響很大，也是關(guān)鍵的核心部分。MySQL的設(shè)置是否合理優(yōu)化，直接影響到軟件運行的速度和承載量。同時，MySQL也是優(yōu)化難度最大的一個部分，不但需要理解一些MySQL專業(yè)知識，同時還需要長時間的觀察統(tǒng)計并且根據(jù)經(jīng)驗進行判斷，然后設(shè)置合理的參數(shù)。下面我們研究一下MySQL(my.cnf/my.ini)的配置文件，通過在(my.cnf/my.ini)中添加/修改參數(shù)項來對Mysql進行優(yōu)化。
linux環(huán)境MySql配置優(yōu)化
1、配置文件
MySQL服務(wù)器的許多參數(shù)會影響服務(wù)器的性能表現(xiàn)，而且我們可以把這些參數(shù)保存到配置文件，使得每次MySQL服務(wù)器啟動時這些參數(shù)都自動發(fā)揮作用。這個配置文件就是my.cnf。
MySQL服務(wù)器提供了my.cnf文件的幾個示例，它們可以在/usr/local/mysql/share/mysql/目錄下找到，名字分別為 my-small.cnf、my-medium.cnf、my-large.cnf以及my-huge.cnf。文件名字中關(guān)于規(guī)模的說明描述了該配置文件適用的系統(tǒng)類型。例如，如果運行MySQL服務(wù)器的系統(tǒng)內(nèi)存不多，而且MySQL只是偶爾使用，那么使用my-small.cnf配置文件最為理想，這個配置文件告訴mysqld daemon使用最少的系統(tǒng)資源。反之，如果MySQL服務(wù)器用于支持一個大規(guī)模的在線網(wǎng)站，系統(tǒng)擁有4G以上的內(nèi)存，那么使用mysql-huge.cnf 最為合適。
要使用上述示例配置文件，我們應(yīng)該先復(fù)制一個最適合要求的配置文件，并把它命名為my.cnf。這個復(fù)制得到的配置文件可以按照如下三種方式使用：
全局：把這個my.cnf文件復(fù)制到服務(wù)器的/etc目錄，此時文件中所定義的參數(shù)將全局有效，即對該服務(wù)器上運行的所有MySQL數(shù)據(jù)庫服務(wù)器都有效。
局部：把這個my.cnf文件復(fù)制到[MYSQL-INSTALL-DIR]/var/將使該文件只對指定的服務(wù)器有效，其中[MYSQL-INSTALL-DIR]表示安裝MySQL的目錄。
用戶：最后，我們還可以把該文件的作用范圍局限到指定的用戶，這只需把my.cnf文件復(fù)制到用戶的根目錄即可。
那么，如何設(shè)置my.cnf文件中的參數(shù)呢？或者進一步說，哪些參數(shù)是我們可以設(shè)置的呢？所有這些參數(shù)都對MySQL服務(wù)器有著全局性的影響，但同時每一個參數(shù)都和MySQL的特定部分關(guān)系較為密切。例如，max_connections參數(shù)屬于mysqld一類。那么，如何才能得知這一點呢？這只需執(zhí)行如下命令：
%>/usr/local/mysql/libexec/mysqld –help
該命令將顯示出和mysqld有關(guān)的各種選項和參數(shù)。要尋找這些參數(shù)非常方便，因為這些參數(shù)都在“Possible variables for option –set-variable (-O) are”這行內(nèi)容的后面。找到這些參數(shù)之后，我們就可以在my.cnf文件中按照如下方式設(shè)置所有這些參數(shù)：
例如：
set-variable = max_connections=1000
這行代碼的效果是：同時連接MySQL服務(wù)器的最大連接數(shù)量限制為1000。
2、參數(shù)優(yōu)化
2.1 back_log
back_log = 500要求 MySQL 能有的連接數(shù)量。當主要MySQL線程在一個很短時間內(nèi)得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。只有如果期望在一個短時間內(nèi)有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。你的
操作系統(tǒng)在這個隊列大小上有它自己的限制。試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效的。當你觀察你的主機進程列表，發(fā)現(xiàn)大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大 back_log 的值了。默認數(shù)值是50，如果訪問量大可以它改為500。
2.2 key_buffer_size
Key_read_requests ：從緩存讀取索引的請求次數(shù)；
Key_reads ：從磁盤讀取索引的請求次數(shù)；
key_buffer_size指定用于索引的緩沖區(qū)大小。如果Key_reads太大,則應(yīng)該把key_buffer_size變大。增加它可得到更好處理的索引(對所有讀和多重寫)，如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。對于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為384M或512M。通過檢查狀態(tài)值Key_read_requests和 Key_reads,可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好(上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%'獲得)。注意：該參數(shù)值設(shè)置的過大反而導(dǎo)致是服務(wù)器整體效率降低。
2.3 max_allowed_packet
設(shè)置最大包,限制server接受的數(shù)據(jù)包大小，避免超長SQL的執(zhí)行有問題 默認值為16M，當MySQL客戶端或mysqld服務(wù)器收到大于max_allowed_packet字節(jié)的信息包時，將發(fā)出“信息包過大”錯誤，并關(guān)閉連接。對于某些客戶端，如果通信信息包過大，在執(zhí)行查詢期間，可能會遇到“丟失與MySQL服務(wù)器的連接”錯誤。
增加該變量的值十分安全，這是因為僅當需要時才會分配額外內(nèi)存。例如，僅當你發(fā)出長查詢或mysqld必須返回大的結(jié)果行時mysqld才會分配更多內(nèi)存。該變量之所以取較小默認值是一種預(yù)防措施，以捕獲客戶端和服務(wù)器之間的錯誤信息包，并確保不會因偶然使用大的信息包而導(dǎo)致內(nèi)存溢出。
2.4 table_cache
table_cache = 512指定表高速緩存的大小, 如果opened_tables太大,應(yīng)該把table_cache變大。每當MySQL訪問一個表時，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內(nèi)容。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn) open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了(上述狀態(tài)值可以使用SHOW STATUS LIKE ‘Open%tables'獲得)。注意，不能盲目地把table_cache設(shè)置成很大的值。如果設(shè)置得太高，可能會造成文件描述符不足，從而造成性能不穩(wěn)定或者連接失敗。
2.5 sort_buffer_size
屬重點優(yōu)化參數(shù)
sort_buffer_size = 4M查詢排序時所能使用的緩沖區(qū)大小。注意：該參數(shù)對應(yīng)的分配內(nèi)存是每連接獨占，如果有100個連接，那么實際分配的總共排序緩沖區(qū)大小為100 × 4 = 400MB。所以，對于內(nèi)存在4GB左右的服務(wù)器推薦設(shè)置為4-8M。
2.6 read_buffer_size
read_buffer_size = 4M讀查詢操作所能使用的緩沖區(qū)大小。和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享。MySql讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySql會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。
2.7 join_buffer_size
join_buffer_size = 8M聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每連接獨享。
2.8 thread_cache_size
thread_cache_size = 64服務(wù)器線程緩存這個值表示可以重新利用保存在緩存中線程的數(shù)量，如果Threads_created太大,就要增加thread_cache_size的值。當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程，增加這個值可以改善系統(tǒng)性能.通過比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個變量的作用
2.9 max_connections
max_connections = 300指定MySQL允許的最大連接進程數(shù)。如果在訪問論壇時經(jīng)常出現(xiàn)Too Many Connections的錯誤提示，則需要增大該參數(shù)值。
2.10 max_connect_errors
max_connect_errors = 10000000對于同一主機，如果有超出該參數(shù)值個數(shù)的中斷錯誤連接，則該主機將被禁止連接。如需對該主機進行解禁，執(zhí)行：FLUSH HOST;。
2.11 wait_timeout
wait_timeout = 10指定一個請求的最大連接時間，對于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。
2.12 thread_concurrency
屬重點優(yōu)化參數(shù)
thread_concurrency = 8設(shè)置thread_concurrency的值的正確與否, 對mysql的性能影響很大, 在多個cpu(或多核)的情況下，錯誤設(shè)置了thread_concurrency的值, 會導(dǎo)致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時刻只能一個cpu(或核)在工作的情況。thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍. 比如有一個雙核的CPU, 那么thread_concurrency的應(yīng)該為4; 2個雙核的cpu, thread_concurrency的值應(yīng)為8。
2.13 tmp_table_size
如果Created_tmp_disk_tables太大, 就要增加tmp_table_size的值,用基于內(nèi)存的臨時表代替基于磁盤的。tmp_table_size 的默認大小是 32M。如果一張臨時表超出該大小，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，可以增加 tmp_table_size 值。
2.14 read_rnd_buffer_size
MySql的隨機讀（查詢操作）緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySql會為每個客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當設(shè)置該值，以避免內(nèi)存開銷過大。
2.15 query_cache_size屬重點優(yōu)化參數(shù)
這個參數(shù)也是一個重要的優(yōu)化參數(shù)。但隨著發(fā)展，這個參數(shù)也爆露出來一些問題。機器的內(nèi)存越來越大，人們也都習慣性的把以前有用的參數(shù)分配的值越來越大。這個參數(shù)加大后也引發(fā)了一系列問題。我們首先分析一下 query_cache_size的工作原理：一個SELECT查詢在DB中工作后，DB會把該語句緩存下來，當同樣的一個SQL再次來到DB里調(diào)用時，DB在該表沒發(fā)生變化的情況下把結(jié)果從緩存中返回給Client。這里有一個關(guān)建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內(nèi)沒有發(fā)生變更。那如果該表在發(fā)生變更時，Query_cache里的數(shù)據(jù)又怎么處理呢？首先要把Query_cache和該表相關(guān)的語句全部置為失效，然后在寫入更新。那么如果Query_cache非常大，該表的查詢結(jié)構(gòu)又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎么這么慢了。所以在數(shù)據(jù)庫寫入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過大。而且在高并發(fā)，寫入量大的系統(tǒng)，建系把該功能禁掉。