MySQL vs MongoDB when to use which Technology

1 . MySQL vs MongoDB When to Use Which Technology Peter Zaitsev CEO Percona University, Ghent June 22nd, 2017 1 © 2017 Percona 

2 .In This Presentation Very brief discussion on merits of MySQL and MongoDB 2 © 2017 Percona 

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

4 .Why MySQL and MongoDB ? Two Technologies Percona Provides Solutions For 4 © 2017 Percona 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

19 . 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 19 /etc/mongod.conf © 2017 Percona 

20 . 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 20 | zips | © 2017 Percona +----------------+ 

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

22 . Where is my Processlist? mysql> show processlist\G > db.currentOp() *************************** 1. row { "inprog" : [ *************************** { Id: 137259 "desc" : "conn28", User: root "threadId" : "0x19b85260", "connectionId" : 28, Host: localhost "opid" : 27394208, db: geonames "active" : true, Command: Query "secs_running" : 3, "microsecs_running" : Time: 0 NumberLong(3210539), State: init "op" : "query", Info: show processlist "ns" : "osm.points3", "query" : { Rows_sent: 0 "name" : "Durham" Rows_examined: 0 }, 1 row in set (0.00 sec) "planSummary" : "COLLSCAN", "client" : "127.0.0.1:58835", "numYields" : 24905, "locks" : { "Global" : "r", "Database" : "r", "Collection" : "r" }, 22 © 2017 Percona "waitingForLock" : false, ... 

23 . Where are my Grants? mysql> grant all on *.* to > use products user@localhost identified by ‘pass’; db.createUser( { user: "accountUser", pwd: "password", roles: [ "readWrite", "dbAdmin" ] } ) 23 © 2017 Percona 

24 . Where is my Index? MySQL MongoDB mysql> show keys from zips\G > db.zips.getIndexes() *************************** 1. row [ *************************** Table: zips { Non_unique: 0 "v" : 1, Key_name: PRIMARY "key" : { Seq_in_index: 1 "_id" : 1 Column_name: id Collation: A }, Cardinality: 0 "name" : "_id_", Sub_part: NULL "ns" : "zips.zips" Packed: NULL } Null: Index_type: BTREE ] Comment: Index_comment: *************************** 2. row *************************** Table: zips Non_unique: 1 24 Key_name: postal_code © 2017 Percona Seq_in_index: 1 

25 . Where is my add index? mysql> alter table zips add key > db.zips.createIndex({ state : 1 } ) { (postal_code); "createdCollectionAutomatically" : Query OK, 0 rows affected (0.10 false, "numIndexesBefore" : 1, sec) "numIndexesAfter" : 2, Records: 0 Duplicates: 0 "ok" : 1 } Warnings: 0 // Index can be sorted: > db.zips.createIndex({ state : -1 } ) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } 25 © 2017 Percona 

26 . Where is my Slow Query Log? MySQL MongoDB mysql> set global long_query_time = 0.1; db.setProfilingLevel(level, slowms) Query OK, 0 rows affected (0.02 sec) Level: 0 for no profiling, 1 for only slow operations, or 2 for all mysql> set global slow_query_log = 1; operations. Query OK, 0 rows affected (0.02 sec) Slowms = long_query_time but in milliseconds > db.setProfilingLevel(2, 100); mysql> show global variables like { "was" : 0, "slowms" : 100, "ok" : 1 } 'slow_query_log_file'; +---------------------+------------------------------+ > db.system.profile.find( { millis : { $gt : 100 } } | Variable_name | Value | ).pretty() +---------------------+------------------------------+ { | slow_query_log_file | /var/lib/mysql/thor-slow.log | "op" : "query", +---------------------+------------------------------+ 1 row in set (0.00 sec) "ns" : "zips.zips", "query" : { "city" : "DURHAM" }, "ntoreturn" : 0, .. 26 © 2017 Percona 

27 . From to Export from MySQL 5.7: mysql> SELECT JSON_OBJECT('name', replace(name, '"', ''), 'other_tags', replace(other_tags, '"', ''), 'geometry', st_asgeojson(shape)) as j FROM `points` INTO OUTFILE '/var/lib/mysql-files/points.json'; Query OK, 13660667 rows affected (4 min 1.35 sec) 27 © 2017 Percona 

28 . From to Load to MongoDB (parallel): mongoimport --db osm --collection points -j 24 --file /var/lib/mysql- files/points.json 2016-04-11T22:38:10.029+0000 connected to: localhost 2016-04-11T22:38:13.026+0000 [........................] osm.points 31.8 MB/2.2 GB (1.4%) 2016-04-11T22:38:16.026+0000 [........................] osm.points 31.8 MB/2.2 GB (1.4%) 2016-04-11T22:38:19.026+0000 [........................] osm.points 31.8 MB/2.2 GB (1.4%) … 2016-04-11T23:12:13.447+0000 [########################] osm.points 2.2 GB/2.2 GB (100.0%) 2016-04-11T23:12:15.614+0000 imported 13660667 documents 28 © 2017 Percona 

29 .Thinking about using MongoDB ? Consider trying out Percona Server for MongoDB 29 © 2017 Percona