copyright © 2016 speedemyspeedemy.com/files/ebook1/speedemy-mysql-configuration-tuning … · so...

AbouttheAuthor

AurimasMikalauskasisadataperformanceandscalabilityexpertwithover12yearsofexperiencewithMySQLandanumberofrelatedtechnologies.FormerlyaPerconaarchitectforover9years,Aurimasiscurrentlyteachingperformanceoptimizationatspeedemy.com.

WhileatPercona,AurimasworkedwithcompaniessuchasBBC,EngineYard,Groupon,Boardreader,wellknownsocialnetworksandoverahundredofothercompanieslargeandsmall.WhilehisfocuswasMySQLperformance,scalabilityandhighavailability,hehasalsoimplementedanumberof“onebilliondocuments”fulltextsearch

systems,largescaleapplicationsandhehaspublishedanumberofarticlesonTheMySQLPerformanceBlog.

Tokeephisskillssharpanduptodate,Aurimasisstilltakingonchallenging(readexciting)performanceoptimizationorscalabilitycases.Feelfreetoreachouttohimifyoufeellikeyouhaveaninterestingchallengeyourself.

TableofContents

AbouttheAuthor 2......................................................................................................................................................

TableofContents 2.......................................................................................................................................................

Introduction 4................................................................................................................................................................

AquicknoteonOraclekillingMySQL 5..............................................................................................................

DifferentMySQLdistributions 6............................................................................................................................

CommunityMySQL 6..............................................................................................................................................

PerconaServer 7......................................................................................................................................................

MariaDB 8...................................................................................................................................................................

WebScaleSQL 9..........................................................................................................................................................

TheEssentialsofMySQLPerformanceTuning 11.........................................................................................

EnterMySQLCon]igurationFile 11.................................................................................................................

MostCommonMySQLCon]igurationMistakes 11....................................................................................

Changingcon]igurationonline 13....................................................................................................................


http://www.speedemy.com/

http://www.speedemy.com/mysql/mysql-performance-consulting/

Global-vs-Localscope 14....................................................................................................................................

17KeyMySQLvariables 16......................................................................................................................................

1.default_storage_engine–choosetherightengine]irst 16................................................................

2.innodb_buffer_pool_size–getthebestoutofyourmemory 19.....................................................

3.innodb_log_]ile_size–roomforMySQL’sredolog 23..........................................................................

4.innodb_]lush_log_at_trx_commit–durableornot?Thatisthequestion! 27............................

5.sync_binlog–that’sfordurablebinlog 27...............................................................................................

6.innodb_]lush_method–yourchancetoavoiddoublebuffering 28..............................................

7.innodb_buffer_pool_instances–reduceglobalmutexcontention 28..........................................

8.innodb_thread_concurrency–havebettercontroloveryourthreads 29..................................

9.skip_name_resolve–doskipthatreverseIPlookup 30.....................................................................

10.innodb_io_capacity,innodb_io_capacity_max–capInnoDBIOusage 31.................................

11.innodb_stats_on_metadata–turnthemOFF! 31................................................................................

12.innodb_buffer_pool_dump_at_shutdown&innodb_buffer_pool_load_at_startup 32.........

13.innodb_adaptive_hash_index_parts–splittheAHImutex 33.......................................................

14.query_cache_type–ON?OFF?ONDEMAND? 33................................................................................

15.innodb_checksum_algorithm–thesecrethardwareaccelerationtrick 34.............................

16.table_open_cache_instances–useit 34..................................................................................................

17.innodb_read_io_threads&innodb_write_io_threads–lastand,yes,least 34........................

TheEnd(Almost) 36...................................................................................................................................................

Bonus:UnderstandingMySQLStatusCounters 37........................................................................................

HowtoLookAtMySQLStatus 37.....................................................................................................................

KeyMySQLStatusVariablesToLookAt 38..................................................................................................

FreeToolsToMakeMonitoringMySQLEasier 41.....................................................................................

Whatnow 42...................................................................................................................................................................


Introduc4onWithversion5.7,MySQLnowhasover450settings!ThatgivesuserslikeyouandmealotofpowerandClexibilitytoCine-tuneittoperfection.Butatthesametime,itleavesalotofroomforerrors.

HavingspentoveradecadetuningMySQLforbestperformance,runninglargescaleaswellasmicrobenchmarks,I’mprettycon]identthereareveryfewsettingsthatactuallymatterforMySQLperformance.Andthise-bookisheretoguideyouthroughthem.TakeafewminutestostudythemwithyourenvironmentinmindandyouwillneverlookatMySQLcon]igurationthesameway.

Beforewegettocon]igurationthough,let’s]irstdiscussaquestionofMySQLlifetime,differentMySQLdistributionsthatareavailableonthemarkettoday,andtheessentialsofMySQLcon]igurationtuning.


AquicknoteonOraclekillingMySQLIfyouwereatanyoftheMySQLconferencesduringpast6years,you’veprobablyheardthisoverandoveragain:

OracleisgoingtokillMySQL

IwanttoacknowledgethisFUDthathasbeenspreadingsinceApril2009,whenOracleacquiredSun(andconsequently,MySQL).

Firstofall,OraclewasneverplanningtokillMySQLandverywiselyso,giventhenumberofotheropensourceproductsthatarealreadyavailableonthemarket.Theydeclaredof]iciallythattheyaregoingtokeepMySQLOpentotheworldandtheywillkeepimprovingit.

Moreimportantlythough,OracleprovedoverandoveragainthattheyhavelearnedtheopensourcemodelandtheykeepdoingagreatjobdevelopingMySQL-theamountofimprovementsMySQLgotduringlast6yearsisjustamazing.JusttakealookatMySQL5.7andyouwillrealisethatMySQLhasneverbeenbetter.

TherearesomepeoplesayingMySQLisnowgettingslower,whichisonlysomewhattrueifyoulookatisolatedcaseswithzero-concurrencyworkload.However,whenyoulookatthethroughput,concurrencylevelsthatMySQLisnowabletowithstandnotloosinganystability,youwillseeitscalesbetterthaneverbefore.Andbeatsmostotherproductsonthemarket,ifyoudoafaircomparison.

OhandletmenameafewfeaturesthatwouldhavelikelynotseenthelightofdayforanumberofyearsifnotOracle:

• InnoDBbufferpoolinstances• InnoDBfulltextsearch• JSONsupport• GroupReplication• Multi-threadedreplication• Onlinebufferpoolresize• NativeInnoDBpartitions

Ofcourse,theywereabletomonetiseMySQLbycreatingsomespecialEnterprisefeatures,likeEnterpriseMonitor,AuditPluginorEncryption.Howeverwhenyouthinkofit,thatisagoodthing,becauseitmeansthiswaytheycankeepMySQLfreefortherestofus.

AndMySQLecosystemwasquicktorespondtothisbycreatingOpenSourcealternativesformostoftheseproductsandarefreetocontinuetodoso.


DifferentMySQLdistribu4onsWhydon’twenowspendafewminutestalkingaboutdifferentMySQLdistributionsthatareavailableonthemarketthesedays.There’stheOracle’sCommunityMySQL,PerconaServer,MariaDBandWebScaleSQL.Let’squicklydiscusswhattheyhaveincommonandwhatarethekeydifferencessoyouknowwhentousewhich.

It’sveryimportanttonotethat,aslongasyou’reusingthesamemajorversion,youcanusemostoftheminterchangeably-youdon’tneedtodomigration,orevenupgradesforyourtables.Youcanjuststoptheserver,switchthebinariesandrunthedifferentversionusingthesamedatadirectory.

Yourapplicationwon’tnoticethedifference.Wellatleastfromthefunctionalitypointofview.Therecanbesomeexceptionsthough.Letmewalkyouthroughit.

CommunityMySQLThisistheOracledevelopedMySQLthat,inmyview,receivesthemostdevelopmentefforts.ItisalsotheversionthatisusedasanupstreamforalloftheotherMySQLversions-we’lltalkabouthoweachofthemisusingtheupstreamineachsectionseparately.

Intermsofperformance,therearefewimportantthingstoknow:

1. CommunityMySQL5.1doesnothaveInnoDBpluginenabledbydefault,soifyourunwithdefaultcon]iguration,youwillhaveaverypoorperformanceatscaleandalsoyouwillbemissingoutonalotoffeatures.That’snottosayyoushouldbeusing5.1atall,butifyouarecurrently,bewarethatthevery]irstthingyoucandototakeitsperformancetothenextlevelisenabletheinnodbplugin.

2. CommunityMySQL5.5alreadyhasstrengthenedInnoDBatitscore,howeverthere’sstilllotsofperformanceimprovementsaswellasfeaturesmissingthatyoucan]indinanappropriatePerconaServerwithXtraDBandMariaDBwithXtraDBversions,especiallywhenitcomestostabilityathighwriterates.Otherthanthat,youcangetprettyfarwithMySQL5.5asyourbaseserver.

3. CommunityMySQL5.6gotalotofnewfeaturesandperformanceimprovements-includingoptimizerimprovements,anumberofnewmutexesthatwereintroducedtoalleviatecontentiononthekernelmutex,performanceschemaimprovements,multi-threadedpurgethreads,separate]lushthreadandsoonandsoforth.Atthisrelease,thereareevenfewerreasonstouseanalternativeversionsuchasPerconaorMariaDBandwe’lltalkaboutthemintheappropriatesections.

4. CommunityMySQL5.7-thenewestMySQLGAreleaseyetiscurrentlytheserverwithmostfeatures-includingsuchnewfeatureslikemulti-sourcereplication,JSONsupport,propermulti-threadedreplication,onlinebufferpoolresize,spatialdatatypesforInnoDB,sysschema,etc.etc.Also,accordingtoOracle’sbenchmarks,itisthebestperformingMySQLserverthat’scurrentlyavailableonthemarket.(Bewarethatsome


vendorshaveattemptedtomanipulatebenchmarkresultsshowingbetterperformanceontheirversionsbyusingadifferentdefaultcon]iguration,sodonotbeconfusedbythat).

ItookabitmoretimetalkingaboutCommunityMySQL,becauseit’susedatthecoreofmostoftheotherservervariants.ToexplainwhatImeanbythat,letmenowswitchtoPerconaServer.

PerconaServerPerconaServerwasoriginallylaunchedaroundJuly2008,whenwestaredreleasingourbuildsofMySQL5.0and5.1withfewadditionalpatches-microsecondresolutionslowquerylog,executionplandetails,additionalinnodbstatisticsanduserstatisticspatchfromGoogle.Althoughitwasn’tcalledPerconaServerbackthenyet-itwascalledsimplyPerconapatchesforMySQL.

Unof]iciallyhoweverPerconahasbeenbuildingpatchedversionsofMySQLfortheircustomersevenearlier-inadditiontoslowquerylogdetailsthathelpedusasconsultantsunderstandbetterwhatishappeninginsidetheserver,thosewerealsovariousperformance]ixes.

