mysql 面試（九）整理之mysql 參數關注與優化

2019-11-08 20:58:36

字體：大中小

來源：轉載

供稿：網友

9.1innodb讀寫參數

（1）讀取參數，globalbuffer pool以及localbuffer

Globalbuffer：

Innodb_buffer_pool_size

innodb_log_buffer_size

innodb_additional_mem_pool_size

localbuffer(下面的都是server層的session變量，不是innodb的)：

Read_buffer_size

Join_buffer_size

Sort_buffer_size

Key_buffer_size

Binlog_cache_size

（2）寫入參數

insert_buffer_size innodb_buffer_pool_size

如果用Innodb，那么這是一個重要變量。相對于MyISAM來說，Innodb對于buffer size更敏感。MySIAM可能對于大數據量使用默認的key_buffer_size也還好，但Innodb在大數據量時用默認值就感覺在爬了。 Innodb的緩沖池會緩存數據和索引，所以不需要給系統的緩存留空間，如果只用Innodb，可以把這個值設為內存的70%-80%。和 key_buffer相同，如果數據量比較小也不怎么增加，那么不要把這個值設太高也可以提高內存的使用率。

innodb_additional_pool_size 這個的效果不是很明顯，至少是當操作系統能合理分配內存時。但你可能仍需要設成20M或更多一點以看Innodb會分配多少內存做其他用途。

innodb_log_file_size對于寫很多尤其是大數據量時非常重要。要注意，大的文件提供更高的性能，但數據庫恢復時會用更多的時間。我一般用64M-512M，具體取決于服務器的空間。

innodb_log_buffer_size 默認值對于多數中等寫操作和事務短的運用都是可以的。如果經常做更新或者使用了很多blob數據，應該增大這個值。但太大了也是浪費內存，因為1秒鐘總會 flush（這個詞的中文怎么說呢？）一次，所以不需要設到超過1秒的需求。8M-16M一般應該夠了。小的運用可以設更小一點。

innodb_flush_log_at_trx_commit （這個很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電池供電緩存（Battery backed up cache）時。設成2對于很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬盤而是寫入系統緩存。日志仍然會每秒flush到硬盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統掛了時才可能丟數據。

9.2 mysql有哪些global內存參數，有哪些local內存參數。

Global:

innodb_buffer_pool_size/innodb_additional_mem_pool_size/innodb_log_buffer_size/key_buffer_size/query_cache_size/table_open_cache/table_definition_cache/thread_cache_size

Local:

read_buffer_size/read_rnd_buffer_size/sort_buffer_size/join_buffer_size/binlog_cache_size/tmp_table_size/thread_stack/bulk_insert_buffer_size

sync_binlog設置為1，保證binlog的安全性。

innodb_flush_log_at_trx_commit：

0：事務提交時不將redo log buffer寫入磁盤(僅每秒進行master thread刷新，安全性最差，性能最好)

1：事務提交時將redo log buffer寫入磁盤(安全性最好，性能最差，推薦生產使用)

2：事務提交時僅將redo log buffer寫入操作系統緩存(安全性和性能都居中，當mysql宕機但是操作系統不宕機則不丟數據，如果操作系統宕機，最多丟一秒數據)

innodb_io_capacity/innodb_io_capacity_max：看磁盤的性能來定。如果是HDD可以設置為200-幾百不等。如果是SSD，推薦為4000左右。innodb_io_capacity_max更大一些。

innodb_flush_method設置為O_DIRECT。

（3）讀取的話，那幾個全局的pool的值的設置，以及幾個local的buffer的設置。

Global:

innodb_buffer_pool_size:設置為可用內存的50%-60%左右，如果不夠，再慢慢上調。

innodb_additional_mem_pool_size：采用默認值8M即可。

innodb_log_buffer_size:默認值8M即可。

key_buffer_size:myisam表需要的buffer size，選擇基本都用innodb，所以采用默認的8M即可。

9.3mysql 參數優化

（1）Mysql層面：

1. innodb_flush_log_at_trx_commit 設置為2

設置0是事務log（ib_logfile0、ib_logfile1）每秒寫入到logbuffer,1是時時寫，2是先寫文件系統的緩存，每秒再刷進磁盤，和0的區別是選2即使mysql崩潰也不會丟數據。

