值得收藏！官方推荐的MySQL参数设置值

概述

今天，在找MySQL补丁的时候，在metalink找到一篇非常好的文章。这是oracle官方推荐的在OLTP环境下，MySQL参数设置的最佳实践。

下面的参数设置，对系统的性能会很有帮助。但是建议大家还是结合实际情况使用。

PURPOSE

StronglyrecommedinitialsettingsforMySQLServerwhenusedforOLTPorbenchmarking.

SCOPE

ForDBAshavingOLTP-likeworkloadsordoingbenchmarking.

DETAILS

Ifasupportengineeradvisesyoutochangeasetting,acceptthatadvicebecauseitwillhavebeengivenafterconsideringthedatatheyhavecollectedaboutyourspecificsituation.

Changestomakeinallcases

Theseimproveonthedefaultstoimproveperformanceinsomecases,reducingyourchanceofencounteringtrouble.

innodb_stats_persistent=1CheckpingreadrequestsinSHOWENGINEINNODBSTATUStoseeifmoremightbeuseful,ifseldommorethan64*innodb_read_io_threads,littleneedformore.

innodb_write_io_threads=4

table_open_cache_instances=16,5.7.3.

Mainsettingstoreview

Alsomaketheseadditionsandadjustasdescribedtofindreasonablyappropriatevalues:

innodb_buffer_pool_size:thesinglemostimportantperformancesettingformostworkloads,_buffer_pool_instances(5.6manual)fromthe8defaulttobufferpoolsize/2GB(so32for64gpool)ifconcurrencyishigh,someoldbenchmarkresultstoillustratewhy..

innodb_stats_persistent_sample_pages:avalueinthe100to1000rangewillproducebetterstatisticillbedoneforeachindex,sousesomecareaboutsettingittoverylargevalues.

innodb_flush_neighbors=0

innodb_page_size:consider4kforSSDbecausethisbettermatchestheinternalsectorsizeonolderdisksbutbeawarethatsomemightusethenewer16ksectorsize,ifso,

innodb_io_capacity:forafewspinningdisksandlowerSSDthedefaultisOK,,seemoremodified//_io_capacity_maxtoohighandwastingdiski/ooperationswithprematureflushing.

innodb_io_capacity_max:forafewspinningdisksandlowerSS,_io_capacitywilloftenbeagoodchoiceandthiscanneverbelowerthaninnodb_io_capacity.

innodb_log_file_size=2000,

innodb_lru_scan_depth:/_buffer_pool_instancessetshowmuchworkthepagecleanerthreaddoeseachsecond,esamewheneveryouchangeinnodb_buffer_pool_instances,muc,give100atryinsteadofthedefaultifyoujustwantalowerstartingpointforyourtuning,_page_cleanerstolowerofCPUcountorbufferpoolsifitcannotkeepup,therearelimitstohowmuchwritingonethreadcangetdone;4,5.7(s)canbeseriouslydelayediftherearenotusuallyfreepages,“LogMessages:page_cleaner:1000”usuallyindicatethatyouhavethepagecleanertoldtodomoreworkthanispossibleinonesecond,soreducethescandepth,,particularlyimportantnottotellittodotoomuchwork,s.

innodb_checksum_algorithm=strict_crc32ifanewinstallation,gesystemslikebus-attachedflashsuchasFusion-IOwithhighwriterates.

innodb_log_compressed_pages=0,onecompressed,onenot,,somethingthatshouldoftenbeavoidedifpracticalthoughitcanhelpwithcommitratesifyoudonothaveawritecachingdiskcontroller,atthecostofprobablyquiteshortenedSSDlifetime.

binlog_row_image=minimalassumingalltableshaveprimarykey,unsafeifnot,,somethingthatshouldoftenbeavoided.

table_definition_cache:_table_,ifthathappens,:,calculationrulesand400canbeabouthalfthatof401ifnoothersettingcauseslargerules.

table_open_cache:setnosmallerthantable_definition_cache,_,ifthathappens,:,,ercalculationrulesand2000canbeabouthalfthatof2001ifnoothersettingcauseslargerules.

max_connections:,considerusinga:eservercalculationrulesand151canbeabouthalfthatof302ifnoothersettingcauseslargerules.

open_files_limit:,

sort_buffer_size=32kislikelytobefasterforOLTP,_merge_oodandsmallersort_buffer_,settingthisto2Mcanreducethroughputforsomeworkloadsby30%_merge_passes,identifythequeriesthatareperformingthesortsandeitherimproveindexingorsetthesessionvalueofsort_buffer_sizetoalargervaluejustforthosequeries.

innodb_adaptive_hash_index(5.6manual)Tryboth0and1,0mayshowimprovementifyoudoalotofindexscans,’_adaptive_hash_index_partswasadded,thismaychangethebestsettingfrom0to1forsomeworkloads,

