mysql最佳化的重要參數 key_buffer_size table_cache

MySQL伺服器端的參數有很多，但是對於大多數初學者來說，眾多的參數往往使得我們不知所措，但是哪些參數是需要我們調整的，哪些對伺服器的效能影響最大呢？對於使用Myisam儲存引擎來說，主要有key_buffer_size和table_cache兩個參數。對於InnoDB引擎來說主要還是以innodb_開始的參數，也很好辨認。查看MySQL參數，可以使用show variables和show status命令查看，前者查看伺服器靜態參數，即在資料庫啟動後不會動態更改的值，比如緩衝區、字元集等。後者查看伺服器的動態運行狀態資訊，即資料庫運行期間動態變化的資訊，比如鎖，當前串連數等。key_buffer_size這個參數是用來設定索引塊index blocks）緩衝的大小，它被所有線程共用，嚴格說是它決定了資料庫索引處理的速度，尤其是索引讀的速度。那我們怎麼才能知道key_buffer_size的設定是否合理呢，一般可以檢查狀態值Key_read_requests和Key_reads，比例key_reads / key_read_requests應該儘可能的低，比如1:100，1:1000 ，1:10000。其值可以用以i下命令查得：mysql> show status like 'key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3916880184 |
| Key_reads | 1014261 |
+-------------------+------------+
2 rows in set (0.00 sec)3916880184/1024/1024=?M //單位為兆650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131229/20431SZ7-0.jpg" width="650" style="margin:0px;padding:0px;border:0px;" alt="201001221264151584140.jpg" />我的key_buffer_size值為：650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131229/20431U320-1.jpg" style="margin:0px;padding:0px;border:0px;" alt="201001221264151803203.jpg" />key_buffer_size=536870912/1024/1024=512M,key_reads / key_read_requests=1014261: 3916880184≈1:4000，照上面來看，健康情況還行。table_cache指定表快取的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/20431S633-2.jpg" width="600" alt="按此在新視窗瀏覽圖片" style="margin:0px;padding:0px;border:0px;" />650) this.width=650;" border="0" src="http://www.bkjia.com/uploads/allimg/131229/20431RD5-3.jpg" style="margin:0px;padding:0px;border:0px;" alt="201001221264154105656.jpg" />open_tables表示當前開啟的表緩衝數，如果執行flush tables操作，則此系統會關閉一些當前沒有使用的表緩衝而使得此狀態值減小；opend_tables表示曾經開啟的表緩衝數，會一直進行累加，如果執行flush tables操作，值不會減小。在mysql預設安裝情況下，table_cache的值在2G記憶體以下的機器中的值預設時256到512，如果機器有4G記憶體,則預設這個值是2048，但這決意味著機器記憶體越大，這個值應該越大，因為table_cache加大後，使得mysql對SQL響應的速度更快了，不可避免的會產生更多的死結dead lock），這樣反而使得資料庫整個一套操作慢了下來，嚴重影響效能。所以平時維護中還是要根據庫的實際情況去作出判斷，找到最適合你維護的庫的table_cache值。就是table_cache加大後碰到檔案描述符不夠用的問題，在mysql的設定檔中有這麼一段提示：
引用
“The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
Therefore you have to make sure to set the amount of open files allowed to at least 4096 in the variable "open-files-limit" in” section [mysqld_safe]”
說的就是要注意這個問題，一想到這裡，部分兄弟可能會用ulimit -n 作出調整，但是這個調整實際是不對的，換個終端後，這個值又會回到原始值，所以最好用sysctl或者修改/etc/sysctl.conf檔案，同時還要在設定檔中把open_files_limit這個參數增大，對於4G記憶體伺服器，相信現在購買的伺服器都差不多用4G的了，那這個這個open_files_limit至少要增大到4096，如果沒有什麼特殊情況，設定成8192就可以了。innodb_buffer_pool_size 這個參數和MyISAM的key_buffer_size有相似之處，但也是有差別的。這個參數主要緩衝innodb表的索引，資料，插入資料時的緩衝。為Innodb加速最佳化首要參數。　　該參數分配記憶體的原則：這個參數預設分配只有8M，可以說是非常小的一個值。如果是一個專用ＤＢ伺服器，那麼他可以佔到記憶體的70%-80%。這個參數不能動態更改，所以分配需多考慮。分配過大，會使Swap佔用過多，致使Mysql的查詢特慢。如果你的資料比較小，那麼可分配是你的資料大小＋１０％左右做為這個參數的值。