Big session tables can crash SQL server

I had a similar situation on two sites. Once this module was installed, the next cron run locked the sessions table while many millions of rows were deleted. On MyISAM, DELETE locks the entire table, which meant that the act of installing this module on a site actually flattened the server while the session table was cleared.

I have been thinking about a couple of potential fixes. One is the LIMIT that chirale proposes, but in order to remove all the extra sessions, that LIMIT would have to be larger than the number of visitors per day to the site in question - otherwise the session table will grow faster than this module will be able to clean it up (because it only gets called once per day).

DELETE LOW PRIORITY looks tempting, but I don't think it will help prevent table locking, and it may be MySQL specific anyway, so that's not a complete solution.

In our case, a fix might have been to be able to schedule the time of day that the cleanup happened. If we could schedule the cleanup for 4am (a low-use period for our site), it would be much more practical for us. This is the best fix I can propose at this point - very keen to get the maintainer's feedback on that idea.

Another useful tool might be to have a cleanup interface added, which says "purge XXX session rows now". Always possible to do this against the DB directly though.

We can do a TRUNCATE the first time, and it is fast. The drawback is that it will log off all users, and I think it is MySQL-ism only.

Other than that we can update the README.txt and the project page to inform the user that the first time may be taxing on the database and the site, and to run it off hours.

Can someone provide a patch for README.txt to that effect?

Here's an idea (cribbed from #559814: Sessions table (InnoDB) locked when expired sessions are being deleted which is essentially a duplicate report):

When enabling Session Expire, check and see the size of the sessions table. If it's "large" (eg, if select count(*) from sessions where sessions.timestamp < unix_timestamp()-7*24*60*60 ; takes more than a second or two to execute) then warn the person installing the module to do a manual cleanup first.

Alternatively, we could do something like this in expiry phase:

• Count the number of expirable sessions. Note the time taken to do the COUNT() above as well.
• If the time taken to do the count is large, restrict the number of deletes using LIMIT with some configurable value.
• Warn the site admin that there are enough sessions in the DB table to cause a performance slowdown, and that a throttle is in effect to prevent site outages.
• Recommend alternate processes to purge the sessions table.

Implementing a Drush command would allow site administrators to use Drush to flush the sessions table at low-activity times, without causing slowdowns during regular cron runs.