溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
MySQL參數優化對于不同的網站，及其在線量，訪問量，帖子數量，網絡情況，以及機器硬件配置都有關系，優化不可能一次性完成，需要不斷的觀察以及調試，才有可能得到最佳的效果。
MySQL的最大連接數，如果服務器的并發連接請求量較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，MySQL回味每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。
數值過小經常會出現ERROR 1040：Too mant connetcions錯誤，可以通過mysql>show status like ‘connections'；通配符來查看當前狀態的連接數量（試圖連接到MySQL（不管是否連接成功）的連接數），以定奪該值的大小。
show variadles like ‘max_connections'最大連接數
show variables like ‘max_used_connection'相應連接數
max_used_connection/max_connections*100%（理想值約等于85%）
如果max_used_connections和max_connections相同，那么就是max_connections值設置過低或者超過服務器的負載上限了，低于10%則設置過大了。
MySQL能夠暫存的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，他就會起作用。如果MySQL的連接數據達到max_connections時，新的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧數量即back_log，如果等待連接的數量超過back_log，將不被接受連接資源。
wait_timeout：指的是MySQL再關閉一個非交互的連接之前所需要等待的秒數。
interative_timeout：指的是關閉一個交互的連接之前所需要等待的秒數。
對性能的影響
wait_timeout
（1）如果設置太小，那么連接關閉的很快，從而使一些持久的連接不起作用
（2）如果設置太大容易造成連接打開時間過長，在show processlist時，能夠看到太多的sleep狀態的連接，從而造成too many connections錯誤。
（3）一般希望wait_timeuot盡可能的低
interative_timeout的設置將對你的web application沒有多大的影響
全局緩沖
key_buffer_size指定索引緩沖區的大小，他決定索引的處理速度，尤其是索引讀的速度。通過檢查狀態值 key_read_requests和key_reads，可以知道key_buffer_size設置是否合理。比例key_reads/key_read_requests應該盡可能的低，至少是1：100，1：1000更好（上述狀態值可以使用show status like ‘key_read%'獲得）
未命中緩存的概率：
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size只對MAISAM表起作用。
如何調整key_buffer_size的值
默認的配置數時8388608（8M），主機有4G內存可以調優值為268435456（256M）
使用查詢緩存，MySQL將查詢結果存放在緩沖區中，今后對同樣的select語句（區分大小寫），將直接從緩沖區中讀取結果。
一個SQL查詢如果以select開頭，那么MySQL服務器將嘗試對其使用查詢緩存。
注：兩個SQL語句，只要相差哪怕是一個字符（例如 大小寫不一樣：多一個空格等），那么兩個SQL將使用不同的cache
通過 show ststus like ‘Qcache%' 可以知道query_cache_size的設置是否合理
Qcache_free_blocks：緩存中相鄰內存塊的個數。如果該值顯示過大，則說明Query Cache中的內存碎片較多了。
注：當一個表被更新后，和他相關的cache block將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。可以用 flush query cache語句來清空free blocks。
Qcache_free_memory:Query Cache 中目前剩余的內存大小。通過這個參數我們可以較為準確的觀察當前系統中的Query Cache內存大小是否足夠，是需要增多還是過多了。
Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢能緩存的效果。數字越大緩存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理后把結果insert帶查詢緩存中。這樣的情況次數越多，表示查詢緩存 應用到的比較少，效果也就不理想。
Qcache_lowmen_prunes：多少條Query因為內存不足而被清除出Query Cache，通過Qcache_lowmem_prunes和Qcache_free_memory 相互結合，能夠更清楚的了解到我們系統中Query Cache的內存大小是否真的足夠，是否非常頻繁的出現因為內存不足而有Query被換出。這個數字最好是長時間來看，如果這個數字在不斷增長，就表示可能碎片化非常嚴重，或者內存很少。
Qcache_queries_in_cache：當前Query Cache 中cache的Query數量
Qcache_total_blocks：當前Query Cache中block的數量
查詢服務器關于query_cache的配置
各字段的解釋：
query_cache_limit：超出此大小的查詢將不被緩存
query_cache_min_res_unit：緩存塊的最小大小，query_cache_min_res_unit的配置是一柄雙刃劍，默認是 4KB ，設置值大對大數據查詢有好處，但是如果你查詢的都是小數據查詢，就容易造成內存碎片和浪費。
query_cache_size：查詢緩存大小（注：QC存儲的單位最小是1024byte，所以如果你設定的一個不是1024的倍數的值。這個值會被四舍五入到最接近當前值的等于1024的倍數的值。）
query_cache_type：緩存類型，決定緩存什么樣子的查詢，注意這個值不能隨便設置必須設置為數字，可選值以及說明如下：
0：OFF 相當于禁用了
1：ON 將緩存所有結果，除非你的select語句使用了SQL_NO_CACHE禁用了查詢緩存
2：DENAND 則只緩存select語句中通過SQL_CACHE指定需要緩存的查詢。
query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成在讀表獲取結果。
查詢緩存碎片率：Qcache_free_block/Qcache_total_block*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_hits+Qcache_inserts)*100%
Query Cache的限制
a）所有子查詢中的外部查詢SQL 不能被Cache：
b）在p'rocedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次執行可能得到不一樣的結果的函數的Query不能被Cache
是一個MySQL中與安全有關的計數器值，他負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼的情況，當超過指定次數，MySQL服務器將禁止host的連接請求，直到mysql服務器重啟或通過flush hotos命令清空此host的相關信息。（與性能并無太大的關系）
每個需要排序的線程分配該大小的一個緩沖區。增加這值加速ORDER BY 或 GROUP BY操作
sort_buffer_size是一個connection級的參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性分配設置的內存。
sort_buffer_size：并不是越大越好，由于是connection級的參數，過大的設置+高并發可能會耗盡系統的內存資源。例如：500個連接將會消耗500*sort_buffer_size(2M)=1G
根據配置文件限制server接受的數據包大小。
用于表示關聯緩存的大小，和sort_buffer_size一樣，該參數對應的分配內存也是每個連接獨享。
服務器線程緩存，這個值表示可以重新利用保存在緩存中的線程數量，當斷開連接時，那么客戶端的線程將被放到緩存中以響應下一個客戶而不是銷毀（前提時緩存數未達上限），如果線程重新被請求，那么請求將從緩存中讀取，如果緩存中是空的或者是新的請求，這個線程將被重新請求，那么這個線程將被重新創建，如果有很多新的線程，增加這個值可以改善系統性能，通過比較Connections和Threads_created狀態的變量，可以看到這個變量的作用。
設置規則如下：1G內存配置為8，2G內存為16.服務器處理此客戶的線程將會緩存起來以響應下一個客戶而不是被銷毀（前提是緩存數未到達上限）
Threads_cached：代表當前此時此刻線程緩存中有多少空閑線程。
Threads_connected：代表當前已建立連接的數量，因為一個連接就需要一個線程，所以也可以看成當前被使用的線程數。
Threads_created：代表最近一次服務啟動，已創建線程的數量，如果發現Threads_created值過大的話，說明MySQL服務器一直在創建線程，這也比較消耗資源，可以適當增加配置文件中thread_cache_size值
Threads_running：代表當前激活的（非睡眠狀態）線程數。并不是代表正在使用的線程數，有時候連接已建立，但是連接處于sleep狀態。
對于innodb表來說，innodb_buffer_pool_size的作用相當于key_buffer_size對于MyISAM表的作用一樣。Innodb使用該參數指定大小的內存來緩沖數據和索引。最大可以把該值設置成物理內存的80%。
主要控制了innodb將log buffer中的數據寫入日志文件并flush磁盤的時間點，取值分別為0，1，2.
實際測試發現，該值對插入數據的速度影響非常大，設置為2時插入10000條記錄只需要兩秒，設置為0時只需要一秒，設置為1時，則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅度提高速度。
此參數用來設置innodb線程的并發數，默認值為0表示不被限制，若要設置則與服務器的CPU核心數相同或是CPU的核心數的2倍。
此參數確定日志文件所用的內存大小，以M為單位。緩沖區更大能提高性能，對于較大的事務，可以增大緩存大小。
此參數確定數據日志文件的大小，以M為單位，更大的設置可以提高性能。
為提高性能，MySQL可以以循環方式將日志文件寫到多個文件。推薦設置為3
MySQL 讀入緩沖區大小。對表進行順序掃描的請求將分配到一個讀入緩沖區MySQL會為他分配一段內存緩沖區
MySQL 的隨機讀（查詢操作）緩沖區大小。當按任意順序讀取行時（例如，按照排序順序），將分配到一個隨機都緩沖區。進行排序查詢時，MySQL會首先掃描一遍該緩沖區，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但是MySQL會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存消耗過大。
注：順序讀是根據索引的葉節點數據就能順序的讀取所需要的行數據。隨機讀是指一般需要根據輔助索引葉節點中的主鍵尋找侍其巷進行數據，而輔助索引和主鍵所在的數據端不同，因此訪問方式是隨機的。
批量插入數據緩存大小，可以有效的提高插入效率，默認為8M
binlog_cache_size=2M //為每個session分配的內存，在事務過程中用來存儲二進制日志的緩存，提高記錄bin-log的效率。
max_binlog_cache_size=8M //表示的是binlog能夠使用的最大cache內存大小
max_binlog_size=512M //指定binlog日志文件的大小。不能將變量設置為大于1G或小于4096字節。默認值為1G.在導入大容量的sql文件時，建議關閉，sql_log_bin，否則硬盤扛不住，而且建議定期做刪除。
expire_logs_days=7 //定義了mysql清除過期日志的時間
[mysqld] basedir = /usr/local/mysql datadir = /usr/local/mysql/data server_id = 1 socket = /usr/local/mysql/mysql.sock log-error = /usr/local/mysql/data/mysqld.err slow_query_log = 1 slow_query_log_file=/usr/local/mysql/data/slow-query.log long_query_time = 1 log-queries-not-using-indexes max_connections = 1024 back_log = 128 wait_timeout = 60 interactive_timeout = 7200 key_buffer_size = 256M query_cache_size = 256M query_cache_type = 1 query_cache_limit = 50M max_connect_errors = 20 sort_buffer_size = 2M max_allowed_packet = 32M join_buffer_size = 2M thread_cache_size = 200 innodb_buffer_pool_size = 2048M innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32M innodb_log_file_size = 128M innodb_log_files_in_group = 3 log-bin=/usr/local/mysql/data/mysqlbin binlog_cache_size = 2M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
