Jump to content

Session handler DB issue


Frank Vèssia
 Share

Recommended Posts

Hello,

I always enabled the session handler DB on my sites and it worked pretty well, but right now I'm facing a big issue because it's slowing down the server.
I have a dedicated server with 32 core and 192MB RAM so it's a good beast but since the traffic on the site is huge the session table is up to millions records each day.
I also setup the config like so
 

ini_set('session.gc_probability', 1);
ini_set("session.gc_divisor", 100);

 

still the amount of records is impressive and the queries deleting the sessions are constantly executing and they are slow...any idea how to optimize this? thanks

Link to comment
Share on other sites

I don't know if this is enough for you. But it slimifies the db table a bit. Place the following class and hook in your ready.php to remove orphaned sessions and to disallow the usage of multiple devices of the same user.

/**
 * Class to limit sessions of a user to the current session, disallow multiple devices
 *
 */  
class SessionHandlerDBRestricted extends SessionHandlerDB {

    /**
     * destroy orphaned sessions
     * @return bool true on success, false on failure
     *
     *
    public function cleanup() {
        $table = parent::dbTableName;
        $database = $this->database;
        $sessionID = $_COOKIE[session_name()];
        $userID = $this->wire('user')->id;
        $query = $database->prepare("DELETE FROM `$table` WHERE user_id=:user_id AND id NOT IN (:id)");
        $query->execute(array(":id" => $sessionID,":user_id" => $userID));
        return true;
    }
}

/**
 * hook
 *
 */
if ($modules->isInstalled('SessionHandlerDB')) {
    $wire->addHookAfter('Session::loginSuccess', function ($e) {
        $shdbr = new SessionHandlerDBRestricted();
        $shdbr->cleanup();
    });
}

 

Link to comment
Share on other sites

  • 5 months later...
16 minutes ago, Sevarf2 said:

well, I solved just changing tables from myIsam to InnoDB, now everything is fast even if that table is huge

Interesting that in this case InnoDB faster and yet there have been other issues where it's much slower: https://github.com/processwire/processwire-issues/issues/692

I am sure there's a good reason for it, but it does make the decision on which to use more difficult. Maybe MyISAM for most tables and InnoDB for the sessions table if you are using SessionHandlerDB?

  • Like 3
Link to comment
Share on other sites

On 9/11/2018 at 12:16 AM, adrian said:

Interesting that in this case InnoDB faster and yet there have been other issues where it's much slower: https://github.com/processwire/processwire-issues/issues/692

I am sure there's a good reason for it, but it does make the decision on which to use more difficult. Maybe MyISAM for most tables and InnoDB for the sessions table if you are using SessionHandlerDB?

I really hope the page creation/deletion slowness can be solved as it is quite disastrous (no blame on Ryan, just an unfortunate glitch). Who knows how many companies have evaluated PW and dropped it because of this without noticing InnoDB is at fault.

Link to comment
Share on other sites

On 9/11/2018 at 12:16 AM, adrian said:

Interesting that in this case InnoDB faster and yet there have been other issues where it's much slower: https://github.com/processwire/processwire-issues/issues/692

I am sure there's a good reason for it, but it does make the decision on which to use more difficult. Maybe MyISAM for most tables and InnoDB for the sessions table if you are using SessionHandlerDB?

I really don't know, but my site now is handling millions of requests per day without any problem since the switch...so something is there but I'm no DB expert...and I have a very powerful machine, 32core 256GB RAM, ssd etc...no apache or cpanel or other slow stuff...

Link to comment
Share on other sites

I would  guess this is down to row level locking in InnoDB over full-table lock in MyISAM. 

@Sevarf2 There is another option for handling a large number of simultaneous sessions: Redis. Your server sounds like it would be well specified to run redis too, though if it's now working well in production with InnoDB tables, I'd be tempted not to change it.

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...