无码人妻视频一区二区三区,亚洲国产精品色一区二区三区色牛,亚洲国产中文精品碰碰,九九视频这里只有精品

1933瀏覽量

MySQL 性能優(yōu)化

來源: 時(shí)間:2014-01-04

數(shù)據(jù)庫屬于 IO 密集型的應(yīng)用程序,其主要職責(zé)就是數(shù)據(jù)的管理及存儲(chǔ)工作。而我們知道,從內(nèi)存中讀取一個(gè)數(shù)據(jù)庫的時(shí)間是微秒級(jí)別,而從一塊普通硬盤上讀取一個(gè)IO是在毫秒級(jí)別,二者相差3個(gè)數(shù)量級(jí)。所以,要優(yōu)化數(shù)據(jù)庫,首先第一步需要優(yōu)化的就是 IO,盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO。本文先從 MySQL 數(shù)據(jù)庫IO相關(guān)參數(shù)(緩存參數(shù))的角度來看看可以通過哪些參數(shù)進(jìn)行IO優(yōu)化

  • query_cache_size/query_cache_type (global)
  • Query cache 作用于整個(gè) MySQL Instance,主要用來緩存 MySQL 中的 ResultSet,也就是一條SQL語句執(zhí)行的結(jié)果集,所以僅僅只能針對(duì)select語句。當(dāng)我們打開了 Query Cache 功能,MySQL在接受到一條select語句的請(qǐng)求后,如果該語句滿足Query Cache的要求(未顯式說明不允許使用Query Cache,或者已經(jīng)顯式申明需要使用Query Cache),MySQL 會(huì)直接根據(jù)預(yù)先設(shè)定好的HASH算法將接受到的select語句以字符串方式進(jìn)行hash,然后到Query Cache 中直接查找是否已經(jīng)緩存。也就是說,如果已經(jīng)在緩存中,該select請(qǐng)求就會(huì)直接將數(shù)據(jù)返回,從而省略了后面所有的步驟(如 SQL語句的解析,優(yōu)化器優(yōu)化以及向存儲(chǔ)引擎請(qǐng)求數(shù)據(jù)等),極大的提高性能。

    當(dāng)然,Query Cache 也有一個(gè)致命的缺陷,那就是當(dāng)某個(gè)表的數(shù)據(jù)有任何任何變化,都會(huì)導(dǎo)致所有引用了該表的select語句在Query Cache 中的緩存數(shù)據(jù)失效。所以,當(dāng)我們的數(shù)據(jù)變化非常頻繁的情況下,使用Query Cache 可能會(huì)得不償失。

    Query Cache的使用需要多個(gè)參數(shù)配合,其中最為關(guān)鍵的是 query_cache_size 和 query_cache_type ,前者設(shè)置用于緩存 ResultSet 的內(nèi)存大小,后者設(shè)置在何場(chǎng)景下使用 Query Cache。在以往的經(jīng)驗(yàn)來看,如果不是用來緩存基本不變的數(shù)據(jù)的MySQL數(shù)據(jù)庫,query_cache_size 一般 256MB 是一個(gè)比較合適的大小。當(dāng)然,這可以通過計(jì)算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進(jìn)行調(diào)整。 query_cache_type可以設(shè)置為0(OFF),1(ON)或者2(DEMOND),分別表示完全不使用query cache,除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有顯示要求才使用query cache(使用sql_cache)。

  • binlog_cache_size (global)
  • Binlog Cache 用于在打開了二進(jìn)制日志(binlog)記錄功能的環(huán)境,是 MySQL 用來提高binlog的記錄效率而設(shè)計(jì)的一個(gè)用于短時(shí)間內(nèi)臨時(shí)緩存binlog數(shù)據(jù)的內(nèi)存區(qū)域。

    一般來說,如果我們的數(shù)據(jù)庫中沒有什么大事務(wù),寫入也不是特別頻繁,2MB~4MB是一個(gè)合適的選擇。但是如果我們的數(shù)據(jù)庫大事務(wù)較多,寫入量比較大,可與適當(dāng)調(diào)高binlog_cache_size。同時(shí),我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設(shè)置的binlog_cache_size是否足夠,是否有大量的binlog_cache由于內(nèi)存大小不夠而使用臨時(shí)文件(binlog_cache_disk_use)來緩存了。

  • key_buffer_size (global)
  • Key Buffer 可能是大家最為熟悉的一個(gè) MySQL 緩存參數(shù)了,尤其是在 MySQL 沒有更換默認(rèn)存儲(chǔ)引擎的時(shí)候,很多朋友可能會(huì)發(fā)現(xiàn),默認(rèn)的 MySQL 配置文件中設(shè)置最大的一個(gè)內(nèi)存參數(shù)就是這個(gè)參數(shù)了。key_buffer_size 參數(shù)用來設(shè)置用于緩存 MyISAM存儲(chǔ)引擎中索引文件的內(nèi)存區(qū)域大小。如果我們有足夠的內(nèi)存,這個(gè)緩存區(qū)域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引,以盡可能提高性能。

    此外,當(dāng)我們?cè)谑褂肕yISAM 存儲(chǔ)的時(shí)候有一個(gè)及其重要的點(diǎn)需要注意,由于 MyISAM 引擎的特性限制了他僅僅只會(huì)緩存索引塊到內(nèi)存中,而不會(huì)緩存表數(shù)據(jù)庫塊。所以,我們的 SQL 一定要盡可能讓過濾條件都在索引中,以便讓緩存幫助我們提高查詢效率。

  • bulk_insert_buffer_size (thread)
  • 和key_buffer_size一樣,這個(gè)參數(shù)同樣也僅作用于使用 MyISAM存儲(chǔ)引擎,用來緩存批量插入數(shù)據(jù)的時(shí)候臨時(shí)緩存寫入數(shù)據(jù)。當(dāng)我們使用如下幾種數(shù)據(jù)寫入語句的時(shí)候,會(huì)使用這個(gè)內(nèi)存區(qū)域來緩存批量結(jié)構(gòu)的數(shù)據(jù)以幫助批量寫入數(shù)據(jù)文件:

    insert … select …
    insert … values (…) ,(…),(…)…
    load data infile… into… (非空表)

  • innodb_buffer_pool_size(global)
  • 當(dāng)我們使用InnoDB存儲(chǔ)引擎的時(shí)候,innodb_buffer_pool_size 參數(shù)可能是影響我們性能的最為關(guān)鍵的一個(gè)參數(shù)了,他用來設(shè)置用于緩存 InnoDB 索引及數(shù)據(jù)塊的內(nèi)存區(qū)域大小,類似于 MyISAM 存儲(chǔ)引擎的 key_buffer_size 參數(shù),當(dāng)然,可能更像是 Oracle 的 db_cache_size。簡(jiǎn)單來說,當(dāng)我們操作一個(gè) InnoDB 表的時(shí)候,返回的所有數(shù)據(jù)或者去數(shù)據(jù)過程中用到的任何一個(gè)索引塊,都會(huì)在這個(gè)內(nèi)存區(qū)域中走一遭。

    和key_buffer_size 對(duì)于 MyISAM 引擎一樣,innodb_buffer_pool_size 設(shè)置了 InnoDB 存儲(chǔ)引擎需求最大的一塊內(nèi)存區(qū)域的大小,直接關(guān)系到 InnoDB存儲(chǔ)引擎的性能,所以如果我們有足夠的內(nèi)存,盡可將該參數(shù)設(shè)置到足夠打,將盡可能多的 InnoDB 的索引及數(shù)據(jù)都放入到該緩存區(qū)域中,直至全部。

    我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計(jì)算緩存命中率,并根據(jù)命中率來調(diào)整 innodb_buffer_pool_size 參數(shù)大小進(jìn)行優(yōu)化。

  • innodb_additional_mem_pool_size(global)
  • 這個(gè)參數(shù)我們平時(shí)調(diào)整的可能不是太多,很多人都使用了默認(rèn)值,可能很多人都不是太熟悉這個(gè)參數(shù)的作用。 innodb_additional_mem_pool_size 設(shè)置了InnoDB存儲(chǔ)引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小,所以當(dāng)我們一個(gè)MySQL Instance中的數(shù)據(jù)庫對(duì)象非常多的時(shí)候,是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率的。

    這個(gè)參數(shù)大小是否足夠還是比較容易知道的,因?yàn)楫?dāng)過小的時(shí)候,MySQL 會(huì)記錄 Warning 信息到數(shù)據(jù)庫的 error log 中,這時(shí)候你就知道該調(diào)整這個(gè)參數(shù)大小了。

  • innodb_log_buffer_size (global)
  • 這是 InnoDB 存儲(chǔ)引擎的事務(wù)日志所使用的緩沖區(qū)。類似于 Binlog Buffer,InnoDB 在寫事務(wù)日志的時(shí)候,為了提高性能,也是先將信息寫入 Innofb Log Buffer 中,當(dāng)滿足 innodb_flush_log_trx_commit 參數(shù)所設(shè)置的相應(yīng)條件(或者日志緩沖區(qū)寫滿)之后,才會(huì)將日志寫到文件(或者同步到磁盤)中。可以通過 innodb_log_buffer_size 參數(shù)設(shè)置其可以使用的最大內(nèi)存空間。
    注:innodb_flush_log_trx_commit 參數(shù)對(duì) InnoDB Log 的寫入性能有非常關(guān)鍵的影響。該參數(shù)可以設(shè)置為0,1,2,解釋如下:

    0:log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log file中,且同時(shí)會(huì)進(jìn)行文件系統(tǒng)到磁盤的同步操作,但是每個(gè)事務(wù)的commit并不會(huì)觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作;
    1:在每次事務(wù)提交的時(shí)候?qū)og buffer 中的數(shù)據(jù)都會(huì)寫入到log file,同時(shí)也會(huì)觸發(fā)文件系統(tǒng)到磁盤的同步;
    2:事務(wù)提交會(huì)觸發(fā)log buffer 到log file的刷新,但并不會(huì)觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外,每秒會(huì)有一次文件系統(tǒng)到磁盤同步操作。

    此外,MySQL文檔中還提到,這幾種設(shè)置中的每秒同步一次的機(jī)制,可能并不會(huì)完全確保非常準(zhǔn)確的每秒就一定會(huì)發(fā)生同步,還取決于進(jìn)程調(diào)度的問題。實(shí)際上,InnoDB 能否真正滿足此參數(shù)所設(shè)置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統(tǒng)以及磁盤本身的限制,可能有些時(shí)候在并沒有真正完成磁盤同步的情況下也會(huì)告訴 mysqld 已經(jīng)完成了磁盤同步。

  • innodb_max_dirty_pages_pct (global)
  • 這個(gè)參數(shù)和上面的各個(gè)參數(shù)不同,他不是用來設(shè)置用于緩存某種數(shù)據(jù)的內(nèi)存大小的一個(gè)參數(shù),而是用來控制在 InnoDB Buffer Pool 中可以不用寫入數(shù)據(jù)文件中的Dirty Page 的比例(已經(jīng)被修但還沒有從內(nèi)存中寫入到數(shù)據(jù)文件的臟數(shù)據(jù))。這個(gè)比例值越大,從內(nèi)存到磁盤的寫入操作就會(huì)相對(duì)減少,所以能夠一定程度下減少寫入操作的磁盤IO。

    但是,如果這個(gè)比例值過大,當(dāng)數(shù)據(jù)庫 Crash 之后重啟的時(shí)間可能就會(huì)很長(zhǎng),因?yàn)闀?huì)有大量的事務(wù)數(shù)據(jù)需要從日志文件恢復(fù)出來寫入數(shù)據(jù)文件中。同時(shí),過大的比例值同時(shí)可能也會(huì)造成在達(dá)到比例設(shè)定上限后的 flush 操作“過猛”而導(dǎo)致性能波動(dòng)很大。

