愿為雙鴻鵠奮翅起_第1頁(yè)
愿為雙鴻鵠奮翅起_第2頁(yè)
愿為雙鴻鵠奮翅起_第3頁(yè)
愿為雙鴻鵠奮翅起_第4頁(yè)
愿為雙鴻鵠奮翅起_第5頁(yè)
已閱讀5頁(yè),還剩36頁(yè)未讀 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

AdatabaseDirection:PostgreSQL,MySQL,Greenplum,Blog: a2MySQL與PostgreSQLMySQLV5.7什么要改進(jìn)代價(jià)模?MySQLV5.7代價(jià)模型有什么變MySQLV5.7代價(jià)模型解決了哪些問(wèn) MySQLV5.7的代價(jià)

365worklogs,added1007MTRtests,fixed2812bug Performance&Scalability:ImprovedInnoDBscalabilityandtemporarytableperformance,enablingfasteronlineandbulkloadoperations,andmore. JSONSupport:WiththenewlyaddedJSONsupportinMySQL,youcannowcombinetheflexibilityofNoSQLwiththestrengthofarelationaldatabase.pGIS:Comple ynewinMySQL5.7andincludingInnoDBspatialindexes,useofBoost.Geometry,alongwithincreasedcompletenessandstandardcompliance. Replicationimprovementsforincreasedavailabilityandperformance.Theyincludemulti-sourcereplication,multi-threadedslaveenhancements,onlineGTIDs,andenhancedsemi-syncreplication. PerformanceSchemadeliveringmuchbetterinsights.We’veaddednumerousnewmonitoringcapabilities,reducedthefootprintandoverhead,andsignificantlyimprovedeaseofusewiththenewSYSSchema. Security:Wearefulfilling“securebydefault”requirementsandmanynewMySQL5.7featureswilluserskeeptheirdatabase Optimizer:Wehaverewrittenlargepartsoftheparser,optimizer,andcostmodel.Thishasimprovedmaintainability,extendability,andperformance. MySQLV5.7的代價(jià)什么是代The“cost”inMySQLisan numbercloselyrelatedtothecost ngarandomreadfromaHDD.Atthetime,thiswasanobviouschoicesincethemaingoalwastoefficientlyutilizethediskbandwidth.Inthefuturewewouldprobablyliketorelatecosttosomethingelsethatmakesmoresensetousers,e.g.wallclocktime. MySQLV5.7的代價(jià)常規(guī)的代價(jià)總代=IO+CPU代價(jià)COST=P*a_page_io_time+W*s*P為計(jì)劃運(yùn)行 MySQLV5.7的代價(jià)p通常,計(jì)算的是IO代價(jià)和CPU使使用文件排序時(shí)的IO代價(jià)計(jì)doubleget_merge_cost(ha_rowsnum_elements,ha_rowsnum_buffers,uintelem_size,constCost_model_table*cost_model){constdoubleio_ops=static_cast<double>(num_elements*elem_size)/IO_SIZE;constdoubleio_cost=cost_model->io_block_read_cost(io_ops);constdouble pare_cost(num_elements*log((double)num_buffers)使用使用文件排序時(shí)的CPU代價(jià)return2*io_cost+使用使用文件排序時(shí)的總代價(jià) MySQLV5.7的代價(jià)pIO和CPU代價(jià),分別有不同的計(jì)Computethecostofaquick_group_min_max_selectforaparticular分組分組操作,利用索引的代價(jià)void constCost_model_table*constcost_model=table-cost_est->add_io(cost_model-IO代IO代價(jià)的計(jì)1.0constdoubletree_traversal_cost=cost_model- CPU代價(jià)cost_model-CPU代價(jià) MySQLV5.7的代價(jià)constCost_model_table*cost_model){constdoubleio_ops=static_cast<double>(num_elements*elem_size)/constdoublecpu_cost= return2*io_cost+}void constCost_model_table*constcost_model=table-constdoubletree_height=table_records==01.0 constdoublecpu_cost=num_groups*(tree_traversal_cost

計(jì)算方不同操作的具體的和CPU代價(jià)的計(jì)算方 MySQLV5.7的代價(jià)MySQL的代價(jià)模型(V5.6、 操作的價(jià)四個(gè)代價(jià)計(jì)四個(gè)代價(jià)計(jì)算的{doubleio_cost;doublecpu_cost;doublemem_cost;

///<costofI/O///<costofCPU///<costofremote///<memoryused///Returnssumoftime-consumingcosts,i.e.,notcountingmemorycostdoubletotal_cost()const{returnio_cost+cpu_cost+import_cost;}doubleget_io_cost() const{returnio_cost;}內(nèi)存代價(jià)暫沒(méi)有使doubleget_cpu_cost() const{returncpu_cost;}doubleget_import_cost()const內(nèi)存代價(jià)暫沒(méi)有使 MySQLV5.7的代價(jià)MySQL的代價(jià)模型(V5.6、 操作的代uint*buffer_size,Costofmemoryisnotincludedinthetotal_cost()functionandthuswillnotbeconsideredwhencomparingcosts.Still,werecorditinthecostestimateobjectforfutureuse.內(nèi)存代價(jià)計(jì)內(nèi)存代價(jià)計(jì)算但沒(méi)有 MySQLV5.7的代價(jià)MySQL的代價(jià)模型(V5.6、總代價(jià)=IO代價(jià)+CPU代價(jià)+ 標(biāo)準(zhǔn)的代價(jià)模 MySQL主節(jié) 節(jié)點(diǎn)(MySQL 計(jì)算 總 總數(shù)代 代被 據(jù)加

IO減

部 數(shù) 被代 存 PostgreSQL的代價(jià)模PostgreSQL的代價(jià)模型總代IOCPU代價(jià)啟動(dòng)MySQLMySQL總部數(shù)考慮typedefstruct{}typedefstruct{}/*tagidentifyingscan/joinmethod/*therelationthispathcanbuildParamPathInfo*param_info;/*parameterizationinfo,orNULLifnone/*estimatedsize/costsforpath(seecostsize.cformoreinfo)/*estimatednumberofresulttuples/*costexpendedbeforefetchinganytuples /*totalcost(assumingalltuplesfetched)/* /*sortorderingofpath'soutputsisaListofPathKeynodes;seeabove PostgreSQL的代價(jià)模順序掃 PostgreSQL的代價(jià)模索引掃索引掃 ToprowDB的代價(jià)模ToprowDB的代價(jià)模型總代價(jià)=IO代價(jià)+CPU代價(jià)+ 標(biāo)準(zhǔn)的代價(jià)模 主節(jié)

節(jié)點(diǎn) 計(jì)算 總 總數(shù)代 代被 據(jù)加

IO減

部 數(shù) 被代 存 ToprowDB的代價(jià)模 MySQLV5.7什么要改進(jìn)代價(jià)

larger

新的硬件架構(gòu) 與 引擎緊密綁 MySQLV5.7什么要改進(jìn)代價(jià)/*V5.6/*V5.7.5classclass///ThecostofoneI/OstaticdoubleIO_BLOCK_READ_COST(){return1.0;m_io_block_read_cost;///Costconstantforreadingarandomdiskdouble 代價(jià)模型,需要與時(shí)俱WL#7315:Optimizercostmodel:mainmemorymanagement代價(jià)模型,需要與時(shí)俱 PostgreSQLV9.4.1支持新 設(shè)備嗎

seq_page_cost=DEFAULT_SEQ_PAGE_COST;random_page_cost=DEFAULT_RANDOM_PAGE_COST;cpu_tuple_cost=DEFAULT_CPU_TUPLE_COST;cpu_operator_cost=DEFAULT_CPU_OPERATOR_COST;{{"random_page_cost",PGC_USERSET,QUERY_TUNING_COST,gettext_noop("Setsthe nner'sestimateofthecostofa"NULL,NULL,NULL{{"cpu_tuple_cost",PGC_USERSET,QUERY_TUNING_COST,gettext_noop("Setsthe nner'sestimateofthecostof"

變量可配支持新 設(shè)NULL,NULL,NULL

"processingeachtuple MySQLV5.7價(jià)模型有什么變mysql>select*from | |cost_value| |comment | NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| NULL|2015-09-2315:58:28| |memory_temptable_create_cost NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| 2rowsinset(0.01ssrrr層 MySQLV5.7價(jià)模型有什么變mysql>select*from | |cost_value| |comment | |

NULL|-09-2315:58:28| -09-2315:58:28| 層NULL|2015-09-2315:58:28-09-2315:58:28| -09-2315:58:28| 層NULL|2015-09-2:2r|L|atesforgeneral|memory_temptable_create_cost NULL||

NULL|2015-09-2315:58:28|NULLoperations| NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28|

存儲(chǔ)引擎層 2rowsinset(0.01

Optimizercostestimatesforoperationsspecifictoparticularstorageengines MySQLV5.7價(jià)模型有什么變mysql>select*from | |cost_value| |comment | | |memory_temptable_create_cost

NULL|-09-2315:58:28| -09-2315:58:28| serverNULL|-09-2315:58:28| -09-2315:58:28| serverNULL|2015-09-2315:58:28| 1:5:2NU 引擎||

NULL| NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| ++ | 0|memory_block_read_cost NULL|2015-09-2315:58:28|++ 2rowsinset(0.01

可配置的參數(shù)不同于server MySQLV5.7價(jià)模型有什么變mysql.server_cost的參數(shù)可配置含disk_temptable_create_costdisk_temptable_row_costpare_costmemory_temptable_row_costrow_evaluate_cost MySQLV5.7價(jià)模型有什么變mysql.server_cost的參數(shù)—什么時(shí)候會(huì)創(chuàng)建/使用臨時(shí)EvaluationofUNIONstatements,withsomeexceptionsdescribed Evaluationofsomeviews,suchthosethatusetheTEMPTABLEalgorithm,UNION,orEvaluationofderivedtables(subqueriesintheFROMTablescreatedforsubqueryorsemi-joinmaterialization(seeSection“Subquery EvaluationofstatementsthatcontainanORDERBYclauseandadifferentGROUPBYclause,orforwhichtheORDERBYorGROUPBYcontainscolumnsfromtablesotherthanthefirsttableinthejoinqueue.EvaluationofDISTINCTcombinedwithORDERBYmayrequireatemporary ForqueriesthatusetheSQL_SMALL_RESULToption,MySQLusesanin-memorytemporarytable,unlessthequeryalsocontainselements(describedlater)thatrequireon-diskEvaluationofmultiple-tableUPDATEEvaluationofGROUP_CONCAT()orCOUNT(DISTINCT) MySQLV5.7價(jià)模型有什么變mysql.engine_cost的參數(shù)可配置含從磁 從內(nèi)存的緩存 MySQLV5.7價(jià)模型有什么變示例1:修 層的SETcost_value=適用于所有 引WHERE適用于所有 引示例2:為InnoDB引擎設(shè)置INSERTINTO指向某個(gè)具體 引VALUES('InnoDB',0,'io_block_read_cost',3.0,CURRENT_TIMESTAMP,'Usinga指向某個(gè)具體 引 MySQLV5.7價(jià)模型解決了哪些問(wèn)問(wèn)題1:HDD換為了SDD怎么mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| 1|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| SETcost_value=0.1WHEREcost_name=FLUSHmysql>select*from |engine_name|device_type| |cost_value| |comment | 0| 0.1|2015-09-2315:59:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| MySQLV5.7價(jià)模型解決了哪些問(wèn)問(wèn)題1PostgreSQL,HDD換為了SDD怎么# nnerCostConstants#seq_page_cost=#random_page_cost=#cpu_tuple_cost=#cpu_index_tuple_cost=#cpu_operator_cost=#effective_cache_size=

#measuredonanarbitrary#samescaleasabove#samescaleasabove#samescaleasabove#samescaleas MySQLV5.7價(jià)模型解決了哪些問(wèn)問(wèn)題2:有多個(gè)不同 引擎怎么設(shè)置engine_namedevice_type 00 cpu_read_random_index_costNULL sequential_block_read_cost cpu_read_random_index_cost INSERTINTOCURRENT_TIMESTAMP,'UsingaslowerdiskforInnoDB');FLUSHOPTIMIZER_COSTS; MySQLV5.7價(jià)模型解決了哪些問(wèn)問(wèn)題3:索引的代價(jià)計(jì)算精度不夠準(zhǔn)確V5.7.4之前的版V5.7.4的版1.99下取整等于1.01下取整等于1.99依舊是1.01依舊是V5.8直方PostgreSQLV5.8直方PostgreSQL已經(jīng)支持直方ToprowDB已經(jīng)支持直方 MySQLV5.7價(jià)模型解決了哪些問(wèn)問(wèn)

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論