有效配置MySQL引數--my.cnf(轉)

有效配置MySQL引數--my.cnf(轉)[@more@]1．獲取當前配置引數
要最佳化配置引數，首先要了解當前的配置引數以及執行情況。使用下列命令可以獲得目前伺服器使用的配置引數：


mysqld –verbose –help


mysqladmin variables extended-status –u root –p


在MySQL控制檯裡面，執行下列命令可以獲取狀態變數的值：


mysql> SHOW STATUS;


如果只要檢查某幾個狀態變數，可以使用下列命令：


mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )


2．最佳化引數
引數最佳化基於一個前提，就是在我們的資料庫中通常都使用InnoDB表，而不使用MyISAM表。在最佳化MySQL時，有兩個配置引數是最重要的，即table_cache和key_buffer_size。


table_cache


table_cache指定表快取記憶體的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。透過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了（上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者連線失敗。


對於有1G記憶體的機器，推薦值是128－256。




案例1：該案例來自一個不是特別繁忙的伺服器


table_cache – 512


open_tables – 103


opened_tables – 1273


uptime – 4021421 (measured in seconds)


該案例中table_cache似乎設定得太高了。在峰值時間，開啟表的數目比table_cache要少得多。




案例2：該案例來自一臺開發伺服器。


table_cache – 64


open_tables – 64


opened-tables – 431


uptime – 1662790 (measured in seconds)


雖然open_tables已經等於table_cache，但是相對於伺服器執行時間來說，opened_tables的值也非常低。因此，增加table_cache的值應該用處不大。




案例3：該案例來自一個upderperforming的伺服器


table_cache – 64


open_tables – 64


opened_tables – 22423


uptime – 19538


該案例中table_cache設定得太低了。雖然執行時間不到6小時，open_tables達到了最大值，opened_tables的值也非常高。這樣就需要增加table_cache的值。




key_buffer_size


key_buffer_size指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。透過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。


key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。


對於1G記憶體的機器，如果不使用MyISAM表，推薦值是16M（8-64M）。




案例1：健康狀況


key_buffer_size – 402649088 (384M)


key_read_requests – 597579931


key_reads - 56188


案例2：警報狀態


key_buffer_size – 16777216 (16M)


key_read_requests – 597579931


key_reads - 53832731


案例1中比例低於1:10000，是健康的情況；案例2中比例達到1:11，警報已經拉響。




最佳化query_cache_size


從4.0.1開始，MySQL提供了查詢緩衝機制。使用查詢緩衝，MySQL將SELECT語句和查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。根據MySQL使用者手冊，使用查詢緩衝最多可以達到238％的效率。


透過檢查狀態值Qcache_*，可以知道query_cache_size設定是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝。


與查詢緩衝有關的引數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查詢緩衝，可以設定為0、1、2，該變數是SESSION級的變數。query_cache_limit指定單個查詢能夠使用的緩衝區大小，預設為1M。query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多，這就表明查詢結果都比較小，此時需要減小query_cache_min_res_unit。




開啟二進位制日誌( Binary Log )


二進位制日誌包含所有更新資料的語句，其目的是在恢復資料庫時用它來把資料儘可能恢復到最後的狀態。另外，如果做同步複製( Replication )的話，也需要使用二進位制日誌傳送修改情況。


開啟二進位制日誌，需要設定引數log-bin。log_bin指定日誌檔案，如果不提供檔名，MySQL將自己產生預設檔名。MySQL會在檔名後面自動新增數字索引，每次啟動服務時，都會重新生成一個新的二進位制檔案。


此外，使用log-bin-index可以指定索引檔案；使用binlog-do-db可以指定記錄的資料庫；使用binlog-ignore-db可以指定不記錄的資料庫。注意的是：binlog-do-db和binlog-ignore-db一次只指定一個資料庫，指定多個資料庫需要多個語句。而且，MySQL會將所有的資料庫名稱改成小寫，在指定資料庫時必須全部使用小寫名字，否則不會起作用。


在MySQL中使用SHOW MASTER STATUS命令可以檢視目前的二進位制日誌狀態。




開啟慢查詢日誌( slow query log )


慢查詢日誌對於跟蹤有問題的查詢非常有用。它記錄所有查過long_query_time的查詢，如果需要，還可以記錄不使用索引的記錄。下面是一個慢查詢日誌的例子：


開啟慢查詢日誌，需要設定引數log_slow_queries、long_query_times、log-queries-not-using-indexes。log_slow_queries指定日誌檔案，如果不提供檔名，MySQL將自己產生預設檔名。long_query_times指定慢查詢的閾值，預設是10秒。log-queries-not-using-indexes是4.1.0以後引入的引數，它指示記錄不使用索引的查詢。




配置InnoDB


相對於MyISAM表來說，正確配置引數對於InnoDB表更加關鍵。其中，最重要的引數是innodb_data_file_path。它指定表資料和索引儲存的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也只有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動增長（以8MB為單位）以容納額外的資料。例如：


innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend


兩個資料檔案放在不同的磁碟上。資料首先放在ibdata1中，當達到900M以後，資料就放在ibdata2中。一旦達到50MB，ibdata2將以8MB為單位自動增長。


如果磁碟滿了，你需要在另外的磁碟上面增加一個資料檔案。為此，你需要檢視最後一個檔案的尺寸，然後計算最接近的整數（MB）。然後手工修改該檔案的大小，並新增新的資料檔案。例如：假設ibdata2已經有109MB資料，那麼可以修改如下：


innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend




flush_time


如果系統有問題並且經常鎖死或重新引導，應將該變數設定為非零值，這將導致伺服器按flush_time 秒來重新整理表的快取記憶體。用這種方法來寫出對錶的修改將降低效能，但可減少表訛誤或資料丟失的機會。


一般使用預設值。




Binlog_cache_size


The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled(--log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.




3．儲存引擎
在MYSQL 3.23.0版本中，引入了MyISAM儲存引擎。它是一個非事務型的儲存引擎，成為了MYSQL的預設儲存引擎。但是，如果使用設定嚮導來設定引數，則它會把InnoDB作為預設的儲存引擎。InnoDB是一個事務型的儲存引擎。


建立表的時候，可以為表指定儲存引擎，語法如下：


CREATE TABLE t (i INT) ENGINE = MyISAM


CREATE TABLE t (i INT) TYPE = MyISAM


如果沒有指定，則使用預設的儲存引擎。也可以使用ALTER TABLE來更換表引擎，語法如下：


ALTER TABLE t ENGINE = MyISAM


同一資料庫中可以包含不同儲存引擎的表。

事務型表具有以下特點：

Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic