mysql服務(wù)性能優(yōu)化my_cnf配置說明詳解16G內(nèi)存

1、mysql 服務(wù)性能優(yōu)化 f 配置說明詳解 (16G 內(nèi)存MYSQL 服務(wù)器 f 配置文檔詳解硬件:內(nèi)存 16Gclientport = 3306mysqlno-auto-rehashmysqlduser = mysqlport = 3306basedir = /usr/local/mysqldatadir = /data/3306/dataopen_files_limit = 10240back_log = 600#在 MYSQL 暫時(shí)停止響應(yīng)新請(qǐng)求之前, 短時(shí)間內(nèi)的多少個(gè)請(qǐng)求可以被存在堆棧中。 如果系統(tǒng) 在短時(shí)間內(nèi)有很多連接,則需要增大該參數(shù)的值,該參數(shù)值指定到來的 TCP/IP連接的監(jiān)聽

2、 隊(duì)列的大小。默認(rèn)值 50。max_connections = 3000#MySQL允許最大的進(jìn)程連接數(shù),如果經(jīng)常出現(xiàn) Too Many Connections的錯(cuò)誤提示,則需要 增大此值。max_connect_errors = 6000#設(shè)置每個(gè)主機(jī)的連接請(qǐng)求異常中斷的最大次數(shù),當(dāng)超過該次數(shù), MYSQL 服務(wù)器將禁止 host 的連接請(qǐng)求,直到 mysql 服務(wù)器重啟或通過 flush hosts命令清空此 host 的相關(guān)信息。table_cache = 614#指示表調(diào)整緩沖區(qū)大小。 # table_cache 參數(shù)設(shè)置表高速緩存的數(shù)目。每個(gè)連接進(jìn)來,都會(huì) 至少打開一個(gè)表緩存。 #因

3、此, table_cache 的大小應(yīng)與 max_connections 的設(shè)置有關(guān)。 例如, 對(duì)于 200 個(gè) #并行運(yùn)行的連接,應(yīng)該讓表的緩存至少有 200 × N ,這里 N 是應(yīng)用可以執(zhí)行的 查詢 #的一個(gè)聯(lián)接中表的最大數(shù)量。此外,還需要為臨時(shí)表和文件保留一些額外的文件描述 符。# 當(dāng) Mysql 訪問一個(gè)表時(shí),如果該表在緩存中已經(jīng)被打開,則可以直接訪問緩存;如果 #還沒有被緩存,但是在 Mysql 表緩沖區(qū)中還有空間,那么這個(gè)表就被打開并放入表緩 #沖區(qū); 如果表緩存滿了,則會(huì)按照一定的規(guī)則將當(dāng)前未用的表釋放,或者臨時(shí)擴(kuò)大表緩存來存放, 使用表緩存的好處是可以更快速地訪問表

4、中的內(nèi)容。執(zhí)行 flush tables 會(huì) #清空緩存的內(nèi)容。 一般來說,可以通過查看數(shù)據(jù)庫(kù)運(yùn)行峰值時(shí)間的狀態(tài)值 Open_tables #和 Opened_tables ,判 斷 是 否 需 要 增 加 table_cache 的 值 (其 中 open_tables 是 當(dāng) #前 打 開 的 表 的 數(shù) 量, Opened_tables 則是已經(jīng)打開的表的數(shù)量 。 即如果 open_tables接近 table_cache的時(shí)候, 并且 Opened_tables這個(gè)值在逐步增加,那就要考慮增加這個(gè) #值的大小了。還有就是 Table_locks_waited比較高的時(shí)候,也需要增加 t

5、able_cache。external-locking = FALSE#使用 skip-external-locking MySQL選項(xiàng)以避免外部鎖定。該選項(xiàng)默認(rèn)開啟max_allowed_packet = 32M#設(shè)置在網(wǎng)絡(luò)傳輸中一次消息傳輸量的最大值。系統(tǒng)默認(rèn)值 為 1MB ,最大值是 1GB ,必須設(shè) 置 1024的倍數(shù)。sort_buffer_size = 2M# Sort_Buffer_Size 是一個(gè) connection 級(jí)參數(shù),在每個(gè) connection (session 第一次需要使用 這個(gè) buffer 的時(shí)候,一次性分配設(shè)置的內(nèi)存。#Sort_Buffer_Size 并

6、不是越大越好, 由于是 connection 級(jí)的參數(shù), 過大的設(shè)置 +高并發(fā)可能會(huì) 耗盡系統(tǒng)內(nèi)存資源。例如:500個(gè)連接將會(huì)消耗 500*sort_buffer_size(8M=4G內(nèi)存#Sort_Buffer_Size 超過 2KB 的時(shí)候, 就會(huì)使用 mmap( 而不是 malloc( 來進(jìn)行內(nèi)存分配, 導(dǎo)致 效率降低。#explain select*from table where order limit;出現(xiàn) filesort#屬重點(diǎn)優(yōu)化參數(shù)join_buffer_size = 2M#用于表間關(guān)聯(lián)緩存的大小,和 sort_buffer_size一樣,該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接

7、獨(dú)享。thread_cache_size = 300# 服務(wù)器線程緩存這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量 , 當(dāng)斷開連接時(shí)如果緩 存中還有空間 , 那么客戶端的線程將被放到緩存中 , 如果線程重新被請(qǐng)求,那么請(qǐng)求將從緩存 中讀取 , 如果緩存中是空的或者是新的請(qǐng)求,那么這個(gè)線程將被重新創(chuàng)建 , 如果有很多新的線 程, 增加這個(gè)值可以改善系統(tǒng)性能 . 通過比較 Connections 和 Threads_created 狀態(tài)的變量, 可 以看到這個(gè)變量的作用。 設(shè)置規(guī)則如下:1GB 內(nèi)存配置為 8, 2GB 配置為 16, 3GB 配置為 32, 4GB 或更高內(nèi)存,可配置更大。thr

8、ead_concurrency = 8# 設(shè)置 thread_concurrency的值的正確與否 , 對(duì) mysql 的性能影響很大 , 在多個(gè) cpu(或多核 的 情況下, 錯(cuò)誤設(shè)置了 thread_concurrency的值 , 會(huì)導(dǎo)致 mysql 不能充分利用多 cpu(或多核 , 出現(xiàn)同一時(shí)刻只能一個(gè) cpu(或核 在工作的情況。 thread_concurrency應(yīng)設(shè)為 CPU 核數(shù)的 2倍 . 比 如 有 一 個(gè) 雙 核 的 CPU, 那 么 thread_concurrency的 應(yīng) 該 為 4; 2個(gè) 雙 核 的 cpu, thread_concurrency的值應(yīng)為 8#

9、屬重點(diǎn)優(yōu)化參數(shù)query_cache_size = 64M# 對(duì)于使用 MySQL 的用戶,對(duì)于這個(gè)變量大家一定不會(huì)陌生。前幾年的 MyISAM 引擎優(yōu)化 中,這個(gè)參數(shù)也是一個(gè)重要的優(yōu)化參數(shù)。 但隨著發(fā)展,這個(gè)參數(shù)也爆露出來一些問題。 機(jī)器 的內(nèi)存越來越大, 人們也都習(xí)慣性的把以前有用的參數(shù)分配的值越來越大。 這個(gè)參數(shù)加大后 也引發(fā)了一系列問題。 我們首先分析一下 query_cache_size的工作原理:一個(gè) SELECT 查詢?cè)?DB 中工作后, DB 會(huì)把該語(yǔ)句緩存下來, 當(dāng)同樣的一個(gè) SQL 再次來到 DB 里調(diào)用時(shí), DB 在該 表沒發(fā)生變化的情況下把結(jié)果從緩存中返回給 Clien

10、t 。這里有一個(gè)關(guān)建點(diǎn),就是 DB 在利用 Query_cache工作時(shí),要求該語(yǔ)句涉及的表在這段時(shí)間內(nèi)沒有發(fā)生變更。那如果該表在發(fā)生 變更時(shí), Query_cache里的數(shù)據(jù)又怎么處理呢?首先要把 Query_cache和該表相關(guān)的語(yǔ)句全 部置為失效,然后在寫入更新。那么如果 Query_cache非常大,該表的查詢結(jié)構(gòu)又比較多, 查詢語(yǔ)句失效也慢,一個(gè)更新或是 Insert 就會(huì)很慢,這樣看到的就是 Update 或是 Insert 怎 么這么慢了。 所以在數(shù)據(jù)庫(kù)寫入量或是更新量也比較大的系統(tǒng), 該參數(shù)不適合分配過大。 而 且在高并發(fā),寫入量大的系統(tǒng),建議把該功能禁掉。#重點(diǎn)優(yōu)化參數(shù)(主庫(kù)

11、 增刪改 -MyISAM query_cache_limit = 4M#指定單個(gè)查詢能夠使用的緩沖區(qū)大小,缺省為 1Mquery_cache_min_res_unit = 2k#默認(rèn)是 4KB ,設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處,但如果你的查詢都是小數(shù)據(jù)查詢,就容易造 成內(nèi)存碎片和浪費(fèi)#查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%#如果查詢緩存碎片率超過 20%,可以用 FLUSH QUERY CACHE整理緩存碎片,或者試試減小 query_cache_min_res_unit,如果你的查詢都是小數(shù)據(jù)量的話。#查詢緩存利用率 =

12、 (query_cache_size Qcache_free_memory / query_cache_size * 100% #查詢緩存利用率在 25%以下的話說明 query_cache_size設(shè)置的過大,可適當(dāng)減小 ; 查詢緩存 利用率在 80%以上而且 Qcache_lowmem_prunes > 50的話說明 query_cache_size可能有點(diǎn)小, 要不就是碎片太多。#查詢緩存命中率 = (Qcache_hits Qcache_inserts / Qcache_hits * 100%default-storage-engine = MyISAM#default_tabl

13、e_type = InnoDBthread_stack = 192K#設(shè)置 MYSQL 每個(gè)線程的堆棧大小,默認(rèn)值足夠大,可滿足普通操作?？稍O(shè)置范圍為 128K 至 4GB ,默認(rèn)為 192KB 。transaction_isolation = READ-COMMITTED# 設(shè)定默認(rèn)的事務(wù)隔離級(jí)別 . 可用的級(jí)別如下 :# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE# 1.READ UNCOMMITTED-讀未提交 2.READ COMMITTE-讀已提交 3.REPEATABLE READ -可重復(fù) 讀 4

14、.SERIALIZABLE -串行tmp_table_size = 256M# tmp_table_size 的 默 認(rèn) 大 小 是 32M。 如 果 一 張 臨 時(shí) 表 超 出 該 大 小 , MySQL 產(chǎn) 生 一 個(gè) The table tbl_name is full 形 式 的 錯(cuò) 誤 , 如 果 你 做 很 多 高 級(jí) GROUP BY 查 詢 , 增 加 tmp_table_size 值。如果超過該值,則會(huì)將臨時(shí)表寫入磁盤。max_heap_table_size = 256Mlong_query_time = 2log_long_format#log-bin = /data/33

15、06/mysql-binlog-binbinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 512Mexpire_logs_days = 7key_buffer_size = 2048M#批定用于索引的緩沖區(qū)大小,增加它可以得到更好的索引處理性能,對(duì)于內(nèi)存在 4GB 左右 的服務(wù)器來說,該參數(shù)可設(shè)置為 256MB 或 384MB 。read_buffer_size = 1M# MySql讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū), MySql 會(huì)為它 分配一段內(nèi)存緩沖區(qū)。 read_buffer_si

16、ze變量控制這一緩沖區(qū)的大小。 如果對(duì)表的順序掃描請(qǐng) 求非常頻繁, 并且你認(rèn)為頻繁掃描進(jìn)行得太慢, 可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小 提高其性能。和 sort_buffer_size一樣,該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。read_rnd_buffer_size = 16M# MySql的隨機(jī)讀 (查詢操作 緩沖區(qū)大小。 當(dāng)按任意順序讀取行時(shí) (例如, 按照排序順序 , 將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí), MySql 會(huì)首先掃描一遍該緩沖,以避免磁盤搜 索, 提高查詢速度,如果需要排序大量數(shù)據(jù),可適當(dāng)調(diào)高該值。 但 MySql 會(huì)為每個(gè)客戶連接 發(fā)放該緩沖空間,所以應(yīng)盡量適當(dāng)設(shè)

17、置該值,以避免內(nèi)存開銷過大。bulk_insert_buffer_size = 64M#批量插入數(shù)據(jù)緩存大小,可以有效提高插入效率,默認(rèn)為 8Mmyisam_sort_buffer_size = 128M# MyISAM表發(fā)生變化時(shí)重新排序所需的緩沖myisam_max_sort_file_size = 10G# MySQL重 建 索 引 時(shí) 所 允 許 的 最 大 臨 時(shí) 文 件 的 大 小 (當(dāng) REPAIR, ALTER TABLE 或 者 LOAD DATA INFILE.# 如果文件大小比此值更大 , 索引會(huì)通過鍵值緩沖創(chuàng)建 (更慢 myisam_max_extra_sort_fil

18、e_size = 10Gmyisam_repair_threads = 1# 如果一個(gè)表?yè)碛谐^一個(gè)索引 , MyISAM 可以通過并行排序使用超過一個(gè)線程去修復(fù)他們 . # 這對(duì)于擁有多個(gè) CPU 以及大量?jī)?nèi)存情況的用戶 , 是一個(gè)很好的選擇 .myisam_recover#自動(dòng)檢查和修復(fù)沒有適當(dāng)關(guān)閉的 MyISAM 表skip-name-resolvelower_case_table_names = 1server-id = 1innodb_additional_mem_pool_size = 16M#這個(gè)參數(shù)用來設(shè)置 InnoDB 存儲(chǔ)的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小, 類似

19、于 Oracle 的 library cache。這不是一個(gè)強(qiáng)制參數(shù),可以被突破。innodb_buffer_pool_size = 2048M# 這對(duì) Innodb 表來說非常重要。 Innodb 相比 MyISAM 表對(duì)緩沖更為敏感。 MyISAM 可以在默 認(rèn)的 key_buffer_size 設(shè)置下運(yùn)行的可以, 然而 Innodb 在默認(rèn)的 innodb_buffer_pool_size 設(shè)置 下卻跟蝸牛似的。由于 Innodb 把數(shù)據(jù)和索引都緩存起來,無需留給操作系統(tǒng)太多的內(nèi)存, 因 此 如 果 只 需 要 用 Innodb 的 話 則 可 以 設(shè) 置 它 高 達(dá) 70-80% 的

20、可 用 內(nèi) 存 。 一 些 應(yīng) 用 于 key_buffer 的 規(guī) 則 有 如 果 你 的 數(shù) 據(jù) 量 不 大 , 并 且 不 會(huì) 暴 增 , 那 么 無 需 把 innodb_buffer_pool_size 設(shè)置的太大了innodb_data_file_path = ibdata1:1024M:autoextend#表空間文件 重要數(shù)據(jù)innodb_file_io_threads = 4#文件 IO 的線程數(shù),一般為 4,但是在 Windows 下,可以設(shè)置得較大。innodb_thread_concurrency = 8#服務(wù)器有幾個(gè) CPU 就設(shè)置為幾,建議用默認(rèn)設(shè)置,一般為 8.i

21、nnodb_flush_log_at_trx_commit = 2# 如果將此參數(shù)設(shè)置為 1,將在每次提交事務(wù)后將日志寫入磁盤。為提供性能,可以設(shè)置為 0或 2,但要承擔(dān)在發(fā)生故障時(shí)丟失數(shù)據(jù)的風(fēng)險(xiǎn)。設(shè)置為 0表示事務(wù)日志寫入日志文件,而 日志文件每秒刷新到磁盤一次。 設(shè)置為 2表示事務(wù)日志將在提交時(shí)寫入日志, 但日志文件每 次刷新到磁盤一次。innodb_log_buffer_size = 16M#此參數(shù)確定些日志文件所用的內(nèi)存大小,以 M 為單位。緩沖區(qū)更大能提高性能,但意外的 故障將會(huì)丟失數(shù)據(jù) .MySQL 開發(fā)人員建議設(shè)置為 1-8M 之間innodb_log_file_size = 128M #此參數(shù)確定數(shù)據(jù)日志文件的大小，以 M 為單位，更大的設(shè)置可以提高性能，但也會(huì)增加恢 復(fù)故障數(shù)據(jù)庫(kù)所需的時(shí)間 innodb_log_files_in_group = 3 #為提高性能，MySQL 可以以循環(huán)方式將日志文件寫到多個(gè)文件。推薦設(shè)置為 3M innodb_max_dirty_pages_pct = 90 # 推 薦 閱 讀 # Buffer_Pool 中 Dirty_Page 所 占 的 數(shù) 量 ， 直 接 影 響 InnoDB 的 關(guān) 閉 時(shí) 間 。 參 數(shù) innodb_max_dirty_pages_pct 可以直接控制了 Dirty_Page 在 Buffer_