MyBB Community Forums

Hi All,

After realising last night that the cause of performance issues and odd stats was my sessions table being innodb and causing slow loads on the site, I have been doing some further investigation. When it comes to advice around performance we often say to change the storage engine of the sessions and posts tables to innodb.

The posts table makes sense as it has an AUTO_INCREMENT primary key that is indexed.

Innodb performs badly when it has a random indexed primary key. As such the sessions table (and I found the myadvertisements table also had this as the unique ID was not indexed rather the advert field was)
The sessions table has a random ID and no index (for obvious reasons)

therefore particularly in the latest versions of mysql and mariadb (or percona for that matter) you will end up with worse performance changng the sessions table to Innodb.

So on to my question

What are your thoughts on adding an index to the SID column or an additional auto increment field into the sessions table?

Or additionally although it wont survive restarts (though those should be very rare on a dedicated or VPS server) changing the sessions to MEMORY table?

My page generation is pretty good now, somewhere around

Generated in 0.1411891

however I am always looking to skim every bit of performance out of the system I can get

I'm more in favor of adding a AI PRIMARY KEY. In theory it makes sense a numeric key would be better indexed than a random string.

Another thing I noticed is the table usage seems very high for the sessions table. My test site is at 21 rows and has 1 MB, but that is from having it be of type MEMORY. It seems like there is quite a number of indexes, which probably aren't needed. I'd suggest giving this a try:

-Drop the primary key on sid. Possibly have that be a unique key instead.
- Add a column that will be an AI and have that be the primary key.
- Drop the other indexes other than time.

Also, if you think it'd help, in the /inc/class_session.php file is where it queries the session table.