MySQL優化參數參考！
- PHPz原創
- 2024-07-01 07:51:191068瀏覽
日常的MySQL運維中說起調優，MySQL的設定檔my.cnf是不可忽視的。 MySQL的預設參數並不能滿足我們日常線上業務的需求，因此對參數進行最佳化也是不可或缺的環節。這裡不想列出my.cnf配置中有多少項和每一項的意思，這些都可以在官方文件上查到。以下僅對日常工作用應該注意的一些參數進行說明。
下面針對一些參數進行說明。當然還有其它的設定可以起作用，取決於你的負載或硬體：在慢記憶體和快磁碟、高並發和寫密集型負載情況下，你將需要特殊的調整。然而這裡的目標是讓你可以快速地獲得一個穩健的MySQL配置，而不用花太多時間在調整一些無關緊要的MySQL設定或讀取文檔，找出哪些設定對你來說是重要的。
從MySQL 5.5版本開始，InnoDB就是預設的儲存引擎並且它比任何其它儲存引擎的使用要多得多。那也是為什麼它需要小心配置的原因。
表的資料和索引存放在共享表空間或單獨表空間。我們的工作場景安裝是預設設定了innodb_file_per_table = ON，這也有助於工作中進行單獨表空間的遷移工作。 MySQL 5.6中，這個屬性預設值是ON。
預設值為1，表示InnoDB完全支援ACID特性。當你的主要關注點是資料安全的時候這個值是最合適的，例如在一個主節點上。但對於磁碟（讀寫）速度較慢的系統，它會帶來很大的開銷，因為每次將改變flush到redo日誌都需要額外的fsyncs。
如果將它的值設為2會導致不太可靠（unreliable）。因為提交的交易僅僅每秒才flush一次到redo日誌，但對於某些場景是可以接受的，例如對於主節點的備份節點這個值是可以接受的。如果值為0速度就更快了，但在系統崩潰時可能會遺失一些資料：只適用於備份節點。說到這個參數一定會想到另一個sync_binlog。
這項配置決定了資料和日誌寫入硬碟的方式。共有三種方式，我們預設使用O_DIRECT 。 O_DIRECT模式：資料檔案的寫入操作是直接從mysql innodb buffer到磁碟的，並沒有通過作業系統的緩衝，而真正的完成也是在flush這步，日誌還是要經過OS緩衝。
這項配置決定了尚未執行的交易分配的快取。其預設值（1MB）一般來說已經夠用了，但是如果你的事務中包含有二進制大物件或大文字欄位的話，這點快取很快就會被填滿並觸發額外的I/O操作。看看Innodb_log_waits狀態變量，如果它不是0，增加innodb_log_buffer_size。
這個參數應該是運維中必須注意的了。緩衝池是資料和索引快取的地方，它屬於MySQL的核心參數，預設為128MB，正常的情況下這個參數設定為物理記憶體的60%~70%。 （不過我們的實例基本上都是多實例混部的，所以這個值還要根據業務規模來具體分析。）
這是redo日誌的大小。 redo日誌被用來確保寫入操作快速且可靠並且在崩潰時恢復。如果你知道你的應用程式需要頻繁地寫入資料並且你使用的是MySQL 5.6，那麼你可以一開始就把它這是成4G。 （具體大小也要根據自身業務進行適當調整）
innodb_support_xa可以開關InnoDB的XA兩段式交易提交。預設情況下，innodb_support_xa=true，支援XA兩段式事務提交。由於XA兩段式事務提交導致多餘flush等操作，效能影響會達到10%，所有為了提高效能，有些DBA會設定innodb_support_xa=false。這樣的話，redolog和binlog將無法同步，可能存在事務在主庫提交，但是沒有記錄到binlog的情況。這樣也有可能造成交易資料的遺失。
此參數用來儲存資料欄位資訊和其他內部資料結構。表越多，需要在這裡分配的記憶體越多。如果InnoDB用光了這個池內的內存，InnoDB開始從作業系統分配內存，並且往MySQL錯誤日誌寫警告訊息，預設8MB。一般設定16MB。
MySQL伺服器預設連線數比較小，一般也就100來個最好把最大值設大一些。一般設定500~1000即可每一個連結都會佔用一定的內存，所以這個參數也不是越大越好。有的人遇到too many connections會去增加這個參數的大小，但其實如果是業務量或程式邏輯有問題或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 –表定义文件描述符缓存，提高数据表打开效率
參數的最佳化最終目的是讓MySQL更好地利用資源透過合理地控制記憶體的分配，合理的CPU使用建議降低Session的記憶體分配。
複製架構時確保 server-id 要不同，通常主ID要小於從ID。
如果你想讓資料庫伺服器充當主節點的備份節點，那麼開啟二進位日誌是必須的。如果這麼做了之後，別忘了設定server_id為一個唯一的值。就算只有一個伺服器，如果你想做基於時間點的資料恢復，這（開啟二進位日誌）也是很有用的：從你最近的備份中恢復（全量備份），並應用二進位日誌中的修改（增量備份）。
二進位日誌一旦建立就將永久保存。所以如果你不想讓磁碟空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件，或者設定expire_logs_days 來指定過多少天日誌將被自動清除。記錄二進位日誌不是沒有開銷的，所以如果你在一個非主節點的複製節點上不需要它的話，那麼建議關閉這個選項。
當客戶端連接資料庫伺服器時，伺服器會進行主機名稱解析，且當DNS很慢時，建立連線也會很慢。因此建議在啟動伺服器時關閉skip_name_resolve選項而不進行DNS查找。唯一的限制是之後GRANT語句中只能使用IP位址了，因此在新增這項設定到一個已有系統中必須格外小心。
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操作，因此此模式也是最慢的一種方式。出於我們的業務考慮在業務壓力允許的情況下預設的都是雙1配置。
當業務中需要使用級聯架構的時候log_slave_update = 1這個參數必須打開，否者第三級可能無法接收到第一級產生的binlog，從而無法進行資料同步。
如果內存臨時表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表，存儲在指定的tmpdir目錄下.因此盡可能將tmpdir配置到性能好速度快的存儲設備上。
slow_query_log = 1 #開啟慢日誌
slow_query_log_file = /mysql/log/mysql.slow
long_query_time = 0.5 #設定超過多少秒的查詢會入慢日誌
隨著科學技術的發展，越來越多的儲存設備開始由傳統的機械組件轉向由電子元件組成的永久存儲，且價格越來越能讓企業接受。儲存組件速度提升後，再用傳統機械組件的DB配置就顯得浪費了，所以就需要針對不同的儲存技術對MySQL配置作出調整，例如innodb_io_capacity需要調大， 日誌檔案和redo放到機械硬碟， undo放到SSD， atomic write不需要Double Write Buffer， InnoDB壓縮， 單機多實例+cgroup等等。分析 I/O 情況，動態調整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；試圖調整 innodb_adaptive_flushing，查看效果。
針對innodb_write_io_threads 和 innodb_read_io_threads 的調優我們目前沒有做，但我相信調整為8或16，系統 I/O 效能會更好。還有，需要注意以下幾點：任何一個調整，都要建立在數據的支撐和嚴謹的分析基礎上，否則都是空談； 這類調優是非常有意義的，是真正能帶來價值的，所以需要多下功夫，並且盡可能地搞清楚為什麼要這麼調整。
- Innodb_thread_concurrency=0
- Innodb_sync_spin_loops=288
- table_definition_cache=2000
- 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優化參數參考！的詳細內容。更多資訊請關注PHP中文網其他相關文章！