Mongodb + Python - Very Slow Simple Query

I have an open source energy monitor (http://openenergymonitor.org) which logs the power usage of my house every five seconds, so I thought this would be a perfect application to p

Solution 1:

Are you sure that your index is created? could you provide the output of getIndexes() of your collection

eg: db.my_collection.getIndexes()

and the explanation of your query

db.my_collection.find({created_at_year:2014,created_at_month:1,created_at_day:28}).explain()

PS: of course I must agree with @Aesthete about the fact that you store much more than you need to...

29/1/2014 update

Perfect! As you see you have four different indexes when you can create ONE compound index which will include all of them.

defining

db.my_collection.ensureIndex({created_at_year: 1, created_at_month: 1, created_at_day: 1, created_at_hour: 1 })

will provide you a more precise index that will enable you to query for:

• year
• year and month
• year and month and day
• year and month and day and hour

This will make your queries (with the four keys) much faster, because all your criteria will be met in the index data!

please note that that the order of keys in ensureIndex() is crucial, that order actually defines the above mentioned list of queries!

Also note that if all you need is these 4 fields, than if you specify a correct projection eg: db.my_collection.find({created_at_year: 2014, created_at_month: 1, created_at_day: 28}, { created_at_year: 1, created_at_month: 1, created_at_day: 1 })

then only the index will be used, which is the maximum performance!

Solution 2:

probably something to do with you saving the date 5 times save it once (ie keep created_at), then if you want the month, day etc in your view, just convert the created_at value to just display the month, day etc

Solution 3:

I wonder if the indexes don't fit in your raspberry pi's memory. Since MongoDB can only use one index per query, and it seems to use only the created_by_day query, you could try dropping the indexes and replacing them with an index on the created_at timestamp. Then you could reduce the size of your documents by getting rid of the created_at_* fields.

You can easily extract the day, month, year etc from an ISO date in a map reduce function, or with the aggregation framework date operators.

The query for today then becomes something like this:

db.reading.find({'created_at':{'$gte':ISODate("2014-01-29"), '$lt':ISODate("2014-01-30")}})

I think it's interesting that you chose a database advertised as suitable for BIG data to run on your embedded device. I'm curious how it will work out. I have a similar gadget, and used BerkeleyDB for storing the readings. Don't forget that MongoDB on a 32 bit OS has a maximum size of 2GB for the entire database.