2. innodb_write_io_threads=16 (該參數需要在配置文件中添加，重啟mysql實例起效)

臟頁寫的線程數，加大該參數可以提升寫入性能.mysql5.5以上才有。

3. innodb_max_dirty_pages_pct 最大臟頁百分數

當系統中臟頁所占百分比超過這個值，INNODB就會進行寫操作以把頁中的已更新數據寫入到磁盤文件中。默認75，一般現在流行的SSD硬盤很難達到這個比例。可依據實際情況在75-80之間調節

4. innodb_io_capacity=5000

從緩沖區刷新臟頁時，一次刷新臟頁的數量。根據磁盤IOPS的能力一般建議設置如下：

SAS 200

SSD 5000

PCI-E 10000-50000

5. innodb_flush_method=O_DIRECT(該參數需要重啟mysql實例起效)

控制innodb數據文件和redo log的打開、刷寫模式。有三個值：fdatasync(默認)，O_DSYNC，O_DIRECT。

6. innodb_adaptive_flushing 設置為 ON （使刷新臟頁更智能）

影響每秒刷新臟頁的數目。規則由原來的“大于innodb_max_dirty_pages_pct時刷新100個臟頁到磁盤”變為 “通過buf_flush_get_desired_flush_reate函數判斷重做日志產生速度確定需要刷新臟頁的最合適數目”；即使臟頁比例小于innodb_max_dirty_pages_pct時也會刷新一定量的臟頁。

7. innodb_adaptive_flushing_method 設置為 keep_average

影響checkpoint，更平均的計算調整刷臟頁的速度，進行必要的flush.（該變量為mysql衍生版本PerconaServer下的一個變量，原生mysql不存在）

8. innodb_stats_on_metadata=OFF

關掉一些訪問information_schema庫下表而產生的索引統計。

當重啟mysql實例后，mysql會隨機的io取數據遍歷所有的表來取樣來統計數據，這個實際使用中用的不多，建議關閉.

9. innodb_change_buffering=all

當更新/插入的非聚集索引的數據所對應的頁不在內存中時（對非聚集索引的更新操作通常會帶來隨機IO），會將其放到一個insertbuffer中，當隨后頁面被讀到內存中時，會將這些變化的記錄merge到頁中。當服務器比較空閑時，后臺線程也會做merge操作。

由于主要用到merge的優勢來降低io，但對于一些場景并不會對固定的數據進行多次修改，此處則并不需要把更新/插入操作開啟change_buffering，如果開啟只是多余占用了buffer_pool的空間和處理能力。這個參數要依據實際業務環境來配置。

10. innodb_old_blocks_time=1000

使Block在old sublist中停留時間長為1s，不會被轉移到new sublist中,避免了BufferPool被污染BP可以被認為是一條長鏈表。被分成young和 old兩個部分，其中old默認占37%的大小（由innodb_old_blocks_pct配置）。靠近頂端的Page表示最近被訪問。靠近尾端的Page表示長時間未被訪問。而這兩個部分的交匯處成為midpoint。每當有新的Page需要加載到BP時，該page都會被插入到midpoint的位置，并聲明為old-page。當old部分的page，被訪問到時，該page會被提升到鏈表的頂端，標識為young。

由于table scan的操作是先load page，然后立即觸發一次訪問。所以當innodb_old_blocks_time=0 時，會導致tablescan所需要的page不讀的作為youngpage被添加到鏈表頂端。而一些使用較為不頻繁的page就會被擠出BP，使得之后的SQL會產生磁盤IO，從而導致響應速度變慢。

這時雖然mysqldump訪問的page會不斷加載在LRU頂端，但是高頻度的熱點數據訪問會以更快的速度把page再次搶占到LRU頂端。從而導致mysqldump加載入的page會被迅速刷下，并立即被evict（淘汰）。因此，time=0或1000對這種壓力環境下的訪問不會造成很大影響，因為dump的數據根本搶占不過熱點數據。不只dump，當大數據操作的時候也是如此。

（2）系統層面：

1. 關閉 numa=off, 或修改策略為interleave(交織分配內存)防止意外的swap

