MySql配置和調(diào)優(yōu)

1、第一部分：安裝mysql壓縮包查看默認(rèn)存儲(chǔ)引擎1. 復(fù)制 mysql-5.6.10-win32.zip 到 d:app 目錄下.2. 解壓mysql5.6.10-win32.zip3. 在 d:appmysql-5.6.10-win32 p,攵制"my-default.ini"文件，生成"攵件my-default.ini"文件。將"復(fù)件my-default.ini"文件重命 名成"my.ini"。（注意：如果直接改my-default.i ni文件，下面的char acter_set_se rver=utf8是不牛

2、.效的。） 修改 d:ap pmysql-5.6.10-win32下的"m y.ini"文件在my sqld中添加以下內(nèi)容mysqldchar acter_set_se rver=utf8max _connecti ons = 200注意：開始把mylsam作為默認(rèn)存儲(chǔ)引擎，所以需要設(shè)置def ault-table-t ype=innodb my sql-5.6.10的默認(rèn)存儲(chǔ)引擎為 innodb ,不用設(shè)置 defaul t-table-type =innodb« 點(diǎn)擊"開始"菜單,打開"運(yùn)行"窗口，輸入"cmd

3、",打開命令窗口,輸入"mysq iu root -p w回 車,系統(tǒng)顯示z/ent er password/',直接回車，進(jìn)入mys ql, 系統(tǒng)顯示"mysq卜show engi nes河以查看默認(rèn)存儲(chǔ)引擎。mysql>sho w variables like /char%,;nj 以查看 charac ter_set_serv er=utf8是否生效。1!mysql配置文件mys ql.ini參數(shù)詳解.mysql性能優(yōu)化my.ini （linux系統(tǒng)下是f）,當(dāng)my sql服務(wù)器啟動(dòng)時(shí)它會(huì)讀取這個(gè)文件，設(shè)置相關(guān)的運(yùn) 行環(huán)境參數(shù)。my. ini

4、分為 兩塊： clie nt sectio n 和 ser ver sectio n 。clientsection 用來配置ysql 客戶端參數(shù)。要查看配置參數(shù)可以用下面的命令：show va riables like innodb%'# 查看 innodb 相關(guān)配置參數(shù)show st atus like innodb%1; #查看innodb相關(guān)的運(yùn)行時(shí)參數(shù)（比如當(dāng)前正在打開的表的數(shù) 量，當(dāng)前已經(jīng)打開的表的數(shù)量）show global statu s like 'open %tablesf; #查看全局的運(yùn)行時(shí)參數(shù)，加上global是對(duì)當(dāng)前m ysql 服務(wù)器中運(yùn)行的所有數(shù)據(jù)

5、庫實(shí)例進(jìn)行統(tǒng)計(jì)。不加global則只對(duì)當(dāng)前數(shù)據(jù)庫實(shí)例進(jìn)行統(tǒng)計(jì)。1、clientsectionclientport = 3306 #設(shè)置my sql客戶端連接服務(wù)端時(shí)默認(rèn)使用的端口 mysqlde fault-charac ter-set=utf8# 設(shè)置 mysql 客戶端默認(rèn)字符集2> serv er sectionmysqldport=3 306# mysql 服務(wù)端默認(rèn)監(jiān)聽(liste n on)的 tcp/ip 端 口basedir="c :/program fi les/mysql/my sql server 5.5/h # 基準(zhǔn)路徑，其他路徑都相對(duì)于這個(gè) 路徑dat

6、adir=,c:/program file s/mysql/mysq l server 5.5/data” # mys ql 數(shù)據(jù)庫文件所在目錄c haracter-set -server=lati nl #服務(wù)端使用的字符集默認(rèn)為8比特編碼的i atinl字符集def ault-storage- engine=innod b #創(chuàng)建新表時(shí)將使用 的默認(rèn)存儲(chǔ)引擎sql-m ode=hstrict_trans_tables zno_auto_cre ate_user,no_engine_subst itution1' # s ql模式為strict模式max_connect ions=1

