mariadb roadshow 2016: optimizing for availability, scalability, and performance
TRANSCRIPT
![Page 1: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/1.jpg)
1
MariaDBRoadshow2016
MariaDBOp*mizingforavailability,scalability,andperformance
![Page 2: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/2.jpg)
2
Contents
Availability
Performance
Scalability
1.
2.
3.
![Page 3: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/3.jpg)
SecurityStack• Data-at-RestEncrypBon• DatabaseFirewall• PasswordValidaBonandmore
HighAvailability• Built-inGaleraCluster
Replica*onScalability• NewReplicaBonMethods
Performance• InnoDBPageCompression• DefragmentaBon
NewinEnterpriseSpring2016
![Page 4: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/4.jpg)
4
HighAvailabilityBackground
HighavailabilityisasystemdesignprotocolandassociatedimplementaBonthatensuresacertaindegreeofoperaBonalconBnuityduringagivenmeasurementperiod.
![Page 5: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/5.jpg)
5
HighAvailabilityBackground
HighAvailabilityisn’talwaysthesameaslongUp*me• Asystemis“up”butmightsBllnotbeaccessible• Asystemthatis“down”justonce,butforalongBme,isn’thighlyavailable
HighAvailabilityrathermeans• LongMeanTimeBetweenFailures(MTBF)• ShortMeanTimeToRecover(MTTR)
![Page 6: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/6.jpg)
6
HighAvailabilityComponentsMonitoringandManagement• Availabilityoftheservicesneedstobemonitored,tobeableto
takeacBonwhenthereisafailureoreventopreventthem• AfailovercanbemanualorautomaBc,butithastobemanaged
FailoverorSwitchoverSolu*on• Somemechanismtoredirecttrafficfromthefailedserveror
Datacentertoaworkingone
DataRedundancy• Forstatefulservices,weneedtomakesurethatdataismaderedundant• AvailabilitysoluBonsdonotreplacebackups
![Page 7: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/7.jpg)
• ClusteredMariaDBnodesforsynchronousreplicaBonwithopBmisBclocking
• Fastfailover• WorkswithMariaDBMaxScale
LoadBalancingandFailover
Applica*on/AppServer
7
HighAvailability
SynchronousHAReplica*onwithMariaDBEnterpriseCluster
![Page 8: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/8.jpg)
8
GaleraisnowinsideMariaDBEnterprise10.1■ TheMariaDBServerand
MariaDBGaleraServerpackageshavebeencombined
■ GalerapackagesandtheirdependenciesgetinstalledautomaBcally
■ TheGalerapartsremaindormantunBlconfigured,likeapluginorstorageengine
EnterprisesupportservicesfortheGaleraClusterfuncBonalityisincludedinourMariaDBEnterpriseClustersubscripBon.
TwonewInforma*onSchematablesforexaminingwsrepinformaBon,WSREP_MEMBERSHIPandWSREP_STATUShavebeenadded.MariaDBEnterpriseClusteraddsnode'sstatusandclustermembershiprelatedinformaBontotheINFORMATION_SCHEMA.ThisinformaBoncanbeusedbymonitoringsystemsandalsobyMariaDBMaxScale.
HighAvalability
![Page 9: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/9.jpg)
9
HighAvailability• Enhancedsemi-synchronousreplicaBondoesCOMMITinthefollowingway:
a. PreparethetransacBoninthestorageengine(s)b. WritethetransacBontothebinlog,flushthebinlogtodisk.c. WaitforatleastoneslavetoacknowledgetherecepBonofthebinlog
eventsforthetransacBon.d. CommitthetransacBontothestorageengine(s)
• Standardsemi-synchronousreplicaBonwouldcommitthetransacBonbefore
itgetstheacknowlegeofthebinlogeventfromaslave
Enhancedsemi-synchronousReplica*onwithMariaDBServer10.1
![Page 10: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/10.jpg)
MaxScalebinlogserver
10
HighAvailability§ AutomaBcdetecBonof
masterfailure§ PromoBonofaslavetoa
masterwithouttouchingotherslaves
AutomaBcFailoverwithMaxScale
Master
script 2
master_downevent
FailoverScript
4CHANGEMASTERtonew_master;
STARTSLAVE
Slaves
MaxScaleCore
3STOPSLAVE
Promoteasmaster
binlogcache
1
2
3
4
3
![Page 11: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/11.jpg)
MaxScaleCore
11
• MaxScaleConnecBonRouBngorStatementRouBng• AlwaysRoutetoavailablenodes• SimplifiesapplicaBons• ClusterconfiguraBon,NodefailurestransparenttoapplicaBons
Minimizemaintenancedown*me
/CREATETABLE/
MySQL5.1
MariaDB10
• MaxScaleQueryTranslaBonandTeefilter• ApplicaBonandDatabasecanbeupgradedasynchronously
• ValidatefuncBonalityandperformanceonnewversionbeforemovingtoproducBon
MaxScaleCore
MaxScaleCore
s/TYPE/ENGINE/
3 4
1 5
2
![Page 12: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/12.jpg)
12
ScalabilitytoSupportYourGrowingUserBase
ReadScaling
§ MariaDBMaster-SlaveReplicaBon
§ GlobaltransacBonID(GTID)uniqueacrossmulBpleindependentreplicaBonstreams
§ MulB-sourcereplicaBon
§ OpBmisBcParallelreplicaBon
§ SlaveexecuBonoftriggers
§ MaxScaleforloadbalancing
ImprovedeveloperproducBvitywithsimplifiedapplicaBons
Client Client Client
MaxScale
![Page 13: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/13.jpg)
13
ScalabilitytoSupportYourGrowingUserBase
WriteScaling§ MariaDBEnterpriseCluster
§ MulB-masterreplicaBonforwriteScalability
§ MaxScaleforloadbalancing
ImprovedeveloperproducBvitywithsimplifiedapplicaBons
Client Client Client
MaxScale
![Page 14: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/14.jpg)
ScalingBinlog
MariaDBMaxScaleBinlogServer:§ Horizontalscalingofslaveswithoutmasteroverload§ Crashsafedisasterrecovery
ScalingMul*-tenantdatabase
Slaves
BinlogCache
MasterMaxScaleMaxScale
§ MulB-tenantdatabasehosBng§ Eachtenantwithitsownschemashard§ Scalethedatabaseenvironmentasuser
baseanddatavolumegrows§ WithoutimpacBngexisBnguserbase
14
Scalability
Slaves
BinlogCache
Shard1 Shard2… Shardn
MaxScale
![Page 15: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/15.jpg)
15
To:TrytoreplicateanytransacBoninparallel,aslongasatransacBoncanberolledbackandre-tried
■ Ifnoconflicts,parallelismimproves
■ Ifconflict,enforcedcommitorderwillcauseittobedetectedasadeadlock,andthelatertransacBonwillberolledbackandretried.
From:onlyruninparalleltransacBonsthatwereknowntobeabletosafelyreplicateinparallel.
Replication Scalability Enhancement
Op*mis*cparallelreplica*on–alltransac*onswillbeconsideredtoberuninparallel,givinganotherperformanceboostinmaster-to-slavereplica*on
![Page 16: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/16.jpg)
16
Page Compression
■ Alternative to compress tables different (but similar) to the InnoDB COMPRESSED storage format
● InnoDB Compressed stores : both uncompressed and compressed pages in the buffer pool
● Page Compression, stores only uncompressed pages
InnoDB Defragmentation ■ Deleted records can create gaps on pages
■ No new SQL literals needed and changes to the server needed
Optimizer enhancements including EXPLAIN JSON and EXPLAIN ANALYZE (with FORMAT=JSON)
■ ANALYZE statement provides output that looks like EXPLAIN output, but also is includes data from the query execution
■ ANALYZE FORMAT=JSON produces detailed information about the statement execution
Performance
![Page 17: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/17.jpg)
17
MariaDBEnterpriseMariaDBEnterprise:PackagedEnterpriseRDBMS1. BasedonMariaDBopensourceproject2. MariaDBMaxScale3. MariaDBConnectors4. Robusttechnicalservicesandsupport5. ProducBvitytools6. CustomerPortal7. 24x7support8. OpBonalConsulBngandRemoteDBAservices
MariaDBEnterpriseCluster▪ Advanced,mulB-masterclustering▪ ExpertsupportfordemandingproducBonapplicaBons
![Page 18: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/18.jpg)
18
Q&A
![Page 19: MariaDB Roadshow 2016: Optimizing for Availability, Scalability, and Performance](https://reader031.vdocuments.us/reader031/viewer/2022030315/587e375c1a28ab9f5d8b4edb/html5/thumbnails/19.jpg)
19
ThankYou