Sessions table causes problems with MySQL

[rickc]

I have sites using SQLite, and one site using MySQL. The SQLite sites haven't caused a problem. The sessions table is continually causing problems with the MySQL site if I don't empty it once or twice a week.

Before I started emptying the table, the site went down badly enough that I couldn't access the database at all and had to file a ticket with my host. They were able to repair the database, and reported that the database had 'dozens of stuck queries' such as those listed in the file I have attached to this ticket.

[ddebernardy]

Related: #1155, #1156, #1157

[rickc]

I've seen instances of this using SQLite, also, but not to the extreme of MySQL.

[chrismeller]

This sounds like a problem with the web host's MySQL box. A simple single-row select query on the primary key? That's about as basic as you can get. There's absolutely no reason that kind of query should ever 'hang' - at least none related to Habari.

Two suggestions:

1) If cleaning session values works, perhaps their PHP is not configured to do garbage collection often enough. Assuming you can't edit the php.ini values (session.gc_probability and / or session.gc_maxlifetime), you could hook the gc_probability filter and return a higher value (100 will always run cleanup, 0 will never). Reducing session expiration would also help (so records get cleaned out faster), but it doesn't look like we provide a way to change that - it relies on the session.gc_maxlifetime ini value.

2) Activate the plugin that prevents sessions for bots. Sounds like either way your session table is getting filled with a lot more junk than it needs, so this might help keep down the size.