Wealwaysfollowedonesimplerule-uselatestupstreamCommunityMySQL,removeanyredundantfeaturesthatarenowimplementedproperlyinMySQLandneverbreakbackwardscompatibility.MeaningthatifyourunPerconaServer5.1.73,youshouldbeabletoswitchbacktoMySQL5.1.73bysimplyswappingthebinaries.

Therewerefewexceptionstothat,likewithadditionalrollbacksegmentsforextremelywrite-heavydatabaseswhich,ifenabled,didn’tallowdowngrade.HoweverinallofthesecasesPerconamadeitveryclear-Imean“DANGERincapitalred”clear-thatthesechangesarenotbackwardscompatibleandtheywereneverenabledbydefault.Andnoneoftheseincompatiblechangesareavailableineither5.5,5.6or5.7versionsofPerconaServer.

Soinanutshell,ifyouarerunningMySQL5.6.28andyouwanttotryoutPerconaServer,justremoveCommunityMySQL5.6.28,installPerconaServer5.6.28andifyoudon’tlikesomething,youcanswitchbacktoCommunityeditionthesameway-it’sjustamatterofstoppingoneversionandstartingtheother.

Nowintermsofperformance,here’sfewinterestingthings:

1. IfyouarerunningMySQL5.1,doupgradetoPerconaServer5.1-youwillbeamazedhowmuchbetteritis.OnPerconaServeryoudon’tneedtoenabletheinnodbplugin,itwillbeenabledbydefault,butithasanenormousamountofperformanceandespeciallyscalabilityimprovements.PlusyouwillhavealltheadditionalfeaturesthatshowedupinMySQLonlyinversions5.5and5.6.

2. PerconaServer5.5stillhasagreatadvantageoverMySQL5.5-adaptivehashindexpartitions,fasterchecksums,bufferpoolscalabilityimprovements,muchbetter


adaptive]lushingalgorithmplusanumberofadditionalpluginsthatareotherwiseonlyavailableinEnterpriseMySQL,suchasPAMauthentication,InnoDBtableimport,AuditlogandThreadpool.Soit’sbothmorestableandmorescalable.

3. PerconaServer5.6hasthesamebene]itsofversion5.5andithasafewperformanceimprovements,butthedifferenceoverMySQL5.6isnowonlyvisiblewithhighendhardware-superfast]lashstorage,hundredsofgigabytesofRAMandtensofCPUcores.

4. Atthistime,PerconaServer5.7isnotyetgenerallyavailable,howeverIwouldonlyexpectPerconaServertomakeadifferenceinedgecasesandformostcommonevenhighperformanceworkloads,MySQL5.7mightbegoodenough.

There’sonebigadvantageofPerconaServerIdidn’ttalkmuchabout.ThatistheEnhancedSlowQueryLog.Forme,itisthetopreasonwhyIrunPerconaServereverywhereIcan.JustbyusingslowquerylogIcangetsuchanenormousamountofinformationaboutthequeriesthatIprettymuchdon’tneedanyotherstatisticsfromMySQL.

Butsincethisisnotaqueryoptimizationcourse,let’snowtalkMariaDB.

MariaDBMariaDBisaforkofMySQLcreatedbyMontyWidenius-themainauthoroftheoriginalMySQL.ItisnowadoptedbymanyLinuxdistributionsandwhileit’sagoodMySQLreplacementinmanyaspects,inmyviewit’sslightlyover-appreciated.

IfyouhaveeverbeentoaMonty’stalk,youknowheisfocusinghismarketingeffortsonmakingOraclelookbadwhilemanyofthethingshehasbeenmanipulatingneverbecametrue.

Anyways,politicsaside,let’stalkabouttwoversionsofMariaDB-MariaDB5.5andMariaDB10.

1. MariaDB5.5isusingMySQL5.5atitscore,plusitaddstheXtraDBstorageengine-areplacementforInnoDBadaptedfromPerconaServer,andsomecodefromMariaDB5.3whichhasanumberofqueryoptimizerimprovements,multi-masterreplication,groupcommit]ixandfewotherfeatures.Soifyouareusing5.5branch,withsomespeci]icquerytypesyouwillseebene]itsbyupgradingtoMariaDB5.5.

2. MariaDB10,ontheotherhand,isverydifferentfromalloftheothervariantsofMySQLinthatithasstarteddivergingfromtheupstreamMySQL.Itiswhatwouldbecalledarealfork(ratherthanaspoon).MariaDBusedMySQL5.6atitsfoundation,butyoushouldexpectthatMariaDBwillnotbebackwardscompatiblewithMySQL5.6,5.7oranylaterMySQLversions.

MostnoticeableMariadbnewfeaturesare:

• Parallelreplication


https://mariadb.com/kb/en/mariadb/what-is-mariadb-100/

• Multi-sourcereplication• Cassandra,SpiderandTokuDBstorageengines

TheadaptionofMariaDBisverylimitedsofarandtherearen’tanygoodqualitybenchmarkscomparingMySQL5.6or5.7toMariaDB10,exceptfortheonesproducedbyMariaDB(whicharepotentiallybiased),soit’stooearlytosayifit’sbettertoswitchtoMariaDB10orstayonMySQL.HowevergiventheamountofdevelopmentresourcesMySQLisgettingandthevastimprovementsinMySQL5.7,myrecommendationistostayonMySQLfornow.

Finally,let’sdiscussthelastplayerinthe]ield-WebScaleSQL.

WebScaleSQLWebScaleSQLisacollaborationamongengineersfromseveralcompaniesthatfacesimilarchallengesinrunningMySQLatscale,namelyAlibaba,Facebook,Google,LinkedIn,andTwitter.

ItisaMySQLforkusingMySQL5.6atitscoreandasfarasI’maware,therearenoplanstouseMySQL5.7astheyconsider5.6agoodenoughversiontouseasabasis.HoweveritisalsonotplanningtodivergefromMySQLupstream,eventhoughsomefeaturesmightmakeyourdata]ilesnotMySQL5.6backwardscompatible.Ohandalso,thecooleststuffisbeingbackportedfromMySQL5.7whenneeded.

It’sveryimportanttomentionthat,unlikeothervariantsmentionedearlier,WebScaleSQLservesaveryspecialpurpose-it’snotmeanttobeusedasageneralpurposeMySQLserver.Rather,itaddressesveryspeci]icneedsofthenamedcompaniesrunningMySQLatscale.WellSCALEwiththecapitalS.SounlessyouarerunningatleastfewdozenMySQLservers,youmaywaitalittleonoptimizingtothatdegree.

Here’sjustafewthingsthataredifferentonWebScaleSQL-thingsthatothervariantsdon’tandmaynotevenhaveplanstohaveforquiteobviousreasons:

• Abilitytospecifymillisecondtimeoutsforvariousnetworkoperations• Superread-onlymode• Abilitytodisabledeadlockdetection(notsomethingyouwanttorunonatypical

transactionaldatabaseserver)• Pre]ixindexqueryoptimization• AbsenceofPerformanceSchemabydefault(ApparentlyPerformanceSchemamay

haveanoticeableoverheadevenwhennotenabled)• InnoDB]lushingperformance]ixes,mostofwhicharealsoavailableinPerconaServer,

MariaDBandnowMySQL5.7.

Youcan]indafulllistwithextensivedetailsinthefollowingblogpostbymyformercolleagueLaurynasonPerconablog.


https://www.percona.com/blog/2014/05/22/comparing-webscalesql-percona-server/

Asyoucansee,unlessyouknowexactlywhatyou’redoing,somefeaturesarepotentiallydangeroustorunwith.Thereforeifyou’reindoubt,sticktoPerconaServer,MariaDBorstockMySQL5.7untilyouareabsolutelysureWebScaleSQLiswhatyouneed.


TheEssen4alsofMySQLPerformanceTuningNowthatyouhavehopefullymadeadecisionwhichMySQLdistributionbestsuitesyourneeds,let’stalkabouttheprocessofMySQLperformancetuning.

Here’safact:MySQLdefaultsarepoor.

Yes,youcanstartMySQLwithnocon]igurationandyoucanstartusingitfordevelopmentrightaway.However,youcan’tjustputMySQLwithdefaultcon]igurationtoproductionandexpectthatitwillhandletheincreasingworkloadwithease-youhavetoprepareyourserverforthat.

There’stwogoodnewsthough:

1. MySQL5.7hasbetterdefaultsthaneverbefore2. MySQLisveryeasytocon]igure.It’sjustonecon]iguration]ileyouhavetodealwith,

my.cnf,andithasoneoptionperline,sotheformatisveryconvenient.

EnterMySQLConfigura4onFileThelocationofMySQLcon]iguration]ilemaybedifferentacrossdifferentoperatingsystemsanddistributions,howeveronLinuxit’stypicallyeither/etc/my.cnf(Redhatstyle)or/etc/mysql/my.cnf(Debianstyle).OnWindows,youcanputitinanumberoflocations,howeverIsuggestyouusethedatadirectoryandcreatemy.cnf]ilethere.

Openthat]ileaswe’llneeditrealsoon.Orcreateitifit’snotthereyet.

Bytheway,ifyoualreadyhaveadefaultcon]iguration]ile(e.g.Debian/Ubuntudefault]ilesusuallycontainalotofoptions),you’llprobablywanttodosomemergingintheeditingprocess.Don’thesitatetoremoveanyperformancerelatedoptionsyou’renotsureabout,orthatarenotmentionedinthise-book(butdokeepabackupjustincase).

MostCommonMySQLConfigura4onMistakesBeforewegettothekeyMySQLcon]igurationvariables,I’dliketoquicklygooverthemostcommonmistakesI’veseenbeingdonewhencon]iguringMySQL.

UsingTrialandErrorapproach

Here’showI’veseenmanycustomersapproachMySQLcon]iguration:theychangeafewthingsandcheckifitfeelsbetter.

AndthisisthebiggestmistakethatIseebeingdonewhenapproachingMySQLcon]iguration.Problemisthatbythetimeyou’remeasuringhowyourapplicationfeels,situationmayhavechangedalready.Plusyouhavetoconsiderthewarmuptimeanddisregardanyoverheadyouseeduetoit.Andmostimportantly,youcan’tactuallybase


youroptimizationeffortsonfeelings–it’seitherfaster,morescalable(andyouunderstandwhy)orit’snot.

Aftersucha“tuning”sessiononemayendupwithaworsecon]igurationthanwhattheyhavestartedwith.Infact,moreoftenthannot,that’sexactlywhatIwould]indwhenacustomerwould]irstcontactme.

Don’tdoit.Instead,understandexactlywhatitisthatyou’rechangingandonlychangeitwhenyouknowthatiswhatyouneed.Oftenrunningamicro-benchmarkinacontrolledenvironmenttoverifysuchchangeisaverygoodidea-thatwayyoucancon]irmthatitisworkingthewayit’ssupposedtoaccordingtoyourunderstanding.

Mostimportantvariablestoperformancewillbediscussedhere,sohopefullythatwillbeagoodstartforyou.

Nowhere’safewothercommonerrors:

UsingGoogleforperformanceadvice

