Max Pool Size Being Encountered With Entity Framework 4.3

Question

This is going to be a long post so please hang with me.

One of the production sites that we implemented here at work a few months back I started seeing the dreaded connection leak error message in our ELMAH logs every so often.

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

This puzzled me as we are using EF and the connection handling should have been automatic.

So I started digging. The first potential culprit I considered was MiniProfiler (or I should say, our implementation of using miniprofiler). We have been using this and it has been working well, but I was worried about potential connection leaks.

    public OurContext() : base(GetProfilerConnection(), true)
    {}

    private static DbConnection GetProfilerConnection()
    {
        return new EFProfiledDbConnection(new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString),
                                          MiniProfiler.Current);
    }       

I was under the impression that the "true" parameter in the base call caused the context to take ownership of the connection so I believe that this should work fine and the connection will be disposed of.

In our Enhancement DEV branch I've removed miniprofiler as it has not been updated to support EF5 and we're going to be migrating at some point in the not too distant future, so this should be removed as a concern.

Concern two was "are we disposing our datacontexts properly"? So I downloaded Entity Framework Profiler on a trial and took our heaviest page and ran a test.

The results showed explicitly that any context we opened was closed, the concerning part to me however is the number of contexts we open. We have our DI container (Ninject 2) setup to instatiate one context per web http request, which I'd consider correct. The problem comes into pay with how we handle images in our application. This page in particular can have up to seven images in the database. Each of these images is included in the page via a MVC action. Like so:

[<img src="/Controller/GetPhotoAction/[ImageId] />]

Since the image is a separte request a separate context is being opened. So, for this particular page, we are utilizing seven different connections from the connection pool if I'm understanding this correctly. Multiply this by however many users and I see how the error message above could be true.

The reason for storing images in the database is twofold. One, our administrative application to manage the data for this app is on the US West Coast, but the servers hosting the application are on the US East Coast. There is a VPN tunnel between our network here at work on the West Coast and the servers on the East Coast. The app is load balanced as well (2 web front ends). The decision was made to store the images in the DB to avoid copying the images through a VPN tunnel and then dealing with permissions to writing the images to a file location within the web app on each server (the two locations are also completely different domains).

In the interim, while we test, we have upped the max connection pool size in our connection string and will be deploying this out to PROD early next week.

So, my questions are:

1) Have I covered my bases in leak checking here? I believe I have. Am I wrong in anything I stated above?

2) If the multiple data contexts on this page prove to be the culprit, suggestions on how to write an image to two servers across the country keeping in mind permissions will cross domains? Realistically, I'd like to do this anway, but the technical hurdles are a bit more than we are willing to do at this time right before the holidays.

3) If you think neither of the above are problems, what could I be missing? Could it really be that requests are high enough that we're encountering this error and we need to scale out? I can dig up logs to view use statistics, but it seems unlikely. This page is cached for an hour (there is a vary by param)

Answer 1

There are more approaches to find out if your web app is leaking connections. Recently I have benefit from using these:

• using SQL profiler (you can see how many connections are opened/closed)
• monitor connection pool on IIS - look here for more - http://msdn.microsoft.com/en-us/library/aa175863%28SQL.80%29.aspx
• set Max Pool Size in your connection string to max value of connections you are expecting for one request - I have tried to run my app which uses the same approach as you for storing and retrieving images with Max Pool Size=1 on the page with more than 10 images and it failed on the same exception you have. When I removed the bug with nested connections per request, it was ok even with those subrequests on images.

And to your question. You should be ok with one data context per request. When it is bound to request scope it will be disposed at the end of the request (actually it is delayed until the garbage collector collects the request object). But beware if you are not using your datacontext directly (e.g. you have some repository pattern wrapper around it) because there could be some hidden (connection leaking) bugs.