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
Recommend
More recommend