Nevertrustthe]irstresponseyou]indonGooglewhensearchingforaperformanceadvice,orvalueforyourspeci]icvariable.Alotoftheadviceontheinternetisverygenericandoftenlackscontext.Forexample,youmay]indalotofcon]iguration]ilesontheinternetthatwereusedforbenchmarks,howeverbenchmarksoftenintentionallydocertainthingsthatshouldnotbedoneonaproductionserver,suchasdisablingdouble-writebuffer,settinginnodb_thread_concurrencyto0andsimilar.

Also,alotofthesettingsarehardwaredependant,whichiswhyit’sevenmoresoimportanttounderstandwhatitisthataspeci]icvariableisdoingexactly.

Obsessingaboutfine-tuningthemy.cnf

Don’tgetobsessedabout]ine-tuningthecon]iguration–usually10-15variableswillgiveyouthemostimpact,and]ine-tuningthevariablesishighlyunlikelytohaveanyadditionalbene]its.Itcandoharmthough.Ifyoustillhaveaperformanceproblemevenafteryouhaveappliedalltherecommendations(andgottenridofeverythingthatyoushouldn’thavetouchedinthe]irstplace),theproblemisprobablysomewhereelse–badqueries,lackofresources,etc.

Changingmanythingsatonce

Whenworkingwithcon]iguration,changeonlyonethingatatime.Especiallyifyoualreadyhaveasolidcon]iguration.Otherwisewhenthingsgobad,itmaybeveryhardorevenimpossibleto]igureoutwhichsettingcouldhavecausedtheissue,soyouwillhavetorollbackallofthechangesandthenstartonebyoneanyway.

Ifit’sanewsetup,oryouwererunningwithdefaultcon]igurationuntilnow,feelfreetogowildandimplementallofthechangesrecommendedhereatonce.Otherwise,changeonethingandtakesometimetomonitortheserverafterthechange.


Notkeepingmy.cnfinsyncwiththechangesyoumake

It’snosecretthatmanythingscannowbechangedonlinewithouteventouchingmy.cnf.EventheinnodbbufferpoolsizecanbechangedonlineinMySQL5.7.That’sveryconvenient,but.Makesureyouupdatemy.cnfafteryouaredonewiththechangesoryouwillloseallthesechangeswhenMySQLisrestartedandyou’llhavetostartover.

Redundantentriesinmy.cnf

Ifyouusethesamevariabletwice,MySQLwillnotcomplainaboutit.Inmostcases,itwilljustusethelastvaluefoundforthesamevariable,sobesureyoudon’taddthesamevariabletwice,otherwiseyoumayendupnotseeingtheimpact.Alsonotethatadash“-”andanunderscore“_”canbeusedinterchangeably,soinnodb-log-file-sizeandinnodb_log_file_sizearebothreferringtothesameserversetting.

Mul4plyingbuffersizes

Whenyouaddmorememorytotheserver,don’tjustmultiplythesizeofallbuffersineffect.Somebuffersarelocal,someglobal.Somearestorageenginerelated,someareserverwide.Infact,thereareveryfewvariablesthatyouneedtoincreaseinsizeasyouaddmorememory.Yes,thesearecrucialtoupdate,oryouwon’thavethedesiredeffect,butonlytheseandnoother.Iwilltalkaboutthesevariablesasweprogress.

Usingthewrongmy.cnfsec4on

WhileMySQLcon]iguration]ileissimple,it’simportanttomentionthatitdoeshavesectionsandtheseareimportant.

Forexample,there’ssuchsectionsas[mysql],[client],[mysqld_safe].Andthere’salsoa[mysqld]section,whichisexactlythesectionyoumustuseifyouwantservercon]igurationtotakeeffect.Soallofthevariablesforservercon]igurationshouldbeplacedafter[mysqld].

Theonlyexceptionhereisifyou’reusingthemysqld_multiscript,inwhichcaseyou’llbeworkingwithseveralsectionsratherthanjustone.

Changingconfigura4ononlineLikeImentionedearlier,thereisawaytochangesomeparametersonline.Andinfactit’ssafetotry,evenifyou’renotsureifyoucanchangeitonline-MySQLwilljusttellyouthatthevariableisread-only,meaningthatyoushouldbechangingthemy.cnf]ileinsteadandrestarttheserver.

Here’showIwouldchangeinnodbbufferpoolsizeto128MBonline:


mysql>setglobalinnodb_buffer_pool_size=128*1024*1024; ERROR1238(HY000):Variable'innodb_buffer_pool_size'isareadonlyvariable

ExceptthatthisisMySQL5.6andnot5.7,whichiswhyIcan’tdoitonline.Howaboutinnodbthreadconcurrency.Let’schangethat,but]irst-let’scheckthecurrentvalueforit:

mysql>showglobalvariableslike'innodb%';+---------------------------+---------+|Variable_name|Value|+---------------------------+---------+...|innodb_thread_concurrency|0|...

It’szero.Let’schangeitto8:

mysql>setglobalinnodb_thread_concurrency=8; QueryOK,0rowsaffected(0.01sec)mysql>select@@global.innodb_thread_concurrency; +------------------------------------+|@@global.innodb_thread_concurrency|+------------------------------------+|8|+------------------------------------+1rowinset(0.00sec)

Thistimewesucceed.Andinadditiontothesyntaxofchangingthecon]iguration,youcanseehowyoucanaccessavalueofasingleglobalcon]igurationvariable.Whichbringsmetothenextsection.

Global-vs-LocalscopeWehavejustseenawaytoalterglobalMySQLcon]iguration,buthere’saninterestingthing-quiteoftenyoudon’tneedtoupdatetheglobalcon]igurationjusttogetthatsinglequeryworkproperly.Infact,veryoftenit’sbetterthatyouleavetheglobalcon]igurationuntouched,becauseanoptimizationforonequerymayaffectalloftheotherqueriesinthenegativeway.

Forexample,oneofthethingsIrecommendistokeepthesort_buffer_sizeatitsdefaultvalue,becauseotherwiseafullbufferisallocatedforanysessionwheresortingisdoneandthatmayendupwastingalotofmemoryandtimeallocatingit.


Butwhatdoyoudoifyouhaveaquerythatisconstantlysortinglargeamountsofdataandyoucan’taddanef]icientindex,butyoualsowanttoavoiddiskbasedsorts?Orwhatifyouhaveaquerythat’sconsistentlyusingindex_merge_intersectionoptimizationandyouknowitwouldbemuchbetteroffnotusingit?

Inthatcase,justsetasessionvariablepriortorunningthatquerybyissuingastatementsimilartothisone:

mysql>setoptimizer_switch='index_merge_intersection=off'; QueryOK,0rowsaffected(0.01sec)

Thiswillonlychangethevalue(andbehaviour)forthissession,sothedayissaved.


17KeyMySQLvariablesNowlet’sgetdowntobusiness.Iwanttointroduceyouto17my.cnfsettingsthatarekeyforoptimalMySQLperformance.

Someofthesesettingsareself-evidentbutoftenforgotten,othersrequireexplanationsoyouknowwhichsettingistherightforyou.Andtherearealsoafewsettingsthatpeopletendtospendhourson,eventhoughtheyaren’tactuallydoinganythingatall.Ididn’tlistthesettingsinthislastcategoryhere,butyouwill]indthemdiscussedinthecommentsinspecialmy.cnfthatIhavepreparedforyourproductionneeds.The]ilehasshortexplanationsandlinkstoappropriatesectionsonmyblogwithmoreverboseexplanations.Ifyouhaven’tdownloadedityet,youcandownloaditbygoingto:

http://www.speedemy.com/17

Basically,youcanusethis]ileasastartingpointforyourproductionserverbytweakingoneortwosettingswhereappropriate.

Nowhere’sthe]irstMySQLsettingweshouldtalkabout.

1.default_storage_engine–choosetherightenginefirstIfallofyourMySQLtablesareusingInnoDBandyoudon’tneedconvincingthatInnoDBisthewaytogo,you’reallsetwiththisone.

Ifyouareunsure,however,bearweme.Wehavesomegroundtocover.

Storageenginewhat?

MySQLhassupportedpluggablestorageenginessinceitsinceptionover20yearsago,butforaverylongtimeMyISAMwasthedefaultstorageengineandmanypeoplerunningMySQLdidn’tevenknowanythingabouttheunderlyingstorageengines.Afterall,MySQLwasinitiallydesignedtobeapracticaldatabaseforsmallwebsitesandmanyapplicationsgotintohabitofusingMyISAMstorageengineexplicitly.

Thisseemedlikeagoodidea]irst,buthere’stheproblem:MyISAMwasNOTdesignedwithhighlyconcurrentworkload,numberofCPUcoresandRAIDarraysinmind.Anditwasnevermeanttoberesilienteither.Soaswebsiteskeptattractingmoretraf]ic,theycouldnolongerscale,becauseMySQLquerieswouldspendsecondswaitingontablelevellocks(theonlylockingmechanismthatMyISAMsupports).Andtheydidn’twanttobeloosingtheirbusinesscriticaldataoneachMySQLcrasheither.

MeetInnoDB

Whatmanypeopledon’tknowthoughisthatvirtuallyforaslongasMySQLexisted,MyISAMstorageenginehadacousincalledInnoDB.Andhighlyconcurrentworkload,



performanceandresilience(alsoatomicity,consistencyandisolation)wasexactlywhereInnoDBshined.

Sure,ithadafewbumpsasitwasgrowingup(mostnotably,scalabilityissuesbeforeversion5.0.30),butoverthelast9yearsInnoDBhasbeenimprovedinallareasyoucanimagine,whereasMyISAMgotvirtuallynoattentionatall.

Therefore,asofMySQL5.5.5,InnoDBbecamethedefaultstorageengineandnowadaysyouwillhardly]indasizeableMySQLinstallationthat’sstillusingMyISAMandnotInnoDB.

Nowbeforewegoanyfurther,letmeshowyouhowyoucanquicklygetacountandalistofMyISAMtablesonyoursystemsoyoucanstartplanningyourmigration.

Storageenginesusedbyyourdatabase

Here’sareallycoolquerythatshowsthestorageenginesyouareusingandanumberoftablesusingeachstorageengine:

mysql>SELECTengine,count(*)asTABLES,concat(round(sum(table_rows)/1000000,2),'M')rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G')DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G')idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G')total_size,round(sum(index_length)/sum(data_length),2)idxfrac FROMinformation_schema.TABLESWHEREtable_schemanotin('mysql','performance_schema','information_schema') GROUPBYengineORDERBYsum(data_length+index_length)DESCLIMIT10;+--------+--------+---------+--------+--------+------------+---------+ |engine|TABLES|rows|DATA|idx|total_size|idxfrac| +--------+--------+---------+--------+--------+------------+---------+ |InnoDB|181|457.58M|92.34G|54.58G|146.92G|0.59| |MyISAM|13|22.91M|7.85G|2.12G|9.97G|0.27| +--------+--------+---------+--------+--------+------------+---------+ 2rowsinset(0.22sec)

Youcanseethatthisparticularcustomerhad13MyISAMtablesholdingover7GBworthofdatacombined.Ifyou]indyourselfinasimilarsituation,don’tworry,we’ll]ixthatsoon.


