Sample Session Providers with PostgreSQL

• Download source code - 960 KB

Introduction

In ASP.NET 2.0, Microsoft has opened up the Session State mechanism so that we can use their provider concept and create our own providers to store ASP.NET session state data.

Background

Since the days of ASP, Microsoft has provided us with a nice mechanism to store data between page calls in an object called Session. For the longest time, Microsoft has controlled how the session stores its data. In ASP.NET 1.1, they gave us three options for storing session data: In Process, State Server, and SQL Server. In ASP.NET 2.0, the same options are available, but there's also a Custom option via their provider model. Microsoft has provided us with several Session State provider examples, but I had to see how well they worked.

Issue

If we made a custom provider to store session state data, would it work well enough to use in a high performance system? It would need to manage locking issues. It would need to support a web cluster. Is there anything else it needs to support? The Microsoft documentation provides us with a decent list of issues to manage with a custom session state provider.

The Providers

A quick look around found two providers nearly ready to go: an ODBC provider in the Visual Studio 2005 documentation, and "The ASP.NET 2.0 Provider Model" paper by Microsoft. I decided to give these a try to see if they would work. Surprisingly, they almost did. Once I had these working, I wanted to take a little leap to a great Open Source and free RDBMS: PostgreSQL. Since PostgreSQL supports Stored Procedures, I made a provider that doesn't use Stored Procedures and another that does.

Observations of PostgreSQL

Having worked with Sybase, Informix, SQL Server, Oracle, and a little DB2, I wanted to see how well PostgreSQL would mature even with MySQL around. After working with it for a little bit, it has matured very well. A little while back, I could easily say that it was ahead of MySQL, and I can still say that today. It's too bad more folks don't use it. Perhaps, if they read the license to MySQL (that I believe has recently changed) and the functionalities of the two products, they might have used PostgreSQL more.

So, what makes PostgreSQL cool? For folks using Oracle, you'll find the PostgreSQL native Stored Procedure language, PL/pgSQL, easy to change to from PL/SQL. You can even write Stored Procedures in Tcl, Perl, and Python. OK, they have something for the Java folks too. Sorry, the C# piece is still in the works. It has more index types to choose from than many of the mainstream RDBMSs. It has the similar concepts of Tablespaces and Schemas. A decent security strategy for users and roles. It supports cubes, custom types, GIS, full text searching, and I'm going to stop before I spoil its thunder. Did I mention it does handle terabyte databases? Did I mention it's free?

So, what makes PostgreSQL not cool? Well, there's the documentation. Luckily, the active community offers help with all kinds of issues. You can even get weekly emails of items they are fixing. ("Email of items they are fixing". Isn't that a cool thing?) I'm sure the documentation will improve with time, the question is can it keep up with all that's offered now and in the future. The UI administration tools are improving, but need a little more work. I felt like I was back in the days of Oracle 7 with the admin tools. I couldn't find an "out of the box" debugger for the Stored Procedures. By observation, on Windows XP SP2, I noticed several processes with the same name starting when I performed load testing, so it's somewhat hard to say if the use of threads has been optimized. Lastly, the free version of the .NET Data Provider does work, but needs a little more touching up to support Microsoft's Enterprise Library, and the documentation needs some touching up too. I actually think the output stream from PostgreSQL needs some touching up to work really well with .NET, but most RDBMSs need to touch up on this. For the majority of applications, these shortcomings are not major hurdles, and you can't beat the cost.

A Few Little Additions

In the sample providers, there's sample code to delete old sessions. The question to ask is, "how do you do this?" For SQL Server, Microsoft uses an agent to call a query to clean old sessions. I thought this was nice, but I wondered about using the application end event. In a default configuration, when there's no activity for 20 minutes, ASP.NET will cycle the site.

You can add the following to the system.web section of the Web.config to change the settings for when to cycle the server:

<!-- Add to system.web to control at application level -->
<hostingEnvironment
    idleTimeout="20"
    shutdownTimeout="30"
/>

When a site shuts down nicely, the application raises its end event. To make sure all the providers here have a common method to remove old sessions, I added an interface to the providers with the method. The interface is a simple one:

public interface IThreeOaksSessionProvider
{
    void DeleteExpiredSessions();
}

Once we had a common method, it's easy to add a call to clear the expired sessions to the application end event in global.asax.cs. Here, I check the Web.config to see what the provider is to call the appropriate one.

