MYSQL6查詢性能測(cè)試TPCH

1、5.6查詢性能測(cè)試報(bào)告網(wǎng)易DBA王洪權(quán)MydbalifeTPC-H介紹：TPC-H測(cè)量在商業(yè)分析中決策支持系統(tǒng)（DSS）的性能。DSS是一種計(jì)算機(jī)應(yīng)用程序，它分析商業(yè)數(shù)據(jù)展現(xiàn)出來使用戶/管理者可以更容易得進(jìn)行商業(yè)決策，例如供求管理、客戶滿意度分析、市場(chǎng)份額分析等。TPC-H模擬了商業(yè)環(huán)境中的分析端，大量的數(shù)據(jù)被細(xì)化，可以幫助企業(yè)進(jìn)行可靠的商業(yè)決策，其中包含一整套面向商業(yè)的特殊查詢和并發(fā)數(shù)據(jù)修改內(nèi)容。該基準(zhǔn)中選擇的查詢 和數(shù)據(jù)庫中的數(shù)據(jù)都具有廣泛的全行業(yè)關(guān)聯(lián)性。這種基準(zhǔn)測(cè)試所描述的決策支持系統(tǒng)可檢查大量的數(shù)據(jù)，所執(zhí)行的查詢也具有很高的復(fù)雜度。并且，TPC-H會(huì)基于數(shù)據(jù)庫的大小將結(jié)果分類.TPC-

2、H是決策支持?jǐn)?shù)據(jù)庫的基準(zhǔn)測(cè)試，它包含了一整套面向商業(yè)的ad-hoc查詢和并發(fā)數(shù)據(jù)修改，強(qiáng)調(diào)測(cè)試的是數(shù)據(jù)庫、平臺(tái)和I/O性能，關(guān)注查詢能力。Tpc-h數(shù)據(jù)庫由定義好的八個(gè)表組成Tpc-H數(shù)據(jù)模型測(cè)試環(huán)境DELL PowerEdge 1750內(nèi)存大小2GMysql版本：5.6.5-m8-log MySQL Community Server (GPL)(最新)5.5.23-55-log Percona Server (GPL), Release rel25.3, Revision 240 ( percona最新)Tpch庫生成：1 下載 tpc_h, /tpch/s

3、pec/tpch 2 8 0.zip :2創(chuàng)建MySQL用戶、數(shù)據(jù)庫、及授權(quán)mysql -u root -pmysql> CREATE USER 'tpch''%' IDENTIFIED BY 'tpch'mysql> CREATE DA TABASE tpch;mysql> GRANT ALL ON tpch.* to 'tpch''%'3然后在tpch文件目錄下，把 makefile復(fù)制并改名成 makefile，接著修改 makefile文件 shell> cp makefile.su

4、ite makefile shell> vim makefilemakefile中相應(yīng)項(xiàng)后面填寫：CC = gcc # Current values for DA TABASE are: INFORMIX, DB2, TDAT (Teradata)# Current values for MACHINE are:# Current values for WORKLOAD are:DATABASE= SQLSERVERMACHINE = LINUXWORKLOAD = TPCH4修改tpch.h文件修改其中的 SQLSERVER段為：#ifdef SQLSERVER#define GEN_

5、QUERY_PLAN#define START_TRAN#define END_TRAN#define SET_OUTPUT#define SET_ROWCOUNT#define SET_DBASE#endif執(zhí)行make;5生成需要用的數(shù)據(jù)shell> ./dbgen -s 1-s數(shù)據(jù)規(guī)模因子，1為1G的數(shù)據(jù)量；這步驟在dbgen下邊生成數(shù)據(jù)，以tblSQLSERVER, SYBASEATT, DOS, HP, IBM, ICL, MVS,SGI, SUN, U2200, VMS, LINUX, WIN32 TPCH"EXPLAIN;""START TRA