TogetalistofallMyISAMtablessortedbysize,justrunthisquery:

SELECTconcat(table_schema,'.',table_name)tbl, engine,concat(round(table_rows/1000000,2),'M')rows, concat(round(data_length/(1024*1024*1024),2),'G')DATA, concat(round(index_length/(1024*1024*1024),2),'G')idx, concat(round((data_length+index_length)/(1024*1024*1024),2),'G')total_size,round(index_length/data_length,2)idxfracFROMinformation_schema.TABLESWHEREtable_schemanotin('mysql','performance_schema','information_schema') ANDengine='MyISAM'ORDERBYdata_length+index_lengthDESC;

Bearinmindthatchangingdefault-storage-enginesettingtoInnoDBorupgradingMySQLdoesn’tautomagicallyconvertallyourtablestoInnoDB.Farfromit.Youactuallyhavetogoandconverttablesonebyone(orhaveascriptdoit).

Nowbeforeyougoonandconvert“justthebigones”toInnoDB,here’ssomethingreallyimportant:

SometimesaspartofmigrationtoInnoDB,DBAsstartwiththelargeMyISAMtablestoseeifthingswillgetbetter.Andsometimesithelps,butinmanycasesitdoesn’t.Here’stheproblem:ifatleastonetableinajoinisMyISAM,theentirequeryisusingtablelevellocks.SohavingevenasmallMyISAMtableinalargejoincanbeverybadforconcurrency.

Thereforewhenyou’rereadytoconvert,makesuretoconvertallMyISAMtablestoInnoDB,notjustthebigones.

Conver4ngtoInnoDB

IrecommendthatyouholdoffwithconversionfornowuntilyouunderstandInnoDBcon]igurationbetterandprepareyourserverforthat,butwhenyou’reready,youcanrunthefollowingquerytogetalistofqueriesthatwillconvertalltablesinagivenschemafromMyISAMtoInnoDB:

SET@DB_NAME='your_database';SELECTCONCAT('ALTERTABLE`',table_name,'`ENGINE=InnoDB;')ASsql_statements FROMinformation_schema.tablesAStbWHEREtable_schema=@DB_NAMEANDENGINE='MyISAM'


ANDTABLE_TYPE='BASETABLE'ORDERBYtable_nameDESC;

WhymytablesarecreatedasMyISAM?

InthebeginningImentionedthatmanyapplicationsareusingMyISAMexplicitly.WhatImeanbythatisthatduringtheinitialisationofthedatabase,whenalltablesarecreated,CREATETABLEstatementsoftenhaveENGINE=MyISAMsetattheend,sotablesarecreatedasMyISAMregardlessofyourdefault-storage-enginesetting.

Thusit’sagoodideatorunthequeryabovetocheckforanyMyISAMtableseverynowandthen.And,ifyou’reusingPerconaServer,youmayalsowanttosetthefollowinginmy.cnf:enforce_storage_engine=InnoDB

2.innodb_buffer_pool_size–getthebestoutofyourmemoryThisisthemostimportantInnoDBvariable.Actually,ifyou’reusingInnoDBasyourmainstorageengine,foryou–it’sTHEmostimportantvariablefortheentireMySQLserver.

WhatisInnoDBBufferPool?

Computersusemostoftheirmemorytoimproveaccesstomostcommonlyuseddata.Thisisknownascachinganditisaveryimportantpartofcomputing,becauseaccessingdataonadiskcanbe100to100,000timesslower,dependingontheamountofdatabeingaccessed.

Justthinkofit,areportthattakes1secondtogeneratewhenalldataisinmemorycouldtakeoveradaytogenerateifalldatahadtobereadfromdiskeverysingletime(assumingalsorandomI/O).

MyISAMisusingOS]ilesystemcachetocachethedatathatqueriesarereadingoverandoveragain.WhereasInnoDBusesaverydifferentapproach.

InsteadofrelyingonOStodothe“rightthing”,InnoDBhandlescachingitself–withintheInnoDBBufferPool–andyouwillsoonlearnhowitworksandwhyitwasagoodideatoimplementitthatway.

InnoDBBufferPoolismorethanjustacache

InnoDBbufferpoolactuallyservesmultiplepurposes.It’susedfor:

• Datacaching–thisisde]initelyabigpartofit• Indicescaching–yes,thesesharethesamebufferpool• Buffering–modi]ieddata(oftencalled“dirtydata”)livesherebeforeit’s]lushed• Storinginternalstructures–somestructuressuchasAdaptiveHashIndex(we’llget

toit)orrowlocksarealsostoredinsidetheInnoDBBufferPool


Here’saverytypicalInnoDBBufferPoolpagedistributionfromacustomermachinewithinnodb-buffer-pool-sizesetto62G:

�

Asyoucansee,BufferPoolismostly]illedwithregularInnoDBpages,butabout10%ofitisusedforotherpurposes.

Ohandincaseyou’rewonderingwhatunitsareusedinthisgraph,that’sInnoDBpages.Asinglepageistypically16kilobytesinsize,soyoucanmultiplythesenumbersby16,384togetasenseofusageinbytes.

SizingInnoDBBufferPool

Sowhatshouldinnodb-buffer-pool-sizebesetto?Well,itdepends.Andmostly,itdependsonhowimportantofaroleisInnoDBplaying.

DEDICATEDSERVER

OnadedicatedMySQLserverrunningfullyonInnoDB,asaruleofthumb,recommendationistosettheinnodb-buffer-pool-sizeto80%ofthetotalavailablememoryontheserver.

Whynot90%or100%?

Becauseotherthingsneedmemorytoo:

• Everyqueryneedsatleastfewkilobytesofmemory(andsometimes–fewmegabytes!)• There’svariousotherinternalMySQLstructuresandcaches• InnoDBhasanumberofstructuresusingmemorybeyondthebufferpool(Dictionary

cache,Filesystem,LocksystemandPagehashtables,etc.)• There’salsosomeMySQL]ilesthatmustbeinOScache(binarylogs,relaylogs,innodb

transactionlogs).• Plus,youwanttoleavesomeroomfortheoperatingsystemmemorystructures.


Bytheway,thisnumberisNOTpulledoutofthehat–we’veseenhundredsofsystemslargeandsmall,andevenontheserverswith512GBofRAM,wefound80%tobeabouttherightsizeforsustainableoperation.

Ifyouseealotoffreememory,sureyoucanbumpitupabit(especiallyasMySQL5.7makesthismucheasier),butdonotletMySQLconsumeallmemory,oryouwillfaceproblems.BIGproblems.Namely,swapping.

Don'tletyourdatabaseserverswap!

Swappingistheworstthingthatcanhappentoadatabaseserver–it’smuchworsethanhavingbufferpoolsizethat’snotlargeenough.OneexamplehowthismaygowrongisInnoDBusingalockthatittypicallyuseswhenaccessingapageinmemory(100nsaccesstime)toaccessthepagethatisswappedout(10msaccesstime).Thiswouldcauseallcurrentlyrunningqueriestostallandasthesethingsusuallydon’twalkalone,youcanguesswherethisisgoing…

SHAREDSERVER

IfyourMySQLserversharesresourceswithapplication,rulesofthumbnolongerwork.

Insuchanenvironmentit’smuchharderto]indtherightnumber.Ontheotherhand,it’susuallylesssoimportantto]indtherightsizeandagoodenoughnumberisoftengoodenoughinasharedenvironment.

Inanycase,]irstIchecktheactualsizeoftheInnoDBtables.Chancesare,youdon’treallyneedmuchmemory.Rememberthisqueryfromtheearliersection:

SELECTengine,count(*)asTABLES,concat(round(sum(table_rows)/1000000,2),'M')rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G')DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G')idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G')total_size,round(sum(index_length)/sum(data_length),2)idxfrac FROMinformation_schema.TABLESWHEREtable_schemanotin('mysql','performance_schema','information_schema') GROUPBYengineORDERBYsum(data_length+index_length)DESCLIMIT10;

Thiswillgiveyouanideahowmuchmemoryyou’dneedforInnoDBbufferpoolifyouwantedtocachetheentiredataset.Andnotethatinmanycasesyoudon’tneedthat,youonlywanttocacheyourworkingset(activelyuseddata).


Ifitall]itsin,say,halfthememoryontheserver,great–setittothesizeofallInnoDBtablescombinedandforgetit(forsometime).Ifnot,letmeteachyouaverysimpletricktodetermineiftheInnoDBbufferpooliswellsized.

Usingservercommandline,runthefollowing(itwillkeeprunninguntilyouhit^C):

$mysqladminext-ri1|grepInnodb_buffer_pool_reads |Innodb_buffer_pool_reads|1832098003||Innodb_buffer_pool_reads|595||Innodb_buffer_pool_reads|915||Innodb_buffer_pool_reads|734||Innodb_buffer_pool_reads|622||Innodb_buffer_pool_reads|710||Innodb_buffer_pool_reads|664||Innodb_buffer_pool_reads|987||Innodb_buffer_pool_reads|1287||Innodb_buffer_pool_reads|967||Innodb_buffer_pool_reads|1181||Innodb_buffer_pool_reads|949|

Whatyouseehereisthenumberofreadsfromdiskintothebufferpool(persecond).Thesenumbersaboveareprettydarnhigh(luckily,thisserverhasanIOdevicethatcanhandlearound4000randomIOoperationspersecond)andifthiswasanOLTPsystem,Iwouldhighlyrecommendtoincreasetheinnodbbufferpoolsizeandaddmorememorytotheserverifneeded.

Ifyoudon’thaveaccesstothecommandline,Isuggestyougetoneasyou’realotmore]lexiblethere.ButifyouwantaGUIalternative,MySQLWorkbenchisyourfriend.UnderPERFORMANCE,opentheDashboardandyouwillseeboth“InnoDBbufferpooldiskreads”andalso“InnoDBDiskReads”(mostofthetimetheygohandinhand).

Ideally,therewouldbenoreadsfromdiskhere.Ifthatisthecase,alldata]itsinthebufferpool,hencebufferpooliswellsized.IfthenumberishigherthanthenumberofrandomI/Ooperationsyourdiskscanhandle,thenit’sagoodindicationyouneedlargerinnodbbufferpool.

Anythinginbetweenshowsthatbufferpoolistoosmall,butifyou’rehappywithqueryresponsetime(astheywillbereadingfromdiskoccasionally),youmaynotneedtodoanythingjustyet.

ChangingInnoDBBufferPoolSize

Finally,here’showyouactuallychangetheinnodb-buffer-pool-size.

Ifyou’realreadyonMySQL5.7,youareextremelylucky,becausethatmeansyoucanchangeitonline!JustrunthefollowingcommandasaSUPERuser(i.e.root)andyou’redone:


https://www.mysql.com/products/workbench/

mysql>SETGLOBALinnodb_buffer_pool_size=size_in_bytes;