protected void Application_End(object sender, EventArgs e)
{
    Debug.WriteLine("Application_End", "global.asax.cs");
       
    //We expect the application to know the provider they are using.
    //So don't do anything fancy to figure out which one's being used.
    Configuration cfg = 
         WebConfigurationManager.OpenWebConfiguration(
         System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
    SessionStateSection configSessionStateSection = 
       (SessionStateSection)cfg.GetSection("system.web/sessionState");
    if (configSessionStateSection != null)
    {
        IThreeOaksSessionProvider sessionProvider = null;
        switch (configSessionStateSection.CustomProvider)
        {
            case "PostgreSQLSessionProvider":
                sessionProvider = new PostgreSQLSessionStateStoreSP();
                break;
            case "OdbcSessionProvider":
                sessionProvider = new OdbcSessionStateStore();
                break;
            case "TextFileSessionStateProvider":
                sessionProvider = new TextFileSessionStateStore();
                break;
        }
        if (sessionProvider != null)
        {
            sessionProvider.DeleteExpiredSessions();
        }
    }

Since you should know the provider you are using up front, the application end procedure becomes much easier. The following shows what it would be for PostgreSQLSessionStateStoreSP:

protected void Application_End(object sender, EventArgs e)
{
    Debug.WriteLine("Application_End", "global.asax.cs");
       
    IThreeOaksSessionProvider sessionProvider = new PostgreSQLSessionStateStoreSP();
    sessionProvider.DeleteExpiredSessions();
}

For those thinking they could retrieve the Session State Provider from the Session or Request or Application, you may have a difficult time. Why should there be any session state provider around when the application ends? A session hasn't caused the application end event to fire. Using lots of Reflection, you may actually retrieve one, but it's not easy. Since in this sample we know our provider, I've kept it simple (KISS).

Observations

So which one worked best? It's somewhat of a tough call. I'm afraid I don't quite have the environment to perform the tests best. Basically, I'm using a wimpy laptop running Windows XP Professional SP2, when I really need a web cluster. I dare not run two or three virtual machines of Windows Server at the same time on my laptop.

In Process Provider:

Being an in-process, this provider should work the fastest on my wimpy laptop. For the test I set up, it ran around 340 to 360 requests per second with 5 connections. I'd hope our custom providers would match the SQL Server out of process provider that seems to run about 1/4 the requests per second as the in-process provider. See Offering a better (ODP Compliant) ASP.NET Session object for a few test results.

Text File Session Provider:

This provider originated from a Microsoft sample. It worked pretty well on my laptop. It ran between 100 to 120 requests per second most of the time with 5 concurrent sessions. The requests per second fluctuated a lot due to caching and such. In a web cluster, setting up a common area to write to may be a security issue. The set up of the hard drive may also be an issue since the OS is somewhat acting like the database. If you are going to test this one, keep an eye on the number of files made.

ODBC Session Provider:

I tried to keep this provider as close as possible to the Microsoft sample, so no Stored Procedures where used. It did not perform that well. I tried PostgreSQL as the database on Windows XP. It ran less than 20 requests per second. I tried with MS Access, and it didn't perform that well either. These results surprised me.

PostgreSQL Session Provider No Stored Procedures:

This provider ran fairly well. It ran between 100 to 150 requests per second most of the time with 5 concurrent sessions. Within a web farm, I'm sure it could handle many more requests per second. This provider is very close to the ODBC Session Provider, but uses the PostgreSQL .NET Data Provider, Npgsql.

PostgreSQL Session Provider Using Stored Procedures:

This provider ran fine. It ran between 100 to 120 requests per second most of the time with 5 concurrent sessions. Why it didn't perform better is a mystery right now. Since there's less traffic between the ASP.NET server and PostgreSQL server, it might do better than the PostgreSQL Session Provider with no Stored Procedures when ASP.NET and PostgreSQL are on different servers. I did normalize the session table into two tables, one to hold the session item and the other to hold the application name. I'd intended to make the table holding session items smaller and indexes on the table smaller and perhaps more efficient. I'm sure, in a web farm, this provider could handle many more requests per second.

Experimentation

At first, I thought Microsoft would have used a class with static methods. I also thought Microsoft would have written up how the Session State provider needs to manage thread issues. However, I didn't see this in the documentation. Using Reflection and walking through the various System classes, it appears Microsoft actually makes a new instance of the provider as needed and stores them for a period of time. My first thought was, "No way". So, I created another provider to test things out by tracing, and it definitely appears they do create a new instance of the provider as needed. ASP.NET will re-use an instantiated provider as needed.

Knowing ASP.NET re-uses Session State providers means we can look for little ways to speed things up. One way to speed things up is to limit the creation of complex objects like the Command object. For now, I've included PostgreSQLSessionSPCommon that re-uses Command objects, but not connections. Finding a way to speed up connections would really help. However, the current version of the PostgreSQL .NET data provider, Npgsql, needs a little more to make this happen well. I feel Npgsql in its next version will provide better support for connection management. We'll just have to wait and see. For the sample, depending on the load and user activity, re-using the command objects provides a nice little performance gain.

The Solution

The sample solution has two projects. The SessionProviders project contains the providers and dependencies. The TestSessionProviders project is the website I used for testing. The default page has various inputs to place into the session state. One button places the inputs into session state. Another button on the default page reads the values from session state and displays them on the screen. Another button gracefully shuts down the application on the server receiving the request. One link on the default page takes the user to a page where it reads the session state in read-only mode. The read-only page has a button to retrieve the values in session state. Another link on the default page takes the user to a page where it does not need the session state. The last link takes the user to a page where frames are used and each page needs the session state in read-write mode. The Web.config has a section for each of the providers; only one can be uncommented at a time. Since the providers use connection strings, there's a connection string section. You may need to adjust the paths to the appropriate locations for the text file provider and ODBC DSN-less connection strings. I have placed debug statements at the beginning of many methods to help observe what's happening. If you have a tool for observing code during execution, use it. At the Solution level, there's a solution folder containing the scripts to set up the PostgreSQL database for the providers here.

Parting Words

Well, here you go, a few Sesssion State providers to try out. Please take the above results with a grain of salt as there are many factors that can affect performance. Hopefully in the near future, I'll have a chance to see what's happened with Npgsql. I'm sure the next versions of PostgreSQL on Windows will produce even better results.

If you try the sample PostgreSQL providers, you will have to install PostgreSQL 8.2.3 and the PostgreSQL .NET Data Provider, Npgsql. The SQL to create the tables and Stored Procedures are a part of the .NET solution. You'll have to adjust the SQL as needed for your database.

History

None yet.