7、00 # m ysql服務(wù)器支持的最大并發(fā)連接數(shù)(用戶數(shù))。但總會(huì)預(yù)留其中 的一個(gè)連接給管理員使用超級(jí)權(quán)限登錄，即使連接數(shù)目達(dá)到最大限制。如果設(shè)置得過小而 用戶比 較多，會(huì)經(jīng)常出現(xiàn)"too many connec tions"錯(cuò)誤。que ry_cache_siz e=0 #查詢緩存大小，用于緩存select查詢結(jié)果。如果有許多返冋相同查 詢結(jié)果的select查詢，并且很少改變表，可以設(shè)置query_cac he.size大于0,可以極大改 善查詢效率。而如果表數(shù)據(jù)頻繁變化，就不要使用這個(gè)，會(huì)適得其反ta ble_cache=256 #這個(gè)參數(shù)在5.1.3之后的版本中叫做t

8、a ble_open_cac he,用于設(shè)置table 高速緩存的數(shù)量。由于每個(gè)客戶端連接都會(huì)至少訪問一個(gè)表，因此此參數(shù)的值與max_co nnections有關(guān)。當(dāng)某一連接訪問一個(gè)表時(shí)，mysql會(huì)檢查當(dāng)前已緩存表的數(shù)量。如果該表 已經(jīng)在緩存屮打開，則會(huì)直接訪問緩存屮的表c加快查詢速度；如果該表未被緩存，則會(huì) 將當(dāng)前的表添加進(jìn)緩存并進(jìn)行查詢。在執(zhí)行緩存操作之前，table_cac he用于限制緩存表的 最大數(shù)目：如果當(dāng)前已經(jīng)緩存的表未達(dá)到table.ca che,則會(huì)將新表添加進(jìn)來；若已經(jīng)達(dá)到 此值，my sql將根據(jù)緩存表的最后查詢時(shí)間、查詢率等規(guī)則釋放之前的緩存。tmp_t able_s

9、ize=34m #內(nèi)存中的每個(gè)臨時(shí)表允許的最大大小。如果臨吋表大小超過該值, 臨時(shí)表 將自動(dòng)轉(zhuǎn)為基于磁盤的表（disk based t able ）。thread _cache_size=8 #緩存的最大線程數(shù)。當(dāng)客戶端連接斷開吋，如杲客戶端總連接數(shù)小 于該值，則處理客戶端任務(wù)的線程放回緩存。在高并發(fā)情況下，如果該值設(shè)置得太小，就 會(huì)有很多線程頻繁創(chuàng)建，線程創(chuàng)建的開銷會(huì)變大，查詢效率也會(huì)下降。一般來說如果在應(yīng) 用端有良好的多線程處理，這個(gè)參數(shù)對(duì)性能不會(huì)有太大的提髙。#mylsam相關(guān)參數(shù)myisa m_max_sort_f ile_size=100g # mysql重建索引時(shí)允許使用的臨時(shí)文件

10、最大大小myisam_sort_buffer_size=68mkey_bu ffer_size=54m # key buff er大小，用于緩存myl sam表的索引塊。決定數(shù)據(jù)庫索引 處 理 的 速 度 （尤 其是 索 引 讀）read_bu ffer_size=64k #用于對(duì)mylsa m表全表打描時(shí)使用的緩沖區(qū)大小。針對(duì)每個(gè)線程進(jìn) 行分配（前提是進(jìn)行了全表掃描）。進(jìn)行排序查詢吋，mysql會(huì)首先掃描一遍該緩沖，以避 免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但mysql會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。read_rnd_b

11、uffer_size=256kso rt_buffer_si ze=256k # co nnection級(jí)參數(shù)（為每個(gè)線程配置）,500個(gè)線程將消耗500*25 6k 的 sort_buff er_size0#innodb相關(guān)參數(shù)inno db_additiona l_mem_pool_s ize=3m # inn odb用于存儲(chǔ)元數(shù)據(jù)信息的內(nèi)存池大小，一般 不需修改innodb_flus h_log_at_trx _commit =1 #事務(wù)相關(guān)參數(shù)，如果值為 則 innodb 在每次commit 都會(huì)將事務(wù)日志寫入磁盤(磁盤10消耗較大)，這樣保證了完全的acid特性。而如果設(shè)置 為0,則

