Page 1 of 1

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

Posted: Thu Jul 02, 2009 2:33 am
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;
    }
}
?>
 

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

Posted: Thu Jul 02, 2009 4:16 am
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.

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

Posted: Thu Jul 02, 2009 6:18 am
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.

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

Posted: Thu Jul 02, 2009 7:20 am
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

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

Posted: Thu Jul 02, 2009 7:38 am
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

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

Posted: Thu Jul 02, 2009 7:40 am
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.

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

Posted: Fri Jul 03, 2009 12:07 am
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?

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

Posted: Fri Jul 03, 2009 4:49 am
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.