Re: Session Vars and Performance

Greg Donald wrote:

On Wed, 16 Feb 2005 09:36:26 -0800 (PST), Richard Lynch <ceo@l-i-e.com> wrote:

It's literally an hour's work to alter the code to use MySQL to store the sessions instead of the hard drive.

Not really, maybe 5 minutes.. here's the code:

The code misses one important thing - row locking. For concurent requests, sess_open must block until the first request does sess_close(). So you need to use InnoDB's row locking or application-level GET_LOCK() and RELEASE_LOCK().

Make the table in your database: CREATE TABLE sessions (

    id varchar(32) NOT NULL default '',
    data text NOT NULL,
    expire int(11) unsigned NOT NULL default '0',
    PRIMARY KEY (id)

); Make a sessions.php file: <?php // MySQL database connection parameters:

$dbhost     = 'localhost';
$dbuser     = 'dbuser';

$dbpasswd = 'dbpassword';

$dbname     = 'dbname';

// Sessions table name: $tb_sessions = 'sessions'; // Session lifetime in seconds: $online_expire = 900; // Use transparent sessions: ini_set( 'session.use_trans_sid', 1); // Below here should not require any changes $sdbh = ''; function sess_open( $save_path, $session_name ) {

    global $sdbh;

    if( ! $sdbh = mysql_pconnect( $dbhost, $dbuser, $dbpasswd ) )
    {

echo mysql_error();

        exit();

}

    return true;

} function sess_close() { return true; } function sess_read( $key ) {

    global $sdbh, $dbname, $tb_sessions;

    $sql = "

SELECT data FROM $tb_sessions WHERE id = '$key' AND expire > UNIX_TIMESTAMP() ";

    $query = mysql_query( $sql ) or die( mysql_error() );

    if( mysql_num_rows( $query ) )
    {

return mysql_result( $query, 0, 'data' ); } return false; } function sess_write( $key, $val ) {

    global $tb_sessions, $online_expire;

    $value = addslashes( $val );

    $sql = "

REPLACE INTO $tb_sessions (

		    id,

data, expire ) VALUES ( '$key', '$value', UNIX_TIMESTAMP() + $online_expire ) ";

    return mysql_query( $sql ) or die( mysql_error() );

} function sess_destroy( $key ) {

    global $tb_sessions;

    $sql = "

DELETE FROM $tb_sessions WHERE id = '$key' ";

    return mysql_query( $sql ) or die( mysql_error() );

} function sess_gc() {

    global $tb_sessions;

    $sql = "

DELETE FROM $tb_sessions WHERE expire < UNIX_TIMESTAMP() ";

    $query = mysql_query( $sql ) or die( mysql_error() );

    return mysql_affected_rows();

} session_set_save_handler(

    'sess_open',
    'sess_close',
    'sess_read',
    'sess_write',
    'sess_destroy',
    'sess_gc'

); session_start();

$sn     = session_name();
$sid    = session_id();

?> Then you just do include( 'sessions.php' ); http://wiki.destiney.com/DBSessions/Install