12、表示事務(wù)日志寫入內(nèi)存log和內(nèi)存log寫入磁盤的頻率都為1次/秒。如果設(shè)為2則表 示事務(wù)日志在每次commit都寫入內(nèi)存log,但內(nèi)存io g寫入磁盤的頻率為1次/秒。innodbjog _buffer_size =2m # innodb日志數(shù)據(jù)緩沖大小，如果緩沖滿了，就會(huì)將緩沖屮的 日志數(shù)據(jù)寫入磁盤(flus h)o由于一般至少都1秒鐘會(huì)寫一次磁盤，所以沒必要設(shè)置過大， 即使是長事務(wù)。innodb_buff er_pool_size =105m # inno db使用緩沖池來緩存索引和行數(shù)據(jù)。該值設(shè)置的越 大，則磁盤10越少。一般將該值設(shè)為物理內(nèi)存的80%。一般設(shè)為 innodb_buff到

13、100%innodb_log_file_size=53 m # 每_個(gè) innod b 事務(wù) 日志的大小。er_pool_size的25% inn odb_thread_c oncurrency=9# innodb 內(nèi)核最大并發(fā)線程數(shù)。在apac he, php, mys ql的體系架構(gòu)中，mys ql對(duì)于性能的彫響最大，也是關(guān)鍵的核心部 分。對(duì)于discuz!論壇程序也是如此，mysql的設(shè)置是否合理優(yōu)化，直接影響到論壇的速度和承載量！同時(shí)，mysql也是優(yōu)化難度最大的一個(gè)部分，不但需要理解一些mysql專業(yè)知 識(shí)，同時(shí)還需要長時(shí)間的觀察統(tǒng)計(jì)并口根據(jù)經(jīng)驗(yàn)進(jìn)行判斷，然后設(shè)置合理的參數(shù)。下面我

14、們了解一下mys ql優(yōu)化的一些基礎(chǔ)，my sql的優(yōu)化我分為兩個(gè)部分，一是服務(wù)器物理硬 件的 優(yōu)化，二是 mysql 自身 （f）的優(yōu)化。一、 服務(wù)器硬件對(duì) mysql 性能的影響 磁盤尋道能力（磁盤i/o）,以目前高轉(zhuǎn)速scsi硬盤（7200轉(zhuǎn)/秒）為例，這種硬盤理論上每秒尋道7200次，這是物理特性決定的，沒有辦法改變。mysql每秒鐘都在進(jìn)行大量、復(fù)雜 的查詢操作，對(duì)磁盤的讀寫量可想而知。所以，通常認(rèn)為磁盤i/o是制約mysql性能的最 大因素z，對(duì)于日均訪問量在100萬pv以上的discuz!論壇，由于磁盤i/o的制約， mysql的性能會(huì)非常低下！解決這一制約因素可以考慮以下兒種解

15、決方案：使用raid-0+1 磁盤陣列，注意不要嘗試使用raid-5, mysql在raid-5磁盤陣列上的效率不會(huì)像你期待的 那樣快。 cpu對(duì)于my sql應(yīng)用，推薦使用s.m.p.架構(gòu)的多路對(duì)稱c pu,例如：可以使用兩顆intel xeon 3.6ghz的cpu,現(xiàn)在我較推薦用4u的服務(wù)器來專門做數(shù)據(jù)庫服務(wù)器，不僅僅是 針對(duì)于mysql。 物理內(nèi)存對(duì)于一臺(tái)使用my sql的database server來說，服務(wù)器內(nèi)存建議不耍小于2gb , 推薦使用4gb以上的物理內(nèi)存，不過內(nèi)存對(duì)于現(xiàn)在的服務(wù)器而言可以說是一個(gè)可以忽略的 問題，工作中遇到了高端服務(wù)器基本上內(nèi)存都超過了 16g。二、my

