利用USL預(yù)測MySQL數(shù)據(jù)庫可伸縮性

1、利用USL預(yù)測MySQL數(shù)據(jù)庫可伸縮性Forecast MySQL Scalability with USLLinear Scalability?What is Scalability?the capability of a system, network, or process to handle a growing amount of work, or its potential to be enlarged in order to accommodate that growthScalability is function.Scalability LawLittles Law (1961

2、)Amdahls Law(1967)Gustafsons Law (1988)Universal Scalability Law (1993)Queueing theory服務(wù)請(qǐng)求量=到達(dá)率 * 駐留時(shí)間(響應(yīng)時(shí)間)隊(duì)列度=到達(dá)率 * 等待時(shí)間利用率=到達(dá)率 * 服務(wù)時(shí)間Amdahls LawN = Processor= Contention (串行化比率)If an amount of work N is completed in time T 1 on a uniprocessor, the same amount of work can be completed in time T p

3、T 1 on a p-way multiprocessor. The speedup S p = T 1 /T p is one measure of scalability.Gustafsons Law負(fù)載擴(kuò)展至n個(gè)節(jié)點(diǎn)Amdahls LawAmdahls law assumes the size of the work is fixed. Gustafsons modification is based on the idea of scaling up the size of the work to match p.USLThe USL is equivalent to the sync

4、hronous queueing bound on throughput for a linear load-dependent machine repairman model of a multiprocessor.N = Concurrency (or Processor) = Contention ( waiting for shared resources ) = Coherency ( waiting data synchronous )A General Theory of Computational Scalability Based on Rational FunctionsU

5、SL(2)(1)(3)(4)(5)Standard MRM在有限的p個(gè)機(jī)器的產(chǎn)線，每作Z段時(shí)間就有機(jī)器故障，需要花費(fèi)S段時(shí)間修復(fù)，如果多個(gè)機(jī)器故障按FIFO順序修復(fù)。Scalability Model Linear Scalability Cost of sharing resources Diminishing returns from contention Negative returns from incoherencyScalability ZonesContention & CoherencyContention ()Coherency ()含義共享數(shù)據(jù)的爭用一致性的開銷舉例不同請(qǐng)求

6、更新相同數(shù)據(jù)行內(nèi)存與磁盤間或不同CPU的 緩存間的一致性根源無法并行的任務(wù)進(jìn)程間同步的開銷自變量N-1: 假設(shè)需要處理N個(gè)進(jìn)程,最壞場景下有N-1個(gè) 進(jìn)程在等待N*(N-1): 假設(shè)需要處理N個(gè)進(jìn) 程, 每個(gè)進(jìn)程間要與N-1個(gè)進(jìn) 程同步，即N*(N-1)PredictPredict maximum scalabilityPredict throughput Xmax at load NmaxDB Capacity Planning基準(zhǔn)測試估計(jì)容量（時(shí)間和成本）沒有完整數(shù)據(jù)庫的負(fù)載組成信息無法準(zhǔn)確度量事務(wù)的執(zhí)行時(shí)間Step to Apply USL選擇度量參數(shù)Load: QPS/TPSConcu

7、rrency: Thread_running(MySQL)搜集數(shù)據(jù)mysqladmin-i1 ext |awk BEGINprintf %5s %5sn, conn,tput / Threads_running/run=$4/Queries/q=$4-qp;qp=$4;printf %5d %5dn, q, run整理數(shù)據(jù)擬合數(shù)據(jù)分析結(jié)果Examplesample - read.csv(“8003.tput,sep=)usl - nls(tput conn/(1+sigma * (conn-1)+ conn*(conn-1),sample,start=c(sigma=0.1,kappa=0.0

8、1)sigma - coef(usl)sigma kappa - coef(usl)kappau=function(x)y=x/(1+sigma * (x-1)+ kappa*x*(x -1)plot(u,0,max(benchmark$conn)*2,xlab=“Concurrency,col=green, ylab=Throughput, lty=“dashed,add=TRUE)points(benchmark$conn,benchmark$tput)https:/kevinbin.shinyapps.io/uslapp/ConclusionsScalability 是可以被量化的線性擴(kuò)展意味著資源翻倍，負(fù)載也翻倍資源垂直擴(kuò)展不意味處性能增加，關(guān)鍵是串行化。即便極小Coherency也會(huì)使Scalability倒退具備良好Scalability的系統(tǒng)應(yīng)盡可能避免Contention和CoherencyReferenceHow to Quantify Scalability (Neil J. Gunther)Getting in the Zone for Successful ScalabilityUSL for R packageA Little Triplet