MongoDB and Mysql: Which one is a better fit for me? Room 204 - - PowerPoint PPT Presentation

MongoDB and Mysql: Which one is a better fit for me? Room 204 - 2:20PM-3:10PM

About us ● Adamo Tonete ○ MongoDB Support Engineer ● Agustín Gallego ○ MySQL Support Engineer

Agenda ● What are MongoDB and MySQL; ● NoSQL and Relational concepts; ● Main differences between MySQL and MongoDB; ● MongoDB and MySQL similarities; ● Query Language; ● Performance comparison; ● Security; ● Best usage cases; ● Q&A

What are MongoDB and MySQL

What is MongoDB ● Document Oriented Database ● NoSQL ● Open source ● It is currently the most common NoSQL database out there. ● High Performance Database ● Different storage engines for different use cases

What is MySQL ● Relational Database Management System ● The "M" in LAMP stack ● Second most popular RDBMS ○ According to DB-Engines' ranking ● Its architecture supports use of different storage engines ● Many different kinds of topologies used: ○ Master - Slave ○ Master - Master (active and passive) ○ Multimaster - Slave ○ Ring replication ○ Tree or pyramid ○ Multimaster Cluster (Group Replication or Galera Cluster)

NoSQL and Relational concepts

Database Concept A database is an organized collection of: ● Data ● Schemas ● Tables ● Queries ● Reports ● Views ● Other elements. Wikipedia

Relational Database Concept ● Written in the early 70s ● Records and attributes define relations ● Uses normalizations ● SQL Language ● Procedures ● Triggers ● Foreign keys ● Transactions - ACID

Non-Relational Database Concept ● Started in the 2000s; ● Non-relational concept. No tables or normalization; ● Query language is different than standard SQL; ● Made for new programming languages. ● Fast development; ● Relies on CAP theorem.

Main differences between MySQL and MongoDB

Differences between MongoDB and MySQL ● Some features we will compare: ○ Normalization ○ Transactions ○ Query language ○ Data storage and retrieval ○ Indexes differences ○ How to distribute and scale

How different is MongoDB from MySQL/RDBS ● NoSQL and SQL are not enemies ○ they are meant to complete each other 
 ● While MongoDB is a young NoSQL database, MySQL is a mature relational database. ● In some cases, using MongoDB as the main database is not the best thing to do. ● However, MongoDB can offer a very fast growing environment without too much effort.

How different is MongoDB from MySQL/RDBS ● Comparing Data distribution: 
 ○ MongoDB expects data to grow beyond machine limitations. ○ MySQL does have a few add-ons to allow data distribution among instances. ○ MySQL expects to work in a single machine (at least for writes). ○ MongoDB doesn't allow ACID transactions, but it works with the CAP theorem.

Normalization Normal forms ● MongoDB features best practices to organize your data, but there are no hard rules to do so. ● MySQL strongly suggests using 3rd normal form (3NF) to avoid data duplication.

Normalization @ each intersection is a single scalar value

Normalization { 
 "_id" : ObjectId("507f1f77bcf86cd799439011"), 
 "studentID" : 100, 
 "firstName" : "Jonathan", 
 "middleName" : "Eli", 
 "lastName" : "Tobin", 
 "classes" : [ 
 { "courseID" : "PHY101", 
 "grade" : "B", 
 "courseName" : "Physics 101", 
 "credits" : 3 }, 
 { "courseID" : "BUS101", 
 "grade" : "B+", 
 "courseName" : "Business 101", 
 "credits" : 3 } 
 ] }

ACID transactions ● What is ACID? ○ Atomicity ■ transactions should function as a single, indivisible unit of work ○ Consistency ■ the database should always move from one consistent state to the next ○ Isolation ■ the results of a transaction are (usually) invisible to other transactions until the transaction is finished ○ Durability ■ once committed, a transaction's changes are permanent

ACID transactions ● How is ACID represented in MySQL? ○ Atomicity ■ if autocommit=ON (default), every statement is committed immediately ■ if not, COMMIT or ROLLBACK should be used explicitly ○ Consistency ■ uses the doublewrite buffer and crash recovery ○ Isolation ■ various isolation levels from which to choose from: RU, RC, RR and S ○ Durability ■ there are many configuration options available for this, among which are: innodb_flush_log_at_trx_commit and sync_binlog

