endlessly growing sessions table

[arlomedia]

I recently moved my forum to a new web host, with newer PHP and MySQL versions. After about two weeks, I realized that the regular purge of the sessions table was taking several minutes, causing connections to queue up and eventually causing connections to fail. On the old server, the sessions table had about 30,000 rows, but on the new server it had 2.6 million rows, leading to very slow delete operations.

I compared the forum settings with my old server and found that it was using level 1 caching with memcache, but the new server had no caching engine available. So I installed memcached [sic] and made sure it was working with PHP, then upgraded SMF from 2.0.19 to 2.1.4 to use memcached, then turned on level 1 caching, and emptied the sessions table.

That initially solved the problem, but three days later the sessions table has grown to 160,000 and is still going. So I expect this will continue until it starts killing the server performance again. I don't know how to tell if some sessions are being deleted, but not as many as on the old server, or if no sessions are being deleted at all.

Does anyone know why the sessions table would grow endlessly on the new server rather than topping off around 30,000 on the old server?

Old server specs: SMF 2.0.19, PHP 7.3, MySQL 5.7, CentOS 7
New server specs: SMF 2.1.4, PHP 8, MySQL 8, Alma Linux 9

I copied the database and SMF files directly from the old to the new server, so all forum settings should be the same (except the new database connection info).

[arlomedia]

P.S. I found this thread, but it seems to no longer apply. A later post said recent forum versions already include the fix, and source code quoted there no longer exists: https://www.simplemachines.org/community/index.php?topic=211513.0

[arlomedia]

The new server has SELinux and the old server did not, so I wondered if that was interfering with memcached. but I just ran memcached-tool 127.0.0.1:11211 stats, then loaded a forum page, then ran it again and compared bytes_written, and it had grown.

[Arantor]

SMF doesn't write sessions to memcached by itself, whether or not it is configured in caching (they are separate).

You need to configure PHP itself to use memcached for sessions, and then turn off SMF's DB-based sessions.

[arlomedia]

With that setup, would the sessions table be used at all? On the old server, DB-based sessions were on, but the sessions table somehow remained at a manageable size.

[arlomedia]

I just checked the sessions table and saw that it had dropped to 34,000 rows, from around 170,000 when I posted this a few hours ago. So I started digging to see how this works. I found the Admin > Maintenance > Scheduled Tasks > Task Log page, which shows that the Weekly Maintenance ran since I posted this. Then I found the scheduled_weekly_maintenance() function in the ScheduledTasks.php file, which deletes sessions more than one day old (86400 seconds). I then ran this SQL query to show me how many rows in the sessions table are more than a day old:

SELECT COUNT(*) FROM sessions WHERE last_update<UNIX_TIMESTAMP()-86400;

That's now showing about 5500, so I expect that is going to keep climbing for a week and then get reset again by the weekly maintenance.

While I'm getting closer to understanding this, I still have some questions:

• The comment before the session purge code in the weekly maintenance function says, "Some OS's don't seem to clean out their sessions." Does that mean some other process normally cleans the sessions table, and this code is just a fallback? If some other process was cleaning the table more often on the old server, that would explain why I never had a problem with this before.
• If I can't get it working like it did on the old server, would there be any disadvantage to moving this code to the scheduled_daily_maintenance() function?
• If the session table is supposed to get cleaned more often but that isn't working, and I switched to memcached sessions, would the same problem affect that and make the memcached memory grow to a problematic size?

[Arantor]

It sounds like it's working just fine and that you had a spell of getting hit by bots not presenting cookies (so they all get new sessions)

[arlomedia]

I wanted to understand this better to be sure I wouldn't run into the same problem, so I kept digging. For my question, "Does that mean some other process normally cleans the sessions table," the answer is yes. Session garbage collection is an automatic PHP process and SMF registers a sessionGC($max_lifetime) function in Sources/Session.php to carry it out when using database-based sessions. $max_lifetime comes from session.gc_maxlifetime in php.ini. Mine was set to 604800 seconds, which is 7 days. I added some logging to this function to verify that garbage collection is running, but not removing anything. That's because with a gc_maxlifetime of a week or more, the weekly maintenance will get to it first.

In short, everything is working correctly, but my sessions table is growing for a week at a time because of session.gc_maxlifetime. I lowered that to a day (86400 seconds) and now the sessions table is staying consistently under 30,000 rows.

One thing I'm still not sure about are the downsides of cleaning this more often (I already know the downside of not cleaning it often enough). If I look up and delete my own session row, I remain logged in. It looks like a separate "SMFCookie*" is used to track the login and the session table is used only to cache info retrieved from the database. That means potentially extra database lookups if it gets cleaned too often. I could try a gc_maxlifetime of two or three days. But the PHP default is 24 minutes and the SMF hard-coded minimum is one minute, so one day seems like plenty.

gc_maxlifetime was 604800 on my old server, too, so I still don't know why the session table wasn't growing as large on the old server as it is on the new. Also, it is currently adding about 1000 sessions an hour, so it would grow to about 170,000 in a week, not the millions that I had when performance started dying. Maybe that was a result of having caching deactivated, or a difference in the previous SMF version I was using, or a coincidental increase in bot activity as Arantor suggested. But for now I'll keep this dialed back just in case.

BTW, there is a "Seconds before an unused session timeout" setting on the Cookies and Sessions admin page, but it looks like that's ignored if PHP's session.gc_maxlifetime is higher. That seems misleading and is not mentioned in the help text, so I'll log an issue about that. Actually if that was used regardless of the php.ini setting, that would have avoided this whole issue for me, so I'm curious what the reasoning is for it to work this way.

[arlomedia]

Issue logged here: hxxp:github.com/SimpleMachines/SMF/issues/8134 [nonactive]

[Arantor]

If I look up and delete my own session row, I remain logged in. It looks like a separate "SMFCookie*" is used to track the login and the session table is used only to cache info retrieved from the database.

Not quite.

The session is not entirely for 'cached' data. There are things that are necessary in session between requests that *aren't* accounted for by the cookie (and shouldn't be, for security reasons). For example, CSRF tokens.

To prove the behaviour: log in, go to a topic, delete all the entries in the session table, now try to use quick reply on that topic - it will fail with a session error.