Performance Comparison of Redis and MongoDB paging/ranking at a large offset

In fact, the questions are not very accurate, because the database does not provide paging, ranking, and other functions. It only provides data access, and paging ranking is our database-based practical case. However, Redis and MongoDB usually have some common paging and ranking methods. This article introduces Redis and MongoD through some test data.

In fact, the questions are not very accurate, because the database does not provide paging, ranking, and other functions. It only provides data access, and paging ranking is our database-based practical case. However, Redis and MongoDB usually have some common paging and ranking methods. This article introduces Redis and MongoD through some test data.

In fact, the questions are not very accurate, because the database does not provide paging, ranking, and other functions. It only provides data access, and paging ranking is our database-based practical case. However, Redis and MongoDB usually have some common paging and ranking methods. This article introduces the performance differences between apsaradb for Redis and apsaradb for MongoDB (as well as traditional relational databases) through some test data.

Paging

First, let's create a page. The sample data in MongoDB is not as follows:

db.scores.find();{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 1, name: 'user_1'}{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 2, name: 'user_2'}{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 3, name: 'user_3'}{lid: ObjectId("4fe506dabb2bfa742d000001"), score: 4, name: 'user_4'}

The lid field is used to differentiate different latitudes and is mainly used for filtering. In the test collection, there are five different lid values, each of which corresponds to 1,200,000 pieces of data, totaling 6,000,000 pieces of data. The index is on the lid and score. (The following query can use the index)

Then we conduct the following performance tests:

collection = Mongo::Connection.new.db('test').collection('scores')Benchmark.bmbm do |x|  x.report("mongo small") do    100.times do |i|      collection.find({:lid => lids.sample}, {:fields => {:_id => false, :score => true, :user => true}}).sort({:score => -1}).limit(20).skip(i * 20).to_a    end  end  x.report("mongo medium") do    100.times do |i|      collection.find({:lid => lids.sample}, {:fields => {:_id => false, :score => true, :user => true}}).sort({:score => -1}).limit(20).skip(i * 1000).to_a    end  end  x.report("mongo large") do    100.times do |i|      collection.find({:lid => lids.sample}, {:fields => {:_id => false, :score => true, :user => true}}).sort({:score => -1}).limit(20).skip(i * 10000).to_a    end  endend

In the above three cases, the number of skips is relatively small, the size is medium, and the size is very large. Limit specifies that 20 data records are obtained in the same way. In these three cases, the test results are as follows: 0.6 seconds, 17 seconds, and 173 seconds.

We can see that for MongoDB, the size of the skip seriously affects the performance, and it is necessary to strictly avoid large skip operations.

Below we will store similar data with Redis Sorted Sets. Perform performance tests.

redis = Redis.new(:driver => :hiredis)Benchmark.bmbm do |x|  x.report("redis small") do    100.times do |i|      start = i * 20      redis.zrevrange(lids.sample, start, start + 20, :with_scores => true)    end  end  x.report("redis medium") do    100.times do |i|      start = i * 1000      redis.zrevrange(lids.sample, start, start + 20, :with_scores => true)    end  end  x.report("redis large") do    100.times do |i|      start = i * 10000      redis.zrevrange(lids.sample, start, start + 20, :with_scores => true)    end  end

Here, the skip value is the same as that in MongoDB, so what is the performance of Redis. The test results are as follows: 0.028 seconds, 0.025 seconds, and 0.028 seconds.

The data structure similar to MongoDB is stored in PostgreSQL and tested in the same way. The results are worse than those of MongoDB. The specific results are as follows:

Mongo small 0.6
Mongo medium 17
Mongo large 173
Redis small 0.028
Redis media 0.025
Redis large 0.028
Pg small 1
Pg medium 122
Pg large 650

Ranking

The ranking function is similar to the paging function. The difference is that the ranking is done by calculating the number of entries greater than a certain value.

For example:

//sqlselect count(*) from scores where lid = $1 and score > $2//mongodb.scores.find({lid: lid, score: {$gt: score}}).count()

Because ranking is similar to paging implementation, the results are actually similar. The test results are as follows:

Mongo top rank 1.155847
Mongo average 22.291007

Redis top rank 0.169442
Redis average 0.162205

Pg top rank 0.714144
Pg average 21.771570

Conclusion

As Compared above, what is the question in this article?

First, try to avoid large skip operations in MongoDB. For example, if you want to know the score of the data to be retrieved on the page, the following method may be used to obtain the data you want, rather than a large skip operation.

  db.scores.find({lid: lid, score: {$lt: last_score}}).sort({score: -1}).limit(20)

In addition, if you need to perform a large number of skip operations or a large number of count operations, you can consider using Redis Sorted Sets.

Postscript

This article has aroused discussion by some technical friends on Weibo. Here is a description of the comparison.

We know that Redis is a memory database, not MongoDB, so some friends question whether the comparison here is only a comparison between memory and disk. In fact, this argument is not without reason. The above test data is from the original author's article, and its article does not mention whether MongoDB is in the memory. According to my own experiment results, when all the data is in the memory, there is no serious performance difference as mentioned in this Article. However, as the skip increases, the operation time is still significantly longer, while the Sorted Sets of Redis are relatively stable.

We also welcome more discussions on the comparative experiment data and principle analysis. Thank you.