Comparing the performance of MongoDB vs. PostgreSQL. Part I: No index

Not long ago, faced with the need to independently evaluate the performance and resource consumption increasingly gaining popularity noSQL database MongoDB. For clarity, decided to compare it with the performance of PostgreSQL, which is also notorious and widely used.

Environment

Tests were performed on a virtual server with the following characteristics:

cpu: 4 cores at 2GHz
RAM: 2GB
OS: Centos 6.4

mongoDB

Version: 2.4.3

Configuration:

the

logpath=/xxx/mongod.log
logappend=true
fork = true
dbpath=/xxx/mongo
pidfilepath = /xxx/mongod.pid

Settings when starting mongod:

the

OPTIONS=" -f $CONFIGFILE"

PostgreSQL:

Version: 8.4.13

Configuration:

the

shared_buffers = 1GB
max_prepared_transactions = 0
work_mem = 64MB
maintenance_work_mem = 512MB
effective_cache_size = 512MB

Subjects

It was decided to create and add to the test collection (a PostreSQL table) exactly 1 000 000 records. The algorithm of generation of the records was the same for MongoDB that PostreSQL. For more closer to "reality" was introduced the element of chance. In the end, the test object had the following structure:

the

id — a random integer in the range from 1 000 to 10 000 000

title is a random string (Cyrillic) in length from 10 to 50 characters

text is a randomly generated text of length 1 000 to 2 000 characters

floatvalue is a random fractional number in the range from 1 000 to 10 000 000

For averaging time of inserting one million records was broken into 4 pieces of 250 000 records. In the first stage of the experiment no indices were not used.

Time measurements on the box gave the following results:

MongoDB

(To generate used native JS. Generation time is included in the total time of insertion)

the

Insert 250 000 records complete! Total time: 62.788 sec
Insert 250 000 records complete! Total time: 62.481 sec
Insert 250 000 records complete! Total time: 62.916 sec
Insert 250 000 records complete! Total time: 61.565 sec

Average time: 62.4375 sec

PostgreSQL

(To generate the used PHP. The generation time is not included a total insertion time)

the

psql -d prefTest -f 250k.p1.sql (Total time: 326.377 sec)
psql -d prefTest -f 250k.p2.sql (Total time: 326.646 sec)
psql -d prefTest -f 250k.p3.sql (Total time: 327.726 sec)
psql -d prefTest -f 250k.p4.sql (Total time: 327.039 sec)

Average time: 326.947 sec

Experiment I: No index

For the experiment was taken the following queries:

MongoDB

the
1. db.tmp.find({id:{$gt:10000}}) /* Compare integers */
the
2. db.tmp.find({floatvalue: {$lt:300000}}) /* Comparing fractional numbers */
the
3. db.tmp.find({title:/^A/}).explain() /* Title starts with "A" */
the
4. db.tmp.find({text:/Habr/}).explain() /* Search for occurrences of a string "Habr" in the text */

PostgreSQL (all the same thing, but in SQL syntax)

the
1. select * from tmp where id>10000
the
2. select * from tmp where floatvalue<300000
the
3. select * from tmp where title like 'A%'
the
4. select * from tmp where text like '%Habr%'

In the course of the experiment for each query was measured not only query execution time but the average resource usage (CPU load and RAM).

For a more visual comparison of performance was introduced attribute speed (the number of processed objects/records per second).

The result of the comparison of the speed of operations in a table/collection is given in the chart:



Here you can see that the maximum difference in the performance of MongoDB reaches the speed of the insertion (4004 of the object in the second against 765 in PostgreSQL). As with the fetch operation on the parameter id PostgreSQL in General has failed, all attempts led to process is terminated (increased shared_buffers, etc. to anything did not lead, digging deeper I did not, because they do not matter).

Of course, this gap is due to MongoDB and the increased use of CPU time. Average figures for all sample requests:

the

30% — 40% CPU load

704 — 706 MB used RAM

While PostgreSQL
the

5% — 25% CPU load

550 — 1000 MB used RAM

If on average, the RAM usage between these DBMSs, there is little difference in the sample. However, when you insert MongoDB, again, uses much more active (1407 MB against 745 MB PostgreSQL).

But, as we remember, that all the results for queries not using index. In real conditions it is difficult to find developers who don't optimize their queries by various indices. Therefore, the second stage of our experiment was the addition of several indexes in a collection of MongoDB and PostgreSQL table. The results were very... uh... unpredictable. About them I will discuss in the next section: performance Comparison of MongoDB vs PostgreSQL. Part II: Index, and sum up the overall results of the test result tables and graphs.

Article based on information from habrahabr.ru