Wellnotexactlydone–youstillneedtochangeitinmy.cnf]iletoo,butatleastyouwillnotneedtorestarttheserverastheinnodbbufferpoolwillberesizedonlinewithsomethingalongtheselinesintheerrorlog:[Note]InnoDB:Resizingbufferpoolfrom134217728to21474836480.(unit=134217728)[Note]InnoDB:disabledadaptivehashindex.[Note]InnoDB:bufferpool0:159chunks(1302369blocks)wereadded. [Note]InnoDB:bufferpool0:hashtableswereresized. [Note]InnoDB:Resizedhashtablesatlock_sys,adaptivehashindex,dictionary.[Note]InnoDB:Completedtoresizebufferpoolfrom134217728to21474836480. [Note]InnoDB:Re-enabledadaptivehashindex.

AllearlierversionsofMySQLdorequirearestart,so:

1. Setanappropriateinnodb_buffer_pool_sizeinmy.cnf2. RestartMySQLserver3. CelebrateimprovedMySQLperformance

Yay!

3.innodb_log_file_size–roomforMySQL’sredologThissetsthesizeoftheInnoDB’sredolog]ileswhich,inMySQLworld,areoftencalledsimplytransactionlogs.AndrightuntilMySQL5.6.8thedefaultvalueofinnodb_log_file_size=5MwasthesinglebiggestInnoDBperformancekiller.StartingwithMySQL5.6.8,thedefaultwasraisedto48Mwhich,formanyintensivesystems,isstillwaytoolow.

Anyways,let’stalkalittleabouttheInnoDBLogFilessoyouhaveabetterunderstandingofwhatitis,howMySQLisusingitandhowyouwouldhavetotuneit.

WhatisInnoDBRedoLog?

HaveyoueverusedanUndoorRedofunctioninawordprocessor,imageeditororvirtuallyanyeditorforthatmatter?I’msureyouhave.Guesswhat,transactionaldatabaseshaveexactlythesamething!Wellnotexactly,buttheprinciplesarethesame.

Andjustlikeit’simportantforyoutoalwayshavetheabilitytogobackafewstepsinyoureditingprocess,soaretheundoandredofunctionsimportantforatransactionaldatabasesystem.Why?Tworeasonsprimarily:

1. Rollingbackatransaction(that’stheUndo)


2. Replayingcommittedtransactionsincaseofadatabasecrash(andthat’sRedo)

Here’showitworks.

Undo

Whenyouareusingatransactionalstorageengine(let’ssticktoInnoDBfornow),andyoumodifyarecord,thechangesarenotwrittentothedata]iledirectly.

First,theyarewrittentoaspecial]ileondiskcalledtransactionlog.Andatthesametime,theyarealsomodi]iedinmemory–theInnoDB’sbufferpool.ThisnewInnoDBpagethatcontainsyourmodi]icationsisnowcalleddirty.

TheoriginalunmodiCiedpageiscopiedtoaspecialareaondiskcalledrollbacksegment.

Sofarsogood?

Now,ifsomeone(orsomething)interruptsatransactionwithaROLLBACKbeforeit’scommitted,Undooperationneedstooccur–yourrecordhastoberestoredtoitsoriginalstate.

Asthechangesweren’twrittentodata]ileyet,thisisprettytrivial–InnoDBjustremovestheoldcopyofthepagefromtherollbacksegment,wipesthedirtypagefrommemoryandmarksinatransactionlogthatthetransactionwasrolledback.

Sothereyougo.Data]ilewasnevermodi]iedandit’sgoodthatitwasn’t,becauseyouhavecancelledanychangesyoumadebeforeevenissuingarandomwriteoperationto]lushthatdirtypagetodisk.

Redo

WhenyouCOMMITthetransaction,however,andInnoDBapprovesyourcommit(i.e.itreturnsfromtheCOMMITcall),changesarereadytobewrittentotheactualdata]iles.

You’dthinktheyarewrittentodata]ilesimmediatelyatthispoint,butthat’snotwhathappens.Why?Becausedoingsowouldbeveryinef]icient.Instead,thechangesareonlywrittentothetransactionlog(thisisveryef]icientsequentialactivitycalledRedologging),whilethemodi]iedrecordstilllivesinmemory–intheInnoDBbufferpoolasadirtypage,foraslongastimecomesto]lushit.

SowhathappensnowifMySQLcrashesatthispoint?

Well,ifInnoDBhadnoredologanditonlykeptdirtypagesinmemory–allofthecommittedtransactionsthatwerenot]lushedtodiskyetwouldbegoneforever.Quiteadisasterifyouconsiderthatoneofthesetransactionsmayhavebeenyoursalarytransferfromcompanyaccounttoyours.

Luckily,thechangesAREalwayswrittentothetransactionlog(a.k.a.REDOlog)beforetheoperationsreturnfromthecall,soallInnoDBneedstodois]indthelastcheckpointintheRedolog(positionthat’sbeensynchronisedtodisk)andRedoallofthemodi]icationsbyre-readingthe“to-be-modi]ied”datafromdiskandre-runningthesamechanges.


Easypeasy,right?

Well,right.Butonlyonthesurface.Underneath,there’salotofreallycomplexstuffhappeningthatyouprobablydon’twanttoknowaboutrightnow.Wecantalkaboutitsometimelater.

Sizema^ers

Onethingyoumaywanttoknowaboutthoughishowtosizetheinnodb_log_file_sizeproperly.Therulesareactuallyprettysimple:

• Smalllog]ilesmakewritesslowerandcrashrecoveryfaster• Largelog]ilesmakewritesfasterandcrashrecoveryslower

WritesbecomeslowwithsmallRedologbecausethetransactionlogsactasabufferforwrites.Andifyouhavealotofwrites,MySQLmaybeunableto]lushthedatafastenough,sowriteperformancedegrades.

Largelog]iles,ontheotherhand,giveyoualotofroomthatcanbeusedbefore]lushingneedstohappen.ThatinturnallowsInnoDBto]illthepagesmorefully(forexample,whenyoumodifyfewrecordsthatareonthesamepage,orinfact,modifysamerecordseveraltimes)andalso,incaseofMagneticdrives,]lushthedirtypagesinamoresequentialorder.

Asforthecrashrecovery–largerRedolog]ilesmeansmoredatatobereadandmorechangestoberedonebeforetheservercanstart,whichiswhyitmakescrashrecoveryslower.

SizingtheRedolog

Finally,let’stalkhowyoucan]igureouttherightsizefortheRedologs.

Luckily,youdon’thavetocomeupwithasizethat’sexactlyright.Here’saruleofthumbthatwefoundtoworklikemagic:

RuleofThumb:CheckthattotalsizeofyourRedologsCitsin1-2hworthofwritesduringyourbusyperiod.

HowdoyouknowhowmuchInnoDBiswriting?Here’sonewaytodoit:

mysql>pagergrepseqmysql>showengineinnodbstatus\Gselectsleep(60);showengineinnodbstatus\G Logsequencenumber1777308180429...Logsequencenumber1777354541591mysql>nopagermysql>select(1777354541591-1777308180429)*60/1024/1024;


+----------------+|(17773.../1024|+----------------+|2652.80696869|+----------------+1rowinset(0.00sec)

Inthiscase,basedon60ssample,InnoDBiswriting2.6GBperhour.So,ifinnodb_log_files_in_groupwasnotmodi]ied(andbydefaultitis2-minimumnumberofRedolog]ilesthatInnoDBneeds),thenbysettinginnodb_log_file_sizeto,say,2560M,youwillhaveexactly5GBofRedologstorageacrossthetwoRedolog]iles.

ChangingtheRedologsize

Howharditwillbetochangetheinnodb_log_file_sizeANDhowlargeyoucansetitto,dependsgreatlyontheversionofMySQL(orPercona,orMariaDB)serverthatyouareusing.

Speci]ically,ifyouareusingversionpriorto5.6,youcan’tsimplychangethevariableandexpectthattheserverwillrestart.Infactitwillstop,butwon’tstart.

Sohere’showyouhavetodoit–IhavedescribedtheprocessonPerconablogfewyearsago.Basically,it’s:

1. Changeinnodb_log_file_sizeinmy.cnf2. StopMySQLserver3. EnsureMySQLhadacleanshutdown(mysqllogisyourfriend)4. Removeoldlog]iles,usuallybyrunningthefollowingcommand:rm-f/var/lib/

mysql/ib_logfile*5. StartMySQLserver–itshouldtakeabitlongertostartbecauseitisgoingtobe

creatingnewtransactionlog]iles.

Finalthingyoushouldbeawareofisthatuntilquiterecently(i.e.untilMySQLversion5.6.2),thetotalRedologsize(acrossallRedolog]iles)waslimitedto4GB,whichwasquiteasigni]icantperformancebottle-neckforwrite-intensiveSSDbackedMySQLservers.(PerconaServer,ontheotherhand,supports512GBsincelikePerconaServer]iveousomething)Inotherwords,beforeyousetinnodb_log_file_sizeto2Gormore,checkiftheversionofMySQLyouarerunningactuallysupportsit.


https://www.percona.com/blog/2013/09/05/my-favorite-secret-mysql-5-6-feature/

4.innodb_flush_log_at_trx_commit–durableornot?Thatistheques4on!Bydefault,innodb_flush_log_at_trx_commitissetto1whichinstructsInnoDBto]lushANDsyncafterEVERYtransactioncommit.Andifyouareusingautocommit(bydefaultyouare),theneverysingleINSERT,UPDATEorDELETEstatementisatransactioncommit.

Syncisanexpensiveoperation(especiallywhenyoudon’thaveanon-volatilewrite-backcache)asitinvolvestheactualsynchronousphysicalwritetothedisk,sowheneverpossible,Iwouldrecommendtoavoidusingthisdefaultcon]iguration.

Twoalternativevaluesforinnodb_flush_log_at_trx_commitare0and2:

• 0meansFLUSHtodisk,butDONOTSYNC(noactualIOisperformedoncommit),• 2meansDON’TFLUSHandDON’TSYNC(againnoactualIOisperformedoncommit).

Soifyouhaveitsetto0or2,syncisperformedonceasecondinstead.Andtheobviousdisadvantageisthatyoumayloselastsecondworthofcommitteddata.Yes,youreadthatright–thosetransactionswouldhavecommitted,butifserverlosespower,thosechangesneverhappened.

Obviouslyfor]inancialinstitutions,suchasbanks.it’sahugeno-go.Mostwebsites,however,can(anddo)runwithinnodb_flush_log_at_trx_commit=0|2andhavenoissueseveniftheservercrasheseventually.Afterall,justfewyearsagomanywebsiteswereusingMyISAM,whichwouldloseupto30sworthofwrittendataincaseofacrash.(nottomentionthecrazyslowtablerepairprocess).

Finally,what’sthepracticaldifferencebetween0and2?Well,performancewisethedifferenceisnegligiblereally,becausea]lushtoOScacheischeap(readfast).Soitkindofmakessensetohaveitsetto0,inwhichcaseifMySQL(butnotthewholemachine)crashes,youdoNOTloseanydataasitwillbeinOScacheandsyncedtodiskfromthereeventually.

BTW,ifyoupreferdurabilityoverperformanceandhaveinnodb_flush_log_at_trx_commitsetto1,letmedrawyourattentiontothenextvariablewhichiscloselyrelated:

