MySQL vs MongoDB Choosing right Technology for your application

1 . MySQL vs MongoDB Choosing right technology for your application Peter Zaitsev CEO, Percona Percona Technical Webinars August 23, 2018 1 © 2018 Percona 

2 .MySQL vs MongoDB VS 2 © 2018 Percona 

3 .Bigger Question What Open Source Database Technologies I should be using for My Application ? 3 © 2018 Percona 

4 .Even Bigger Question What Database Technologies I should be using for My Application 4 © 2018 Percona 

5 .Open Source Increasing number of Open Source First Companies Only use Proprietary Software when there are no good Open Source Choices Cost, Reduced Vendor Lock, Choice Talent access – developers prefer Open Source 5 © 2018 Percona 

6 .Modern = Open Source 6 © 2018 Percona 

7 .Why MySQL and MongoDB ? Most Popular OpenSource SQL and NoSQL Engines 7 © 2018 Percona 

8 .Why MySQL and MongoDB ? Two of Technologies Percona Provides Solutions For 8 © 2018 Percona 

9 .It is not One OR another choice really Many Complex Applications use both MySQL and MongoDB Polyglot Persistence Micro-services with independent persistence choices Cloud, DBaaS allows to simplify DB roll out 9 © 2018 Percona 

10 .Full Disclosure I know MySQL Much better than MongoDB… which will impact my bias 10 © 2018 Percona 

11 .MySQL Relational Database First and Foremost Full SQL Support, Transactions, ACID Designed for a Single Server first Scale-Out as Afterthought 11 © 2018 Percona 

12 .MongoDB Designed for “Web Scale” Scalability, Cloud, Multiple Machines Replication and Sharding part of initial design Only features which can scale 12 © 2018 Percona 

13 .Q1: What do you know and love? Both MySQL and MongoDB are very capable. Your experience and preference matter 13 © 2018 Percona 

14 .Q2: Which data model fits better ? Relational Document Based •MySQL Obvious •MongoDB Obvious Choice choice •MySQL has Document Store starting 5.7 14 © 2018 Percona 

15 .Q3: How Data is Used Data belongs to single Data shared by multiple application applications • JSON model more • Relational structure expressive for application easier to share data structures • Can be more flexible in • Schema designed for how data is accessed specific access paths 15 © 2018 Percona 

16 .Q4: Transactions Both Database Engines Support Transactions Now! Starting MongoDB 4.0, Cross Shard Transactions not Supported Look into Isolation Mode and Locking for Differences 16 © 2018 Percona 

17 .Q5: JOINs Advanced JOINs and other Mainly simple lookups with SQL features filters/sorting • MySQL much more • MongoDB and MySQL both powerful do these very well • $lookup and $graphLookup features in MongoDB aggregation framework 17 © 2018 Percona 

18 .Q6: Scale Single Server is Good Enough Need Massive Scale out • MySQL works great • Automated shading in • Well optimized for Many MongoDB is much better cores; large memory; fast • Replication in MongoDB is storage easier to use • Solutions like Vitess try to make it less painful for MySQL 18 © 2018 Percona 

19 .Q7: Large Scale Aggregation MongoDB MySQL • has built in aggregation • Executes every query framework for parallel single threaded processing • MariaDB ColumnStore • BI Connector and ToroDB (InfiniDB reborn) for SQL access • ClickHouse • Replicate to Hadoop • Replicate to Hadoop 19 © 2018 Percona 

20 .Q8: Access Language SQL CRUD •Structured •Create •Query •Read •Language •Update •Delete 20 © 2018 Percona 

21 .Not just Database but… Language Support MySQL MongoDB •Drivers are very thing •Native look and feel of layer to run SQL Programming Queries Language •Many independent ORM Frameworks 21 © 2018 Percona 

22 .Learning from each other MySQL MongoDB •Doc Store in MySQL •MongoDB BI 5.7 Connector (Enterprise) •JSON to Relational and to run SQL Back in MySQL 8 •MySQL Wire Protocol 22 © 2018 Percona 

23 .MySQL and MongoDB compared Courtesy of Alexander Rubin 23 © 2018 Percona 

24 . Replication? MySQL MongoDB Auto failover 24 © 2018 Percona 

25 .New MySQL Replication 25 © 2018 Percona 

26 . Sharding? MySQL MongoDB: out of the box sharding ● Custom solutions ● ProxySQL ● Vitess 26 © 2018 Percona 

27 . From to MySQL MongoDB mysql> select * from zips limit 1\G MongoDB shell version: 3.0.8 *************************** 1. row connecting to: zips *************************** > db.zips.find().limit(1).pretty() country_code: US { postal_code: 34050 place_name: FPO "_id" : "01001", admin_name1: "city" : "AGAWAM", admin_code1: AA "loc" : [ admin_name2: Erie -72.622739, admin_code2: 029 42.070206 admin_name3: ], admin_code3: "pop" : 15338, latitude: 41.03750000 "state" : "MA" longitude: -111.67890000 accuracy: } 1 row in set (0.00 sec) 27 © 2018 Percona 

28 . Where is my SQL? SQL to MongoDB Mapping Chart https://docs.mongodb.org/manual/reference/sql-comparison/ MySQL MongoDB CREATE TABLE users ( db.users.insert( { id MEDIUMINT NOT NULL user_id: "abc123", AUTO_INCREMENT, age: 55, user_id Varchar(30), status: "A" age Number, }) status char(1), PRIMARY KEY (id) (no schema) ) 28 © 2018 Percona 

29 . Where is my SQL? SQL to MongoDB Mapping Chart https://docs.mongodb.org/manual/reference/sql-comparison/ MySQL MongoDB SELECT * db.users.find( FROM users { status: "A", WHERE status = "A" age: 50 } AND age = 50 ) 29 © 2018 Percona