前言

日常的MySQL運(yùn)維中說(shuō)起調(diào)優(yōu)，MySQL的配置文件my.cnf是不可忽略的。MySQL的默認(rèn)參數(shù)并不能滿足我們?nèi)粘＞€上業(yè)務(wù)的需求，因此對(duì)參數(shù)進(jìn)行優(yōu)化也是不可缺少的環(huán)節(jié)。這里不想列出my.cnf配置中有多少項(xiàng)和每一項(xiàng)的意思，這些都可以在官方文檔上查到。以下僅對(duì)日常工作用應(yīng)該注意的一些參數(shù)進(jìn)行說(shuō)明。

下面針對(duì)一些參數(shù)進(jìn)行說(shuō)明。當(dāng)然還有其它的設(shè)置可以起作用，取決于你的負(fù)載或硬件：在慢內(nèi)存和快磁盤、高并發(fā)和寫密集型負(fù)載情況下，你將需要特殊的調(diào)整。然而這里的目標(biāo)是讓你可以快速地獲得一個(gè)穩(wěn)健的MySQL配置，而不用花費(fèi)太多時(shí)間在調(diào)整一些無(wú)關(guān)緊要的MySQL設(shè)置或讀文檔，找出哪些設(shè)置對(duì)你來(lái)說(shuō)是重要的。

InnoDB配置

從MySQL 5.5版本開始，InnoDB就是默認(rèn)的存儲(chǔ)引擎并且它比任何其它存儲(chǔ)引擎的使用要多得多。那也是為什么它需要小心配置的原因。

innodb_file_per_table

表的數(shù)據(jù)和索引存放在共享表空間里或者單獨(dú)表空間里。我們的工作場(chǎng)景安裝是默認(rèn)設(shè)置了innodb_file_per_table = ON，這樣也有助于工作中進(jìn)行單獨(dú)表空間的遷移工作。MySQL 5.6中，這個(gè)屬性默認(rèn)值是ON。

_flush_log_at_trx_commit

默認(rèn)值為1，表示InnoDB完全支持ACID特性。當(dāng)你的主要關(guān)注點(diǎn)是數(shù)據(jù)安全的時(shí)候這個(gè)值是最合適的，比如在一個(gè)主節(jié)點(diǎn)上。但是對(duì)于磁盤（讀寫）速度較慢的系統(tǒng)，它會(huì)帶來(lái)很巨大的開銷，因?yàn)槊看螌⒏淖僨lush到redo日志都需要額外的fsyncs。

如果將它的值設(shè)置為2會(huì)導(dǎo)致不太可靠（unreliable）。因?yàn)樘峤坏氖聞?wù)僅僅每秒才flush一次到redo日志，但對(duì)于一些場(chǎng)景是可以接受的，比如對(duì)于主節(jié)點(diǎn)的備份節(jié)點(diǎn)這個(gè)值是可以接受的。如果值為0速度就更快了，但在系統(tǒng)崩潰時(shí)可能丟失一些數(shù)據(jù)：只適用于備份節(jié)點(diǎn)。說(shuō)到這個(gè)參數(shù)就一定會(huì)想到另一個(gè)sync_binlog。

innodb_flush_method

這項(xiàng)配置決定了數(shù)據(jù)和日志寫入硬盤的方式。一共有三種方式，我們默認(rèn)使用O_DIRECT 。O_DIRECT模式：數(shù)據(jù)文件的寫入操作是直接從mysql innodb buffer到磁盤的，并不用通過(guò)操作系統(tǒng)的緩沖，而真正的完成也是在flush這步，日志還是要經(jīng)過(guò)OS緩沖。

innodb_log_buffer_size

這項(xiàng)配置決定了為尚未執(zhí)行的事務(wù)分配的緩存。其默認(rèn)值（1MB）一般來(lái)說(shuō)已經(jīng)夠用了，但是如果你的事務(wù)中包含有二進(jìn)制大對(duì)象或者大文本字段的話，這點(diǎn)緩存很快就會(huì)被填滿并觸發(fā)額外的I/O操作。看看Innodb_log_waits狀態(tài)變量，如果它不是0，增加innodb_log_buffer_size。

innodb_buffer_pool_size

這個(gè)參數(shù)應(yīng)該是運(yùn)維中必須關(guān)注的了。緩沖池是數(shù)據(jù)和索引緩存的地方，它屬于MySQL的核心參數(shù)，默認(rèn)為128MB，正常的情況下這個(gè)參數(shù)設(shè)置為物理內(nèi)存的60%~70%。（不過(guò)我們的實(shí)例基本上都是多實(shí)例混部的，所以這個(gè)值還要根據(jù)業(yè)務(wù)規(guī)模來(lái)具體分析。）

innodb_log_file_size

這是redo日志的大小。redo日志被用于確保寫操作快速而可靠并且在崩潰時(shí)恢復(fù)。如果你知道你的應(yīng)用程序需要頻繁地寫入數(shù)據(jù)并且你使用的是MySQL 5.6，那么你可以一開始就把它這是成4G。（具體大小還要根據(jù)自身業(yè)務(wù)進(jìn)行適當(dāng)調(diào)整）

