Which Engine Type? MEMORY or InnoDB?

[drewbee]

Currently I am storing my session data in a MEMORY table. I noticed though as records get deleted (due to inactivity), overhead is generated. Unfortunately, MEMORY does not offer a OPTIMIZE command, nor can memory really be "optimized" anyways.

 

So, from anyone who has ever done this, what is the best way to go? InnoDB or Memory for this type of situation? If you went with memory, what is the deal with the overhead, and what ways did you use to combat it? Or is this just something that doesn't need any attention?

 

As far as I can understand, MEMORY tables automatically allocates the full amount of possible memory usage by the table, so that memory is not usable by anything else other then the table (whether its full or empty). SO what happens when the deleted rows (and overhead) grow to the size of the total memory allocation?

 

Thanks for the insight.

 

[fenway]

I've only ever used memory for temporary tables in quick summary situations...

[drewbee]

So you would recommend storing session data in a memory table? Do you know anything about the overhead phpmyadmin is reporting on it?

[fenway]

So you would recommend storing session data in a memory table? Do you know anything about the overhead phpmyadmin is reporting on it?

I would assume this overhead is representative of gaps in the table... but as I said earlier, since I only use this for summary tables, I don't have this issue.  Be aware that there is no VARCHAR in a temporary table!

[drewbee]

Ok, I understand what you are saying now.

 

If anything, I would say that unless this table is regularly emptied through the truncate command, the gaps (overhead) will fill up all of the alloted space.

 

Their is no varchar? Now I am really confused. Why did it let me create varchar columns then?

 

Here is my current table structure for session:

 

-- 
-- Table structure for table `session`
-- 

CREATE TABLE `session` (
  `SessionID` varchar(32) NOT NULL,
  `AccountID` int(20) NOT NULL,
  `Location` varchar(75) NOT NULL,
  `LastMove` int(20) NOT NULL,
  PRIMARY KEY  (`SessionID`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

 

Thoughts?

 

[fenway]

You may want to check out the relevant refman page -- it's just silently converted.