[Lnmpa] Mysql 配置設定 記憶體&優化 方案

MySQL介紹

MySQL是一個關系型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下産品。MySQL 是最流行的關系型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系資料庫管理系統) 應用軟體。

MySQL記憶體計算方式

全局記憶體消耗（共享記憶體）[單純MYSQL服務（不計算連接配接占用記憶體）機關 M]：

MYSQL_Service_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size 

也可以用SQL指令：select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size )/1024/1024 as “MYSQL_Service_Mem”;

直接在MysQL指令提示符裡輸入就可以輸出結果。

MYSQL服務最大使用記憶體（包含連接配接通路資料庫）機關 M：

MYSQL_Service_Max_Mem = key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + max_connections * ( read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack )

也可以用SQL指令：select (@@key_buffer_size +@@innodb_buffer_pool_size+ @@tmp_table_size +@@query_cache_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size + @@max_connections*(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack) )/1024/1024 as “MYSQL_Service_Max_Mem”;

直接在MysQL指令提示符裡輸入就可以輸出結果。

會話級别的記憶體消耗（連接配接私有記憶體）[Mysql 單個連接配接最大占記憶體，機關 M]：

MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack

也可以用SQL指令：select (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack)/1024/1024 as “MYSQL_Service_Connect_Mem”;

直接在MysQL指令提示符裡輸入就可以輸出結果。

MYSQL優化舉例

例如一台2G記憶體的伺服器，安裝有Nginx、Apache、Php、Mysql等的環境，準備劃分128MB~512MB的記憶體區間給MySQL使用。

首先就可以配置 MYSQL_Service_Mem（ key_buffer_size + innodb_buffer_pool_size + tmp_table_size + query_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size ）這三個參數，保持在128MB左右。

MYSQL_Service_Mem = key_buffer_size （32MB）+ innodb_buffer_pool_size（32MB）+ tmp_table_size（32MB）+ query_cache_size（8MB） + innodb_additional_mem_pool_size（0MB） + innodb_log_buffer_size（8MB）= 112MB

key_buffer_size 參數：（MyISAM）

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

如果你使它太大，系統将開始換頁并且真的變慢了。嚴格說是它決定了資料庫索引處理的速度，尤其是索引讀的速度。

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

怎麼才能知道key_buffer_size的設定是否合理呢，一般可以檢查狀态值Key_read_requests和Key_reads ，比例key_reads / key_read_requests應該盡可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下指令查得：show status like ‘key_read%’;

比如檢視系統目前key_read和key_read_request值為：

+——————-+———————–+

| Variable_name | Value |

+——————-+———————–+

| Key_read_requests | 28535 |

| Key_reads | 269 |

+——————-+———————–+

可知道有28535個請求，有269個請求在記憶體中沒有找到直接從硬碟讀取索引。未命中緩存的機率為：0.94%=269/28535*100%。一般未命中機率在0.1之下比較好，目前已遠遠大于0.1，證明效果不好。若命中率在0.01以下，則建議适當的修改key_buffer_size值。

innodb_buffer_pool_size 參數：（InnoDB）

主要針對InnoDB表性能影響最大的一個參數。功能與Key_buffer_size一樣。InnoDB占用的記憶體，除innodb_buffer_pool_size用于存儲頁面緩存資料外，另外正常情況下還有大約8%的開銷，主要用在每個緩存頁幀的描述、adaptive hash等資料結構，如果不是安全關閉，啟動時還要恢複的話，還要另開大約12%的記憶體用于恢複，兩者相加就有差不多21%的開銷。假設：12G的innodb_buffer_pool_size，最多的時候InnoDB就可能占用到14.5G的記憶體。若系統隻有16G，而且隻運作MySQL，且MySQL隻用InnoDB，那麼為MySQL開12G，是最大限度地利用記憶體了。

另外InnoDB和 MyISAM 存儲引擎不同， MyISAM 的 key_buffer_size 隻能緩存索引鍵，而innodb_buffer_pool_size 卻可以緩存資料塊和索引鍵。适當的增加這個參數的大小，可以有效的減少InnoDB 類型的表的磁盤 I/O 。

