簡介：
Mysql 參數優化
一、Mysql 源碼編譯參數
shell > yum -y install gcc gcc-c++ make cmake ncurses-devel zlib-devel bison shell > cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DTMPDIR=/usr/local/mysql/data \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \ -DSYSCONFDIR=/etc/my.cnf \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DDEFAULT_CHARSET=utf8 \ -DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci
## -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 指定 Mysql 安裝路徑
## -DMYSQL_DATADIR=/usr/local/mysql/data 指定 Mysql 數據目錄
## -DTMPDIR=/usr/local/mysql/data 指定 Mysql 臨時目錄
## -DMYSQL_UNIX_ADDR 指定 Mysql socket
## -DSYSCONFDIR=/etc/my.cnf 指定 Mysql 配置文件
## -DWITH_MYISAM_STORAGE_ENGINE=1 安裝 MyISAM 存儲引擎
## -DWITH_INNOBASE_STORAGE_ENGINE=1 安裝 Innodb 存儲引擎
## -DWITH_FEDERATED_STORAGE_ENGINE=1 安裝 Federated 存儲引擎
## -DDEFAULT_CHARSET=utf8 指定默認字符集為 utf8
## -DEXTRA_CHARSETS=all 安裝擴展字符集
## -DDEFAULT_COLLATION=utf8_general_ci 默認排序規則 utf8_general_ci
Cmake 參數：http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html
---------------------------------------------------------------------------------------------
二、my.cnf 配置文件
## global_buffers 在內存中緩存從數據文件中檢索出來的數據塊，可以大大提高查詢和更新數據的性能 ## 計算公式：Innodb_buffer_pool_size + Innodb_additional_mem_pool_size + Innodb_log_buffer_size + key_buffer_size + query_cache_size ## per_thread_buffers 線程獨享內存大小 ## 計算公式（ read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size ）* max_connections ## 注意：global_buffers + per_thread_buffers 不能大於實際物理內存，否者並發量大時會造成內存溢出、系統死機 ！ [client] port = 3306 socket = /usr/local/mysql/data/mysql.sock ## 定義客戶端連接信息，端口號、socket 存放位置 [mysqld] port = 3306 socket = /usr/local/mysql/data/mysql.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data temdir = /usr/local/mysql/data ## Mysql 基本信息，端口號、socket、安裝目錄、數據存放目錄、臨時目錄 # skip-name-resolve ## 禁止 Mysql 對外部連接進行 DNS 解析，加快連接速度。開啟後所有遠程連接主機只能使用 IP 的方式 skip_external_locking ## 避免 Mysql 外部鎖定，減少出錯幾率、增強穩定性 local-infile = 0 ## 禁止 SQL 讀取本地文件 character-set-server = utf8 ## 默認字符集 utf8 default-storage-engine = innodb ## 默認存儲引擎 # general_log = on ## 開啟查詢日志，一般選擇不開啟，因為查詢日志記錄很詳細，會增大磁盤 IO 開銷，影響性能 # general_log_file = /usr/local/mysql/data/mysql.log ## 查詢日志存放位置及文件名 log-error = /usr/local/mysql/data/error.log ## 錯誤日志位置跟文件名 # slow_query_log = on ## 開啟慢查詢日志，開啟後將會記錄執行時間超過 long_query_time 參數值的 SQL 語句（ 一般臨時開啟即可 ） # long_query_time = 2 ## 定義執行時間超過多少秒為慢查詢，默認 10s # slow_query_log_file = /usr/local/mysql/data/slow.log ## 定義慢查詢日志存放位置 # server-id = 1 ## Mysql Server 唯一標識，用來做主同同步（ 主從時開啟 ） log-bin = mysql-bin ## 開啟 binlog ( 二進制 ) 日志，主要用來做增量備份跟主從同步 binlog_format = mixed ## Mysql binlog 的日志格式，Statement、ROW 跟 Mixed（ 混合模式 ） binlog_cache_size = 2M ## 二進制日志緩沖大小，此參數是為每 Session 單獨分配的，當一個線程開始一個事務時，Mysql 就會為此 Session 分配一個 binlog cache，當這個事務提交時，binlog cache 中的數據被寫入 binlog 文件 ## 通過 show status like 'binlog_cache%'; 來查看使用 binlog cache 的次數及使用磁盤的次數 sync_binlog = 0 ## 這個參數對 Mysql 系統來說很重要，不僅影響到 binlog 對 Mysql 所帶來的性能損耗，還影響到 Mysql 中數據的完整性。 ## 值為 0 時代表事務提交後，Mysql 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什麼時候同步，或者 cache 滿了之後才同步磁盤。 ## 值為 n 時代表進行 n 次事務提交後，Mysql 將進行一次 fsync 之類的磁盤同步指令來將 binlog_cache 中的數據強制寫入磁盤。 ## 系統默認將此參數設置為 0 ，即不做任何強制性的磁盤刷新指令，性能最好，但是風險也最大。當系統崩潰時 binlog_cache 中的所有 binlog 信息都會丟失。 ## 而設置為 1 時，是最安全但是性能損耗最大。當系統崩潰時，最多丟失 binlog_cache 中未完成的一個事務，對實際數據沒有實質性的影響。 expire_logs_days = 30 ## 保留 30 天的 binlog 日志，系統重啟、執行 flush logs 或 binlog 日志文件大小達到上限時刪除 binlog 日志 back_log = 500 ## Mysql 連接請求隊列存放數，當某一時刻客戶端連接請求過多，造成 Mysql Server 無法為其建立連接時存放的數量，最大 65535（ 增大時需同時調整 OS 級別的網絡監聽隊列限制 ） max_connections = 1000 ## Mysql 最大連接數，直接影響 Mysql 應用的並發處理能力（ 500~1000 是個比較合適的值，注意每建立的連接都會占用一定的內存空間，直到連接被關閉才釋放內存 ） wait_timeout = 100 interactive_timeout = 100 ## 服務器關閉非交換連接之前等待活動的秒數，默認 28800 秒（ 注意：在 my.cnf 中修改這個參數需要配合 interactive_timeout (超時間隔)參數一起修改，否則不生效） ##（ Mysql 處理完一條連接後所等待釋放的時間，如果並發很高，默認設置會導致最大連接被占滿，出現 "too many connections" 錯誤 ） ##（ 如果這個值很低，比如 5 ，可能會導致出現 "ERROR 2006 (HY000) MySQL server has gone away" 的錯誤，出現這個錯誤還有可能是 max_allowed_packet 設置過小 ） ## 真實案例 max_connect_errors = 1000 ## 最大連接失敗次數，跟性能沒有太大關系，主要跟安全方面有關（ 達到此上限後會無條件阻止其連接數據庫 ，默認 100 ） connect_timeout = 20 ## 連接超時時間為 20 秒 max_allowed_packet = 16M ## 網絡傳輸中一次消息量的最大值，默認 4M ，必須設為 1024 的整倍數 table_open_cache = 2000 ## 打開文件描述符的緩存個數，防止系統頻繁打開、關閉描述符而浪費資源（ 對性能有影響，默認 2000 ） read_buffer_size = 512K ## Mysql 讀入緩沖區大小，對表進行順序掃描的請求將分配一個讀入緩沖區，Mysql 會為其分配一段內存緩沖區（ 默認 128K ，此參數為每線程分配 ） read_rnd_buffer_size = 512K ## Mysql 隨機 Query 緩沖區大小，當按任意順序讀取行時，將分配一個隨機讀取緩沖區。如進行排序查詢時，Mysql 會首先掃描該緩沖，避免磁盤搜索，提高查詢速度（ 默認 256K ，該緩沖也是為每線程分配 ） sort_buffer_size = 512K ## 系統中對數據進行排序時使用的 buffer ，如果系統中排序比較大，且內存充足、並發不大時，可以適當增大此值（ 默認 256K ，此參數為每線程分配獨立的 buffer ） join_buffer_size = 512K ## join 為 ALL、index、rang 或 index_merge 時使用的 buffer（ 默認 256K ，每 Thread 都會建立自己獨立的 buffer ） thread_stack = 256K ## 參數表示每線程的堆棧大小 thread_cache_size = 64 ## Thread Cache 池中存放的連接線程數（ 此池中的線程不是啟動服務時就創建的，而是隨著連接線程的創建和使用，逐漸將用完的線程存入其中，達到此值後將不再緩存連接線程 ） ## 緩存命中率計算公式：Thread_Cache_Hit = ( Connections - Thread_created ) / Connections * 100% ## 系統運行一段時間後，Thread Cache 命中率應該保持在 90% 以上 explicit_defaults_for_timestamp = 1 ## 如果此參數不開啟，error_log 中會有警告信息 query_cache_type = 1 ## 是否啟用 query_cache ，0 為不使用（ 若要關閉 query_cache 時，需同時將 query_cache_size 、query_cache_limit 設為 0 ） query_cache_size = 32M ## 查詢緩沖大小，當重復查詢時會直接從該緩沖中獲取，但是當所查詢表有改變時，緩沖的查詢將失效（ 頻繁寫入、更新、高並發的環境下建議關閉此緩沖 ） query_cache_limit = 1M ## 單個查詢所能夠使用的緩沖區大小 ft_min_word_len = 1 ## 使用全文索引最小長度 transaction_isolation = REPEATABLE-READ ## 事務隔離級別，為了有效保證並發讀取數據的正確性（ 默認 Repeatables Read 即：可重復讀 ） ## Innodb 有四種隔離級別：Read Uncommitted（ 未提交讀 ）、Read Committed（ 已提交讀 ）、Repeatable Read（ 可重復讀 ）、Serializable（ 可序列化 ） tmp_table_size = 32M ## 臨時表大小 key_buffer_size = 32M ## 用來緩存 MyISAM 存儲引擎的索引（ 默認 8M ，如果使用 Innodb 存儲引擎，此值設為 64M 或更小 ） ## 計算公式：key_reads / key_read_requests * 100% 的值小於 0.1% ## Innodb 存儲引擎相關參數 innodb_file_per_table = 0 ## 關閉獨享表空間，使用共享表空間 innodb_buffer_pool_size = 256M ## Innodb 存儲引擎核心參數，用於緩存 Innodb 表的索引、數據（ 默認 128M ，單獨使用 Innodb 存儲引擎且單一 Mysql 服務時建議設為物理內存的 70% - 80 % ） ## 可以通過 show status like 'innodb_buffer_pool_%'; 來獲取 innodb buffer pool 的實時狀態信息 ## Innodb_buffer_pool_pages_total 總共的 pages（ Innodb 存儲引擎中所有數據存放最小物理單位 page ，每個 page 默認為 16KB ） ## Innodb_buffer_pool_pages_free 空閒的 pages ## Innodb_buffer_pool_pages_data 有數據的 pages ## Innodb_buffer_pool_read_requests 總共的 read 請求次數 ## Innodb_buffer_pool_reads 讀取物理磁盤讀取數據的次數，即：在 buffer pool 中沒有找到 ## Innodb_buffer_pool_wait_free 因 buffer 空間不足而產生的 wait_free ## Innodb_buffer_pool_read_ahead_rnd 記錄進行隨機讀的時候產生的預讀次數 ## Innodb_buffer_pool_read_ahead_seq 記錄連續讀的時候產生的預讀次數 ## Innodb_buffer_pool_size 使用率 = innodb_buffer_pool_pages_data / innodb_buffer_pool_pages_total * 100% ## Innodb_buffer_pool_read 命中率 = （ innodb_buffer_pool_read_requests - innodb_buffer_pool_reads ）/ innodb_buffer_pool_read_requests * 100% innodb_use_sys_malloc = 1 ## 使用系統自帶的內存分配器，替代 innodb_additional_mem_pool_size 參數 innodb_data_file_path = ibdata1:512M:autoextend ## 指定一個大小為 512M 的、可擴展的 ibdata1 數據文件 innodb_read_io_threads = 4 innodb_write_io_threads = 4 ## Innodb 使用後台線程處理數據頁上的 IO 請求，根據 CPU 核數修改，默認 4 innodb_thread_concurrency = 0 ## Innodb 線程並發數，0 為不限制，默認 0 innodb_flush_log_at_trx_commit = 2 ## Innodb 事務日志刷新方式，0 為每隔一秒 log thread 會將 log buffer 中的數據寫入到文件，並通知文件系統進行文件同步 flush 操作，極端情況下會丟失一秒的數據 ## 1 為每次事務結束都會觸發 log thread 將 log buffer 中的數據寫入文件並通知文件系統同步文件，數據最安全、不會丟失任何已經提交的數據 ## 2 為每次事務結束後 log thread 會將數據寫入事務日志，但只是調用了文件系統的文件寫入操作，並沒有同步到物理磁盤，因為文件系統都是有緩存機制的，各文件系統的緩存刷新機制不同 ## 當設為 1 時是最為安全的，但性能也是最差的。0 為每秒同步一次，性能相對高一些。設為 2 性能是最好的，但故障後丟失數據也最多（ OS 跟主機硬件、供電足夠安全可以選擇，或對數據少量丟失可以接受 ）。 innodb_log_buffer_size = 8M ## 事務日志所使用的緩沖區。Innodb 在寫事務日志時，為了提高寫 Log 的 IO 性能，先將信息寫入 Innodb Log Buffer 中，當滿足 Innodb_flush_log_trx_commit 參數或日志緩沖區寫滿時，再將日志同步到磁盤中。 ## 默認 8M ，一般設為 16~64M 即可，可以通過 show status like 'innodb_log%'; 查看狀態 innodb_buffer_pool_dump_at_shutdown = 1 ## 關閉數據庫時把熱數據 dump 到本地磁盤。 innodb_buffer_pool_dump_now = 1 ## 采用手工方式把熱數據 dump 到本地磁盤。 innodb_buffer_pool_load_at_startup = 1 ## 啟動時把熱數據加載到內存。 innodb_buffer_pool_load_now = 1 ## 采用手工方式把熱數據加載到內存。 ## 以上四條參數會快速預熱 Buffer_pool 緩沖池，當機器正常重啟後，熱數據還保留在內存中，避免瞬間連接數爆滿導致機器死機。 [mysqldump] quick max_allowed_packet = 4M ## 使用 mysqldump 工具備份數據庫時，當某張表過大時備份會報錯，需要增大該值（ 增大到大於表大小的值 ）
