MySQL vs MongoDB

1 . MySQL vs MongoDB Choosing right technology for your application Peter Zaitsev CEO, Percona All Things Open, Raleigh,NC October 23rd, 2017 1 © 2017 Percona 

2 .MySQL vs MongoDB VS 2 © 2017 Percona 

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

4 .Even Bigger Question What Database Technologies I should be using for My Application 4 © 2017 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 © 2017 Percona 

6 .Modern = Open Source 6 © 2017 Percona 

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

8 .Why MySQL and MongoDB ? Two Technologies Percona Provides Solutions For 8 © 2017 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 © 2017 Percona 

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

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

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

13 .Q1: What do you know and love? Both MySQL and MongoDB are very capable. Your experience and preference matter 13 © 2017 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 © 2017 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 © 2017 Percona 

16 .Q4: Transactions Do not need Need full Transactions Transactions • MySQL can be better • MongoDB can be great choice choice • One of the main • Can do Atomic benefits of MySQL Document Updates Document Store 16 © 2017 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 © 2017 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 © 2017 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 © 2017 Percona 

20 .Q8: Access Language SQL CRUD •Structured •Create •Query •Read •Language •Update •Delete 20 © 2017 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 © 2017 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 © 2017 Percona 

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

24 . 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) 24 © 2017 Percona 

25 . 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) ) 25 © 2017 Percona 

26 . 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 ) 26 © 2017 Percona 

27 . Where is my /etc/my.cnf? MySQL MongoDB /etc/my.cnf /etc/mongod.conf # Where and how to store data. storage: dbPath: /datawt journal: enabled: true engine: wiredTiger ... /usr/bin/mongod -f 27 /etc/mongod.conf © 2017 Percona 

28 . Where are my databases/tables? MySQL MongoDB Databases Databases mysql> show databases; > show dbs; +--------------------+ admin 0.000GB | Database | local 0.000GB +--------------------+ osm 13.528GB | information_schema | test 0.000GB ... zips 0.002GB mysql> use zips > use zips Database changed switched to db zips Tables Collections mysql> show tables; > show collections +----------------+ zips | Tables_in_zips | > show tables // same +----------------+ zips | zips | 28 © 2017 Percona +----------------+ 

29 . Where is my InnoDB? MySQL MongoDB MyISAM MMAPv1 memory mapped stored engine, InnoDB WiredTiger transactional, with compression, btree TokuDB Percona Memory Engine MyRocks (RocksDB)* MongoRocks (RocksDB) 29 © 2017 Percona