different databases for handling sessions...am I doing the right thing?

Question

I'm looking for some advice on whether or not I should use a separate database to handle my sessions.

We are writing a web app for multiple users to login and check/update their account specific information. We didn't want to use the file storage method on the webserver for storing session information, so we decided to use a database (MySQL). It's working fine, but I'm wondering about performance when this gets into production.

Currently, we have two databases (rst_sessions, and rst). The "RST" database is where all the tables are stored for the webapp...they are all MYSQL InnoDB using Referential Integrity/foreign keys to link the tables. The "RST_SESSIONS" database simply has one table and all the session information gets stored there.

Here's one of my concerns. In the PHP code if I want to run a query against "RST" then I have to select that database as such inside php ( $db->select("RST") )...when I'm done with the query I have to re-select the "RST_SESSIONS" ( $db->select("RST_SESSIONS") ) or else the session specific information doesn't get set. So, throught the webapp the code is doing a lot of selecting and reselecting of the two databases. Is this likely to cause performance issues with user base of say (10,000 - 15,000)? Would we be better off moving the RST_SESSIONS table into the RST database to avoid all the selecting?

One reason we initially set things up this way was to be able to store the sessions information on a separate database server so it didn't interfere with the operations of the webapp database.

What are some of the pro's and con's of both methods and what would you suggest we do for performance? Thanks in advance.

Answer 1

If you can live with it, just open a second connection to the database. That way you won't have to switch between databases at all. Of course, now you consume twice as many connections, and may need to bump the limit.

Answer 2

If you're worrying about performances, another alternate solution would be to not store your sessions in database, but to use something like memcached -- the PHP library to dialog with memcached already provides a handler for sessions.

A couple of advantages of using memcached :

• No hit to the disk : everything is in RAM
  • Of course, this means sessions will be lost if your server crashes ; but if a crash happens, you'll probably have other troubles than jsut losing sessions, and this is not likely to happen often
• Used in production by many websites, and works well (I'm using it for a couple of websites)
• Better scalability : if you need more RAM or more CPU-power for your memcached cluster, just add a couple of servers
• And I would add : once you've started using memcached, you can also use it as a caching mecanism ;-)

Now, to answer to your specific questions :

Instead of selecting the DB, I would use two distinct connections :

• One for the DB that's use for the application,
• And one other for the DB that's used for the sessions.

Of course, this means a bit more load on the server (it doubles the number of opened connections), but it make sure that, the day it becomes needed, you'll be able to move the "session" database to another server : you'll just have to re-configure a connection string ; and as the application already uses two separate connections, it'll still work fine.

Answer 3

Unless there's some overriding reason to put your auth information in a separate database, why not put it with the rest of your data? You may find it convenient to have everything in one place.

Notice also that you can qualify your table names in your sql queries with a schema (database) name e.g.

SELECT ACTIVE
FROM RST_SESSIONS.SESSION
WHERE SID=*whatever*

This may get you out of the need to switch dbs explicitly, if they're both on the same server.