Rails + Mongoid slow queries in production
I am facing some performance issues with my Mongo queries, especially in production. My initial idea was that it had something to do with hosting (was on the smallest instance of Linode), but when I upgraded to the larger instance the request rate improved slightly. In comparison, a product page will take around 800ms - 1000ms to load into production (60ms in development), but after hosting migration it ranges from 300ms to 800ms in production. This leads me to think that the slowness is caused by inefficient queries along with possible n + 1 side effects. Here's a snapshot of what happens when I hit my Product API:
MOPED: 173.255.252.208:27017 QUERY database=* collection=orders selector= {"$query"=>{"user_id"=>"52cc529eec5cb38bbf000001"}, "$orderby"=>{:_id=>1}} flags=[] limit=-1 skip=0 batch_size=nil fields=nil (20.6976ms)
MOPED: 173.255.252.208:27017 QUERY database=* collection=orders selector={"$query"=>{"user_id"=>"52cc529eec5cb38bbf000001"}, "$orderby"=>{:_id=>1}} flags=[] limit=-1 skip=0 batch_size=nil fields=nil (13.5436ms)
MOPED: 173.255.252.208:27017 COMMAND database=* command={:count=>"orders", :query=>{"user_id"=>"52cc529eec5cb38bbf000001"}} (18.1813ms)
MOPED: 173.255.252.208:27017 QUERY database=* collection=orders selector={"$query"=>{"user_id"=>"52cc529eec5cb38bbf000001"}, "$orderby"=>{:_id=>1}} flags=[] limit=-1 skip=0 batch_size=nil fields=nil (15.9233ms)
MOPED: 173.255.252.208:27017 QUERY database=* collection=orders selector={"$query"=>{"user_id"=>"52cc529eec5cb38bbf000001"}, "$orderby"=>{:_id=>1}} flags=[] limit=-1 skip=0 batch_size=nil fields=nil (29.9242ms)
MOPED: 173.255.252.208:27017 QUERY database=* collection=orders selector={"$query"=>{"user_id"=>"52cc529eec5cb38bbf000001"}, "$orderby"=>{:_id=>1}} flags=[] limit=0 skip=0 batch_size=nil fields=nil (69.3288ms)
It looks like this is the main part of what's causing the slowness, however why would a query on the Orders collection be so slow? Below is the statistics () from Mongodb:
db.orders.stats()
{
"ns" : "orders",
"count" : 21535,
"size" : 15068736,
"avgObjSize" : 699.7323426979336,
"storageSize" : 23617536,
"numExtents" : 7,
"nindexes" : 3,
"lastExtentSize" : 9555968,
"paddingFactor" : 1.025000000000004,
"systemFlags" : 1,
"userFlags" : 0,
"totalIndexSize" : 5567856,
"indexSizes" : {
"_id_" : 711312,
"order_number_index" : 972944,
"_keywords_1" : 3883600
},
"ok" : 1
}
The collection itself seems to be quite small (22k records), so it gets confused as to why the queries are so slow. As you can see, I've added indexes to the collection, so I'm stuck on what I can do to improve the query speed. Help would be greatly appreciated! Thank!
source to share
Seeing that you are requesting orders from user_id, it might be a good idea to add an index to user_id. Indexing { user_id: 1 }
must be accurate.
If you put the associated code inside your application, it would be easier to determine if it is an n + 1 request. However, it looks like you are requesting a collection of orders multiple times; a more efficient query will select orders where user_id is in the user_ids array. See here for mongoid docs on active load. Basically, if you are repeating orders by a user with something like User.all.each { |u| puts u.orders }
, you can make it a lot more efficient by simply adding .includes(:orders)
before .each
.
If you're still having sluggishness issues, it might be wise to take the lines of code that query the database and run .explain
them in the console. See here for help understanding the results, or here if you're on 2.6 instead of 3.0. The most important thing to pay attention to is the number of documents checked versus the number of documents that meet the criteria in the output. For example, if you have 21535 documents and no index in user_id, you probably go through all 21535 documents every time to find the one that matches that user_id. After adding indexes .explain
will show you if you are using index in plans section.
source to share