上面這幾個(gè)參數(shù)是 MySQL 中為了減少磁盤物理IO而設(shè)計(jì)的主要參數(shù),對(duì) MySQL 的性能起到了至關(guān)重要的作用。
—EOF—
按照 mcsrainbow 朋友的要求,這里列一下根據(jù)以往經(jīng)驗(yàn)得到的相關(guān)參數(shù)的建議值:
  • query_cache_type : 如果全部使用innodb存儲(chǔ)引擎,建議為0,如果使用MyISAM 存儲(chǔ)引擎,建議為2,同時(shí)在SQL語句中顯式控制是否是喲你gquery cache
  • query_cache_size: 根據(jù) 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進(jìn)行調(diào)整,一般不建議太大,256MB可能已經(jīng)差不多了,大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大
  • binlog_cache_size: 一般環(huán)境2MB~4MB是一個(gè)合適的選擇,事務(wù)較大且寫入頻繁的數(shù)據(jù)庫環(huán)境可以適當(dāng)調(diào)大,但不建議超過32MB
  • key_buffer_size: 如果不使用MyISAM存儲(chǔ)引擎,16MB足以,用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲(chǔ)引擎,在內(nèi)存允許的情況下,盡可能將所有索引放入內(nèi)存,簡(jiǎn)單來說就是“越大越好”
  • bulk_insert_buffer_size: 如果經(jīng)常性的需要使用批量插入的特殊語句(上面有說明)來插入數(shù)據(jù),可以適當(dāng)調(diào)大該參數(shù)至16MB~32MB,不建議繼續(xù)增大,某人8MB
  • innodb_buffer_pool_size: 如果不使用InnoDB存儲(chǔ)引擎,可以不用調(diào)整這個(gè)參數(shù),如果需要使用,在內(nèi)存允許的情況下,盡可能將所有的InnoDB數(shù)據(jù)文件存放如內(nèi)存中,同樣將但來說也是“越大越好”
  • innodb_additional_mem_pool_size: 一般的數(shù)據(jù)庫建議調(diào)整到8MB~16MB,如果表特別多,可以調(diào)整到32MB,可以根據(jù)error log中的信息判斷是否需要增大
  • innodb_log_buffer_size: 默認(rèn)是1MB,系的如頻繁的系統(tǒng)可適當(dāng)增大至4MB~8MB。當(dāng)然如上面介紹所說,這個(gè)參數(shù)實(shí)際上還和另外的flush參數(shù)相關(guān)。一般來說不建議超過32MB
  • innodb_max_dirty_pages_pct: 根據(jù)以往的經(jīng)驗(yàn),重啟恢復(fù)的數(shù)據(jù)如果要超過1GB的話,啟動(dòng)速度會(huì)比較慢,幾乎難以接受,所以建議不大于 1GB/innodb_buffer_pool_size(GB)*100 這個(gè)值。當(dāng)然,如果你能夠忍受啟動(dòng)時(shí)間比較長(zhǎng),而且希望盡量減少內(nèi)存至磁盤的flush,可以將這個(gè)值調(diào)整到90,但不建議超過90

