Simple Machines Community Forum

Hi everyone,
If you're using Debian Linux to host a SMF forum and are using database-driven sessions, I'd suggest to apply the below change immediately. Due to how Debian manages sessions, if you're using database-driven sessions, the garbage collection (deletion of old sessions) is not running and your smf_sessions table will grow to a massive size (mine got to 250,000 rows, consuming over 200 MB of space.)

In Load.php, find:

if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Replace with:

if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
{
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
ini_set('session.gc_probability', '1');
}
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Assuming that you're reading this, your sessions table is probably already rather full. The best  thing is to empty it via a "TRUNCATE" (you may do so in phpMyAdmin, or TRUNCATE TABLE smf_sessions via MySQL).

---

Technical explanation:
Because of Debian's security, /var/lib/php5 (the directory sessions are stored in) is unreadable by root users (essentially, CHMODded to 0733). Files have the permissions set normally, so PHP can still write files to the directory and read the files (providing it knows the file name, which would be the session ID), but no users can get a directory listing of the /var/lib/php5 directory. This is for additional security, I suppose - Trying to get a directory listing of the directory by whatever means will just give "Access Denied" messages, preventing local/hosted users from getting session data unless they know the session ID (on some other systems, getting a listing of /tmp/ will show all sessions, and then other users may steal these sessions).

Because of this, PHP's garbage collection won't work (because it can't get a list of all the files). Instead, Debian's PHP package comes with a cleanup cron job that runs every 30 minutes or so (I think) to remove the old files. This works fine for file-based sessions, but with database-based sessions, garbage collection needs to be implemented manually (or, like I've done here, the in-built PHP garbage collection needs to be turned on again).

Hope this helps :)

As this is a very good recommendation, I will sticky this. (If the other managers feel this isn't needed, please remove this line and unsticky. Thanks.)

Considering Ubuntu and its family are based on Debian, if you are running a server on one of those, it might be worthwhile to check if this applies to you as well.

Thanks for the tip.

I haven't been looking in my database for some time now.

1 247 304 rows, 1,3 Gb size.

I'll truncate when there are less people online because it might take a while.

I'll keep you updated how things go.

Very nice tip! I hope this is php-version independent... i currently use PHP 5.0.25 with suoshin-package...
Also i think in the next version this should be included....

Regards, Christian

Done, everything went fine. No I have 1.6gb smaller database. :)

Hopefully your Load.php hack works!

QuoteVery nice tip! I hope this is php-version independent... i currently use PHP 5.0.25 with suoshin-package...

There is no PHP 5.0.25... I think you mean PHP 5.2.5? :)
It is version independant, I'm using the PHP packages from dotdeb.org, but this also applies for the standard Debian PHP packages.

QuoteHopefully your Load.php hack works!

I didn't test it extensively, but it should work :)

Ahh I was wondering why my sessions table was 30mb in 1 week after clearing the table... Thanks. :)

I suppose this will do the same trick for SMF2.0b1?

Thanks for this tip. No wonder my sessions table was so huge :D Dou you put this patch to 1.1.5?

A Cronjob could be set up to empty it couldnt it?

Quote from: Last.Karrde on January 22, 2008, 08:19:06 PM
A Cronjob could be set up to empty it couldnt it?

That's like putting air in you tire every day instead of fixing the leak.

Quote from: Daniel15 on December 16, 2007, 08:00:14 PM
Hi everyone,
If you're using Debian Linux to host a SMF forum and are using database-driven sessions, I'd suggest to apply the below change immediately.

my hosts uses Debian Linux ver-4.0
regarding this data base driven - how to know whether i am using this feature or not..

i am new to smf.. but i am interested to learn

could u pls help me..

Are you on shared hosting or have your own VPS or Dedi?

QuoteI suppose this will do the same trick for SMF2.0b1?

It should... I've been clearing mine manually, haven't had time to implement this yet. :P

QuoteA Cronjob could be set up to empty it couldnt it?