16、 sql自身因素當(dāng)解決了上述服務(wù)器硬件制約因素后，讓我們看看mysql自身的優(yōu) 化是如何操作的。對(duì)m ysql自身的優(yōu)化主要是對(duì)其配置文件f中的各項(xiàng)參數(shù)進(jìn)行優(yōu) 化調(diào)整。下面我們介紹一些對(duì)性能影響較大的參數(shù)。由于m f文件的優(yōu)化設(shè)置是與服 務(wù)器硬件配置息息相關(guān)的，因而我們指定一個(gè)假想的服務(wù)器硬件環(huán)境：cp u: 2顆intel x eon 2.4ghz內(nèi)存：4gb ddr硬盤：scsi 73gb （很常見的2u服務(wù)器）。下面，我們根據(jù)以上硬件配置結(jié)合一份己經(jīng)優(yōu)化好的m f進(jìn)行說明：#v im /etc/my.c nf以下只列出 f文件中mysqld段落中的內(nèi)容,其他段落內(nèi)容對(duì) mysql 運(yùn)行性

17、能影 響甚微，因而姑且忽略。mysqld3306skip-locki ng#避免mys ql的外部鎖定，減少出錯(cuò)幾率增強(qiáng)穩(wěn)定性。skipn ame-resolve#禁止mysql對(duì)外部連接進(jìn)行dns解析，使用這一選項(xiàng)可以消除mysq l進(jìn)行dns解析的 時(shí)間。但需要注意，如果開啟該選項(xiàng)，則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用ip地址方式，否 則 mysql 將 無 法 正 常處 理 連 接 請(qǐng) 求！backlog384#ba ckjog參數(shù)的值指出在mysql暫時(shí)停止響應(yīng)新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被 存在堆棧屮。如果系統(tǒng)在一個(gè)短時(shí)間內(nèi)有很多連接，則需要增大該參數(shù)的值，該參數(shù)值指 定到來的tcp

18、/ip連接的偵聽隊(duì)列的大小。不同的操作系統(tǒng)住這個(gè)隊(duì)列大小上有它自己的限 制。試圖設(shè)定b ackjog高于你的操作系統(tǒng)的限制將是無效的。默認(rèn)值為50。對(duì)于linux系 統(tǒng) 推 薦 設(shè) 置 為 小 于 512 的 整 數(shù)。key_buffer_size256m#key.buf fer.size指定用于索引的緩沖區(qū)大小，增加它可得到更好的索引處理性能。對(duì)于內(nèi) 存在4gb左右的服務(wù)器該參數(shù)可設(shè)置為256m或384mo注意：該參數(shù)值設(shè)置的過大反而會(huì)是服務(wù)器整體效率降低！max_allowed_packet=4mthread_stack=256ktable_cache128ksort buffer siz

19、e6m#查詢排序時(shí)所能使用的緩沖區(qū)大小。注意：該參數(shù)對(duì)應(yīng)的分配內(nèi)存是每連接獨(dú)占，如果有 100個(gè)連接，那么實(shí)際分配的總共排序緩沖區(qū)大小為100 x 6 = 600mbo所以，對(duì)于內(nèi)存在4 gb 左 右 的 月艮 務(wù) 器 推 薦 設(shè) 置為 6-8m。read_buffer_size4m#讀查詢操作所能使用的緩沖區(qū)大小。和sor t_buffer_siz e 一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也 是每連接獨(dú)享。join buffer size8m#聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort.buffe r_size 一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)亨qmyisam_sort_buffer_size

20、=64mtable_cache=512thread_cache_size二64query_cache_size二64m#指定mysql查詢緩沖區(qū)的大小?？梢酝ㄟ^在mysql控制臺(tái)觀察，如果qcacheowmem_pr unes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況；如果qcache_hi ts的 值非常大，則表明查詢緩沖使用非常頻繁，如果該值較小反而會(huì)影響效率，那么可以考慮 不用查詢緩沖；qcache_free _blocks,如果該值非常大，則表明緩沖區(qū)屮碎片很多。tmp_table_size=256mmax_c onn ections768#指定mysql允許的最人連接進(jìn)程數(shù)。如果在訪問

21、論壇時(shí)經(jīng)常出現(xiàn)too man y connection s 的錯(cuò)誤提 示，則需要增 大該參數(shù)值。max_conn ect_errors1000000010ait timeout#指定一個(gè)請(qǐng)求的最大連接時(shí)間，對(duì)于4gb左右內(nèi)存的服務(wù)器可以設(shè)置為5-10 othread_con curre ncy=8#該參數(shù)取值為服務(wù)器邏輯cpu數(shù)量*2,在本例中，服務(wù)器有2顆物理cpu,而每顆物理cp u 又支持 h.t 超線程，所 以實(shí)際取值為4*2=8 skip-n etworking#開啟該選項(xiàng)可以徹底關(guān)閉mysql的tcp/ip連接方式，如果web服務(wù)器是以遠(yuǎn)程連接的方 式訪問mysql數(shù)據(jù)庫服務(wù)器則不