innodb_support_xa

innodb_support_xa可以開關(guān)InnoDB的XA兩段式事務(wù)提交。默認(rèn)情況下，innodb_support_xa=true，支持XA兩段式事務(wù)提交。由于XA兩段式事務(wù)提交導(dǎo)致多余flush等操作，性能影響會(huì)達(dá)到10%，所有為了提高性能，有些DBA會(huì)設(shè)置innodb_support_xa=false。這樣的話，redolog和binlog將無(wú)法同步，可能存在事務(wù)在主庫(kù)提交，但是沒(méi)有記錄到binlog的情況。這樣也有可能造成事務(wù)數(shù)據(jù)的丟失。

innodb_additional_mem_pool_size

該參數(shù)用來(lái)存儲(chǔ)數(shù)據(jù)字段信息和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)。表越多，需要在這里分配的內(nèi)存越多。如果InnoDB用光了這個(gè)池內(nèi)的內(nèi)存，InnoDB開始從操作系統(tǒng)分配內(nèi)存，并且往MySQL錯(cuò)誤日志寫警告信息，默認(rèn)8MB。一般設(shè)置16MB。

max_connections

MySQL服務(wù)器默認(rèn)連接數(shù)比較小，一般也就100來(lái)個(gè)最好把最大值設(shè)大一些。一般設(shè)置500~1000即可每一個(gè)鏈接都會(huì)占用一定的內(nèi)存，所以這個(gè)參數(shù)也不是越大越好。有的人遇到too many connections會(huì)去增加這個(gè)參數(shù)的大小，但其實(shí)如果是業(yè)務(wù)量或者程序邏輯有問(wèn)題或者sql寫的不好，即使增大這個(gè)參數(shù)也無(wú)濟(jì)于事，再次報(bào)錯(cuò)只是時(shí)間問(wèn)題。在應(yīng)用程序里使用連接池或者在MySQL里使用進(jìn)程池有助于解決這一問(wèn)題。

Seesion級(jí)的內(nèi)存分配

max_threads(當(dāng)前活躍連接數(shù))* (
read_buffer_size– 順序讀緩沖，提高順序讀效率
+read_rnd_buffer_size– 隨機(jī)讀緩沖，提高隨機(jī)讀效率
+sort_buffer_size– 排序緩沖，提高排序效率
+join_buffer_size– 表連接緩沖，提高表連接效率
+binlog_cache_size– 二進(jìn)制日志緩沖，提高二進(jìn)制日志寫入效率?
+tmp_table_size– 內(nèi)存臨時(shí)表，提高臨時(shí)表存儲(chǔ)效率
+thread_stack– 線程堆棧，暫時(shí)寄存SQL語(yǔ)句/存儲(chǔ)過(guò)程
+thread_cache_size– 線程緩存，降低多次反復(fù)打開線程開銷
+net_buffer_length– 線程持連接緩沖以及讀取結(jié)果緩沖
+bulk_insert_buffer_size– MyISAM表批量寫入數(shù)據(jù)緩沖
)

global級(jí)的內(nèi)存分配

global buffer(全局內(nèi)存分配總和) =
innodb_buffer_pool_size
— InnoDB高速緩沖，行數(shù)據(jù)、索引緩沖，以及事務(wù)鎖、自適應(yīng)哈希等
+ innodb_additional_mem_pool_size
— InnoDB數(shù)據(jù)字典額外內(nèi)存，緩存所有表數(shù)據(jù)字典
+innodb_log_buffer_size
— InnoDB REDO日志緩沖，提高REDO日志寫入效率
+key_buffer_size
— MyISAM表索引高速緩沖，提高M(jìn)yISAM表索引讀寫效率
+query_cache_size
–查詢高速緩存，緩存查詢結(jié)果，提高反復(fù)查詢返回效率+table_cahce — 表空間文件描述符緩存，提高數(shù)據(jù)表打開效率
+table_definition_cache
–表定義文件描述符緩存，提高數(shù)據(jù)表打開效率

參數(shù)的優(yōu)化最終目的是讓MySQL更好地利用資源通過(guò)合理地控制內(nèi)存的分配，合理的CPU使用建議降低Session的內(nèi)存分配。

server-id

復(fù)制架構(gòu)時(shí)確保 server-id 要不同，通常主ID要小于從ID。

log_bin

如果你想讓數(shù)據(jù)庫(kù)服務(wù)器充當(dāng)主節(jié)點(diǎn)的備份節(jié)點(diǎn)，那么開啟二進(jìn)制日志是必須的。如果這么做了之后，還別忘了設(shè)置server_id為一個(gè)唯一的值。就算只有一個(gè)服務(wù)器，如果你想做基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)，這（開啟二進(jìn)制日志）也是很有用的：從你最近的備份中恢復(fù)（全量備份），并應(yīng)用二進(jìn)制日志中的修改（增量備份）。