6、NSACTION;n""COMMIT;n”"""limit %d;n""use %s;n"結(jié)尾。6創(chuàng)建庫中相關(guān)表mysql -e "source /home/tpch/dbgen/dss.ddl"載入數(shù)據(jù)myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINA TED BY '|'"myqsl -e "LO

7、AD DATA INFILE '/home/tpch/dbgen/orders.tbl' INTO TABLE ORDERS FIELDSTERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINA TED BY '|'"myqsl -e "LOAD DA TA INFILE '/home/tpch/dbgen/nation

8、.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINA TED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/part.tbl' INTO TABLE PART FIELDS TERMINATED B

9、Y '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/region.tbl' INTO TABLE REGION FIELDSTERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINA TED BY '|'"7修改tpch目錄下的dss.ri文件1. 刪除&

10、quot;CONNECT TO TPCD;"2. 刪除所有的"TPCH.”(注意有個(gè)點(diǎn))3. 刪除所有的"COMMIT WORK;”(注意分號(hào)也要?jiǎng)h除)8執(zhí)行該文件，創(chuàng)建相關(guān)約束。myqsl -e "source /home/tpch/dbgen/dss.ri"實(shí)際上dss.ri這個(gè)文件是為庫中的表添加相關(guān)約束，先添加主鍵，后添加外鍵。Dss.ri文件內(nèi)容如下：-For table REGIONALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);-For table NATIONALTER TABL

11、E NATION ADD PRIMARY KEY (N_NATIONKEY);-For table PARTALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);-For table SUPPLIERALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);-For table PARTSUPPALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);-For table CUSTOMERALTER TABLE CUSTOMER ADD PRIMARY K

12、EY (C_CUSTKEY);-For table LINEITEMALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);-For table ORDERSALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);為各個(gè)表添加外鍵。ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) references REGION(R_REGIONKEY);ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY

13、) referencesNATION(N_NATIONKEY);ALTER TABLECUSTOMERADDFOREIGNKEY(C_NATIONKEY)referencesNATION(N_NATIONKEY);-For table PARTSUPPALTER TABLEPARTSUPPADDFOREIGNKEY(PS_SUPPKEY)referencesSUPPLIER(S_SUPPKEY);ALTER TABLEPARTSUPPADDFOREIGNKEY(PS_PARTKEY)referencesPART(P_PARTKEY);-For table ORDERSALTER TABLEOR

14、DERSADDFOREIGNKEY(O_CUSTKEY)referencesCUSTOMER(C_CUSTKEY);-For table LINEITEMALTER TABLELINEITEMADDFOREIGNKEY(L_ORDERKEY)referencesORDERS(O_ORDERKEY);ALTER TABLE LINEITEM ADDFOREIGN KEY(L_PARTKEY,L_SUPPKEY)referencesPARTSUPP(PS_PARTKEY, PS_SUPPKEY);由于查詢中使用的是小寫表名，而使用dss.ddl生成的表名是大寫的，所以轉(zhuǎn)換表名成小寫alter ta

15、ble NATION rename nation;alter table SUPPLIER rename supplier;alter table REGION rename region;alter table PARTSUPP rename partsupp;alter table PART rename part;alter table ORDERS rename orders;alter table LINEITEM rename lineitem;alter table CUSTOMER rename customer;cd /home/tpch/dbgen/queries這下已經(jīng)成

16、了 16個(gè)sql文件，為測(cè)試性能sql.可以放在數(shù)據(jù)庫內(nèi)通過source執(zhí)行，也可以采用以下方式執(zhí)行。./qgen -c tpch -s 1 1（這步可以這樣，也可以直接在mysql那里執(zhí)行：source /home/asd/1.sql）MRR(Multi Range Read ) 測(cè)試測(cè)試語句:DBT-3? QUERY 10select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment fromcustom

17、er, orders, lineitem, nation wherec_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date('1993-10-01') and o_orderdate < date('1993-10-01') + '3 month' and l_returnflag = 'R'and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal,

18、c_phone, n_name, c_address, c_comment order by revenue desc執(zhí)行計(jì)劃5.6中執(zhí)行計(jì)劃idhselect-tyoet加t推釁沌七倬加？1fey.| 聽1SIMPLE11洲供尸口倘工郎我七:頃住冊(cè)如CJ.DERDATE:NULL1頸iLStcm：reqjrF捋河尿辦3PF.：WY1SDiPLE低ticP俏PE橢11匚機(jī)Eliehm，西L皿配帝l15.5中執(zhí)行計(jì)劃idJSElst_typeLjtabletypepossibldceysIkeyLJkeyjen芷f| r郵rH:r-HINPLErH做懺r-"HrangerPF.：Rvj

19、0_CJSTKE7,D.0RDER0ATC 瓦鄴汩r-HOJ)R)ERD4TEr 3- - -NLL133215IHP.Eojncmereq_refmw41|：h.3fder5,O_CLSTKEY11SIMPLEnaticieqjefPHI傾FFW4tpch.custmer.CJttTIONkEY11SIMPLElineitEfti音LJORDERKEYIJWER 舊4tF：h,order5,0_0RDERKE1Extrausing where; using Temporary; using filesortusing where測(cè)試為了測(cè)試的目的，我使用了TPC-H Query #10并且運(yùn)

20、行他在 TPC-H數(shù)據(jù)。的配置如下。同時(shí)注意在mysqloptimizer_switch=optimizer_switch=optimizer_switch=優(yōu)化器設(shè)置：'index_condition_pushdown=off'mrr=on '' mrrcostbased=off ' read_rnd_buffer_size=4M (only on MySQL 5.6)注意在這里我關(guān)閉了 ICP優(yōu)化這個(gè)特性，目的是為了達(dá)到測(cè)試的目的，因?yàn)槲蚁氩榭?一下單個(gè)的MRR對(duì)于性能的的影響， 同時(shí)也注意我把 mrr_cost_based給關(guān)掉了。這是因?yàn)?基于成

21、本的算法在選擇執(zhí)行計(jì)劃的時(shí)候計(jì)算MRR花費(fèi)的成本。MRR使用的場(chǎng)景見下圖:在內(nèi)存負(fù)載為主的情況下現(xiàn)在讓我們看看當(dāng)所要查詢的數(shù)據(jù)完全裝在到內(nèi)存中時(shí)候，MRR工作是怎么樣的。測(cè)試采用了以內(nèi)存工作為主的情況，InnoDB緩沖池的大小設(shè)置為512M ,緩沖池中所有的數(shù)據(jù)已經(jīng)預(yù)熱，使相關(guān)數(shù)據(jù)頁已經(jīng)完全加載在緩沖池中。注意，在開始看基準(zhǔn)測(cè)試結(jié)果之前，我 們確定了要查詢的InnoDB數(shù)據(jù)集的大小是大約1.2G.完全在內(nèi)存的時(shí)候：可以看到查詢時(shí)間基本上差不多。統(tǒng)計(jì)值查詢耗時(shí)柱狀圖如下:Mysql 5.52min 16.90sMysql 5.6 啟 用MRR2m14sIO為主的情況下，設(shè)置BP大小為512M,數(shù)

22、據(jù)不能完全裝在在BP中的時(shí)候：MySQL5.6CounterMySQL5.5MySQL5.6MySQL5.5 read rnd bufeNameCreated_tr_size=4Mread_rnd_bufer_size=4Mmp_disk_t ablesHandler r0000ead_keyHandler r283752340796283752340796ead_nextHandler_r286093286093286093286093ead_rnd_n ext Innodb_bu ffer pool37944379443794437944read_ahea d017776017776Inn

23、odb_buffer_pool_ read_requ ests1551628146196415513841461847Innodb_buffer_pool_77336403717734740370readsInnodb_da ta_read1269256192(1.2G)954863616(910M)1269436416(1.2G)954847232(910M)Innodb_da ta_reads77350581617736158160Innodb_pa ges_read77335581467734658145Innodb_ro ws_read398157455197398157455197S

24、elect_sca n1111Sort_scan1111查詢耗時(shí)10m57.584s2m18.757s6m31.233s2m43.835s注意這里有兩個(gè)參數(shù)需要理解：Handler_read_key Innodb_rows_read (為什么MRR會(huì)更多呢？)因?yàn)檎５脑挍]有啟用MRR的情況下，會(huì)先讀2級(jí)別索引，找出rowid然后再讀以及索引，找到具體記錄。這個(gè)過程會(huì)調(diào)用存儲(chǔ)引擎，所以調(diào)用影響的參數(shù) Handler_read_XXX ,和 Innodb_rows_read這2個(gè)參數(shù)每調(diào)用一次會(huì)分別加1, MRR讀記錄的時(shí)候會(huì)分開調(diào)用，會(huì)引起Handler_read_XXX，和Innodb_ro

25、ws_read分別加2，這會(huì)看起來 MRR做事情更糟糕， 實(shí)時(shí)上不是的，查詢?nèi)詥巫x同一個(gè)索引的表的記錄，MRR使得磁盤的讀更加順序。從表格中可以看出 Innodb_buffer_pool_reads(物理讀次數(shù))，5.5(77336) VS 5.6 (40370),可 以得出物理讀越來越少，因?yàn)槭褂昧薓RR,使得數(shù)據(jù)訪問變得順序，避免了對(duì)同一個(gè)數(shù)據(jù)頁的多次訪問，Innodb_buffer_pool_read_ahead (預(yù)讀的次數(shù))，5.5 (0) VS 5.6 ( 17776)可 以看出，5.6由于使用了 MRR,使得訪問格式變得順序，因次預(yù)讀次數(shù)大大增加。最后再從Innodb_data_

26、read (數(shù)據(jù)讀取總量來看)，5.5 (1.2G) VS 5.6 (910M )，可以看出5.6 大大的減少了數(shù)據(jù)的讀取量。因此可以得出MRR使得大量的隨即讀取變得順序，在順序訪問的同時(shí)，因?yàn)?mysql有預(yù)讀的功能，所以會(huì)看到預(yù)讀次數(shù)會(huì)大大增加。統(tǒng)計(jì)值查詢耗時(shí)Mysql 5.510m57.584sMysql 5.62m18.757sMySQL5.5 read_rnd_buf er_size=4M 6m31.233sMySQL5.6 read_rnd_buf er_size=4M 2m43.835s柱狀圖如下:NysqlS, 5lysdl 5. 6啟用眼KMyEQl 5,5 (read_rn

27、OLifer_size (4H)總結(jié):經(jīng)過測(cè)試，發(fā)現(xiàn)新版本的 mysql 5.6開啟MRR的預(yù)熱要比 mysql 5.5.23預(yù)熱時(shí)間快接近5倍。但是也可以看出MRR也很依賴于read_rnd_bufer_size的大小，當(dāng)設(shè)置一定大的時(shí)候，性能提升很大，MRR使得隨即讀取轉(zhuǎn)換成順序讀，大大的減少了數(shù)據(jù)的訪問量，節(jié)省了 BP的資源，同時(shí)采用 MRR也可以使得查詢時(shí)間大大縮短。MRR測(cè)試腳本：#!/bin/bashsed -i 's/innodb_buffer_pool_size = 512M/innodb_buffer_pool_size = 2M/' /etc/f grep

28、-i innodb_buffer_pool_size /etc/f service mysql restartmysql -e "flush status;"|grep -v "Logging"mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handl er_read_key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_p

29、ool_read_ahead|Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innod b_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'sed -i 's/innodb_buffer_pool_size = 2M/innodb_buffer_pool_size = 512M/' /etc/f grep -i innodb_buffer_

30、pool_size /etc/f service mysql restartmysql -e "set global optimizer_switch='index_condition_pushdown=off;"|grep -v "Logging"mysql -e "set global optimizer_switch='mrr=on'"|grep -v "Logging"mysql -e "set global optimizer_switch='mrr_cost_b

31、ased=off;"|grep -v "Logging"#mysql -e "set global read_rnd_buffer_size=4194304;"|grep -v "Logging" sh sar.sh & sh iostat.sh &time mysql -e "use tpch;select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount) as revenue, c_acctbal, n_name, c_addre

32、ss, c_phone, c_commentfromcustomer, orders, lineitem, nationwherec_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1993-08-01'and o_orderdate < date_add( '1993-08-01' ,interval '3' month)and l_returnflag = 'R'and c_nationkey = n_nationkeygrou

33、p byc_custkey, c_name, c_acctbal, c_phone, n_name,c_address,c_commentorder byrevenue descLIMIT 20;"|grep -v "Logging"|tail -n3|egrep -w "real|user|sys"Mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handl er_read_

34、key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_ buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innod b_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'BKA ( Batched Key Acces® 測(cè)試Benchmark :出于測(cè)試的目

35、的，我使用了 TPC-H Query #3并且運(yùn)行它在 TPC-H數(shù)據(jù)庫，innodb dataset 大小317M注意出于測(cè)試的目的查詢緩存的cache也是禁用。這里分別測(cè)試了默認(rèn)情況下，兩種版本查詢的不同，還有分別調(diào)整了join_buffer_size和read_rnd_bufer_size的情況下，性能的對(duì)比。MySQL 5.6配置如下：optimizer_switch='index_condition_pushdown=off optimizer_switch='mrr=on' optimizer_switch='mrr_cost_based=off o

36、ptimizer_switch='batched_key_access=on' join_buffer_size=6M read_rnd_buffer_size=6M注意我這里關(guān)閉了ICP由于我想只看 BKA算法對(duì)于查詢時(shí)間的影響。因?yàn)?BKA 一來于MRR接口，所以這里我打開了MRR。數(shù)據(jù)集大小2.5G, BP大小設(shè)置為512M.查詢使用TPC-H Query #3 selectl_orderkey,sum(l_extendedprice * (1 - l_discount) as revenue, o_orderdate, o_shippriorityfromcustome

37、r, orders, lineitem FORCE INDEX (L_ORDERKEY) wherec_mktsegment = 'AUTOMOBILE' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < '1995-03-09' and l_shipdate > '1995-03-09'group byl_orderkey, o_orderdate, o_shippriority order byrevenue desc, o_orderd

38、ate LIMIT 3;5.6執(zhí)行計(jì)劃如下：I id | stlslt/pt | taHe | type | pssibldeys | 好 | ley.len | rr| m Extra十十t+t1SWFLEMLF»'WJLNUL150M1Using 岫 UsingUsing fihsort1SI1FLE0挑refm(4tpci. customer?Using 曲國 Jsiig join bra (latcheil 時(shí)頊:優(yōu)時(shí)1HSMFLE1血叫refHHLJfOEEBlweiw4tpcixrdefs.UB®'B t加g柚弓Jsiig ：dih Wfr 3拙

39、恭州工扇.，T一T5.5執(zhí)行計(jì)劃如下id | selKtJype | table | type | 哪派怫 | key | 晚加 | ref| 顧 | EM咫郵 kULL hull null1(953? using mponry; jrng;jl昭鞠虹助餌 ojot i圳mtaiteKOJRn1 .ihgtee'SDMHLMfflUt附尚波觸炯2 Using toe完全在內(nèi)存中的情況Mysql 5.6 啟Mysql用Mysql5.5(join buf統(tǒng)計(jì)值fer_size(128BKA(join_bu ffer_size(15.5(join_buffer_size(6M)K),read

40、 rnd'28K),read_r,read_rnd_bufer_size(6bufersize(nd_bufer_siM)256K)ze(256K)查詢耗時(shí)734.140s712.655s740.137sMysql 5.6 啟用BKA(join_buffer_size(6M),read_rnd_bufer_size(6M)324.877s柱狀圖如下:數(shù)據(jù)在cache中的時(shí)間時(shí)間C/s)不完全在內(nèi)存中的情況如下Counter NameMySQL5.5 默認(rèn) 配置(join_buffer_s ize=128K read_rnd_bufer _size=128K)MySQL5.6 默認(rèn) jo

41、in_buffer_siz e=128K read_rnd_bufer _size=256KMySQL5.5 join_buffer_siz e=6M read_rnd_bufer _size=6MMySQL5.6 join_buffer_size=6 M read_rnd_bufer_siz e=6MCreated_tmp_0000disk_tablesHandler_read_20394410767462039441076746keyHandler_read_ next Handler_read_ rnd_next Innodb_buffer _pool_read_ah ead Innod

42、b_buffer _pool_read_re quests Innodb_buffer _pool_reads Innodb_data_r ead Innodb_data_r eads Innodb_pages _read Innodb_rows_ read Select_scan Sort_scan 查詢耗時(shí)872798161500172641414201648882731986944(2.5G)16662716661310227982120m10.271s87279887279887279816423716150016150016631728658263898204414121233605

43、74180491164838135842986594304(2.727312005121303236608(1.2G)G)(2.5G)182167166579794231821531665657940918955961022798189559622211119m55.038s18m39.165s6m16.147s重點(diǎn)關(guān)注對(duì)象Innodb_buffer_pool_read_ahead ,這個(gè)參數(shù)說明了訪問的順序變的越來越順序了。Innodb_buffer_pool_reads 不能從BP中讀入數(shù)據(jù)的時(shí)候，從物理磁盤讀數(shù)據(jù)到內(nèi)存中的次Innodb_data_read至此已經(jīng)讀取的數(shù)據(jù)數(shù)量（字節(jié)）從

44、表格中的數(shù)據(jù)可以看出，Innodb_buffer_pool_read_ahead （預(yù)讀的頁的頁數(shù)），可以看出，5.6 IO的訪問格式上來看變得越來越順序了，這個(gè)也很能理解，因?yàn)?BKA使用了 MRR接口，從Innodb_data_read （總計(jì)讀的數(shù)據(jù)量）和 Innodb_data_reads （物理讀的次數(shù)） 這兩個(gè)參數(shù)可以看出，5.6中數(shù)據(jù)讀取越來越少，5.5 （2.5G） VS 5.6（1.2G,從數(shù)據(jù)讀取量上來說5.6是5.5的一半，物理讀5.5（166627） vs 5.6（79423）上來看，5.6基本上物理讀是 5.5的1/2, 再從buffer大小對(duì)于查詢時(shí)間上的影響可以看

45、出，BKA 很依賴于 join_buffer_size 和read_rnd_buffer_size，如果采用系統(tǒng)默認(rèn)的值的時(shí)候，5.5和5.6的查詢時(shí)間相差較小，加大這兩個(gè)變量，可以使得查詢性能呈現(xiàn)直線上升。數(shù)據(jù)不在cache中查詢耗時(shí):Mysql 5.6 啟Mysql用Mysql5.5(join buf統(tǒng)計(jì)值fer_size(128BKA(join_bu ffer_size(15.5(join_buffer_size(6M)K),read rnd'28K),read_r,read_rnd_bufer_size(6bufer size(nd_bufer_siM)256K)ze(256K

46、)查詢耗時(shí)1210.271s1195.038s1119.165sMysql 5.6 啟用BKA(join_buffer_size(6M),read_rnd_bufer_size(6M)376.47s柱狀圖如下:數(shù)據(jù)不在cache中的查詢時(shí)間口數(shù)據(jù)不在。配詭中的查詢時(shí)間時(shí)間(/s)結(jié)論：BKA算法在以IO為主的查詢能大大的縮短查詢時(shí)間，BKA可以大大減少物理讀的次數(shù)，節(jié)省了大量寶貴的內(nèi)存資源，有利于BP更好的緩沖熱點(diǎn)數(shù)據(jù)，但是以內(nèi)存為主的查詢的性能提高不是很大，BKA 算法取決于join_buffer_size和read_rnd_bufer_size的設(shè)置，會(huì)隨著設(shè)置的大小，查詢時(shí)間呈現(xiàn)明顯縮短

47、。BKA測(cè)試腳本#!/bin/bashsed -i 's/innodb_buffer_pool_size = 512M/innodb_buffer_pool_size = 2M/' /etc/fgrep -i innodb_buffer_pool_size /etc/fservice mysql restartmysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handler_read_key|Handler_r

48、ead_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innodb_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'sed -i 's/innodb_buffer_pool_size = 2M/innodb_buffer_pool_si

49、ze = 512M/' /etc/fgrep -i innodb_buffer_pool_size /etc/fservice mysql restartmysql -e "set global join_buffer_size=6291456;"|grep -v "Logging"mysql -e "set global read_rnd_buffer_size=6291456;"|grep -v "Logging"a='mysqladmin ext|grep -i Innodb_buffer_p

50、ool_pages_data|awk '(print $4'、mysql -e "flush status;"|grep -v "Logging"sh sar.sh &sh iostat.sh &time mysql -e "use tpch;selectl_orderkey,sum(l_extendedprice * (1 - l_discount) as revenue, o_orderdate,o_shippriorityfromcustomer,orders,lineitem FORCE INDEX (L

51、_ORDERKEY)wherec_mktsegment = 'AUTOMOBILE'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < '1995-03-09'and l_shipdate > '1995-03-09'group byl_orderkey,o_orderdate,o_shippriorityorder byrevenue desc,o_orderdateLIMIT 10;"|grep -v "Logging&qu

52、ot;|tail -n3|egrep -w "real|user|sys”b='mysqladmin ext|grep -i Innodb_buffer_pool_pages_data|awk '(print $4'、c='expr $b - $a'mysql -e "select $c*16/1024/1024”mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Ha

53、ndler_read_key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_ buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innodb_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'ICP 測(cè)試(index condition push down)Mysql

54、5.6配置如下：mysql -e "set global optimizer_switch='index_condition_pushdown=on'"|grep -v "Logging5.5保持默認(rèn)配置測(cè)試兩個(gè)版本的 mysql均保持默認(rèn)設(shè)置，采用 TPC-H query 19進(jìn)行壓力測(cè)試。TPC-H Query #19 語句如下：selectsum(l_extendedprice* (1 - l_discount) as revenuefromlineitem force index(i_l_partkey),partwhere(p_partk

55、ey = l_partkeyand p_brand = 'Brand#45'and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')and l_quantity >= 1 and l_quantity <= 1+10and p_size between 1 and 5and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER I

56、N PERSON')or(p_partkey = l_partkeyand p_brand = 'Brand#24'and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')and l_quantity >= 14 and l_quantity <= 14+10and p_size between 1 and 10and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERS