the Fictional Realm

We had an issue today on a server with a high load because of ADODB’s session mechanism using MySQL as session storage engine with MyISAM table engine as advised by the manual of ADODB-session. Our server’s load is: 2500 new session creation every minute with about 17000 concurrent user sessions. A separate dedicated server (Amazon 2.3Ghz dualcore Xeon) was for he sole purpose of storing session data but the machine was not catching up.

So what basically happens here is that the default MyISAM table blows up in a matter of 10-20 minutes because of the tremendous amount of inserts/updates done on the table. Adding the advised by the manual

ADODB_Session::optimize(true);

option for ADODB to do a table optimization every time it deletes dead sessions from the table(garbage collection) doesn’t work because the optimization itself takes a lot of time on a table of this size(~17000 records). Changing table type to InnoDB and setting innodb_flush_log_at_trx_commit to 1 significantly improved performance but it was still not enough. Changing the table type to MEMORY limit’s the row’s size to a maximum of 64KB and besides this ADODB is unable to use this engine without code modification(MEMORY engine doesn’t support BLOB & TEXT columns so you’ll have to change these to 64KB chars but adodb’s queries are written in a way that works only with BLOB cells).

The sad thing here is that there’s basically no effective way to improve a single MySQL’s performance in a case with this high load directed on a single table without hardware upgrade.

Having experienced everything described above I highly recomend using memcache powered session mechanism whenever there’s a risk of having high server load. You can not do this by reconfiguring ADODB-Session but that’s not a big deal. Writing/finding a piece of code making the session mechanism work using the memcached server is a pretty quick task to do.