Sameer Parwani

Now that I have covered how to load balance multiple web servers and how to keep their content synchronized there is one more major problem to solve: sessions. You need sessions to identify a particular user from request to request (remember HTTP is stateless). Usually session data is stored on the local filesystem. However with multiple load balanced web servers, a user can be thrown from one web server to another meaning that you can not count on saving session data in the local filesystem.

Most load balancers, including nginx (through the ip_hash command), do allow you to make your sessions “sticky” which means that a particular user will be sent to the same web server for the duration of his session. This allows for you to again rely on the local filesystem to save your sessions. However, sticky sessions have a greater likelihood for uneven load distribution. Plus when a particular web server goes down, all of its user’s sessions will be lost.

It would be better if sessions could be stored in a location that all the web servers could access. If you have a SAN, that would be one option. But, what most people already have is their database. So, let’s save our sessions in MySql. The obvious downside to using your database for sessions is that the database is slower than using a local filesystem. However, for most sites (even many large ones), the performance difference will be negligible.

I’m not going to reinvent the wheel here, so I’m pasting some code I found at php.net. In a future post, I will discuss the merits of saving sessions in Memcached as well as a possible Memcached/MySQL combination.

<?php
/**
 * PHP session handling with MySQL-DB
 *
 * Created on 12.03.2008
 * @license    http://www.opensource.org/licenses/cpl.php Common Public License 1.0
 */

class Session
{
    /**
     * a database connection resource
     * @var resource
     */
    private static $_sess_db;

    /**
     * Open the session
     * @return bool
     */
    public static function open() {

        if (self::$_sess_db = mysql_connect('localhost',
                                            'root',
                                            '')) {
            return mysql_select_db('my_application', self::$_sess_db);
        }
        return false;
    }

    /**
     * Close the session
     * @return bool
     */
    public static function close() {
        return mysql_close(self::$_sess_db);
    }

    /**
     * Read the session
     * @param int session id
     * @return string string of the sessoin
     */
    public static function read($id) {
        $id = mysql_real_escape_string($id);
        $sql = sprintf("SELECT `session_data` FROM `sessions` " .
                       "WHERE `session` = '%s'", $id);
        if ($result = mysql_query($sql, self::$_sess_db)) {
            if (mysql_num_rows($result)) {
                $record = mysql_fetch_assoc($result);
                return $record['session_data'];
            }
        }
        return '';
    }

    /**
     * Write the session
     * @param int session id
     * @param string data of the session
     */
    public static function write($id, $data) {
        $sql = sprintf("REPLACE INTO `sessions` VALUES('%s', '%s', '%s')",
                       mysql_real_escape_string($id),
                       mysql_real_escape_string(time()),
                       mysql_real_escape_string($data)
                       );
        return mysql_query($sql, self::$_sess_db);
    }

    /**
     * Destroy the session
     * @param int session id
     * @return bool
     */
    public static function destroy($id) {
        $sql = sprintf("DELETE FROM `sessions` WHERE `session` = '%s'", $id);
        return mysql_query($sql, self::$_sess_db);
    }

    /**
     * Garbage Collector

     * @param int life time (sec.)
     * @return bool
     * @see session.gc_divisor      100
     * @see session.gc_maxlifetime 1440
     * @see session.gc_probability    1
     * @usage execution rate 1/100
     *        (session.gc_probability/session.gc_divisor)
     */
    public static function gc($max) {
        $sql = sprintf("DELETE FROM `sessions` WHERE `session_expires` < '%s'",
                       mysql_real_escape_string(time() - $max));
        return mysql_query($sql, self::$_sess_db);
    }
}

//ini_set('session.gc_probability', 50);
ini_set('session.save_handler', 'user');

session_set_save_handler(array('Session', 'open'),
                         array('Session', 'close'),
                         array('Session', 'read'),
                         array('Session', 'write'),
                         array('Session', 'destroy'),
                         array('Session', 'gc')
                         );

if (session_id() == "") session_start();
//session_regenerate_id(false); //also works fine
if (isset($_SESSION['counter'])) {
    $_SESSION['counter']++;
} else {
    $_SESSION['counter'] = 1;
}
echo '<br/>SessionID: '. session_id() .'<br/>Counter: '. $_SESSION['counter'];
?>

And the mysql table:

CREATE TABLE IF NOT EXISTS `sessions` (
  `session` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `session_expires` int(10) unsigned NOT NULL default '0',
  `session_data` text collate utf8_unicode_ci,
  PRIMARY KEY  (`session`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;