Performance test comparison between MongoDB and MySQL

Today, through an experiment, we compared the performance of MongoDB and MySQL. Note that this comparison only reflects the situation in our application scenario, and does not represent the general situation.

We want to store some historical video traffic, which needs to be stored for one year, and the daily active videos are on the order of millions, and the traffic can be represented by an integer. In our application, write performance is a priority factor, so the first step of the experiment mainly compares the write performance. Since the stored data is exclusively shared by a single application (process), sqlite also meets our application scenarios. So we also tested the write performance of sqlite.

Experimental environment:

Single server RHEL 5.5 64bit, dual-core Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
CPU, 8G memory, local disk.

Test program (python):

MySQL: MySQLdb

Sqlite: pysqlite2

MongoDB: pymongo

DB Schema:

1. MySQL (InnoDB) & Sqlite:

CREATE TABLE `inventory` (
`date` DATE NOT NULL,

`id` INT(11) NOT NULL,
`views` INT(11) NOT NULL,

PRIMARY KEY (`date`, `id`));

2. MongoDB index

inventory.create_index([(‘date’,ASCENDING), (‘id’,ASCENDING)],
unique=True, dropDups=True)

Test method:

In our application scenario, today’s traffic on any item will be randomly inserted or updated. Therefore, we test insert and update in random order by id, first insert one million pieces of data, and then update the one million pieces of data. For MongoDB, both inserts and updates use the update method:

inventory. update({‘date’: date, ‘id’: id},
{‘$inc’:{‘views’:1000}}, True)

Test results:

Among them, the results of MySQL and Sqlite are obtained under the condition that 128K pieces of data are inserted and updated in batches as a transaction. If it is changed to insert and update one by one, the insertion and update time of MySQL will increase by about 30%, while the performance of Sqlite will increase. Dropped to unacceptable, see one of my previous tests
. It seems that the performance of using MongoDB in this way is not very good, and we need to further test how to use MongoDB in our application scenarios to improve performance.