Session handler DB issue

[Frank Vèssia]

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

[kixe]

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();
    });
}

 

[Frank Vèssia]

I'll try that, thanks

[Frank Vèssia]

nothing changed

[Mirza]

Hi @Sevarf2

I am also dealing with the same problem, by any chance did you have any solution to this problem?

[Frank Vèssia]

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

[adrian]

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?

[Beluga]

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.

[Frank Vèssia]

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...

[horst]

1 hour ago, Sevarf2 said:

32core 256GB RAM, ssd etc.

Cool specs! ?

[netcarver]

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.