MongoDB and MySQL performance comparison

Overview

This repo is a part of my master's thesis, of which the main topic was comparing a performance between databases (MongoDB, MySQL) and web services (written in Node.JS 7, PHP 7).

Requirements

• Docker for building one, equivalent environment in both images. They are based on the same image: phusion/baseimage:0.9.9, which is very light.
• Python 3.6.2 for running measurements, collecting results, preparing and populating test data.
• sys, datetime, pymongo, PyMySQL libraries installed

Build images and run containers

MongoDB

1. Go to mongo directory and run

   docker build -t mongo-image .

2. Run a container with mongod by running

   docker run -p 27017:27017 --name=my-mongo mongo-image

3. Run mongo in the newly created container to make sure that everything is up and working :)

   docker ps
   docker exec -it my-mongo bash
   cd /data/db
   mongo
   

NOTICE: If you have already installed MongoDB on your local machine, make sure, that you don't have any running mongod processes: ps aux | grep mongo If so, then kill it: kill PID_ID. Remember, if you won't kill them, then all python scripts will be trying to connect with your MongoDB on your local machine instead of container's one.

MySQL

1. Go to mysql directory and run

   docker build -t mysql-image .

2. Run a container by running

   docker run -p 3306:3306 --name=my-mysql mysql-image

3. Log into mysql and set grants in the newly created container

   docker ps
   docker exec -it my-mysql bash
   mysql -u root -p
   # password: root
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.1' IDENTIFIED BY '';
   

NOTICE: I assume you have already noticed difference between MongoDB and MySQL insertions. In MongoDB, we have more information than in MySQL (like category for example), because I used the second normal form. Thus, all information about categories and tags are in other tables - we don't need them to make performance tests. But if you want to create other tables, then run simply ./createTables.py <documents_number> in mysql directory.

Test MongoDB performance

1. Install pymongo library by running

   python3 -m pip install pymongo

2. Run inserting data performance test passing the number of documents to be inserted as an argument.

   ./insertDataToMongoDB.py <documents_number>

INFO: You can change the number of inserted documents. The schema of a document is a blog post - you can also change it, but keep in mind that all Python scripts are prepared for that schema.

3. Run fetching data performance test passing the required number of documents (to connect with proper database) and an option, which represents a query. Skip, limit and uniqueNumber arguments are optional.

   Possible options (queries):

   • uniqueCategory (required option: unique number)
   • nonUniqueCategory
   • uniqueCategoryAndUniqueTag (required option: unique number)
   • nonUniqueCategoriesAndTags
   • favouritePosts
   • skip (required options: skip and limit numbers)

   Command for running tests:

   ./fetchingDataFromMongoDB.py <documents_number> <query_option> <other_arguments>

   Running tests examples:

   # Fetching data from 10-element's database and SKIP 5 elements LIMIT to 3 documents
   ./fetchingDataFromMongoDB.py 10 skip 5 3
   
   # Fetching data with nonunique category from 100-element's database
   ./fetchingDataFromMongoDB.py 100 nonUniqueCategory

Test MySQL performance

1. Install pymysql library by running

   python3 -m pip install PyMySQL

2. Run inserting data performance test passing the number of rows to be inserted as an argument.

   ./insertDataToMySQL.py <documents_number>

INFO: In insertDataToMySQL.py script we also measure duration with creating database, because as you probably now, in MongoDB there is no possibility to create database or collection without insert operation - that's why our MySQL tests (without creating databases) could be a little bit distorted.

3. Run fetching data performance test passing the required number of rows (to connect with proper database) and an option, which represents a query. Skip, limit and uniqueNumber arguments are optional.

   Possible options (queries):

   • uniqueCategory (required option: unique number)
   • nonUniqueCategory
   • uniqueCategoryAndUniqueTag (required option: unique number)
   • nonUniqueCategoriesAndTags
   • favouritePosts
   • skip (required options: skip and limit numbers) - it behaves differently from MongoDB option!

   Command for running tests:

   ./fetchingDataFromMySQL.py <rows_number> <query_option> <other_arguments>

   Running tests examples:

   # Fetching data from 10-element's database and SKIP 3 elements LIMIT to 5 rows
   ./fetchingDataFromMySQL.py 10 skip 5 3
   
   # Fetching data with nonunique category from 100-element's database
   ./fetchingDataFromMySQL.py 100 nonUniqueCategory