怎麼才能知道innodb_buffer_pool_size的設定是否合理呢，可以通過(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率，并根據命中率來調整innodb_buffer_pool_size 參數大小進行優化。值可以用以下指令查得：show status like ‘Innodb_buffer_pool_read%’;

比如檢視目前系統中系統中

+—————————————+———+

| Innodb_buffer_pool_read_requests | 1283826 |

| Innodb_buffer_pool_reads | 519 |

+—————————————+———+

其命中率99.959% =（1283826-519）/1283826*100% 命中率越高越好。

tmp_table_size 參數：

控制記憶體臨時表的最大值,超過限值後就往硬碟寫，寫的位置由變量 tmpdir 決定。

通過設定tmp_table_size選項來增加一張臨時表的大小，例如做進階分組排序操作生成的臨時表。如果調高該值，MySQL同時将增加heap表的大小，可達到提高聯接查詢速度的效果，建議盡量優化查詢，要確定查詢過程中生成的臨時表在記憶體中，避免臨時表過大導緻生成基于硬碟的MyISAM表。

怎麼才能知道tmp_table_size的設定是否合理呢，可以通過 Created_tmp_disk_tables / Created_tmp_tables * 100% 計算緩存命中率，并根據命中率來調整 tmp_table_size 參數大小進行優化。值可以用以下指令查得：show global status like ‘created_tmp%’;

+——————————–+———+

| Variable_name　　 | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

未命中率為：Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，數字越低越好。

query_cache_size 參數：

MySQL對于查詢的結果會進行緩存來節省解析SQL、執行SQL的花銷，query_cache是按照SQL語句的Hash值進行緩存的，同時SQL語句涉及的表發生更新，該緩存就會失效，是以這個緩存對于特定的讀多更新少的庫比較有用，對于絕大多數更新較多的庫可能不是很适用，比較受限于應用場景，是以AWS也把這個緩存給關了。我覺得這個值預設應該關閉，根據需求調整。

Query Cache的使用需要多個參數配合，其中最為關鍵的是query_cache_size和query_cache_type，前者設定用于緩存 ResultSet的記憶體大小，後者設定在何場景下使用Query Cache。query_cache_type可以設定為0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，隻有顯示要求才使用query cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖. 如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；

怎麼才能知道query_cache_size的設定是否合理呢，命中率:(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進行調整，通過指令：show status like ‘Qcache_%’;

+————————-+———–+

| Variable_name | Value |

+————————-+———–+

| Qcache_hits | 1892463 |

| Qcache_inserts | 35627 |

+————————-+———–+

命中率 98.17% = 1892463/(1892463 +35627 )*100，命中率越高越好。

innodb_additional_mem_pool_size 參數：

這個參數用來設定 InnoDB 存儲的資料目錄資訊和其它内部資料結構的記憶體池大小。應用程式裡的表越多，你需要在這裡配置設定越多的記憶體。對于一個相對穩定的應用，這個參數的大小也是相對 穩定的，也沒有必要預留非常大的值。如果 InnoDB 用光了這個池内的記憶體， InnoDB 開始從作業系統配置設定記憶體，并且往 MySQL 錯誤日志寫警告資訊。預設值是 1MB ，當發現錯誤日志中已經有相關的警告資訊時，就應該适當的增加該參數的大小。 

自從Msql 5.6.3 開始，就不需要這個參數。

innodb_log_buffer_size 參數：

這是InnoDB存儲引擎的事務日志所使用的緩沖區。類似于Binlog Buffer，InnoDB在寫事務日志的時候，為了提高性能，也是先将資訊寫入Innofb Log Buffer中，當滿足innodb_flush_log_trx_commit參數所設定的相應條件(或者日志緩沖區寫滿)之後，才會将日志寫到檔案 (或者同步到磁盤)中。可以通過innodb_log_buffer_size 參數設定其可以使用的最大記憶體空間。

這個值的大小主要影響到刷磁盤的次數，設定的過小，Buffer容易滿，就會增加fsync的次數，設定過大，占用記憶體。該值預設是8M，個人覺得目前每次送出都會刷buffer，是以除非有大事務的情況，一般buffer不太可能被占滿，是以沒必要開的很大， 8M應該是滿足需求的。

然後再配置每個連接配接占用記憶體大小：MYSQL_Service_Connect_Mem = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack，計劃512MB配置設定給Mysql使用，剛剛已經配置設定了112MB的記憶體，剩下384MB記憶體，這個時候就需要考慮最大的連接配接數，因為這個參數對連接配接記憶體影響很大，計劃最大連接配接數為128個，那麼每個連接配接的記憶體就占用3MB左右。

MYSQL_Service_Connect_Mem = read_buffer_size (0.25MB) + read_rnd_buffer_size (0.5MB) + sort_buffer_size (0.5MB) + join_buffer_size (0.25MB) + binlog_cache_size (1MB) + thread_stack (0.25MB) = 2.75MB

那麼連接配接數max_connections可以設定為：384/2.75=139個 

read_buffer_size 參數：

是MySql讀入緩沖區大小，對表進行順序掃描的請求将配置設定一個讀入緩沖區，MySql會為它配置設定一段記憶體緩沖區，read_buffer_size變量控制這一緩沖區的大小，如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及記憶體緩沖區大小提高其性能。

256K适用于512MB記憶體，1GB記憶體則可以設定成1M，依次類推即可。

read_rnd_buffer_size 參數：

當以任意順序讀取行時，可以配置設定随機讀取緩沖區，通過該緩沖區讀取行，以避免磁盤尋找。read_rnd_buffer_size系統變量決定緩沖器大小。但MySql會為每個客戶連接配接發放該緩沖空間，是以應盡量适當設定該值，以避免記憶體開銷過大。

512K使用于 512MB記憶體，1GB記憶體則可以設定成1MB，依次類推即可。

sort_buffer_size 參數：

每一個要做排序的請求，都會分到一個sort_buffer_size大的緩存，用于做order by和group by的排序，如果設定的緩存大小無法滿足需要，MySQL會将資料寫入磁盤來完成排序。因為磁盤操作和記憶體操作不在一個數量級，是以sort_buffer_size對排序的性能影響很大。由于這部分緩存是即使不用這麼大，也會全部配置設定的，是以對系統記憶體配置設定開銷是比較大的，如果是希望擴大的話，建議在會話層設定，預設值2M。

512K使用于 512MB記憶體，1GB記憶體則可以設定成1MB，依次類推即可。

join_buffer_size 參數：

MySQL伺服器用來作普通索引掃描、範圍索引掃描和不使用索引而執行全表掃描這些操作所用的緩存大小。通常，擷取最快連接配接的方法是增加索引。當不能增加索引的時候，使全連接配接變快的方法是增大join_buffer_size參數。對于執行全連接配接的兩張表，每張表都被配置設定一塊連接配接記憶體。對于沒有使用索引的多表複雜連接配接，需要多塊連接配接記憶體。通常來說，可以将此參數在全局上設定一個較小的值，而在需要執行大連接配接的會話中在會話級别進行設定。預設值是256KB。

binlog_cache_size 參數：

類似于innodb_log_buffer_size緩存事務日志，binlog_cache_size緩存Binlog，不同的是這個是每個線程單獨一個，主要對于大事務有較大性能提升。預設32K。

如果設定太大的話，會比較消耗記憶體資源（Cache本質就是記憶體），更加需要注意的是：binlog_cache是不是全局的，是按SESSION為機關獨享配置設定的，也就是說當一個線程開始一個事務的時候，Mysql就會為這個SESSION配置設定一個binlog_cache。

設定太小的話，如果使用者送出一個“長事務（long_transaction）”，比如：批量導入資料。那麼該事務必然會産生很多binlog，這樣cache可能不夠用（預設binlog_cache_size是32K），不夠用的時候mysql會把uncommitted的部分寫入臨時檔案（臨時檔案cache的效率必然沒有記憶體cache高），等到committed的時候才會寫入正式的持久化日志檔案。

如何檢視設定的binlog_cache_size是否夠用，可以使用指令：show status like ‘binlog_%’;

+———————–+———–+ 

| Variable_name | Value | 

+———————–+———–+ 

| Binlog_cache_disk_use | 0 | 

| Binlog_cache_use | 120402264 | 

+———————–+———–+

運作情況Binlog_cache_use 表示binlog_cache記憶體方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache臨時檔案方式被用上了多少次。Binlog_cache_disk_use現在等于0，表示記憶體cache是夠用的，從來不需要使用到臨時檔案。

thread_stack 參數：

預設256K，MySQL為每個線程配置設定的堆棧大小，當線程堆棧太小時，這限制了伺服器可以處理的SQL語句的複雜性。這個值一般認為預設就可以應用于大部分場景了,除非必要非則不要動它。

網際網路也有類似計算MysqL占用記憶體的網站：http://www.mysqlcalculator.com/

其他參數優化建議：http://blog.daobidao.com/mysql-optimize-advise.html