innodb_doublewrite(5.6manual)consider0/offinsteadofthedefault1/

WherethereisarecommationtocheckSHOWGLOBALSTATUSoutputyoushoulddothata

SSD-specificsettings

Ensurethattrimsupportisenabledinyouroperatingsystem,itusuallyis.

Setinnodb_page_size=4kunlessyouwantalargersi_flush_neighbors=0.

MemoryusageandInnoDBbufferpool

ForthecommoncasewhereInnoDBisstoringmostdata,settinginnodb_buffer_pool_,likelyatleast50%onadedicateddatabaseserver.

ThePerformanceSchemacanbeafarmoresubstantialuserofRAMthaninpreviousversions,,MANCE__connections,open_files_limit,table_open_cacheortable_definition_cacheabovethedefaultscausesPSto_connections,table_definition_cacheandtable_open_cachearethesameasorlowerthantheir151,400hesmallmemoryconsumption().Ifanyismorethantwicethedefault,largeruleswillbeusedandthememoryusagecanbeabouteighttimesthesmallconsumption().Forthisreason,avoidgoingjustoverthe302,800and4000valuesforthesesettingsifPSisbeingused,,ikelytobetroublesomeandmemoryusagewillvarymorewithdemandthanstartupsettings.

Veryfrequentandunnecessarilylargememoryallocationsarecostlyandper-connectionallocation:read_buffer_size,read_rnd_buffer_size,join_buffer_size,sort_buffer_size,binlog_cache_sizeandnet_buffer_,increasingsort_buffer_sizefromthedefault256kto4MwasenoughtocutOLTPperformancebyabout30%,doitonlyinthesessionrunningthequerythatneedssomethingdifferent.

Theoperatingsystemislikelytocachethetotalsizeoflogfilesconfiguredwithinnodb_log_file_

Thread_stackisalsoasessionsettingbutitissettotheminimumsafevalueforusingstoredprocedures,’

Operatingsystems

CPUaffinity:ifyouarelimitingthenumberofCPUcores,useCPUaffinitytousethesmallestpossiblenumberofphysicalCPUstogetthatcorecount,/AFFINITYortheTaskManageraffinitycontroloptions.

Linux

Memoryallocator:_safe—malloc-liboptionorLD_PRELOADorexperimentwiththesimilarandpossiblyslightlyfasterjemalloc,whichmightdobetterwithmemoryfragmentation,oseewhichworksbestforyourworkloadandsystem.

IOscheduler:,perhapsonSANsystems,/sys/block/{DEVICE-NAME}/queue/scheduler.

nice:usingnice-10inmysqld_safecanmakeasmallperformancedifferenceondedicateservers,,use-19soyoucanstillsettheclientmysqlto-20togetinandkillaroguequery.

Usecat“/proc/pgrep-nmysqld/_files_limitsettingshouldsetthisbutverifyandadjustdirectlyifneeded.

Itisoftensuggestedtouse“numactl–interleaveall”topreventheav,usingmultipleInnoDBb,checkusingSHOWVARIABLESwhetheryourversionhasbeenbuiltwithsupportforthesettinginnodb_numa_,turniton,(MPOL_INTERLEAVE)beforeallocatingthebufferpool(s)thenbacktostandard(MPOL_DEFAULT)

=1in/etc/increasetheOSdiskcachesize,areally,getNUMAsettingsinplacebeforeblamingswapping,velsifNUMAsettingsaren’tright,thefixistoadjusttheNUMAsettings,notswappiness.

,,higherorlowercanbeuseful,youmighttryexperimentingwithecho1000/sys/block/[DEVICE]/queue/nr_,morelikelyonRAIDsetups.

,/proc/sys/vm/dirty_background_ratioandecho60/proc/sys/vm/dirty_/etc/:_background_ratio=5_ratio=60.Pleasedofollowtheinstructiontotest,itisvitalnottojustchangethisand5and60arejustexamples.

Toolstomonitorvariouspartsofalinuxsystem.

LinuxFilesystems

Werecommthatyouuseext4mountedwith(rw,noatime,nodiratime,nobarrier,data=ordered)unlessultimatespeedisrequired,robablyimproveyourwriteperformancebyasmuchas50%byusingtheext4optiondata=journalandthentheMySQLoptionskip–innodb_

XFSislikelytobefasterthanext4,perhapsforfsyncspeed,(rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k).

ext3isn’,

NFSinhomebrewsetupshasmorereliabilityproblemsthanNFSinprofessionalSANorotherstoragesystemswhich’sabalanceoffeaturesandperformance,wi,,notaproblemnormallyobservedinpractice,considerusingTCPandhard,intrmountoption.

Windows

