PaaS Database Benefits #1 :  someone else is watching

I have my MongoDB hosted in Azure with MongoLab.  Every week they send me an email that gives me a list of the top 3 queries ordered by total server time.  It gives, me a view of what is really taking the most time,  either because it’s slow and operated every so often or its run all the time.  Both of these scenarios are classic high value for optimization places.  Relevant XKCD

So this is a snippet of what the email looks like :

rips.ListItems

query

{“PolicyId”: “<val>”, “d”: “<val>”}

total time:
28441 ms
count:
55
average time:
517 ms
recommended index

{“PolicyId”: 1, “d”: 1}

 

collection

rips.Reminders

query

{“Completed”: “<val>”, “ClientId”: “<val>”}

total time:
5736 ms
count:
26
average time:
220 ms
recommended index

{“ClientId”: 1, “Completed”: 1}

 

This is where MongoLab really shines,  they then take a look at the execution details,  and they tell me what index (if any) would make the query faster.  So simply I can take the index and execute it against the server,  and have a much more performant system, with minimal effort.

If my database changes or my usage changes then new items will show up the next week and I will fix those. Easy, no need to worry about premature optimizations, worst case scenario is one week of lower than optimal performance, which for most any system is okay.  If I told your boss that a feature would be shipped hours earlier, and it would be improved if necessary in a week.  What percentage of bosses would take that?   I’m thinking the answer is (100-PP)%. Where PP is the percentage of bosses that have pointy hair.

 

Pointy Haired Boss

There is one aspect about this I especially like.  They let you know what the problem is, but don’t auto fix it.  This allows the decision to fix to be in the hands of those responsible.  If you don’t want to suffer the penalty of the index,  you are welcome to not utilize it.  I think for things like this the power of informing without commanding is the best of both worlds.

I wish the sql azure people did this,  the world would be a better place if they could do this.  I am not engaging in hyperbole,  think of how much better it would be if everyone had their databases better tuned, especially those who don’t have a dedicated DBA.