That should also work :)
Although, I think using the in-built garbage collection is better. Basically, it runs the sessionGC() function in Load.php to delete old sessions:

function sessionGC($max_lifetime)
{
global $db_prefix, $modSettings;

// Just set to the default or lower?  Ignore it for a higher value. (hopefully)
if (!empty($modSettings['databaseSession_lifetime']) && ($max_lifetime <= 1440 || $modSettings['databaseSession_lifetime'] > $max_lifetime))
$max_lifetime = max($modSettings['databaseSession_lifetime'], 60);

// Clean up ;).
return db_query("
DELETE FROM {$db_prefix}sessions
WHERE last_update < " . (time() - $max_lifetime), __FILE__, __LINE__);
}


Quoteregarding this data base driven - how to know whether i am using this feature or not..

Admin panel → Server Settings → Feature Configuration → Use database driven sessions. If that's ticked, it's using database-driven sessions.

Whoa, exactly my issue!!

I'm on Ubuntu 6.06 and began researching because my sessions table reached 250Mb..

Thank you Daniel.

PS: lucky for me I was patient enough. Was searching for the 'sessions table' using the forum's search function, and this post was the LAST one. Why aren't sticky posts displayed first in the search result ?

Thanks for the info, I've got a fairly large Debian hosted forum which currently has a 2.8 GB sessions table, even after having truncated it about a year ago. :P
Looks like I'll be truncating the table again for sure and everybody will have to log back in, oh well. At least after this change the sessions table should remain a manageable size.

Again, many thanks.

Glad I could help :)

QuotePS: lucky for me I was patient enough. Was searching for the 'sessions table' using the forum's search function, and this post was the LAST one. Why aren't sticky posts displayed first in the search result ?

Hmmm... No idea why they're not first.  Anyways, I modified the subject so it's hopefully more relevant :)

I wanted to say that the session table will eventually cleared itself, I didn't have to truncate it. So let it be a couple of days, and probably those sessions will start to expire or something..

Ahhh...! The infamous gc_probability setting. On one of my servers (forum.cyna.net), this was set to 0 indeed. Meaning that the garbage collector had zero chance of being triggered. I remember spending something like a month with my server admin trying to figure out why the session tables wouldn't empty like they do on my other (shared hosting) server. In the end, I found out about gc_probability, and that was it.
My server admin always thought the variable was reset unknowingly by another admin. I will have to tell him this was due to a bug in Debian ;)
Thanks for the tip!

QuoteI will have to tell him this was due to a bug in Debian

Not a bug, a feature ;)
Read the technical explanation I posted :)

Quote from: Daniel15 on February 05, 2008, 09:49:06 PM

QuoteI will have to tell him this was due to a bug in Debian

Not a bug, a feature ;)
Read the technical explanation I posted :)

I'm not sure he understood it either  O:)

Can't we make a quick mod of it (until it get implemented in 1.1.5 - if they planning to do so)?

Regards, Christian

I don't think it's worth packaging a mod for this fix, seeing as it's a really small one...

Is this fixed in 2.0? Or is it still an issue?

this is the code from 2.0:



                // Use database sessions? (they don't work in 4.1.x!)
                if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
                {
                        session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
                        @ini_set('session.gc_probability', '1');
                }
                elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))
                        @ini_set('session.gc_maxlifetime', max($modSettings['databaseSession_lifetime'], 60));


From what i can see its been solved for a long time

That code is in 1.1.5 also and my table is 1.5 MB with about 10 000 members, 200 online... so this shouldn't be sticky...

Indeed, after upgrading to 2.0b3.1 I've tested it for a couple of weeks without the modification above, and have observed that the sessions table now stays at a reasonable size.
I also occasionally check the running queries for MySQL and have spotted the query running which cleans out old stuff from the sessions table.

Speaking about this kind of thing...

