高效能的MySQL（8）最佳化伺服器配置：記憶體

配置MySQL伺服器離不開設定檔，接下來就開始這一部分的內容。

首先一定要清楚設定檔的位置，如果不知道可以嘗試下面的操作：

/usr/local/mysql/bin/mysqld  --verbose --help | grep -A 1 'Default options'
#結果如下
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

有時候可以使用show global status 的輸出來看狀態，有的時候也需要看隨著時間的變化而變化的狀態值，可以使用命令：

/usr/local/mysql/bin/mysqladmin extended-status -ri60 -uroot -p123456

接下來看看記憶體的配置使用了。

MySQL可以使用的記憶體會受到作業系統位元的限制，MySQL保持一個串連（線程）只需要少量的記憶體，同時也需要為高峰時期執行大量查詢預留好足夠的記憶體。
下面是我們認為比較重要的緩衝：

a、InnoDB緩衝池

b、InnoDB記錄檔和MyISAM資料的作業系統緩衝

c、MyISAM金鑰快取

d、查詢快取

InnoDB緩衝池並不僅僅緩衝索引，還會緩衝行的資料，自適應雜湊索引，插入緩衝，鎖，以及其他內部資料結構。很大的緩衝池，預熱和關閉都很話費很長時間，重啟伺服器也會話費很長的時間，尤其是磁碟很慢的時候，可以使用Percona Server的快速預熱功能，如果不能使用可以在重啟時立刻進行全表掃描或者索引掃描，把索引載入緩衝池。可以通過使用init_file設定來實現這個功能。

如果大部分表都是MySIAM表，就應該為金鑰快取分配比較多的記憶體。最重要的配置是key_buffer_size，在決定金鑰快取需要分配多少記憶體之前，需要先瞭解MySIAM索引實際佔用多少空間。查詢INFORMATION_SCHEM表的INDEX_LENGTH欄位，把他們的值相加就可以得到索引佔用空間。如下：

linux下也可以使用如下命令：

du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`

預設情況下，MySIAM將所有索引都緩衝在預設的金鑰快取中，但也可以建立多個命名的鍵，可以在設定檔中添加如下

key_buffer_1.key_buffer_size=1G

key_buffer_2.key_buffer_size=1G

可以使用cache index 命令來將表映射到對應的緩衝區，比如下面的語句，使用key_buffer_1來緩衝t1和t2表的索引

cache index t1,t2 in key_buffer_1;

也可以把表的索引預載入緩衝中，如下命令:

load index into cache t1,t2;

即使全部是InnoDB表，沒有MySIAM表，依然需要將key_buffer_size設定為一個較小的值，因為伺服器有時會在內部使用MySIAM，例如GROUP BY語句可能會使用MySIAM作為暫存資料表。

線程緩衝儲存那些當前沒有串連但是準備為後面的串連服務的線程，thread_cache_size變數指定了MySQL可以保持在緩衝中的線程數。

一個好的辦法是觀察Threads_connected變數並且嘗試thread_cache_size，沒有必要設定的非常大。

InnoDB有自己的表緩衝，可以稱為資料字典，當InnoDB開啟一個表就增加了一個對應的對象到資料字典，當表關閉的時候也不會從資料字典移除。

另一個效能問題是第一次開啟表會計算統計資訊，這需要很多的I/O操作，相比MySIAM，InnoDB沒有將統計資訊持久化，而是在每次開啟時重新計算，包括改變表的內容或者查詢INFORMATION_SCHEMA表也會重新計算統計資訊，如果有很多表，伺服器可能會花費數個小時來啟動並完成預熱。MySQL5.6以後，可以通過innodb_analyze_is_persistent選項來持久化儲存統計資訊到磁碟上。

可以關閉innodb_stats_on_metadata選項來避免耗時的表統計資訊的重新整理。

本文出自 “phper-每天一點點~” 部落格，請務必保留此出處http://janephp.blog.51cto.com/4439680/1320197