5.sync_binlog–that’sfordurablebinlogAlothasbeenwrittenaboutsync_binlogandit’srelationshipwithinnodb_flush_log_at_trx_commit,butletmesimplifyitforyoufornow:

a. IfyourMySQLserverhasnoslavesandyoudon’tdobackups,setsync_binlog=0andbehappywithagoodperformance


b. Ifyoudohaveslavesandyoudobackups,butyoudon’tmindlosingafeweventsfromthebinarylogsincaseofamastercrash,youmaystillwanttousesync_binlog=0forthesakeofabetterperformance.

c. Ifyoudohaveslavesand/orbackups,andyoudocareaboutslavesconsistencyand/orpointintimerecovery(abilitytorestoreyourdatabasetoaspeci]icpointintimebyusingyourlatestconsistentbackupandbinarylogs)andyouarealsorunninginnodb_flush_log_at_trx_commit=1,thenyoushouldseriouslyconsiderusingsync_binlog=1.

Problemisofcoursethatsync_binlog=1hasaprettysigni]icantprice–noweverysingletransactionisagainsyncedtodisk–tothebinarylogs.

You’dthinkwhynotdobothsyncoperationsatonce,andyou’dberight–newversionsofMySQL(both5.6and5.7,MariaDB5.5andPerconaServer5.6+)alreadyhaveaproperlyworkingbinloggroupcommit,inwhichcasethepriceforrunningwithsync_binlog=1getsreallysmall(assumingyouarerunningwithinnodb_flush_log_at_trx_commit=1anyway),butolderversionsofMySQLhaveareallysigni]icantperformancepenalty,somakesureyouwatchyourdiskwrites.

Sofarsogood?Good.Next.

6.innodb_flush_method–yourchancetoavoiddoublebufferingSetinnodb_flush_methodtoO_DIRECTtoavoiddouble-buffering.TheonlycaseyoushouldNOTuseO_DIRECTiswhenitisnotsupportedbyyouroperatingsystem.Butifyou’reonLinux,useO_DIRECTtoenabledirectIO.

DirectI/OmeansthatInnoDB’sreadandwritecallsarebypassingOScacheandaregoingdirectlytotheI/Oschedulertogetsenttothedisks.

WithoutdirectIO,double-bufferinghappensbecausealldatabasechangesare]irstwrittentoOScacheandthentheyaresyncedtodisk–soyouendupwiththesamedatainInnoDBbufferpoolANDinOScache.Yes,thatmeansinawrite-intensiveenvironmentyoucouldbelosinguptoalmost50%ofmemory,especiallyifyourbufferpooliscappedat50%oftotalmemory.Andifnot,servercouldendupswappingduetohighpressureontheOScache.

Inotherwords,dosetinnodb_flush_method=O_DIRECT,please.

7.innodb_buffer_pool_instances–reduceglobalmutexconten4onMySQL5.5introducedbufferpoolinstancesasameanstoreduceinternallockingcontentionandimproveMySQLscalability.Inversion5.5thiswasknowntoimprovethethroughputtosomedegreeonly,howeverMySQL5.6wasabigstepup,sowhileinMySQL


5.5youmaywanttobemoreconservativeandhaveinnodb_buffer_pool_instances=4,onMySQL5.6and5.7feelfreetogowith8-16bufferpoolinstances.

Yourmileagemayvaryofcourse,butwithmostworkloads,thatshouldgiveyoubestresults.

Ohandobviouslydonotexpectthistomakeanydifferencetoasinglequeryresponsetime.Thedifferencewillonlyshowwithhighlyconcurrentworkloadsi.e.thosewithmanythreadsdoingmanythingsinMySQLatthesametime.

8.innodb_thread_concurrency–havebe^ercontroloveryourthreadsYoumayhearveryoftenthatyoushouldjustsetinnodb_thread_concurrency=0andforgetit.Well,that’sonlytrueifyouhavealightormoderateworkload.However,ifyou’reapproachingthesaturationpointofyourCPUorIOsubsystem,andespeciallyifyouhaveoccasionalspikeswhenthesystemneedstooperateproperlywhenoverloaded,thenIwouldhighlyrecommendtotackleinnodb_thread_concurrency.

Here’sthething–InnoDBhasawaytocontrolhowmanythreadsareexecutinginparallel–let’scallitaconcurrencycontrolmechanism.Andforthemostpartitiscontrolledbyinnodb_thread_concurrency.Ifyousetittozero,concurrencycontrolisoff,thereforeInnoDBwillbeprocessingallrequestsimmediatelyastheycomein(andasmanyasitneedsto).

Thisis]ineifyouhave32CPUcoresand4requests.Butimagineyouhave4CPUcoresand32CPUintensiverequests–ifyouletthe32requestsruninparallel,you’reaskingfortrouble.Becausethese32requestswillonlyhave4CPUcores,theywillobviouslybeatleast8timesslowerthanusually(inreality,morethan8timesslowerofcourse),buteachofthoserequestswillhaveitsownexternalandinternallockswhichleavesgreatopportunitiesforallthequeriestopileup.

Tosolvethat,youcancontrolhowmanythreadsInnoDBallowstoexecuteatanygivenpointintime.Otherthreadswaitinalinedupqueue.Butit’sNOTasimpleFirstIn,FirstOutqueue(a.k.a.FIFO),it’smuchmoreinterestingthatthat.Here’showitworks.

AthreadenteringanInnoDBqueueisgivenacertainnumberoftickets,equaltothevalueofinnodb_concurrency_tickets-500bydefaultonMySQL5.5orearlier,5000startingMySQL5.6.

Thenitwaitsinaqueueuntilaslotbecomesavailableforit.Finally,itstartsexecuting.Andeverytimethethreaddoesacertainoperation,itlosesoneticketandgoesthroughacheckforthenumberofremainingtickets.Forexample,athreadwouldloseaticketeverytimearowisread,insertedorupdated(anditwouldlose300ticketsforasinglereadof300rows).

Nowhere’swhereitgetsinteresting-assoonasthethreadrunsoutoftickets,i.e.theticketcountdecreasesto0,thethreadisplacedatthebackofthequeueandneedstowaituntil


theslotbecomesavailableagain,assumingthecurrentnumberofrunningthreadsisabovethevalueofinnodb_thread_concurrency.Thatwaythenumberofqueriesthatareexecutingatanygivenpointislimited,yetlongrunningqueriesdon’tpreventquickqueriesfromenteringaqueueforaverylongtime.

Soyouseewealreadyhavetwovariables:

• innodb_thread_concurrency,whichcontrolshowmanythreadsareallowedtorunatthesametime,

• innodb_concurrency_tickets,whichcontrolshowmanyticketsathreadisgiveneverytimeitre-entersthequeue.

There’sonemorevariablerelatedtothis-innodb_thread_sleep_delay,whichhasadefaultvalueof10,000us.ThissetshowlonginnodbthreadssleepbeforejoiningtheInnoDBqueue,thuscontrollingtheconcurrencytoacertaindegree.

Ifyouwanttochangethevalueforinnodb_thread_concurrency,youcandoitonlinebyrunningthefollowingcommand:SETglobalinnodb_thread_concurrency=X;

Formostworkloadsandservers,8isagoodstartandthenyoushouldonlyincreaseitgraduallyifyouseethattheserverkeepshittingthatlimitwhilehardwareisunder-utilised.ToseewheretheThreadsstandatanygivenmoment,runshowengineinnodbstatus\GandlookforasimilarlineintheROWOPERATIONSsection:22queriesinsideInnoDB,104queriesinqueue

Bothinnodb_concurrency_ticketsandinnodb_thread_sleep_delaycanalsobechangedonlineifyou’dliketotryoutdifferentcon]igurationforthese.

9.skip_name_resolve–doskipthatreverseIPlookupThisisafunnyone,butIcouldn’tnotmentionitasit’sstillquitecommontoseeitnotbeingused.

Essentially,youwanttoaddskip_name_resolvetoavoidDNSresolutiononconnection.Mostofthetimeyouwillfeelnoimpactwhenyouchangethis,becausemostofthetimeDNSserverswork,theyworkwellandtheyalsotendtocacheresults.

ButwhenaDNSserverwillfail,itcouldbereallytime-consumingto]igureoutwhatarethose“unauthenticatedconnections”doingonyourserverandwhythingsallofthesuddenseemslow…

So.Don’twaituntilthishappenstoyou.Addthisvariablenowandgetridofanyhostnamebasedgrants.Theonlyexceptionhereisifyou’reusinghosts]ilebasednameresolution.OrifyourDNSserverswillneverfail(haha).


10.innodb_io_capacity,innodb_io_capacity_max–capInnoDBIOusageHere’swhatthesetwoIOcapacitysettingscontrolinanutshell:

• innodb_io_capacitycontrolshowmanywriteIOrequestspersecond(IOPS)willMySQLdowhen]lushingthedirtydata,

• innodb_io_capacity_maxcontrolshowmanywriteIOPSwillMySQLdo]lushingthedirtydatawhenit’sunderstress.

So,]irstofall,thishasnothingtodowithforegroundreads–onesperformedbySELECTqueries.Forreads,MySQLwilldothemaximumnumberofIOPSpossibletoreturntheresultassoonaspossible.Asforwrites,MySQLhasbackgroundfuzzy]lushingcyclesandduringeachcycleitcheckshowmuchdataneedstobe]lushed.Then,itwillusenomorethaninnodb_io_capacityIOPStodothe]lushing.Thatalsoincludeschangebuffermerges(changebufferisawheresecondarykeysdirtypagesarestoreduntiltheyare]lushedtodisk).

Second,Ineedtoclarifywhat“understress”means.This,whatMySQLcallsan“emergency”,isasituationwhenMySQLisbehindwith]lushinganditneedsto]lushsomedatainordertoallowfornewwritestocomein.Then,MySQLwillusetheinnodb_io_capacity_maxamountofIOPS.

Nowhere’sthe$100question:sowhatdoyousettheseto?

Bestsolution–measurerandomwritethroughputofyourstorageandsetinnodb_io_capacity_maxtothemaximumyoucouldachieve,andinnodb_io_capacityto50-75%ofit,especiallyifyoursystemiswrite-intensive.

OftenyoucanpredicthowmanyIOPSyoursystemcando,especiallyifithasmagneticdrives.Forexample,815kdisksinRAID10candoabout1000randomwriteIOPS,soyoucouldhaveinnodb_io_capacity=600andinnodb_io_capacity_max=1000.ManycheapenterpriseSSDscando4,000-10,000IOPS.

Nothingbadwillhappenifyoudon’tmakeitperfect.But.Bewarethatthedefaultvaluesof200and400respectivelycouldbelimitingyourwritethroughputandconsequentlyyoumayhaveoccasionalstallsforthe]lushingactivitytocatchup.Ifthat’sthecase–youareeithersaturatingyourdisks,oryoudon’thaveahighenoughvaluesforthesevariables.

11.innodb_stats_on_metadata–turnthemOFF!Ifyou’rerunningMySQL5.6or5.7andyoudidn’tchangethedefaultinnodb_stats_on_metadatavalue,you’reallset.


