Converting Session Table into MEMORY Engine

[eluser]renownedmedia[/eluser]

Code:

CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '0',
  `ip_address` varchar(16) NOT NULL DEFAULT '0',
  `user_agent` varchar(50) NOT NULL,
  `last_activity` int(10) unsigned NOT NULL DEFAULT '0',
  `user_data` varchar(512) NOT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

(Note that the MEMORY engine can't use TEXT columns)

Has anyone tried this, or does anyone know of any potential pitfalls? We're developing an application and anticipate up to a thousand simultaneous users, and minimizing disk I/O would be optimal.

Code:

1,000 USERS x 628 Bytes (give or take) <= 1MB

Sure, given a reboot the data would be lost, but it wouldn't seem like session data would be too important. The server/application would likely undergo weekly maintenance and a reboot, so any data in the session could possibly be incompatible with the updated code anyway.

[eluser]WanWizard[/eluser]
In terms of optimalisation, I wonder it that is what you have to focus on.

Session I/O is simple and straight forward compared to the normal I/O of an application. In terms of performance I would start profiling (both code and I/O), and attach the big issues first.

How heavy is the use of session variables in your application? In standard CI, every time you use set_userdata(), the session table get's updated. For our project we've modified the session class so it doesn't do that, we only update the session at the end of script execution, or when using a redirect. This has saved us dozens of I/O's per page request.

[eluser]jpi[/eluser]

For our project we’ve modified the session class so it doesn’t do that, we only update the session at the end of script execution, or when using a redirect. This has saved us dozens of I/O’s per page request.

Yeah, sure. But this approach could be tricky. For instance, if a user wants to close his session on your website, then you destroy his session with CI at the beginning of the script. But if later on, another part of your script check the user's session to see if he is logged in, you have no way to see that he is not.

I like Thomas Hunter idea. For sure the gain will be low, but it's still a valid optimization. Especially if you don't want to set up a memcached server to store your session data.

[eluser]danmontgomery[/eluser]
per http://dev.mysql.com/doc/refman/5.0/en/m...ngine.html,

Memory used by a MEMORY table is not reclaimed if you delete individual rows from the table. Memory is only reclaimed when the entire table is deleted. Memory that was previously used for rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted you should use ALTER TABLE ENGINE=MEMORY to force a table rebuild.

and I'm not sure that 628b number is accurate,

The memory needed for one row in a MEMORY table is calculated using the following expression:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))
ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

[eluser]WanWizard[/eluser]
[quote author="jpi" date="1276635024"]Yeah, sure. But this approach could be tricky. For instance, if a user wants to close his session on your website, then you destroy his session with CI at the beginning of the script. But if later on, another part of your script check the user's session to see if he is logged in, you have no way to see that he is not.[/quote]
That's another 'missing feature' of the session class that we've fixed. If you call $this->session->sess_destroy(), it should also delete all loaded userdata. The CI session class doesn't do that (and I think that's a bug), which could lead to errors like that.

BTW: it's quite common to perform logoff tasks, then redirect to a page that says that you're logged out (or to another public page). I don't consider it good practice to destroy a session, and continue with a script that requires a valid session. If you have such a script, you should immediately create a new session after destroying the old one.

[eluser]jpi[/eluser]
To be complete on the subject, session_id is generated using the md5 function (even in CI 2) so the column should be CHAR(32).

Beware that, as data are serialize, varchar(512) might not be enough for the user_data column.