mysql vs mongodb - percona · my mysql vs mongodb most popular open source relational and non...
TRANSCRIPT
©2017Percona1
PeterZaitsev
MySQLvsMongoDBChoosingrighttechnologyforyourapplication
CEO,PerconaSCALE15x3March2017,Pasadena,CA
©2017Percona5
OpenSource
IncreasingnumberofOpenSourceFirstCompanies
OnlyuseProprietarySoftwarewhentherearenootherchoices
Cost,ReducedVendorLockInarethemainfactors
©2017Percona9
ArchitectureExamples
MainOperationStore+AuxiliaryServices
MicroServiceswiththeirownstores
DataBus/DataFlow
©2017Percona10
ForExample
MySQLasmaindatastore
UseMemcacheorRedisforcaching
ElasticSearchforSearch
KafkatotransferdatatoElasticSearch,Hadoop
©2017Percona12
ManyNoSQLDataModels
KeyValue(Memcache)
WideColumn(Cassandra)
Document(MongoDB)
Graph(Neo4J)
©2017Percona14
MyMySQLvsMongoDB
MostPopularOpenSourceRelationalandNonRelationalstorage
BothTechnologiesstartedwithFocusonExceptionalEaseofUse
Theseare2TechnologieswefocusatPercona
©2017Percona15
PerconaSoftwareforMySQLandMongoDB
MySQL
• PerconaServerforMySQL• PerconaXtraDBCluster• PeronaXtrabackup• PerconaToolkit• PerconaMonitoringandManagement
• TokuDBStorageEngine
MongoDB
• PerconaServerforMySQL•MongoRocksDBStorageEngine(RocksDBBased)
• PerconaMemoryEngineforMongoDB
• PerconaToolkit• PerconaMonitoringandManagement
©2017Percona17
NoSQLonMySQL
StartingMySQL5.7MySQLSupportsNoSQLInterface
MongoDBLikeCRUDaccess
WithTransactionSupport
WithoutGoodShardingSolution
Notyetcommonlyadopted
©2017Percona18
TopThingstoConsider
TeamPreferencesandExperience
DevelopmentProcessandApplicationLifecycle
DataModel TransactionsandConsistency
Performance Scalability Administration Legal
©2017Percona19
TeamPreferencesandExperience
AbsolutelyKeyQuestion!
UnlovedTechnology– RecipeforDisaster
Problemsmayhavemorethanonesolution
ConsiderOriginalDevelopmentandFullLifecycle
©2017Percona20
TeamExperienceMySQLvsMongoDB
MySQL
• VeryMatureTechnology• SQLStandard• Easiertomovetootherrelationaldatabases
• Transactions• AlotofChoices
MongoDB
•ModernandDynamic• DonotneedtolearnnastySQL• DynamicSchema• ComplexQueriesarerecipeforcomplexproblems
• EasierScalability• NativeIntegrationJavascript,JSON
©2017Percona21
DevelopmentProcessandLifeCycle
FasterDevelopmentpaceormorecontrol?
DataAlwaysHaveSchema
IsDataBelongtoApplicationorusedbyMultipleApplications
ApplicationLifeTime
ApplicationActivedevelopmenttime
©2017Percona22
DevelopmentinMySQLandMongoDB
MySQL
• RelationalDatabaserequiresmoreplanningandcontrol
• Easytousedatafrommanyapplications
• Manyapplications15+Years• CanuseSQLandNoSQLInterface
MongoDB
• DevelopmentPace• NoNeedtoSynchronizeSchemabetweenApplicationandDatabase
• ClearPathtoScalability
©2017Percona24
DataModelinMySQLvsMongoDB
MySQL
• RelationalDataModel• Easytoreflectrelationshipsbetweenobjects
• Changedatainoneplace• QueryResult- Table
MongoDB
• DocumentOrientedDataModel• Documentscanembedotherdocuments
• EasytorepresentJSONdatacommoninWebApplications
• ComplexJOINscanoftenbeavoided• QueryResult– ListofDocuments(differentstructure)
©2017Percona25
ModelDesignExample
PeopleandtheirPassports
Peoplemayhavemorethanonpassport
NotEveryoneHasaPassport
©2017Percona27
EasyToAnswerVariousQuestions• NumberofPeople
• SELECTcount(*)FROMpeople• ValidityDateofPersonspassport
• SELECTvalid_until frompassportsps joinpeoplepl ONps.people_id =pl.idWHEREname='Stephane‘
• WhoDoesnothaveAPassport?• SELECTnameFROMpeoplepl LEFTJOINpassportsps ONps.people_id =
pl.idWHEREps.idISNULL
©2017Percona29
MongoDBandMySQLTerms
MySQL
• Database/Schema• Table• Row• Column• PrimaryKey• Join
MongoDB
• Database• Collection• Document• Field• PrimaryKey• EmbeddedDocumentorLinking
©2017Percona38
TransactionsinMySQLvsMongoDB
MySQL
•ACIDTransactionsofarbitrarysize
MongoDB
•ACIDonthedocumentlevel
©2017Percona39
MongoDBReadsareDirtyReads
ReadingaLargeResultwillReturnDocumentsfromdifferentpointintime
SameDocumentCan(rarely)beReturnedMorethanOnce
©2017Percona40
Performance
VeryHardtoCompareDirectly
DependsalotontheApplicationArchitecture
ManyBenchmarkscomparedifferentDurabilityorSettings
MongoDBScalesBettersosinglenodeperformancelessofafocus
©2017Percona42
Scalablilty
•Scalabilitywithinsinglesystem•Scalabilityformultiplesystems•Scalingreads,writes,datasize
WhatdoIneedtodotoscalemyApplicationfrom100to100.000.000
users?
©2017Percona43
ScalabilityMySQLandMongoDB
MySQL
• Scaleswellwithsinglesystemsize
• CanbescaledtoFacebookScale• Easytoscaleformediumsizeapplications
• ScalingReadswithReplication• ShardingisPainful
MongoDB
• Scalabilitywasfocuseduponfromtheearlyversions
• BuiltinSharding• ShardingforScalability,ReplicationisforHA
©2017Percona44
DatabaseOperationsWhatDevelopersdonotlikethinkingabout
Automation
Backups
VersionUpdates
Monitoring
DisasterRecovery
PerformanceManagement
©2017Percona45
OperationsMySQLandMongoDB
MySQL
• Flexibility• Manywaystosolveproblems• ManyOpenSourceToolsavailable
• OutoftheboxManualFailoverinReplication
MongoDB
• Simplicity• VirtuallyZeroAdministration• FewerOpenSourceOperationTools
• StrongConnectiontoMongoDBOpsManager
©2017Percona47
SolvedMongoDBProblems• PoorconcurrentwriteperformancewithMMAPv1
• FixedbyWiredTiger andMongoRocks• PoordiskspaceusagewithMMAPv1
• FixedbyWiredTiger andMongoRocks• Nowaytovalidatedocuments
• DocumentValidationsinceMongoDB3.2• NoJOIN
• LimitedsupportinaggregationframeworkstartingMongoDB3.2
©2017Percona48
TypicalMySQLExample
• FullFeatureTransactionsareimportant• RelationalModelisagoodfit• Datasizeistypicallynotrequiringsharding• LongTermApplicationdevelopment• Samedatausedbymanyapplicationsdirectly
OnlineEcommerce
©2017Percona49
TypicalMongoDBExample
• Extremescalingmightbeneededifgamebecomespopular
• Dataisusedbyoneapplication• Dataiscomplicatedanddoesnotfitrelationalmodelwell
• Documentlevelconsistencyisoftenenough• Lowpaceofdevelopmentafter“launch”
OnlineGame
Backend
©2017Percona52
PerconaServerforMongoDB3.4
100%FreeandOpenSource
100%CompatiblewithMongoDB3.4
AdditionalMemoryEngineandMongoRocks
PluggableAuthenticationas
inMongoDBEnterprise
AuditingasinMongoDBEnterprise
AdvancedQueryPerformanceMonitoring
HotBackupsforWiredTiger
©2017Percona53
PerconaMonitoringandManagement
• Comprehensive Database Focused Monitoring
• 100% Open Source Roll-your-own solution
• Easy to install and use
• Supports MySQL and MongoDB
• Version 1.1 focuses on Trending and Query Analyses
• Management Features to Come
©2017Percona65
AboutPerconaSolutionsforyoursuccesswithMySQLandMongoDB
Support,ManagedServices,Software
OurSoftwareis100%OpenSource
SupportBroadEcosystem– MySQL,MariaDB,AmazonRDS
InBusinessformorethan10years
Morethan3000customers,includingtopInternetcompaniesandenterprises