Very large modSession

Hello all ;-)

I've a client who runs pretty busy website on MODX 2.3.x.
modSession table is like 140k records/2+GB of data, sessions are cleaned up after 24 hrs (this is via server setting. MODX setting does not work - and the admins probably won't let it ;-) )
The website runs pretty ok, but the admins complain database is bit big (its dedicated machine)

I'm thinking of two solutions:
1. Switch to default PHP session handler (file based)? Have never tried before, and with that busy website I'm bit afraid.
2. Drop and recreate table (to free disk space, it's InnoDB). Then create two snippets run from cron (hopefully ;-) )
a) every hour: deletes entries older than 4-6 hrs in chunks of 1000 rows. This probably should be much faster with smaller table and not so many outdated entries.
b) every 24 hours: creates an empty copy of modx_session table, (probably inserts records not older than say an hour), then drops the old one

Maybe it's better if I switched back to MyISAM (we have no idea why it is InnoDB now)?

What do you think?

IIRC, xPDO doesn't run very well with InnoDB. I could be wrong.

Instead of b), have you looked into OPTIMIZE TABLE? Not sure what versions it is available for, but it might be the safer option. (http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html)

Quote from: gissirob at Dec 07, 2015, 05:34 PM

Instead of b), have you looked into OPTIMIZE TABLE? Not sure what versions it is available for, but it might be the safer option. (http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html)

Optimize does not release space with InnoDB unless innodb_file_per_table.

I've experimented a bit on my development Ubuntu Server VM, not really optimized but pretty fast (through phpMyAdmin/pure SQL)
Truncated DB to 35k records, then recreated the table to make sure it's not filesystem/cache/whatever influenced and to reduce size to ~350MB.

- delete 10000 rows took about half a min* for InnoDB, and 0.15s for MyISAM,
- create temp, insert 35k rows from orig table, drop mod_session, rename temp table to mod_session: 1m04s for Inno, 8s for MyISAM

* do not remember exact, I've written it down, but it's in the office. A bit busy day ;-)

I've read that MyISAM would be faster in reads. But this? (again, no MODX/xPDO - just SQL through phpMyAdmin)

How about "old fashioned" file based sessions? Any cons?

I have no experience with file based sessions but I have done quite a lot of work with trimming large log files. Unix is very efficient at doing this but the problem arises if / when the file is in use by another process (eg writing a new session) when you are trying to do the trimming. Quite a lot of jumping through hoops to get around this whereas the database handles locking much more efficiently.

Sessions are kept in separate files, so no issue with locking.