What are the benefits of storing Session data in the db?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

What are the benefits of storing Session data in the db?

Post by jaoudestudios »

I need to store session data in the database for load balancers and failover servers. Are there any other benefits for doing this?

My code below for reference (any suggestions/improvements welcome)...

Code: Select all

 
<?php
/*
 * Session Control
 *
 * @desc                Control of SESSION data and stores in the database instead of in files in the temp directory (/tmp)
 * @example         new sessionCtrl; // create instance of the object at the top of every page and the object will take care of the rest
 * @version         0.0.4
 * @author          Eddie Jaoude
 */
 
class sessionCtrl
{
    /*
     * @desc        database connection
     * @access  protected
     * @var         resource
     */
    static protected $_rConnection;
    
    /*
     * @desc        default lifetime expiry - this is overwritten by php.ini file
     * @access  protected
     * @var         integer
     */
    public $lifetime = 7200;
    
    /*
     * @desc        default method - run everytime object is initiated. Setup configuration
     * @access  public
     * @param   void
     * @return  void
     */
    public function __construct()
    {
        # get lifetime
        $this->lifetime     = get_cfg_var('session.gc_maxlifetime');    
        
        # remove any session auto start
        session_write_close();
 
        # set functions for SESSIONs to use
        # modified to use object reference making it more robust
        session_set_save_handler(   array(&$this, 'start'),   
                                                            array(&$this, 'end'),
                                                            array(&$this, 'read'),    
                                                            array(&$this, 'write'),
                                                            array(&$this, 'destroy'), 
                                                            array(&$this, 'gc'));   
 
        # start the SESSION as normal
        session_start();
    }
    
    /*
     * @desc        start - run on session_start() in the __construct method. Initiates the session creation in the database
     * @access  public
     * @param   $save_path  string
     * @param   $name               string
     * @return  boolean
     */
    public function start($save_path, $name)
    {
 
        self::$_rConnection = mysql_connect('localhost','******','*******');
        $database = mysql_select_db('session', self::$_rConnection);
        return $database;
    }                                                   
    
    /*
     * @desc        end - run on session_close(). Closes the database connection
     * @access  public
     * @param   $save_path  string
     * @param   $name               string
     * @return  boolean
     */
    public function end()
    {
        $close = mysql_close(self::$_rConnection);
        return $close;
    }                                                   
    
    /*
     * @desc        read - reads the session from the database based on the unique id
     * @access  public
     * @param   $id     string
     * @return  string
     */
    public function read($id)
    {
        $q = 'SELECT * 
                    FROM session 
                    WHERE id = "'.$id.'"';
        $sql = mysql_query($q, self::$_rConnection);
        if (mysql_num_rows($sql))
        {
            $result = mysql_fetch_assoc($sql);
            return $result['data'];
        }
    }
 
    /*
     * @desc        write - writes the session to the database. If session->id does not already exist INSERT otherwise UPDATE (REPLACE)
     * @access  public
     * @param   $id     string
     * @param   $data   string
     * @return  boolean
     */
    public function write($id, $data)
    {
        $q = 'REPLACE INTO session 
                    SET 
                        id = "'.$id.'", 
                        data = "'.mysql_real_escape_string($data).'"';
        $sql = mysql_query($q, self::$_rConnection);
        return $sql;
    }
 
    /*
     * @desc        destroy - deletes the session from the database based on the session->id
     * @access  public
     * @param   $id     string
     * @return  boolean
     */
    public function destroy($id)
    {
        $q = 'DELETE 
                    FROM session 
                    WHERE id = "'.$id.'"';
        $sql = mysql_query($q, self::$_rConnection);
        return $sql;
    }
 
    /*
     * @desc        garbage collection - runs randomly, probability is in the php.ini file
     * @access  public
     * @param   $id     string
     * @return  boolean
     */
    public function gc()
    {       
        $q = 'DELETE FROM session 
                    WHERE modified < DATE_SUB(NOW(), INTERVAL '.$this->lifetime.' SECOND)';
        $sql = mysql_query($q, self::$_rConnection);
        return $sql;
    }
}
?>
 
BornForCode
Forum Contributor
Posts: 147
Joined: Mon Feb 11, 2008 1:56 am

Re: What are the benefits of storing Session data in the db?

Post by BornForCode »

There are drawn backs when you use session with db, but as you said this is the only viable solution when you have multiple web servers for an application.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: What are the benefits of storing Session data in the db?

Post by kaisellgren »

It's not the only viable solution. You could also store session data on memcached servers like Facebook does.

If the session data is stored in the file-system, you will have tough time trying to scale your system... and this not because of the file storage demands but the performance hit you will face on larger systems.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: What are the benefits of storing Session data in the db?

Post by jaoudestudios »

kaisellgren wrote:It's not the only viable solution. You could also store session data on memcached servers like Facebook does.
Could you elaborate? or provide a link to more details.
Thanks
SeaJones
Forum Commoner
Posts: 48
Joined: Tue Jun 30, 2009 5:40 pm

Re: What are the benefits of storing Session data in the db?

Post by SeaJones »

Back in days when it was acceptable to make fairly poor quality webapps and still have them to be stupidly popular, LiveJournal has a problem with it's server not coping too well, and their bods coded an app called MemCache. Memcached is the POSIX daemon that runs it.

It's an effective way of caching RAM that can be stretched across a cluster of servers, so when you have your servers set up, and you have memcache implemented, the session variables could be shared across those servers.

Memcache isn't actually autonomous, it doesn't interrupt actions of your system and then share them as such. You have to write memcache friendly apps, and they many examples on their website.

Implementation goes like this:
  • You have main site already
  • Set up memcache on new boxes
  • Alter your app to use memcache, starting with the most draining functions first.
By functions I don't mean actual php function functioname(args), I mean the operations that would form steps of a programme flow diagram.

http://www.danga.com/memcached/
http://en.wikipedia.org/wiki/Memcached
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: What are the benefits of storing Session data in the db?

Post by kaisellgren »

Memcached homepage for some basic information: http://www.danga.com/memcached/

On Linux run:

Code: Select all

apt-get install memcached
Then use session_save_path() to change the path to something like: tcp://server:port (e.g. tcp://127.0.0.1:11211 , you can also specify parameters: ?persistent=1&weight=1&timeout=1&retry_interval=10)

Use session_set_save_handler() to change the handler to: memcached

That should get you started. Memcached is used on several big websites such as Facebook, Livejournal and Youtube to distribute session data among several servers. The data is stored in the memory and offers high performance. You can also use the Memcached class http://fi.php.net/manual/en/book.memcached.php to do things manually and to build an array of different memcached server addresses to use for each session/cache/etc if you want.

Edit: I only suggest to use Memcached if you have at least 2 Memcached servers because otherwise you could just store the data on the RAM directly with APC, for instance. This should be a choice for the site owner to make and you just make sure your code supports whatever the site admin decides to do.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: What are the benefits of storing Session data in the db?

Post by jaoudestudios »

Thanks for the feedback everyone and kaisellgren thanks for the detailed description.

I will have to give it a test and see how it goes.

Unfortunately, this application that I have inherited has session data that exceeds 14MB, which I guess could be a problem?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: What are the benefits of storing Session data in the db?

Post by kaisellgren »

14 MB of session data doesn't sound like a problem... RAM is cheap nowadays, it's just one setting in the memcached servers so that they are capable of using more system RAM.
Post Reply