Mongodb – Multi-tenant reduce number of MongoDB connections

You say that everything is fine, then after a couple of weeks, performance drops. (Usually, people claim that performance drops quickly, or at specific times, or at seemingly random intervals. That could mean bad I/O performance or lock storms or cpu-intensive queries running at wierd times, or a heavyweight scheduled job or lack of indexing or bad stats causing cpu-intensive queries or disk reads. Or other stuff.) Weeks is unusual.

My hypothesis is that another application on your server is leaking memory. I have seen this with virus software (every DBA's favorite server software villain) and 3rd party monitoring software. I would double check the memory usage of SQL Server, over time, and I'd grab all of the memory usage of all of the other applications on the box as well. If you have hard limits set on SQL Server's memory usage and have it set to not allow paging, it might be other apps that are getting paged out and eating up I/O capacity.

It's not hard to look for. If you are not already keeping metrics on the server, I would just start up Perfmon and have it grab a sample every 30 or 60 minutes. After a few days, you may see another applications memory usage creep upwards.

Are there error messages in the SQL Server log stating that "significant portions of sql server have been paged out"? That would also be a big clue.

Some thoughts on each of the options:

1. I would say that writing this code in any case is probably a good idea - if something does go wrong on the performance side of things you already have the code written to deal with it in some way, rather than just sending exceptions or timeout messages back to the users.
2. This sounds like pre-splitting and pre-allocation, which again, is probably a good idea to do anyway - why wait until just before use to create a database? In fact, this would mirror how mongodb does data file allocation, it always has one empty data file waiting to be filled before it is needed. The pre-allocation it does is for adding data to a DB though, it can't psychically figure out the need for a new DB, hence that initial allocation may be slowing you down. Also, make sure you are on ext4/XFS and not using an old kernel to ensure that the allocation is as quick as possible.
3. I believe that doing pre-allocation is a better option here - I have not heard of anyone using copyDatabase in this manner, so it would require a lot of testing. Pre-allocating databases in advance though is quite common.
4. This does sound a bit painful, I think there are better options described above

Overall, if you know you are going to need a new database every hour, create multiple databases in advance of their need. I know this would mean extra disk usage, but it should be quite predictable (look into the --smallfiles option if your data set is quite small).