I've noticed that my smf_log_digest table is growing large. It's not TOO large, but it's supposed to be purged at least on a weekly basis, and I've noticed that my table is filled with message notifications dating from last november! All my 2706 entries have a "daily" value of zero. At line 605 of ScheduledTasks (SMF 2.0):

WHERE ' . ($is_weekly ? 'ld.daily != {int:daily_value}' : 'ld.daily IN (0, 2)'),

"daily_value" is set to 2. So, if $is_weekly is set, all my entries will be used. If it is not set... They will still all be used!

I'm not sure this is the intended behavior... I need to point out that my Scheduled Tasks options for "weekly digest" and "daily digest" are both enabled. I don't know what else is wrong.

Hi, SMF newbie here. 

I am not a techie so please bear with me.   ;)

Would like to know if the problems we're experiencing with our host regarding increase in server memory utilization is related to this?  Just installed SMF about a week ago only. 

I have no idea how to work out the solution, but will give it a try by applying the code change as tipped in this topic. 

here is a screen of the report we got from the host.  Don't know if this is enough info to tie in our problems to the recommended fix for large sessions table.



I also noticed having problems when uploading files via FTP.  The FTP app tells me it couldn't transfer files since there are "too many connections (8) from this IP".  That happens when I am logged on SMF.  When I log-out and closed the browser for the SMF, the FTP file transfer proceeded without problems.

