We are thinking about moving the logs from MySQL to MongoDB. In the community they say that for such tasks Mongo is a good fit - fast recording speed and fast request processing.

We decided to check the speed of writing and sampling prior to switching to MongoDB compared to MySQL. It is important for us that the logs can be filtered by 4-6 parameters

Both databases are on the same machine.

MySQL schema:

  • id int
  • response ENUM
  • type_id int (fk)
  • status_id int (fk)

Example string: | 1 | CATCH | 3 | 4 |

MongoDB schema:

  • _id ObjectId
  • response STRING
  • data STRING
  • type STRING
  • status STRING

Document example: | blabla | CATCH | VISIT | PROCESSING |

Each database was added 4 million records. The writing process in MongoDB was about 2 times faster.

In each database, single indices were added for the listed key fields.

The result of testing queries is as follows (LIMIT 10000):

  • By type (s) MongoDB: 0.11 MySQL 0.32
  • By type + status (sec) MongoDB: 5.5 MySQL 0.40
  • By type + status (sec) MongoDB: 4.7 MySQL 0.5

The query cache in MySQL has been turned off. Question: Why is the sample in MySQL faster than in MongoDB, although the community says the opposite?

  • one
    and there is. writing to mongo is faster, reading is at the same level or slower. you can see the article on Habré - habr.com/post/87620 - Novitskiy Denis
  • @NovitskiyDenis thanks for the link. I was embarrassed that from the 2 sampling conditions, the Mongo straight works much slower - the difference is 10 times ... - RostD
  • probably because multiple indices were not created, but only single ones - Daniel Khoroshko
  • Typical database use in 80% of applications is reading. you just have to remember it. this is what the community says, which I won’t say, secrets ...))) - Sergey V.

0