Tosupportmoreconnectionsorconnectionrateshigherthanabout32persecondyoumayneedtosetMaxUserPorthigherandTcpTimedWaitDelayforTCP/IP,_taftertheportlimitisreached,resumingataslowrateasthetimeoutslowlyfreesports.

Hardware

Battery-backedwrite-cachiisks,,abledtheharddrivewritebuffers.

Itisbesttosplitfilesacrossdisktypesinthesegeneralgroups:

SSD:data,InnoDBundologs,maybetemporarytablesifnotusingtmpfsorotherRAM-basedstorageforthem.

Spinningdisks:Binarylogs,InnoDBredologs,,largeSATAdrivesarecheapandusefulforworkingandarchivalspaceaswellasthebiggestofbulkdatasets.

Bus-attachedSSD:thetableswiththeveryhighestchangeratesithemosthighlyloadedsystemsonly.

YoucanputindividualInnoDBtablesondifferentdrives,allowinguseofSSDforfaststorageandSATAforbulk.

itisenabled,

Threadpool

Usethethreadpoolifyations,whichistypical,useinnodb_thread_concurrencyifyouseethatyourserverhastroublewithabuild-upofqu,thennegativescalabilitybutinnodb_thread_concurrency=0hasloweroverheadwhenthatregulatingisnotneeded,t,butwhichdoesnotsetinnodb_thread_concurrency,thatisanindicationthatyoushouldnottrustthebenchmarkresult:olimittoInnoDBconcurrency.

Background

Herearemoredetailsofwhysomeofthesechangesshouldbemade.

innodb_stats_persistent=1

EnablespersistentstatisticsinInnoDB,,mainlytheneedforANALYZETABLEsometimes.

innodb_read_io_threads=16,innodb_write_io_threads=4

IncreasesthenumberofthreadsusedforsometypesofInnoDBoperation,,thoughsometimescontentionfordiskresourcesbetweenthesethreadsandforegroundthreadsmightbeanissueifdiskutilisationisnear100%.

table_open_cache_instances=16

Improvesthespeedofoperationsinvolvingtablesathigherconcurrencylevels,imelackofcostitisbesttosetithighenoughandneverworryaboutit.

metadata_locks_hash_instances=256

Reducestheeffectsoflocking,thehashingalgorithmusedhasbeenshowntobenon-idealforsomesituationsandthatalsomakesitdesirabletoincreasethisvalueabovethedefault,’readdressingthathashalsobutthiswillstillbeausefulsettingwithnosignificantnegatives.

innodb_flush_neighbors=0

Whensetto1InnoD,1,0isprobablybest.

innodb_log_file_size=2000M

Thisisacriticalsettingforworkloadsthatdolotsofdatamod5%.Youmustalsoconsidertheeffectofyouradaptiveflushingsettingsandensurellusuallybecachedinoperatingsystgthatthisissettoasuitablyhighvaluetoallowlotsofdirtypagecachingandwritecombiningbeforepagesareflushedtodisk.

table_definition_cache

Reducestheneemoryusageconsiderations.

table_open_cache

emoryusageconsiderations.

sort_buffer_size=32k

eiseeveryexcessivenumbersofSort_merge_passes–,itisfarbettertoadjustthesettingonlhentheworkloadismixeditcanbeusefultousehigherthanidealOLTPvaluestoaddresstheneedsofthemixtureofqueries.

Otherobservations

Querycache

,,_free_blocksinglobalstatusisnotabove10,000.5,000,checkwithFLUSHQUERYCACHE,whichdefragmentsthefreelist,abytes,ifyou’reusinghundredsofmegabytesyoushouldcheckperformancewithgreatcare,it’:

SELECTEVENT_NAMEASnm,COUNT_STARAScnt,sum_timer_wait,CONCAT(ROUND(sum_timer_wait/1000000000000,2),‘s’)ASsec

FROMperformance__stages_summary_global_by_event_nameWHERECOUNT_STAR0ORDERBYSUM_TIMER_WAITDESCLIMIT20;

AlsoseeMySQLQueryCacheFragmentationSlowsDowntheServer().

Sync_binlogandinnodb_flush_log_at_trx_commit

T_binlog=1canbeexpectedtocauseperhapsa20%throughputdropwithconcurrentcommitand30connectionstryingtocommit,aneffectthatreducesasactivelyworkingconnectionscou,justsetsync_binlogto0andobserve,thensetinnodb_flush_log_at_trx_commit=0_flush_log_at_trx-commit=2also,iskswithlowconcurrencyandlowestathigherconcurrencyonfastSSDorwithwritecachingdiskcontrollers.

Notethatitismandatorytouseinnodb_flush_log_at_trx_commit=1fulldurabilitycombinedwithlowperformancepenaltyisachieved.

真心看不懂的朋友也可以用百度翻译直接翻译着看，当然建议是多习惯去用英文看，毕竟大多数官网都是英文的。

很赞哦!（76）