ACID transactions ● How is ACID represented in MongoDB? ○ Atomicity ■ single document level & no snapshotting for reads ○ Consistency ■ primary = strong ■ secondaries = your choice ○ Isolation ■ not really, but $isolated can help ○ Durability ■ configurable w:majority and/or j:true

CAP theorem ● CAP theorem was proposed by Eric Allen in 2000 ● A distributed system can't have the 3 guarantees at the same time. One must be sacrificed

CAP theorem ● Consistence A ● Availability ● Partition Tolerant Anyone will get the same response, data is consistent among instances C P

CAP theorem ● Consistence A ● Availability ● Partition Tolerant System will always respond to requests, no downtime. C P

CAP theorem ● Consistence A ● Availability ● Partition Tolerant System can handle errors (network, hardware failure) C P

CAP theorem A Relational Databases 
 Cassandra 
 MySQL 
 Riaki Postgres C P MongoDB 
 Redis

Data Storage and Data Retrieval ● MySQL has predefined table definitions ● Each column can have one (and only one) data type assigned to it ● There are some limits imposed: ○ columns: 4096 ○ row length: 64 Kb ○ these can change depending on which storage engine is used ● SQL is a declarative language ● We can tell MySQL what we want, without worrying about how it is looked for ● From the application side, there are connectors available for communicating with the server ○ https://www.mysql.com/products/connector/

Data Storage and Data Retrieval ● Unlike MySQL, MongoDB doesn't have a predefined schema but it does use declarative query language. ● Documents can have different fields with different data types, for example {x : 1, y : ['test']} and {x : 'percona', y : ISODate('2018-01-01')} are both valid MongoDB documents for the same collection.

Data Storage and Data Retrieval ● MongoDB doesn't use 3rd form normalization but MySQL does. ● All documents must contain as much information as possible. There are no joins , only linked documents. ● Max document size is 16 MB.

Comparing topologies ● Replica-sets ● Clusters and shards ● Master Slave

Scalability ● What is scalability? ○ "the ability to add capacity by adding resources" ● Scale up (a.k.a.: vertically) ○ improve hardware resources ● Scale out (a.k.a.: horizontally) ○ add more nodes

Scalability ● MongoDB: ○ uses shards to scale writes ○ uses secondaries to scale reads ● MySQL: ○ can use partitioning and sharding to scale writes (but it's not easy to implement) ○ uses slaves to scale reads

MongoDB and MySQL similarities

How similar is MongoDB to MySQL ● But these databases are not completely different ● They share: ○ Security ○ Indexing ○ Multi-user access ○ Concurrency

How similar is MongoDB to MySQL ● Database terms and concept mapping MySQL MongoDB Database Database Table Collection Row Document Column Key

How similar is MongoDB to MySQL Security: ● Granular security level ● User roles Different storage engines: ● Both mongodb and MySQL share the idea of pluggable storage engine ● MongoDB engines are: WiredTiger, MMAPv1, InMemory, RocksDB ● MySQL engines are: InnoDB, MyISAM, MyRocks, Memory, and many more

Query Language

Query Language ● We will compare mongo SQL and mysql SQL languages briefly ● and we'll see simple workflow for both: ○ create schema ○ create table ○ insert into table ○ select from table ○ update and delete ○ select with join (mysql only)

Query Language - MySQL

Query Language - MySQL

SQL Definition https://dev.mysql.com/doc/refman/5.7/en/select.html

MongoDB Query Language

"NoSQL" Query Language ● NoSQL ● CQL ● Graph ● Javascript

Security

Security ● Both databases feature user and roles as well as enhanced security such as LDAP integration, certificates, and audits ● Percona Server for MongoDB and Percona Server for MySQL do offer entreprise-grade authentication plugins for free

Security - MongoDB ● MongoDB has roles since version 2.4 ● Currently we can set collection at table level granularity ● LDAP is only available on MongoDB Enterprise but Percona server comes with this plugin free of charge. ● Audit plugin