Handling Anonymous User Sessions in Site-Wide Implementation

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
JackRoofman
Registered User
Posts: 10
Joined: Wed Aug 15, 2007 7:47 pm

Handling Anonymous User Sessions in Site-Wide Implementation

Post by JackRoofman » Sun Mar 13, 2011 2:08 am

I've had phpBB integrated into my site for a while now. We just recently switch from a managed hosting solution to a VPS, giving us root access. MySQL is running painfully slow and consuming far more than its fair share of CPU, and one of the problems we've identified is with the phpBB integration, as it's at the top of every page.

The following query is routinely executed:
SELECT COUNT(session_id) AS sessions
FROM [sessions table]
WHERE session_user_id = 1
AND session_time >= [timestamp];

The problem is that because this is run at the top of every page, there are more than 13000 rows in the sessions table, and even with the WHERE clause it's still doing a full-table scan, since MySQL doesn't use indexes on range queries.

Running an EXPLAIN on the query shows it scanning 10000 of the 13000 rows, and adding a FORCE INDEX() on either session_time or session_user_id bumps it up to a true full-table scan of all the rows.

For reference, the data in the table looks like:
24a9f2ec915078b9c9caaf06612c7a7f, 1, 0, 1299977332, 1299977332, 1299977603, 98.157.98.41, Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) Ap..., ../index.php, 1, 0, 0

There is no need to be storing this data; it serves me no purpose at all. But adding these records to the sessions table, and then having to scan all those extra records is proving to be incredibly expensive.

What is the suggested method for disabling the session behavior for anonymous users?

JackRoofman
Registered User
Posts: 10
Joined: Wed Aug 15, 2007 7:47 pm

Re: Handling Anonymous User Sessions in Site-Wide Implementa

Post by JackRoofman » Sun Mar 20, 2011 1:15 am

Bump, because my mysql daemon continues to consume far too much CPU, and running mytop gives me a screen full of that query.

Any advice on how to disable this behavior without affecting the actual forum software or registered users would be appreciated.

Locked

Return to “[3.0.x] Support Forum”