二進(jìn)制日志一旦創(chuàng)建就將永久保存。所以如果你不想讓磁盤空間耗盡，你可以用 PURGE BINARY LOGS 來(lái)清除舊文件，或者設(shè)置expire_logs_days 來(lái)指定過(guò)多少天日志將被自動(dòng)清除。記錄二進(jìn)制日志不是沒(méi)有開銷的，所以如果你在一個(gè)非主節(jié)點(diǎn)的復(fù)制節(jié)點(diǎn)上不需要它的話，那么建議關(guān)閉這個(gè)選項(xiàng)。

skip_name_resolve

當(dāng)客戶端連接數(shù)據(jù)庫(kù)服務(wù)器時(shí)，服務(wù)器會(huì)進(jìn)行主機(jī)名解析，并且當(dāng)DNS很慢時(shí)，建立連接也會(huì)很慢。因此建議在啟動(dòng)服務(wù)器時(shí)關(guān)閉skip_name_resolve選項(xiàng)而不進(jìn)行DNS查找。唯一的局限是之后GRANT語(yǔ)句中只能使用IP地址了，因此在添加這項(xiàng)設(shè)置到一個(gè)已有系統(tǒng)中必須格外小心。

sync_binlog

sync_binlog 的默認(rèn)值是0，像操作系統(tǒng)刷其他文件的機(jī)制一樣，MySQL不會(huì)同步到磁盤中去而是依賴操作系統(tǒng)來(lái)刷新binary log。

當(dāng)sync_binlog =N (N>0) ，MySQL 在每寫N次二進(jìn)制日志binary log時(shí)，會(huì)使用fdatasync()函數(shù)將它的寫二進(jìn)制日志binary log同步到磁盤中去。當(dāng)innodb_flush_log_at_trx_commit和sync_binlog  都為 1 時(shí)是最安全的，在mysqld服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log只有可能丟失最多一個(gè)語(yǔ)句或者一個(gè)事務(wù)。但是魚與熊掌不可兼得，雙1會(huì)導(dǎo)致頻繁的IO操作，因此該模式也是最慢的一種方式。出于我們的業(yè)務(wù)考慮在業(yè)務(wù)壓力允許的情況下默認(rèn)的都是雙1配置。

log_slave_update

當(dāng)業(yè)務(wù)中需要使用級(jí)聯(lián)架構(gòu)的時(shí)候log_slave_update = 1這個(gè)參數(shù)必須打開，否者第三級(jí)可能無(wú)法接收到第一級(jí)產(chǎn)生的binlog，從而無(wú)法進(jìn)行數(shù)據(jù)同步。

tmpdir

如果內(nèi)存臨時(shí)表超出了限制，MySQL就會(huì)自動(dòng)地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲(chǔ)在指定的tmpdir目錄下.因此盡可能將tmpdir配置到性能好速度快的存儲(chǔ)設(shè)備上。

慢日志相關(guān)

slow_query_log = 1   #打開慢日志

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5  #設(shè)置超過(guò)多少秒的查詢會(huì)入慢日志

其他問(wèn)題

SSD對(duì)參數(shù)的影響

隨著科學(xué)技術(shù)的發(fā)展，越來(lái)越多的存儲(chǔ)設(shè)備開始由傳統(tǒng)的機(jī)械組件轉(zhuǎn)向由電子元件組成的永久存儲(chǔ)，且價(jià)錢越來(lái)越能讓企業(yè)接受。存儲(chǔ)組件速度提升后，再用傳統(tǒng)機(jī)械組件的DB配置就顯得浪費(fèi)了，所以就需要針對(duì)不同的存儲(chǔ)技術(shù)對(duì)MySQL配置作出調(diào)整，比如 innodb_io_capacity需要調(diào)大， 日志文件和redo放到機(jī)械硬盤， undo放到SSD， atomic write不需要Double Write Buffer， InnoDB壓縮， 單機(jī)多實(shí)例+cgroup等等。分析 I/O 情況，動(dòng)態(tài)調(diào)整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；試圖調(diào)整 innodb_adaptive_flushing，查看效果。

線程池設(shè)置

針對(duì)innodb_write_io_threads 和 innodb_read_io_threads 的調(diào)優(yōu)我們目前沒(méi)有做，但我相信調(diào)整為8或者16，系統(tǒng) I/O 性能會(huì)更好。還有，需要注意以下幾點(diǎn)：任何一個(gè)調(diào)整，都要建立在數(shù)據(jù)的支撐和嚴(yán)謹(jǐn)?shù)姆治龌A(chǔ)上，否則都是空談； 這類調(diào)優(yōu)是非常有意義的，是真正能帶來(lái)價(jià)值的，所以需要多下功夫，并且盡可能地搞明白為什么要這么調(diào)整。

CPU相關(guān)

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• table_definition_cache=2000

IO相關(guān)的

• Innodb_flush_method 建議用O_DIRECT
• Innodb_io_capacity 設(shè)置成磁盤支持最大IOPS
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• Innodb的預(yù)讀方面，如果基于主建或是唯一索引的系統(tǒng)，建議禁用預(yù)讀
• Innodb_random_read_ahead = off

以上就是MySQL優(yōu)化參數(shù)參考！的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論