MySQL配置參數 my.ini/my.cnf的詳細解析

2015.09.10 | 351閱讀 | 0條評論 | 數據庫

1．獲取當前配置參數要優化MySQL配置參數，首先要了解當前的配置參數以及運行情況。使用下列命令可以獲得目前服務器使用的配置參數：



	mysqld?–verbose?–help


	mysqladmin?variables?extended-status?–u?root?–p

在MySQL控制臺里面，運行下列命令可以獲取狀態變量的值：



	mysql>?SHOW?STATUS;

如果只要檢查某幾個狀態變量，可以使用下列命令： mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 ) 2．優化參數參數優化基于一個前提，就是在我們的數據庫中通常都使用InnoDB表，而不使用MyISAM表。在優化MySQL時，有兩個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表來說，正確MySQL配置參數對于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 秒來刷新表的高速緩存。用這種方法來寫出對表的修改將降低性能，但可減少表訛誤或數據丟失的機會。 ? 文章來源：http://database.51cto.com/art/201005/201257.htm

MySQL配置參數 my.ini/my.cnf的詳細解析

發表評論