OnMySQL5.5or5.1,however,IhighlyrecommendtoturnthisOFF–thatwaycommandslikeshowtablestatusandqueriesagainstINFORMATION_SCHEMAwillbeinstantaneousinsteadoftakingsecondstorunandcausingadditionaldiskIO.

MyformercolleagueStephaneCombaudonfromPerconahaswrittenaverygoodblogpostonthis.

Ohandnotethatstartingwith5.1.32,thisisadynamicvariable,soyoudon’tneedtorestartMySQLjusttodisablethat.

12.innodb_buffer_pool_dump_at_shutdown&innodb_buffer_pool_load_at_startupItmayseemthetwovariablesinnodb_buffer_pool_dump_at_shutdownandinnodb_buffer_pool_load_at_startuparenotperformancerelated,butifyouareoccasionallyrestartingyourMySQLserver(e.g.toapplycon]igurationchanges),theyare.

Whenbothareenabled,thecontentsofMySQLbufferpool(morespeci]ically,cachedpages)aresavedintoa]ileuponshutdown.AndwhenyoustartMySQL,itstartsabackgroundthreadthatloadsbackthecontentsofbufferpoolandimprovesthewarm-upspeedthatwayupto3-5times.

Coupleofthings:

First,itdoesn’tactuallycopythecontentsofthebufferpoolintoa]ileuponshutdown,itmerelycopiesthetablespaceIDsandpageIDs–enoughinformationtolocatethepageondisk.Then,itcanloadthosepagesreallyfastwithlargesequentialreadsinsteadofhundredsofthousandsofsmallrandomreads.

Second,loadingofthecontentsonstartuphappensinthebackground,henceMySQLdoesn’twaituntilthebufferpoolcontentsareloadedandstartsservingrequestsimmediately(soit’snotasintrusiveasitmayseem).

Third(Iknow,Iknow,threeisn’treallyacoupleanymore),startingwithMySQL5.7.7,only25%ofleastrecentlyusedbufferpoolpagesaredumpedonshutdown,butyouhaveatotalcontroloverthat–useinnodb_buffer_pool_dump_pcttocontrolhowmanypages(expressedinpercents)wouldyouliketobedumped(andrestored).Ivote75-100.

Andfourth(sorry!),whileMySQLonlysupportsthissinceMySQL5.6,inPerconaServeryouhadthisforquiteawhilenow–soifyouwanttostayonversion5.1or5.5,orifyouarealreadyusingPerconaServer,checkthisout(version5.1link).


https://www.percona.com/blog/2013/12/03/innodb_stats_on_metadata-slow-queries-information_schema/

https://www.percona.com/doc/percona-server/5.5/management/innodb_lru_dump_restore.html

https://www.percona.com/doc/percona-server/5.1/management/innodb_lru_dump_restore.html

13.innodb_adap4ve_hash_index_parts–splittheAHImutexIfyou’rerunningalotofSELECTqueries(andeverythingelseisperfectlyintune),thenAdaptiveHashIndexislikelygoingtobeyournextbottle-neck.

AdaptiveHashIndexesaredynamicindexesmaintainedinternallybyInnoDBthatimproveperformanceforyourmostcommonlyusedquerypatterns.Thisfeaturecanbeturnedoffwithaserverrestart,butbydefaultitisONinallversionsofMySQL.

Whileit’squiteacomplextechnology(moreonithere),inmostcasesitgivesaniceboosttomanytypesofqueries.Thatis,untilyouhavetoomanyquerieshittingthedatabase,atwhichpointtheystartspendingtoomuchtimewaitingontheAHIlocksandlatches.

Ifyou’reonMySQL5.7,youwon’thaveanyissueswiththat–innodb_adaptive_hash_index_partsvariableisthereandit’ssetto8bydefault,sotheadaptivehashindexissplitinto8partitions,thereforethere’snoglobalmutexandyou’regoodtogo.

AllMySQLversionsbefore5.7,unfortunately,havenocontroloverthenumberofAHIpartitions.Inotherwords,there’soneglobalmutexprotectingtheAHIandwithmanyselectqueriesyou’reconstantlyhittingthiswall.

Soifyou’rerunning5.5or5.6,andyouhavethousandsofselectqueriespersecond,theeasiestsolutionwouldbetoswitchtosameversionofPerconaServerandenableAHIpartitions.ThevariableinPerconaServeriscalledinnodb_adaptive_hash_index_partitions.

14.query_cache_type–ON?OFF?ONDEMAND?AlotofpeoplethinkQuerycacheisgreatandyoushouldde]initelyuseit.Well,sometimesit’strue–sometimesitisuseful.Butit’sonlyusefulwhenyouhavearelativelylightworkloadandespeciallyiftheworkloadisprettymuchread-onlywithveryfeworvirtuallynowrites.

Ifthat’syourworkload,setquery_cache_type=ONandquery_cache_size=256Mandyou’redone.Note,however,youshouldneversetthequerycachesizeanyhigher,oryouwilllikelyrunintoseriousserverstallissuesduetoquerycacheinvalidation.I’veseenthishappentoomanytimesanduntilsomeone]iguresoutawaytosplitaglobalquerycachemutex,thiswillnotgoaway.

Ifyouhaveanintensiveworkload,however,thenyoushouldnotonlysetthequery_cache_size=0butalsoit’sveryimportantthatyousetquery_cache_type=OFFandrestarttheserverwhenyoudothat–thiswayMySQLwillstopusingquerycachemutexforallqueries–eventhosethatwouldn’tusethequerycacheanyway.

BTW,thisworkswithMySQL5.5ornewer,youcan’treallydisablethequerycachemutexintheearlierversionsofMySQL.InPerconaServer,youcanalsodisableitinversion5.1.


https://dev.mysql.com/doc/refman/5.5/en/innodb-adaptive-hash.html

Soifyou’restillusingthequerycacheandyoushouldn’t,makethesechangesnowbecauseyourqueriesarelikelyalreadysufferingduetoquerycachemutexcontention.

15.innodb_checksum_algorithm–thesecrethardwareaccelera4ontrickMostmainstreamCPUsnowadayssupportnativecrc32instructionsandMySQLcan]inallymakeuseofthattoimprovethespeedofcalculatingtheInnoDBchecksumssigni]icantly.

Toenablethat,settheinnodb_checksum_algorithm=crc32.ThisisavailablesinceMySQL5.6.StartingwithMySQL5.7.7,innodb_checksum_algorithm=crc32issetbydefault.

Checksumsarecalculatedeverysingletimeapage(orlogentry)isreadorwritten,sothisisde]initelyYUUGE!(right,Donald?)

OhandBTW,thisistotallysafetochangeonaserverthatalreadyhastablescreatedwithchecksumtype“innodb”.Infact,youcanchangeitdynamically,online,whiletheserverisrunning(There,Isaiditthreetimes,nowit’sfullyredundant).

16.table_open_cache_instances–useitStartingwithMySQL5.6.6,tablecachecanbesplitintomultiplepartitionsandifyou’rerunningMySQL5.6ornewer,youshouldde]initelydothat.

Tablecacheiswherethelistofcurrentlyopenedtablesisstoredandthemutexislockedwheneveratableisopenedorclosed(and,infact,inanumberofothercases)–evenifthat’sanimplicittemporarytable.

Andusingmultiplepartitionsde]initelyreducespotentialcontentionhere.I’veseenthisLOCK_openmutexissueinthewildtoomanytimesanditcausesagreatdealoftrouble,especiallywithslowerdiskI/Oor]ilesystems.

StartingwithMySQL5.7.8,table_open_cache_instances=16isthedefaultcon]iguration,andI’dsayitisde]initelyagoodstartingpointbothonMySQL5.6and5.7.

17.innodb_read_io_threads&innodb_write_io_threads–lastand,yes,leastI’veplacedthislastbecauseit’sreallynotasimportantasitmayseem.

Firstofall,yourMySQLislikelyalreadyusingAsynchronousIO(AIO)whichonLinuxissupportedsinceMySQL5.5(onWindows,it’sbeensupportedforawhilenow).


Second,bothinnodb_read_io_threadsandinnodb_write_io_threadsareonlyusedforthebackgroundactivity,suchascheckpointing(]lushingdirtypagestodisk),changebuffermergeoperationsandsometimes,read-aheadactivity.

So,whileit’snotakeyvariabletotune,aligningittothenumberofbearingdisksisstillagoodidea.SoforexampleonRAID10with8disks,youmaywanttohaveinnodb_read_io_threads=8andinnodb_write_io_threads=4.IfyouhaveanSSD,wellthenjusthaveitaround16-32,butdonotexpectmuchperformancedifference,unlessyourserverisextremelywrite-heavyanddiskIOisthebottle-neck.


TheEnd(Almost)We’venowcoveredallthedifferentMySQLdistributions,rightandwrongwaystoapproachMySQLcon]igurationandthekeyvariablestolookat.It’squiteamazinghowmuchyoucanimprovethingswithsmallchangesoverthevanillaMySQLcon]iguration,isn’tit?Butyouknowwhat-wehavenonlymade]irstperformanceoptimizationsteps.

Imean,improvingMySQLcon]igurationwillde]initelyimpactitsperformancegreatlyanditissurelythefoundationofawellperformingMySQLserver.

However,ifyouhaveaserverthatisotherwisetunedprettywell,youshouldn’texpectafewcon]igurationchangestoimprovethingsdrastically.Mostofthetime,thedevilisinthequeries.

OccasionallyIamrunningfreewebinarsonqueryoptimizationandsimilartopicswhereyoucanlearnmorein-depthaboutMySQLPerformanceOptimizationandgetyourquestionsanswered.Youcanseealistofupcomingwebinarsandsignupatspeedemy.com/webinars.

Alsodonotforgettodownloadthemy.cnfIhavementionedifyouhave’tdonesoyet.

Nowlet’smovetothe]inal-bonus-partofthisebook.


http://www.speedemy.com/webinars


Bonus:UnderstandingMySQLStatusCountersIntheearlydaysofMySQL,MySQLstatuswasprettymuchtheonlywaytolookatMySQLperformance,thereforeitwasaprettycommonpracticeamongstMySQLconsultantsandsupportengineerstolookatMySQLstatustounderstandwhat’shappeningwiththeserver.

Andeventhoughnowtherearebetterwaystolookatspeci]icissues,forexampleanalyzequeryperformanceusingtheextendedslowqueryloginPerconaServerorPERFORMANCE_SCHEMAstartingwithMySQL5.6,MySQLstatusisstillagoodwaytolookatthegeneralmetricsoftheserver.However,it’sreallyimportanttolookatitcorrectly,whichiswhatIwanttoshowyounow.

Inthisquicklesson:

• Iamgoingtoshowyouhowtolookatmysqlstatustherightway• Wewillidentifyfewkeystatusvariablestolookat• AndyouwilllearnwhatfreetoolsyoucanusetomakemonitoringMySQLstatusmuch

easier

