locked
Session state using MySQL RRS feed

  • Question

  • User-365409839 posted

    Our websites have reached a limit of the server they're sitting on and I want to spread them over multiple servers, but they use session state.

    I've setup MySQL as a Master-Master replication on the web nodes and pointed the session to the "localhost" mysql server. The replication works fine and in testing you can move from one server to the other without issue. However, when it comes to putting the website under load (even a couple of hundred users) the MySQL becomes a real bottleneck and can't cope even thought the resources are minimal.

    The connection string i'm using is

    <connectionStrings> 
        <remove name="LocalMySqlServer" />
        <add name="LocalMySqlServer" connectionString="database=ASPSession;server=localhost;Maximum Pool Size=100;Minimum Pool Size=10;pooling=True;Cache Server Properties=True;Connection Reset=True;User Id=ASPSession;password=ThePassword;Connection Lifetime=5" providerName="MySql.Data.MySqlClient" />
      </connectionStrings> 
      <system.web> 
         <sessionState cookieName="Session" mode="Custom" cookieless="false" timeout="20" regenerateExpiredSessionId="true" customProvider="MySqlSessionStateProvider">
          <providers>
            <add name="MySqlSessionStateProvider" type="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" applicationName="MySQL Test site" description="" connectionStringName="LocalMySqlServer" writeExceptionsToEventLog="False" autogenerateschema="True" />
          </providers>
        </sessionState>
      </system.web>


    Does anyone have any suggestions as to why this would cause such a bottleneck? Or any alternatives for distributed cache?

    Thanks

    Wednesday, February 6, 2013 1:22 AM

All replies

  • User-760709272 posted

    DB Session state was designed for SQL Server...a high performance database engine, one of the fastest and most stable on the market.  You are using it with a free download that some 16 year olds knocked up in their bedroom.

    There is a third option that might work for you which is StateServer.  This is the same concept as what you are doing, in that session is held on a remote machine, however it is held in-memory in a service rather than in a database.

    http://msdn.microsoft.com/en-us/library/ms178586(v=vs.100).aspx

    Wednesday, February 6, 2013 4:58 AM
  • User-365409839 posted

    Isn't a StateServer a single node? I'm trying to avoid a single node setup so I can grow the system moving forward.

    Wednesday, February 6, 2013 5:42 AM
  • User-760709272 posted

    What do you mean "single node"?  It's the same architecture as the database storage you are using, only rather than storing it in a database it is managed in-memory in a windows services.  You decide which machine is going to be your state server, and all sites in your farm are configured to use it to store their state.  This also means your sessions survive app recycles.

    Wednesday, February 6, 2013 5:53 AM
  • User-365409839 posted

    By "single node" I mean that there will be a single point of failure, and I am not able to add further nodes in as the site grows?

    Wednesday, February 6, 2013 9:53 AM