numa策略引入了node的概念，每個物理CPU都被視為一個node，而每個node都有一個localmemory，相對這個node之外的其它node都屬于外部訪問。

NUMA的內存分配策略有localalloc（默認）、PReferred、membind、interleave。

localalloc規定進程從當前node上請求分配內存；

preferred比較寬松地指定了一個推薦的node來獲取內存，如果被推薦的node上沒有足夠內存，進程可以嘗試別的node。

membind可以指定若干個node，進程只能從這些指定的node上請求分配內存。

interleave規定進程從指定的若干個node上以Round-roll算法交織地請求分配內存。

每個進程（或線程）都會分配一個優先node,對于系統默認的localalloc策略會有一個問題，對于mysql這種幾乎占滿整個系統內存的應用來說，很容就把某個node的資源給占滿，若linux又把一個大的資源分配到這個已經占滿資源的node時，會資源不足，造成內存數據于磁盤進行交換，或者摒棄buffer_pool里的活躍數據。在實際測試中發現比如有node0、node1 兩個物理node，當系統負載很高的時候，node0資源被占滿，node1雖然仍有部分空閑內存，但是系統即使進行內存到磁盤交換也不會去利用node1上的空閑資源。

因此建議對于像mysql這樣的單實例的龐大復雜的進程來說，關閉numa或者設置策略為交織分配內存更合理。但對于一個機器上有多少個實例，可以每個實例綁定一個CPU核上。然后就可以充分利用numa的特性，更高效。

2. 增加本地端口，以應對大量連接

echo ‘1024 65000′ >/proc/sys/net/ipv4/ip_local_port_range

該參數指定端口的分配范圍，該端口是向外訪問的限制。mysql默認監聽的3306端口即使有多個請求鏈接，也不會有影響。但是由于mysql是屬于高內存、高cpu、高io應用，不建議把多少應用于mysql混搭在同一臺機器上。即使業務量不大，也可以通過降低單臺機器的配置，多臺機器共存來實現更好。

3. 增加隊列的鏈接數

echo ‘1048576’ >/proc/sys/net/ipv4/tcp_max_syn_backlog

建立鏈接的隊列的數越大越好，但是從另一個角度想，實際環境中應該使用連接池更合適，避免重復建立鏈接造成的性能消耗。使用連接池，鏈接數會從應用層面更可控些。

4. 設置鏈接超時時間

echo ’10’ >/proc/sys/net/ipv4/tcp_fin_timeout

該參數主要為了降低TIME_WAIT占用的資源時長。尤其針對http短鏈接的服務端或者mysql不采用連接池效果比較明顯。

Local:

join_buffer_size：當sql有BNL和BKA的時候，需要用的buffer_size(plain index scans,range index scans的時候可能也會用到)。默認為256k，建議設置為16M-32M。

read_rnd_buffer_size：當使用mrr時，用到的buffer。默認為256k，建議設置為16-32M。

read_buffer_size:當順序掃描一個myisam表，需要用到這個buffer。或者用來決定memory table的大小。或者所有的engine類型做如下操作：order by的時候用temporary file、SELECT INTO …OUTFILE 'filename' 、For caching results ofnested queries。默認為128K，建議為16M。

sort_buffer_size：sql語句用來進行sort操作(order by,group by)的buffer。如果buffer不夠，則需要建立temporary file。如果在show global status中發現有大量的Sort_merge_passes值，則需要考慮調大sort_buffer_size。默認為256k，建議設置為16-32M。

binlog_cache_size：表示每個session中存放transaction的binlog的cachesize。默認32K。一般使用默認值即可。如果有大事務，可以考慮調大。

thread_stack：每個進程都需要有，默認為256K，使用默認值即可。

（4）還有就是著名的query cache了，以及query cache的適用場景了，這里有一個陷阱，就是高并發的情況下，比如雙十一的時候，query cache開還是不開，開了怎么保證高并發，不開又有何別的考慮？

建議關閉，上了性能反而更差。

innodb_double_write

innodb_write_io_thread

innodb_flush_method

（3）與IO相關的參數

Sync_binlog

Innodb_flush_log_at_trx_commit

Innodb_lru_scan_depth

Innodb_io_capacity