MySQL優化之組態檔
文章目錄
- MySQL優化之組態檔
- 前言
- 優化配置如下
前言
- 通常默認的 my.cnf 組態檔無法發揮出 MySQL 最高的性能，所以需要根據不同的硬體進行優化，組態檔的優化也是重點，下面是物理記憶體為 32G 的資料庫優化引數，具體從全域、二進制日志、主從、innodb、myisam 幾個方面優化，僅供參考，
優化配置如下
1. default-time-zone=+8:00
#默認 mysql 使用的是系統時區，修改為北京時間，也就是所說的東八區，
2. interactive_timeout = 120
#服務器關閉互動式連接前等待活動的秒數，
3. wait_timeout = 120
#服務器關閉非互動連接之前等待活動的秒數，
4. open_files_limit = 10240
#MySQL 服務器打開檔案句柄數限制，
5. group_concat_max_len = 102400
#mysql 默認的拼接最大長度為 1024 個位元組，由于 1024 個位元組會出現不夠用的情況， 根據實際情況進行修改，
6. user=mysql
#使用 mysql 用戶運行，character-set-server=utf8 init_connect='SET NAMES utf8' #設定字符集為 utf8
7. back_log = 600
#在 MySQL 暫時停止回應新請求之前，短時間內的多少個請求可以被存在堆疊中，如果系統在短時間內有很多連接，則需要增大該引數的值，該引數值指定到來的 TCP/IP 連接的監聽佇列的大小，默認值 50，
8. max_connections = 5000
#MySQL 允許最大的行程連接數，如果經常出現 Too Many Connections 的錯誤提示， 則需要增大此值，
9. max_connect_errors = 6000
#設定每個主機的連接請求例外中斷的最大次數，當超過該次數，MySQL 服務器將禁止host 的連接請求，直到 mysql 服務器重啟或通過 flush hosts [命令](http://www.linuxyw.com/a/Linuxmingling/)清空此 host 的相關資訊，
10. table_cache = 1024
#指示表調整緩沖區大小，它設定表高速快取的數目，每個連接進來，都會至少打開一個表快取，因此，table_cache 的大小應與 max_connections 的設定有關，例如，對于 200 個并行運行的連接，應該讓表的快取至少有 200 × N ，這里 N 是應用可以執行的查詢的一個連接中表的最大數量，此外，還需要為臨時表和檔案保留一些額外的文 件描述符， 當 [Mysql](http://www.linuxyw.com/a/Mysql/) 訪問一個表時，如果該表在快取中已經被打開，則可以直接訪問快取；如果還沒有被快取，但是在 Mysql 表緩沖區中還有空間，那么這個表就被打開并放入表緩沖區；如果表快取滿了，則會按照一定的規則將當前未用的表釋放，或者臨時擴大 表 緩 存 來 存 放 ， 使 用 表 緩 存 的 好 處 是 可 以 更 快 速 地 訪 問 表 中 的 內 容 ， 執行 flush tables 會清空快取的內容，一般來說，可以通過 show status 命令查看[資料](http://www.linuxyw.com/a/shujuku/)庫運行峰值時間的狀態值 Open_tables 和 Opened_tables ， 判斷是否需要增加 table_cache 的 值 （ 其 中 open_tables 是 當 前 打 開 的 表 的 數量， Opened_tables 則是已經打開的表的數量），即如果 open_tables 接近 table_cache 的時候，并且 Opened_tables 這個值在逐步增加，那就要考慮增加這個值的大小了，還有就是 Table_locks_waited 比較高的時候，也需要增加 table_cache，
11. table_open_cache = 2048
指定表高速快取的大小，每當 MySQL 訪問一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容，
12. max_heap_table_size = 256M
這個變數定義了用戶可以創建的記憶體表(memory table)的大小.這個值用來計算記憶體表的最大行數值，這個變數支持動態改變，即 set @max_heap_table_size=#
但是對于已經存在的記憶體表就沒有什么用了，除非這個表被重新創建(create table) 或者修改(alter table)或者 truncate table，服務重啟也會設定已經存在的記憶體表為全域
max_heap_table_size 的值，
13. external-locking = false
#使用 skip-external-locking MySQL 選項以避免外部鎖定，該選項默認開啟，
14. max_allowed_packet = 32M
#設定在網路傳輸中一次訊息傳輸量的最大值，系統默認值 為 1MB，最大值是 1GB，必須設定 1024 的倍數，
15. sort_buffer_size = 512M
# Sort_Buffer_Size 是一個 connection 級引數，在每個 connection（session）第一次需要使用這個 buffer 的時候，一次性分配設定的記憶體，Sort_Buffer_Size 并不是越大越好，由于是 connection 級的引數，過大的設定+高并發可能會耗盡系統記憶體資源，
16. join_buffer_size = 8M
#用于表間關聯快取的大小，和 sort_buffer_size 一樣，該引數對應的分配記憶體也是每個連接獨享，
17. thread_cache_size = 300
# 服務器執行緒快取這個值表示可以重新利用保存在快取中執行緒的數量,當斷開連接時如果快取中還有空間,那么客戶端的執行緒將被放到快取中,如果執行緒重新被請求，那么請求將從快取中讀取,如果快取中是空的或者是新的請求，那么這個執行緒將被重新創建,如果有很多新 的 線 程 ， 增 加 這 個 值 可 以 改 善 系 統 性 能 . 通 過 比較 Connections 和 Threads_created 狀態的變數，可以看到這個變數的作用，設定規則如下：1GB 記憶體配置為 8，2GB 配置為 16，3GB 配置為 32，4GB 或更高記憶體，可配置更大，
18. thread_concurrency = 8
# 設定 thread_concurrency 的值的正確與否, 對 mysql 的性能影響很大, 在多個[cp](http://www.linuxyw.com/a/wenjianguanli/20130505/203.html)u(或多核)的情況下，錯誤設定了 thread_concurrency 的值, 會導致 mysql 不能充分利用多 cpu(或多核), 出現同一時刻只能一個 cpu(或核)在作業的情況，thread_concurrency 應設為 CPU 核數的 2 倍. 比如有一個雙核的 CPU, 那么 thread_concurrency 的應該為4; 2 個雙核的 cpu, thread_concurrency 的值應為 8，
19. query_cache_size = 512M
# 對于使用 MySQL 的用戶，對于這個變數大家一定不會陌生，前幾年的 MyISAM 引擎優化中，這個引數也是一個重要的優化引數，但隨著發展，這個引數也爆露出來一些問題，機器的記憶體越來越大，人們也都習慣性的把以前有用的引數分配的值越來越大，這個引數加大 后也引發了一系列問題，我們首先分析一下 query_cache_size 的作業原理：一個 SELECT 查詢在 DB 中作業后，DB 會把該陳述句快取下來，當同樣的一個 SQL 再次來到 DB 里呼叫時， DB 在該表沒發生變化的情況下把結果從快取中回傳給 Client，這里有一個關建點，就是 DB 在利用 Query_cache 作業時，要求該陳述句涉及的表在這段時間內沒有發生變更，那如果該表在發生變更時，Query_cache 里的資料又怎么處理呢？首先要把 Query_cache 和該表相關的陳述句全部置為失效，然后在寫入更新，那么如果 Query_cache 非常大，該表的查詢結構又比較多，查詢陳述句失效也慢，一個更新或是 Insert 就會很慢，這樣看到的就是 Update 或是Insert 怎么這么慢了，所以在資料庫寫入量或是更新量也比較大的系統，該引數不適合分配過大，而且在高并發，寫入量大的系統，建議把該功能禁掉，
20. query_cache_limit = 4M
#指定單個查詢能夠使用的緩沖區大小，預設為 1M，
21．query_cache_min_res_unit = 2k
#默認是 4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易 造 成 內 存 碎 片 和 浪 費 ， 查 詢 緩 存 碎 片率 = Qcache_free_blocks / Qcache_total_blocks * 100%，如果查詢快取碎片率超 過 20% ， 可 以 用 FLUSH QUERY CACHE 整 理 緩 存 碎 片 ， 或 者 試 試 減 小query_cache_min_res_unit ， 如 果 你 的 查 詢 都 是 小 數 據 量 的 話 ， 查 詢 緩 存 利 用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%， 查詢快取利用率在 25%以下的話說明 query_cache_size 設定的過大，可適當減小;查詢快取利用率在 80%以上而且 Qcache_lowmem_prunes > 50 的話說明 query_cache_size 可能有點 小 ， 要 不 就 是 碎 片 太 多 ， 查 詢 緩 存 命 中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100% ，
22. default-storage-engine = innodb
#默認引擎，現在一般都是 innodb 引擎表居多，
23. thread_stack = 192K
#設定MYSQL 每個執行緒的堆疊大小，默認值足夠大，可滿足普通操作，可設定范圍為 128K 至 4GB，默認為 192KB，
24. transaction_isolation = READ-COMMITTED
# 設定默認的事務隔離級別，READ COMMITTEE 是讀已提交
25. tmp_table_size = 256M
# tmp_table_size 的默認大小是 32M，如果一張臨時表超出該大小，MySQL 產生一個 The table tbl_name is full 形式的錯誤，如果執行很多高級 GROUP BY 查詢，增加 tmp_table_size 值，如果超過該值，則會將臨時表寫入磁盤，
26. key_buffer_size = 1024M
#批定用于索引的緩沖區大小，增加它可以得到更好的索引處理性能，
27. read_buffer_size = 2M
# MySql 讀入緩沖區大小，對表進行順序掃描的請求將分配一個讀入緩沖區，MySql 會為它分配一段記憶體緩沖區，read_buffer_size 變數控制這一緩沖區的大小，如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體 緩沖區大小提高其性能，和 sort_buffer_size 一樣，該引數對應的分配記憶體也是每個連接獨享，
28. read_rnd_buffer_size = 256M
# MySql 的隨機讀（查詢操作）緩沖區大小，當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀快取區，進行排序查詢時，MySql 會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量資料，可適當調高該值，但 MySql 會為每個客戶連接發放該緩沖空間，所以應盡量適當設定該值，以避免記憶體開銷過大，
29. bulk_insert_buffer_size = 64M
#批量插入資料快取大小，可以有效提高插入效率，默認為 8M，
30. skip-name-resolve
#禁止域名決議，包括主機名，所以當授權的時候使用 IP 地址，
31. ft_min_word_len = 1
#從 Mysql 4.0 開始就支持全文索引功能，但是 Mysql 默認的最小索引長度是 4，如果是英文默認值是比較合理的，但是中文絕大部分詞都是 2 個字符，這就導致小于 4 個字的詞都不能被索引，Mysql 全文索引是專門為了解決模糊查詢提供的，可以對整篇文章預先按照詞進行索引，搜索效率高，能夠支持百萬級的資料檢索，
- 下面幾個引數時關于 MySQL 二進制日志檔案的優化，
32. log-bin=mysql-bin
#打開 MySQL 二進制功能，
33. binlog_cache_size = 4M
#在事務程序中容納二進制日志 SQL 陳述句的快取大小，二進制日志快取是服務器支持事務存盤引擎并且服務器啟用了二進制日志(—log-bin 選項)的前提下為每個客戶端分配的記憶體，注意，是每個 Client 都可以分配設定大小的 binlogcache 空間，可以通過 MySQL 的以下兩個狀態變數來判斷當前的 binlog_cache_size 的狀況： Binlog_cache_use 和Binlog_cache_disk_use，
34. max_binlog_cache_size = 128M
#但是所代表的是 binlog 能夠使用的最大 cache 記憶體大小，當我們執行多陳述句事務的時候 ， max_binlog_cache_size 如 果 不 夠 大 的 話 ， 系 統 可 能 會 報 出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorag e”的錯誤，
35. max_binlog_size = 1G
#Binlog 日志最大值，一般來說設定為 512M 或者 1G，但不能超過 1G，該大小并不能非常嚴格控制Binlog 大小，尤其是當到達Binlog 比較靠近尾部而又遇到一個較大事務的時候，系統為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有 SQL 都記錄進入當前日志，直到該事務結束，這一點和 Oracle 的 Redo 日志有點不一樣，因為 Oracle 的Redo 日志所記錄的是資料檔案的物理位置的變化，而且里面同時記錄了 Redo 和 Undo 相關的資訊，所以同一個事務是否在一個日志中對 Oracle 來說并不關鍵，而 MySQL 在 Binlog 中所記錄的是資料庫邏輯變化資訊，MySQL 稱之為 Event，實際上就是帶來資料庫變化的 DML 之類的 Query 陳述句，
36. sync_binlog=1
#在 MySQL 中系統默認的設定是 sync_binlog=0，也就是不做任何強制性的磁盤重繪指令，這時候的性能是最好的，但是風險也是最大的，因為一旦系統 Crash，在 binlog_cache 中的所有 binlog 資訊都會被丟失，而當設定為“1”的時候，是最安全但是性能損耗最大的設定，因為當設定為 1 的時候，即使系統 Crash，也最多丟失 binlog_cache 中未完成的一個事務，對實際資料沒有任何實質性影響，從以往經驗和相關測驗來看，對于高并發事務的系統來說，“sync_binlog”設定為 0 和設定為 1 的系統寫入性能差距可能高達 5 倍甚至更多，
37. binlog_format=mixed
#默認使用 statement 模式，基于 SQL 陳述句的復制，另外一種是基于行的復制，為提升效率，可以將以上兩種模式的混合使用，一般的復制使用 STATEMENT 模式保存 binlog，對于 STATEMENT 模式無法復制的操作使用 ROW 模式保存 binlog，MySQL 會根據執行的 SQL 陳述句選擇日志保存方式，
38. expire_logs_days = 7
二進制日志只留存最近 7 天，不用人工手動洗掉，
39. log-slave-updates
#這條引數只讀主從架構適用，當從庫 log_slave_updates 引數沒有開啟時，從庫的binlog 不會記錄來源于主庫的操作記錄，只有開啟 log_slave_updates，從庫 binlog 才會記錄主庫同步的操作日志，
40. slow_query_log
#打開慢查詢日志
41. slow_query_log_file=slow.log
#慢查詢日志檔案位置
42. long_query_time = 2
#記錄超過 2 秒的 SQL 查詢
- 關于引擎是 innodb 的優化如下：
43. innodb_additional_mem_pool_size = 64M
#這個引數用來設定 InnoDB 存盤的資料目錄資訊和其它內部資料結構的記憶體池大小， 類似于 Oracle 的 lib[rar](http://www.linuxyw.com/a/tiaoyou/20130521/421.html)y cache，這不是一個強制引數，可以被突破，
44. innodb_buffer_pool_size = 20480M
# 這對 Innodb 表來說非常重要，Innodb 相比 MyISAM 表對緩沖更為敏感，MyISAM 可以在 默 認 的 key_buffer_size 設 置 下 運 行 的 可 以 ， 然 而 Innodb 在 默 認的 innodb_buffer_pool_size 設定下卻跟蝸牛似的，由于 Innodb 把資料和索引都快取起來， 無需留給作業系統太多的記憶體， 因此如果只需要用 Innodb 的話則可以設定它高達 70-80% 的可用記憶體，一些應用于 key_buffer 的規則有 — 如果你的資料量不大， 并且不會暴增，那么無需把 innodb_buffer_pool_size 設定的太大了，
45. innodb_data_file_path = ibdata1:1024M:autoextend
#表空間檔案 重要資料
46. innodb_file_io_threads = 4
#檔案 IO 的執行緒數，一般為 4，但是在 Windows 下，可以設定得較大，
47. innodb_thread_concurrency = 8
#服務器有幾個 CPU 就設定為幾，建議用默認設定，一般為 8，
48. innodb_write_io_threads = 8
# InnoDB 使用后臺執行緒處理資料頁上寫 I/O（輸入輸出）請求的數量，一般設定為 CPU 核數，比如 CPU 是 2 顆 8 核的，可以設定為 8，
49. innodb_read_io_threads = 8
# InnoDB 使用后臺執行緒處理資料頁上讀 I/O（輸入輸出）請求的數量，一般設定為 CPU 核數，比如 CPU 是 2 顆 8 核的，可以設定為 8，
50. innodb_flush_log_at_trx_commit = 2
# 如果將此引數設定為 1，將在每次提交事務后將日志寫入磁盤，為提供性能，可以設定為 0 或 2，但要承擔在發生故障時丟失資料的風險，設定為 0 表示事務日志寫入日志檔案，而日志檔案每秒重繪到磁盤一次，設定為 2 表示事務日志將在提交時寫入日志，但日志檔案每次重繪到磁盤一次，
51. innodb_log_buffer_size = 16M
# 此引數確定些日志檔案所用的記憶體大小，以 M 為單位，緩沖區更大能提高性能，但意外的故障將會丟失資料.MySQL 開發人員建議設定為 1－8M 之間
52. innodb_log_file_size = 256M
# 此引數確定資料日志檔案的大小，以 M 為單位，更大的設定可以提高性能，但也會增加恢復故障資料庫所需的時間
53. innodb_log_files_in_group = 3
# 為提高性能，MySQL 可以以回圈方式將日志檔案寫到多個檔案，
54．innodb_file_per_table = 1
# 獨享表空間（關閉）
55. innodb_max_dirty_pages_pct = 90
# Buffer_Pool 中 Dirty_Page 所占的數量， 直接影響 InnoDB 的關閉時間，引數innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page 在Buffer_Pool 中所占的比率， 而且幸運的是 innodb_max_dirty_pages_pct 是可以動態改變的，所以，在關閉 InnoDB 之前先將 innodb_max_dirty_pages_pct 調小， 強制資料塊 Flush 一段時間， 則能夠大大縮短 MySQL 關閉的時間，
56. innodb_lock_wait_timeout = 120
# InnoDB 有其內置的死鎖檢測機制，能導致未完成的事務回滾，但是，如果結合InnoDB 使用 MyISAM 的 lock tables 陳述句或第三方事務引擎,則 InnoDB 無法識別死鎖，為消除這種可能性，可以將 innodb_lock_wait_timeout 設定為一個整數值，指示 MySQL 在允許其他事務修改那些最終受事務回滾的資料之前要等待多長時間(秒數)，
57. innodb_open_files = 8192
#innodb 打開檔案句柄數，
- 關于引擎是 myisam 的優化如下：
58. myisam_sort_buffer_size = 128M
# MyISAM 表發生變化時重新排序所需的緩沖
59. myisam_max_sort_file_size = 10G
# MySQL 重 建 索 引 時 所 允 許 的 最 大 臨 時 文 件 的 大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE)，如果檔案大小比此值更大,索引會通過鍵值緩沖創建(更慢)
60. myisam_repair_threads = 1
# 如果一個表擁有超過一個索引, MyISAM 可以通過并行排序使用超過一個執行緒去修復，這對于擁有多個 CPU 以及大量記憶體情況的用戶,是一個很好的選擇，
61. myisam_recover
#自動檢查和修復沒有適當關閉的 MyISAM 表
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/73533.html
標籤：其他
上一篇：資料中心視頻下載