HowtoLookAtMySQLStatusFirst,letmeshowyouwhatisthewrongwaytolookatMySQLstatus.Runshowglobalstatus;inMySQLconsoleormysqladminextinthecommandlineandyouwillbeoverwhelmedwithhugenumbers.

Thesecommandswillshowyouthecurrentcountforeachstatusvariable.Ohandbearinmindthatnotallstatusvariablesarecounters,somearegauges,displayingcurrentvalue.Youwillhavetolookattheseseparately.

So,therightwaytolookatstatusvariablesisoveracertainperiodoftime.Forexample,ifyourunthefollowingcommand,youwillgettotalcountforeachcounternow,followedbyatotalcounter60secondslater:$mysqladminext-i60-c3

-cspeci]ieshowmanyiterationstoshow,henceinthiscasecounterswillbedisplayed3times.

Nowthisisstillnotveryuseful,becauseformostvariablesyouareinterestedindeltaratherthanthetotalsandIwillshowyoutwowaystogettherelativenumbers:

• Add-rtomysqladminandthatwillshowyoudeltarightaway:

$mysqladminext-ri60-c3

• Another,moreelegantwaytogetthesamedataisbyusingpt-mext:

$pt-mext-r--mysqladminext-i60-c3


AlthoughIshouldadmit,Ineveruseitthatway.Instead,Iwould]irstwritemysqladminoutputintoa]ileandthenIwouldreadthat]ilewithpt-mext:

$mysqladminext-i60-c3>mysqladmin.txt$pt-mext-r--catmysqladmin.txt

Thisallowsmetogetadifferentcutforthesamedatainmysqladmin.txt.Forexample,Icancollect100samplesevery1sandthenanalyzethe]ilewithgreptolookatgauges,andusept-mexttoanalyzecounters.

KeyMySQLStatusVariablesToLookAtNowlet’stakealookatafewvariablesthataremoreinteresting.Bewarethatwe’refocusingonperformanceonly,sowewillnotlookatall400counters.

Com_*Counters

Ialwayslookatthesetounderstandtheworkloadthatserveriscurrentlydealingwith:

Com_beginCom_commitCom_deleteCom_insertCom_selectCom_update

Thiswillgiveyouanideaofhowmanycommandspersecondtheserverisrunningatanygivenpoint(soyoucancorrelatethesetoanyothercounters)

Temporarytables

It’salwaysagoodideatoseehowmanytemporarytablesyouhaveondisk-vs-totalnumberoftemporarytables,soyouhavetolookatthefollowingvariables:

Created_tmp_disk_tablesCreated_tmp_tables

Notethatveryoftenthereasonon-disktemporarytablesarecreatedisnotatoosmallsettingfortmp_table_sizeormax_heap_table_size,ratherit’svariablesizecolumnsthatareusedinthequeries,e.g.textorblobcolumnsthatcan’tbeusedinthe]ixedsizetemporarytables.Forthese,temporarytableondiskwillbeusedevenifthetemporarytablewillcontainasinglerecord.

Otherwise,manytemporarytablescanbeavoidedbyreviewingthequeriesthatcreatethemand]ixinginef]icientexecutionplansbyeitherindexesorchangesinthequeries.


Handler_*Counters

Theseareinternaloperationcounters,oftenaccountingforeverysinglerecordaccess.Themostinterestingonesyouwanttolookatare:

Handler_read_firstHandler_read_keyHandler_read_nextHandler_read_prevHandler_read_rnd_next

Forwhattheystandfor,pleasehavealookatMySQLmanualoverhere.However,thesecountersaremuchmoreinterestingtolookatwhenyou’reanalysingabehaviourofaspeci]icquery.Forthat,youwanttousesessionstatuscountersinthefollowingfashion:

mysql>flushstatus;mysql>RUNYOURQUERY;mysql>showstatus;

Thatwillgiveyouamuchbetterunderstandingofwhataspeci]icqueryisdoinginternallyandpotentiallyhowyoucanoptimizeit.

Innodb_*Counters

InnoDBhasplentyofcounterstolookat.Additionally,youcangetawholelotdeeperwithshowengineinnodbstatus\Gcommand.Here’sthemosttypicalthingsIwouldlookatandwhy:

• Innodb_buffer_pool_pages_flushed-numberofpages]lushedfrombufferpool-goodwaytomonitorpage]lushactivity.

• Innodb_buffer_pool_reads-numberofDiskIOcallstoreadintothebufferpool-seehowclosethatistohowmanyrandomreadscanyourdisksactuallydeliver.

• Innodb_data_fsyncs-numberoffsync()callsexecuted-seeifit’snottoohighforyourhardware.

• Innodb_data_pending_*-gaugesshowinganumberofpendingfsync,readorwritecalls-couldpotentiallyshowsaturatedIOresources.

• Innodb_data_reads/writes-numberofrandomread/writediskIOoperationsfordata]ilesspeci]ically.

• Innodb_history_list_length-gaugeshowinganumberoftransactionsthathaven’tbeencleanedupafter.

• Innodb_ibuf_merges-numberofinsertbuffermergeoperations.HighnumbersherecouldexplainintenseIOspikes.


http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Handler_read_first

• Innodb_log_waits-numberoftimeslogbufferwastoosmall.Goodindicatorinnodb_log_buffer_sizeneedsaraise.

• Innodb_lsn_current-numberofbyteswrittentothetransactionlog.Helpsyoutuneinnodbredolog]iles.

• Innodb_mutex_os_waits-ifthisishigh,youcouldbehavinginternalmutexcontention.

• Innodb_rows_*-helpsyouunderstandinternalactivity-numberofrowsread,inserted,deletedorupdated.

• Innodb_row_lock_time*-showshowmuchtimeisspentonlogicallocks.

NotethatsomeofthesevariablesareonlyavailableinPerconaServer.Ifyoucan’t]inditonyourserver,checkshowengineinnodbstatus\G-it’snotasconvenient,butthat’swhereyouwill]indeverythingaboutInnoDBoperation.

Opened_*stuff

Checkthesetounderstandifyour]ilecachesaresizeddecently.Ideally,Opened_tablesandOpened_table_definitionsshouldnotbeincreasingmuchoratall.

Querycache

YoucanmonitorthequerycacheactivitybylookingatQcache_*counters.MostimportantistocompareQcache_hitstoCom_selecttounderstandhowmanySELECTqueriesoutoftotalareservedfromthequerycache.AlthoughQcache_lowmem_prunesandQcache_insertscanbejustasinterestingtounderstand.Notehoweverthatevenifyoudoseelowmemprunes,donotincreasethesizeofthequerycacheabove256MB.

Selectcounters

There’safewinterestingSelect_*counterstolookat,here’swhattheystandfor:

• Select_full_join-showsanumberofqueriesthatmadeatablescanduringajoin(evenifjoinbufferwasused).Thesecouldbeprettybadqueriesinsomecases,butsometimesit’salsotinytablesthatareveryfasttojoinanyway.

• Select_full_range_join-numberofjoinsthatusedarangesearchonareferencetable.Thesearen’tcommon,buttheyaren’talwaysharmfuleither.

• Select_range-thisisaverycommonrangeaccesspatternusedbyqueries.FYIonly.

• Select_range_check-numberofjoinswithoutkeys,withadditionalkeyusagecheckeachtimerowisread.Goodindicatorofbadindexing.Rarelyseeninthewildthough.


• Select_scan-tablescanonthe]irst(ortheonly)tableinthejoin.Alsoshowsbadindexing.

So,ifeitherSelect_full_join,Select_range_checkorSelect_scanisrelativelyhigh,chancesareyouhaveaprettybadindexingandIhighlyrecommenddoingaqueryoptimizationround.

Threads_*counters

OneofthesecountersisthemainindicatorofanyperformanceissuesinMySQLandIuseitinmytroubleshootingpracticeallthetime.Here’swhattheThreadscountersshow:

• Threads_cached-gauge,showingacurrentnumberofcachedthreads.Notsuperinteresting.

• Threads_connected-numberofthreadscurrentlyconnectedtotheserver.Alotofthesecouldbesleepingthreadsandsleepingthreadsareverycheaptohave,sodon’tmindthesetoomucheither.

• Threads_created-counter,showinganumberofthreadscreatedbecausethenumberofcachedthreadswasn’tenough.Ifthisishigh,considerincreasingthethread_cache_size.

• Threads_running-]inally,thisisthemostinterestingcounterofthemall.ItisagaugeshowinganumberofthreadsthatarecurrentlyexecutinginsideMySQL.TheycouldbedoinganythingfromwaitingintheInnoDBqueuetocommittingdatatodisk,butthekeythingisthatifthisnumberisoccasionallyabnormallyhigh(i.e.largerthanthenumberofCPUcoresordiskspindlesthatyouhave),youaremostlikelyhavingprettyseriousMySQLPerformanceissues.

Seethefollowingblogposttolearnhowyoucandealwiththose.

FreeToolsToMakeMonitoringMySQLEasierFinally,let’slookatsomefreetoolsthatwillmakeyourlifebetterwhenitcomestoMySQLStatusmonitoring.

Cac4&&Zabbix

Ifyou’renotusingeitheroftwo,Iwouldhighlyrecommendtogiveitatry:

• Cacti• Zabbix

However,theyarenotusefulforMySQLmonitoringinitself,youneedtoaddsomeadditionaltemplates.Perconahasareallynicepackagejustforthat:


http://www.speedemy.com/mysql-troubleshooting-crash-course/

http://www.cacti.net

http://www.zabbix.com

• PerconaMonitoringPluginsforMySQL

pt-mext

Ihavealreadymentionedpt-mext,whatIdidnotmentionhowever,ishowyoucangetit.YoucaneitherinstallPerconaToolkitwhichisnowincludedinanumberofdistributionstoo,oryoucandownloadpt-mextasastandalonecommandlineutilitywhichiswhatIoftendowhendoingaconsultinggig:

$wgetpercona.com/get/pt-mext$chmod+xpt-mext$./pt-mext-r--mysqladminext-i1-c5

innotop

It’satop-likecommandlinetooltomonitorMySQLStatuswithsomeemphasisonInnoDB.It’sareallycomprehensivetoolandIhighlyrecommendtocheckitout.

WhatnowDidIanswerallofyourquestionsrelatingtoMySQLcon]iguration?Ifnot,leaveacommentheresowecanhaveadiscussion.

Otherwise,ifyou’redonewithMySQLcon]iguration]ile,nextlogicalstepintheoptimizationprocesswouldbequeryoptimization.Andit’sawholenewdisciplinetolearn.

IamcurrentlyworkingonaMySQLperformanceoptimizationcourse,soifyou’dliketolearnwhenit’s]inished,signupforanewsletteronspeedemy.comandIwillletyouknowassoonasitbecomesavailable.


https://www.percona.com/software/mysql-tools/percona-monitoring-plugins

https://www.percona.com/software/mysql-tools/percona-toolkit

http://innotop.googlecode.com/svn/html/manual.html

http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/

http://www.speedemy.com/signup/

copyright © 2016 speedemyspeedemy.com/files/ebook1/speedemy-mysql-configuration-tuning … · so...

Documents