选择合适的innodblogfilesizeMySQL综合 电脑资料.doc.pdf

选择适宜的innodblogfilesizeMySQL综合电脑资料

Choosingproperinnodblogfilesize

选择适宜的innodblogfilesize

Ifyou’redoingsignificantamountofwritestoInnodb

tablesdecentsizeofinnodblogfilesizeisimportantfor

MySQLPerformance.Howeversettingittoolargewi

increaserecoverytime,soincaseofMySQLcrashorpower

failureitmaytakelongtimebeforeMySQLServeris

operationalagain.

如果对Innodb数据表有大量的写入操作，那么选择适宜的

innodblogfilesize值对提升MySQL性能很重要，

Sohowtofindtheoptimalbination?

那么，怎么才能找到最正确的配置组合呢？

Firstletmeexplainwhathappensonrecoveryandwhylarge

innodblogfilesizeslowsdownrecovery.Duringstartupafter

crashInnodbscanslogfilestofindlogrecordswhichonly

havebeenappliedinmemoryanddonotexistintablespace.

Logrecordsformodificationswhichdidnotmakeittothe

tablespacearethenapplied.Thisiscaedredophaseof

recovery.Itcantakeprettylongtimeandthistime

dependsonnumberofvariables-howlargearerows?

(smaerlogrecordsmeanmorerecordsforsamesizedlogs),

howrandomweredatamodifications(randomupdateswi

needrandomIOtocheckifpagesareuptodate),numberof

unflushedpagesininnodbbufferpoolanditssizeaswe

asperformanceofIOsubsystem.Astherearesomany

factors,itishardtoeupwithanygeneralguidelines,

somethinglike1GBper10minutesofrecoverytime-

insteadyouwouldneedtoapplyloadwhichistypicalfor

yourapplication,crashMySQLinthemiddleandwatchitto

recover.Doingthisseveraltimesyoushouldbeableto

estimatehowlongrecoverytimetakeandadjustyourlogs

apropriately.Thegoodthingis-redophaseisclosetobe

proportionaltosizeoflogfiles,soexpect1GBlogsto

taketwicetimetoapplyparedto512MBlogs.

首先，让我先来解释一下恢复时都发生了什么事情以及为什么设置

innodblogfilesize的值太大了会让恢复过程变慢

关键字：MYSQL