An immediate reply would be appreciate as our account has been suspended already.  :(  We're on shared hosting, btw.

Thanks!

I don't think it's related...
Your best bet to check for a possible relation would be to launch phpMyAdmin and check whether smf_sessions is huge or not.
Usually problems happen after several months or years, not after a week...

Quote from: Nao 尚 on July 17, 2008, 04:50:28 AM
I don't think it's related...
Your best bet to check for a possible relation would be to launch phpMyAdmin and check whether smf_sessions is huge or not.
Usually problems happen after several months or years, not after a week...

Thanks, Nao!

Is this still issue? Unsticky?

Quote from: Owdy on December 05, 2009, 02:32:42 PM
Is this still issue? Unsticky?

It's still an issue, especially with SMF 1.1.

2.0?

Can I ask what are the problems caused by this (Other than a large file, is there any load issues ?) and do they still exist in smf 2 ?

I believe this has been fixed in 2.0 (don't quote me on that though).

I don't believe it will cause any load issues immediately, but if it grows to millions of rows, it will slow down accesses slightly.

QuoteI don't believe it will cause any load issues immediately, but if it grows to millions of rows, it will slow down accesses slightly.

And if it's using MyISAM (the default MySQL engine), the table locking will  eventually become really slow and kill your performance :P

I have moved to cloud hosting, which I keep crashing almost hourly. The hosts technical help department are struggling to locate the problem. Whats the chances that this file is causing my problem ? Im on the latest version of smf 2

If their servers are using Debian, it's possible, otherwise it's not likely.

Quote from: glennk on January 20, 2010, 04:27:22 AM
I have moved to cloud hosting, which I keep crashing almost hourly. The hosts technical help department are struggling to locate the problem. Whats the chances that this file is causing my problem ? Im on the latest version of smf 2

Get a competent host?

Ok, Ive spoke to the host and apparently Im on centos so this isnt my issue I dont think. Thankyou for your help with this one.

Maybe someone should add that this has been fixed in SMF 2.0 RC3 to the first post?
My server is on Ubuntu Lucid and I was checking SMF source codes and what Daniel was saying is already implemented in the standart file. Not sure about other versions of 2.0, I just checked RC3. Also I haven't noticed any database related problems, I'll keep an eye on sessions, and drop a note here later.

im running 1.1.11 is it already fixed?? otherwise im in a sheared enviroment, should i use database driven session or not? thx

(i mean i use a hosting that gave mi a /home/myuser account i think thats a sheared enviroment)

Quote from: Daniel15 on December 16, 2007, 08:00:14 PM
In Load.php, find:

Code Select Expand


if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Replace with:

Code Select Expand


if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
{
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
ini_set('session.gc_probability', '1');
}
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Seems this is changed in 1.1

Quote from: Daniel15 on December 16, 2007, 08:00:14 PM
Hi everyone,
If you're using Debian Linux to host a SMF forum and are using database-driven sessions, I'd suggest to apply the below change immediately. Due to how Debian manages sessions, if you're using database-driven sessions, the garbage collection (deletion of old sessions) is not running and your smf_sessions table will grow to a massive size (mine got to 250,000 rows, consuming over 200 MB of space.)

In Load.php, find:

Code Select Expand


if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Replace with:

Code Select Expand


if (!empty($modSettings['databaseSession_enable']) && @version_compare(PHP_VERSION, '4.2.0') != -1)
{
session_set_save_handler('sessionOpen', 'sessionClose', 'sessionRead', 'sessionWrite', 'sessionDestroy', 'sessionGC');
ini_set('session.gc_probability', '1');
}
elseif (@ini_get('session.gc_maxlifetime') <= 1440 && !empty($modSettings['databaseSession_lifetime']))


Assuming that you're reading this, your sessions table is probably already rather full. The best  thing is to empty it via a "TRUNCATE" (you may do so in phpMyAdmin, or TRUNCATE TABLE smf_sessions via MySQL).

---

Technical explanation:
Because of Debian's security, /var/lib/php5 (the directory sessions are stored in) is unreadable by root users (essentially, CHMODded to 0733). Files have the permissions set normally, so PHP can still write files to the directory and read the files (providing it knows the file name, which would be the session ID), but no users can get a directory listing of the /var/lib/php5 directory. This is for additional security, I suppose - Trying to get a directory listing of the directory by whatever means will just give "Access Denied" messages, preventing local/hosted users from getting session data unless they know the session ID (on some other systems, getting a listing of /tmp/ will show all sessions, and then other users may steal these sessions).

Because of this, PHP's garbage collection won't work (because it can't get a list of all the files). Instead, Debian's PHP package comes with a cleanup cron job that runs every 30 minutes or so (I think) to remove the old files. This works fine for file-based sessions, but with database-based sessions, garbage collection needs to be implemented manually (or, like I've done here, the in-built PHP garbage collection needs to be turned on again).

Hope this helps :)

Thanks a lot there OP. Quick question - does it matter which PHP version I have?

i can't understand your code but i am try..

nice info i am using this os so i wanna change this immediately,i never had any idea about this

Is this already on 2.0.1 right? I looked in my Load.php and this modifiacation was already been done...
thanks

thanks for really helping information

I've hunted around the SMF site, checked the manuals and this thread appears to be the best place to hopefully find an answer (or answers) ... and I will apologize up front if this appears to be step backwards, but most of the posts here are a couple of years old and there have been a few SMF upgrades since then and given that I am a novice at this, I don't wish to make more mistakes than absolutely necessary.

I've been running a SMF forum for a few years now and I am not a techie. My forum isn't large (or that's what I understand), runs on SMF 1.1.16, and I have only one theme mod (though I would like to add two more).   My Forum is seasonally busy, mostly from March to September and much quieter the rest of the year.  My Forum runs fine but it seems that some of the tables when I look at my databse in phpMyAdmin are very large. 

In particular:
   _sessions = 1,220,458 records / 670.0 MiB
   _messages = 60,693 records / 29.9 MiB
   _personal_messages = 6,118 records / 3.7 MiB
  ... the rest are all KiB and much smaller

It isn't really a surprise that _messages and _personal_messages are large just by their names - my members like to talk to one another both on the boards and via PM.  The _sessions file I'm afraid I just don't understand fully what all is being stored there and why is should be so very large, considering the size of my forum.  If someone could help me with a simpler explanation, I would appreciate it! 

I have never made a manual change to any of my SMF files, but from what I have read above, this might be necessary ... but before I do that, I would appreciate an opinion more knowledgeable than mine.  Or if there is something else I can/should do either in phyMyAdmin or SMF.

Thanks for everyone's patience on this.  My forum has/does run fine but I would prefer to proactively address problems if I can.  And if there isn't a problem, except in my lack of technical knowledge, that's fine too!  ;)  And if more information is needed, just let me know.

Tansy

PS - sorry, I can't say if my forum is a "Debian/Ubuntu Linux hosted forum", but if I know what to look for/ask about I can find out ...

Forgive me if this has been answered already:

Does this problem still need manual correction on a Ubuntu 12.04 server with a fresh install of SMF 2.0.4?

Or has this been corrected over the last few years since the but/thread was first set sticky?

Yes, this has been fixed in SMF2.0.x

Quote from: shadow82x on June 23, 2013, 01:48:16 AM
Yes, this has been fixed in SMF2.0.x

Thank you. :)

what the better windows or linux?

Almost without exception, Linux.

Ideally you should be using something like memcache or even better redis for storing sessions. The performance will improve drastically. Just sayin'

Except that most SMF users are on shared hosting where such things are not available.

Wicked case of deja-vu all over again ... back in 2012, I had to back up my forum quickly as it seem to be degrading but because I had some very large dbase files, I was having trouble so I posted a message here for some information, can't remember if anyone pm'd me with an answer (link to post (https://www.simplemachines.org/community/index.php?topic=211513.msg3324310#msg3324310)) (apologies) but somehow managed to backup and restore and forum has been working well.  Now I am in the same position again albeit for different reasons.

Now I am being inundated by error messages and from reading on other boards, that really my only option is to upgrade to SMF 2.x.x.  Fair enough, been hoping to find time to do that though I would have preferred it was during my less busy season - but needs must, so I delved back into what I needed to do to make a backup so I could upgrade it and keep my original forum intact until I've completed the upgrade.  (At the moment my forum is shut down).  Problem is that my _sessions file is ginormous (yes, we are getting technical now) and I have cannot for the life of me a) remember how I did it or b) find notes on what I did.  Rest of my forum files I have backed up very easily, it is just this _sessions file that is problematic, but no wonder it has 2million rows.

So what have I tried? (please note, I am a novice who knows just enough to get me in trouble)  I've tried to export the _sessions table separately, using myPHPadmin, by staggering the SQL download in 50,000 row segments but it stalls out when starting at row 300,001 - a long way from where I need to be.  I remember from last time how to stitch tables back together to import them but not how to get it downloaded in the first place.  I've looked at other software to stagger the dump, but am doing something wrong which is unsurprising as it likely has to do with configuration files or ? - this would be the trouble arising from a little bit of knowledge.

Anyone have any suggestions?  My frustration is starting to whisper the question "how important can the _sessions file really be" in my ear , so any ideas or suggestions would be appreciated  :o

Thanks!

Not that important if you can clear it all or clear based on date/time. It just holds whether people are logged in or not. If cleared they will need to relogin and enter their username or password.

Quote from: vbgamer45 on April 23, 2017, 01:52:02 PM
Not that important if you can clear it all or clear based on date/time. It just holds whether people are logged in or not. If cleared they will need to relogin and enter their username or password.

Really?  If I have to do all the work to upgrade the forum so it continues to work for their enjoyment, I have no qualms about getting them to log in again.  Heck, I'll even give them a heads up so they can track down their logins or have me reset them!!  If I did just clear all the data, would you know would it reset?  Post counts?  (wouldn't think so)  Time online?  Anything else?  Not that I would really care in either of those examples, I can find other ways of giving them credit for being long-time members and its the price they would pay to have a forum that works ...

If you just clear the sessions table, literally nothing else is affected other than 'they'll have to log in again'. It just tracks who is online right now.

Quote from: Arantor on April 23, 2017, 03:20:08 PM
If you just clear the sessions table, literally nothing else is affected other than 'they'll have to log in again'. It just tracks who is online right now.

Thank you vbgamer45 and Arantor, I think you just made my entire year!! ;D