XF_Session - Very slow as InnoDB

[nrep]

I've found that having xf_session as InnoDB dramatically adds to the page generation time. On a powerful test server, with optimised my.ini settings and nothing else running (other that me hitting that page), the forum index page generation times are as follows:

xf_session = MyISAM = 0.03s
xf_session = Innodb = 0.10 - .015s

That's over a 3 fold increase from this one query:

UPDATE `xf_session` SET `expiry_date` = ? WHERE (session_id = '2cb236cf10c72722230ac458f575e89e')

Is there anything I can do to speed things up, other than switching to MyISAM? I'd like to avoid doing so, as if the table is marked as crashed then it would bring the forum down (and it'll happen at some point!).

 

[Paul B]

other than switching to MyISAM? I'd like to avoid doing so, as if the table is marked as crashed then it would bring the forum down (and it'll happen at some point!).

There's no reason why the session table should crash, unless there is an underlying issue on the server.

Have you considered using Memcached and opting in to session caching?

$config['cache']['cacheSessions'] = true;

 

[nrep]

Yeah, it shouldn't - but it has happened on occasion, often when something else crashed and we've had to reboot the server unexpectedly (happens a couple of times per year at most).

Thanks for the memcached suggestion. We're running on a Windows server, so it's not as straightforward - but it may be worth a shot!

 

[Paul B]

often when something else crashed and we've had to reboot the server unexpectedly

That would likely explain it.

MySQL should be stopped and started gracefully to avoid table crashes.