前言

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

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

InnoDB配置

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

innodb_file_per_table

表的數據和索引存放在共享表空間里或者單獨表空間里。我們的工作場景安裝是默認設置了innodb_file_per_table = ON，這樣也有助于工作中進行單獨表空間的遷移工作。MySQL 5.6中，這個屬性默認值是ON。

_flush_log_at_trx_commit

默認值為1，表示InnoDB完全支持ACID特性。當你的主要關注點是數據安全的時候這個值是最合適的，比如在一個主節(jié)點上。但是對于磁盤（讀寫）速度較慢的系統，它會帶來很巨大的開銷，因為每次將改變flush到redo日志都需要額外的fsyncs。

如果將它的值設置為2會導致不太可靠（unreliable）。因為提交的事務僅僅每秒才flush一次到redo日志，但對于一些場景是可以接受的，比如對于主節(jié)點的備份節(jié)點這個值是可以接受的。如果值為0速度就更快了，但在系統崩潰時可能丟失一些數據：只適用于備份節(jié)點。說到這個參數就一定會想到另一個sync_binlog。

innodb_flush_method

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

innodb_log_buffer_size

這項配置決定了為尚未執(zhí)行的事務分配的緩存。其默認值（1MB）一般來說已經夠用了，但是如果你的事務中包含有二進制大對象或者大文本字段的話，這點緩存很快就會被填滿并觸發(fā)額外的I/O操作?？纯碔nnodb_log_waits狀態(tài)變量，如果它不是0，增加innodb_log_buffer_size。

innodb_buffer_pool_size

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

innodb_log_file_size

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

innodb_support_xa

innodb_support_xa可以開關InnoDB的XA兩段式事務提交。默認情況下，innodb_support_xa=true，支持XA兩段式事務提交。由于XA兩段式事務提交導致多余flush等操作，性能影響會達到10%，所有為了提高性能，有些DBA會設置innodb_support_xa=false。這樣的話，redolog和binlog將無法同步，可能存在事務在主庫提交，但是沒有記錄到binlog的情況。這樣也有可能造成事務數據的丟失。

innodb_additional_mem_pool_size

該參數用來存儲數據字段信息和其他內部數據結構。表越多，需要在這里分配的內存越多。如果InnoDB用光了這個池內的內存，InnoDB開始從操作系統分配內存，并且往MySQL錯誤日志寫警告信息，默認8MB。一般設置16MB。

max_connections

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

Seesion級的內存分配

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

global級的內存分配

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

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

server-id

復制架構時確保 server-id 要不同，通常主ID要小于從ID。

log_bin

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

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

skip_name_resolve

當客戶端連接數據庫服務器時，服務器會進行主機名解析，并且當DNS很慢時，建立連接也會很慢。因此建議在啟動服務器時關閉skip_name_resolve選項而不進行DNS查找。唯一的局限是之后GRANT語句中只能使用IP地址了，因此在添加這項設置到一個已有系統中必須格外小心。

sync_binlog

sync_binlog 的默認值是0，像操作系統刷其他文件的機制一樣，MySQL不會同步到磁盤中去而是依賴操作系統來刷新binary log。

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

log_slave_update

當業(yè)務中需要使用級聯架構的時候log_slave_update = 1這個參數必須打開，否者第三級可能無法接收到第一級產生的binlog，從而無法進行數據同步。

tmpdir

如果內存臨時表超出了限制，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下.因此盡可能將tmpdir配置到性能好速度快的存儲設備上。

慢日志相關

slow_query_log = 1   #打開慢日志

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5  #設置超過多少秒的查詢會入慢日志

其他問題

SSD對參數的影響

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

線程池設置

針對innodb_write_io_threads 和 innodb_read_io_threads 的調優(yōu)我們目前沒有做，但我相信調整為8或者16，系統 I/O 性能會更好。還有，需要注意以下幾點：任何一個調整，都要建立在數據的支撐和嚴謹的分析基礎上，否則都是空談； 這類調優(yōu)是非常有意義的，是真正能帶來價值的，所以需要多下功夫，并且盡可能地搞明白為什么要這么調整。

CPU相關

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

IO相關的

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

以上就是MySQL優(yōu)化參數參考！的詳細內容，更多請關注腳本之家其它相關文章！