22、要開啟該選項(xiàng)！否則將無法正常連接！table_cache=1024#物理內(nèi)存越大，設(shè)置就越大默認(rèn)為2402,調(diào)到 512-1024最佳#默認(rèn)為2minno db_flusho g_at_trx_commit=l#設(shè)置為0就是等到innod b_log_buffer _size 列隊(duì)滿后再統(tǒng)一儲(chǔ)存，默認(rèn)為1innodb_log_buffer_size=2m#mil認(rèn)為1minno db_thread_c on curren cy=8#你的服務(wù)器cpu有幾個(gè)就設(shè)置為幾，建議用默認(rèn)一般為8key_buffer_size=256m#默認(rèn)為218,調(diào)到128最佳tmp_table_size=64m#默認(rèn)

23、為16m,調(diào)到64-256最掛read_buffer_size=4m#默認(rèn)為64 kread_rnd_buffer_size=16m#默認(rèn)為256ksort_buffer_size=32m#認(rèn)為256kthread_cache_size=120#默認(rèn)為60n al_mem_poolinnodb additiosize=4msize=32mquery_cache很 多 情 況 需 要 具 體一、如果 ke y_reads 太大，則應(yīng)該把 f 中 key _buffer_size 變大，保持 key_reads/key_read_ requests 至少 1/100 以上，越小越好。二、如果 qc

24、acheo wmem_prunes 很大，就要增加 query_cache_size 的值。mysql性能調(diào)優(yōu)m y.ini試驗(yàn)table_cache=512或inno db_additional_mem_pool_size1024=2minno db_flush_log_at_trx commit=0nno db_log_buffer_size=lminnodb_threa d_concurrenc y=8你的服務(wù)器cpu有兒個(gè)就設(shè)置為兒，默認(rèn)為8key_buffer_size=l28mtmp_tablere_size=128msort_bufferad_buffer_siead_r nd_

25、buffze=64k或128ker_size=256ksize=512kmax _conn ecti ons =1024 試table_cache=512innodbadditional_m二 :或1024em_pool_size=8minnodb flush_log_at_trx commit=0nno db_log_buffer_size=4mkey_buffemp_table_sizr_size=128me=128mread_bread_rnd_busort_bufferuffer_size=4ffer_size=16mmsize=32mmax _conn ecti ons =1024t

26、able cache=512inno db_ innodb key tmp_tab readbuffer read_ sort_max_c onnenn odb_additiodb_flush_loonal_mem_poog_at_trx_coml_size=8mmit=0log_buffer_size=4m_thread_c oneurre ncy 二 8_buffer_size=128mle_size=128msize=4mrn d_buffer_size=16mbuffer_size=32mctions=1024經(jīng)過測(cè)試.沒有特殊情況,最好還是用默認(rèn)的.2g 內(nèi)存 ，針對(duì)站多，抗壓型的設(shè)

27、置table_ca che=1024物理內(nèi)存越大，設(shè)置就越大.默認(rèn) 為2402,調(diào)到512- 1024最佳innodbadditi onalmem_pool_size=4m默認(rèn)為 2minnodb_flush _log_at_trx_commit= 1 （設(shè)置為0就是等到 innodb_log_buffer_s ize 列隊(duì)滿后再統(tǒng) 一儲(chǔ)存，默認(rèn)為1）innodbog_buffer_size=2m默 認(rèn) 為 1minnodb_thre ad_concurren cy=8 你的服務(wù)器cpu有兒個(gè)就設(shè)置為兒，建議用默認(rèn)一般為8key_buffer_size=256m默認(rèn)為218調(diào)到1 28最佳tmp_table_size=64m默認(rèn)為1 6m調(diào)到64-256最掛read_buffer_size=4m默認(rèn)為64kread_rn d_buffer_size=16m默認(rèn)為256ks