MongoDB vs PostgreSQL performance comparison. Part I: No index

Not so long ago, there was a need to independently evaluate the performance and resource consumption of the increasingly gaining popularity of noSQL MongoDB DBMS . For clarity, I decided at the same time to compare it with the performance of PostgreSQL, which is also notorious and actively used.

Habitat

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:

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

Parameters when starting mongod:

OPTIONS=" -f $CONFIGFILE"

PostgreSQL:

Version: 8.4.13

Configuration:

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

Experimental

It was decided to create and add exactly 1,000,000 records to the test collection (and for PostreSQL - the table) . The record generation algorithm was the same for MongoDB as for PostreSQL. To bring it closer to "reality", an element of chance was introduced. As a result, the test object had the following structure:

• id - a random integer in the range from 1,000 to 10,000,000
• title - a random string (cyrillic) with a length of 10 to 50 characters
• text - randomly generated text with a length of 1,000 to 2,000 characters
• floatvalue - a random fractional number in the range from 1,000 to 10,000,000

To average insertion time, a million records were divided into 4 parts of 250,000 records. At the first stage of the experiment, no indexes were used.

Measurements of insertion time gave the following results:

MongoDB

(Native JS was used for generation. Generation time is included in the total insertion time)

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

(PHP was used for generation. Generation time is not included in the total insertion time)

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

The following queries were taken for the experiment:

MongoDB

1. db.tmp.find({id:{$gt:10000}}) /* Сравнение целых чисел */
2. db.tmp.find({floatvalue: {$lt:300000}}) /* Сравнение дробных чисел */
3. db.tmp.find({title:/^А/}).explain() /* Заголовок начинается с «А» */
4. db.tmp.find({text:/хабр/}).explain() /* Поиск вхождения строки «хабр» в тексте */

PostgreSQL (all the same, but in SQL syntax)

1. select * from tmp where id>10000
2. select * from tmp where floatvalue<300000
3. select * from tmp where title like 'А%'
4. select * from tmp where text like '%хабр%'

During the experiment, for each request, not only the query execution time was measured, but also the average indicators of resource use (processor load and RAM size).

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

The result of comparing the speed of operations in the table / collection is shown in the diagram:



Here you can see that MongoDB reaches its maximum performance difference due to the insertion speed ( 4004 objects per second versus 765 for PostgreSQL). And PostgreSQL didn’t cope with the operation of selecting by id parameter , all attempts led to process terminated(increase of parametersshared_buffers etc. they didn’t lead to anything, I didn’t dig deeper, because it doesn’t matter).

Naturally, such a separation of MongoDB is also due to more active use of processor time. Average figures for all queries of the sample :

MongoDB

• 30% - 40% CPU load
• 704 - 706 MB of used RAM

While PostgreSQL

• 5% - 25% CPU load
• 550 - 1000 MB of used RAM

If you average, then there is no particular difference in the selection of RAM between these DBMSs. However, when inserting MongoDB, again, it uses it much more actively ( 1407 MB , compared to 745 MB for PostgreSQL).

But, as we remember, these are all results for queries that do not use the index. In real conditions, it is difficult to find developers who do not optimize their queries with one or another index. Therefore, the second stage of our experiment was the addition of several indexes to the MongoDB collection and the PostgreSQL table. His results were very ... mm ... unpredictable. I will definitely tell you about them in the next part: MongoDB vs PostgreSQL performance comparison. Part II: Index, as well as summarize the general results of testing with the resulting plates and graphs.