Jump to content
Sevarf2

Session handler DB issue

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

Share this post


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

 

Share this post


Link to post
Share on other sites

Hi @Sevarf2

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
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.

Share this post


Link to post
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...

Share this post


Link to post
Share on other sites
1 hour ago, Sevarf2 said:

32core 256GB RAM, ssd etc.

Cool specs! 😎

  • Like 2

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...