注:以上取值范圍僅僅只是我的根據(jù)以往遇到的數(shù)據(jù)庫場(chǎng)景所得到的一些優(yōu)化經(jīng)驗(yàn)值,并不一定適用于所有場(chǎng)景,所以在實(shí)際優(yōu)化過程中還需要大家自己不斷的調(diào)整分析,也歡迎大家隨時(shí)通過 Mail 與我聯(lián)系溝通交流優(yōu)化或者是架構(gòu)方面的技術(shù),一起探討相互學(xué)習(xí)。

聯(lián)系我們

一次需求提交或許正是成就一個(gè)出色產(chǎn)品的開始。
歡迎填寫表格或發(fā)送合作郵件至: qczsky@126.com

大理青橙科技

電話:13988578755 13988578755

郵箱:qczsky@126.com

地址:大理市下關(guān)龍都春天10層

如果您無法識(shí)別驗(yàn)證碼,請(qǐng)點(diǎn)圖片更換

梅河口市| 屏山县| 宁安市| 常山县| 溆浦县| 拉萨市| 玛纳斯县| 涞源县| 临漳县| 衡水市| 从江县| 乾安县| 横峰县| 宜春市| 故城县| 水城县| 乌鲁木齐县| 浑源县| 张家界市| 当雄县| 宁强县| 邯郸县| 文安县| 庄河市| 荥经县| 屯门区| 陆良县| 福贡县| 汤阴县| 丰县| 陆丰市| 嘉定区| 西宁市| 阳新县| 砚山县| 镇江市| 恩施市| 聂荣县| 四子王旗| 阿勒泰市| 仙游县|