MySQL - question about the MEMORY engine

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

MySQL - question about the MEMORY engine

Post by mecha_godzilla »

Hi All,

I'm currently using a custom session handler in my scripts (the PHPSEC one) so that sessions are stored in my database and not on the server in some random world-readable directory. At the moment the table in question is using MyISAM but I figured that the MEMORY engine would make more sense. However, I've just been on the MySQL site and got confused while reading the following paragraph:
Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only 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, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
Because my script will be continually adding and deleting rows in this table (as people are logging-in and logging-out) will the memory used for a particular row be freed-up when it is deleted IE the memory will be available for another row to use? The table size doesn't need to be massive because the session data is very small (so I'm not looking to reclaim the memory I've set-aside for the purpose) but what I don't want is a situation where the allocated memory just fills up and fills up with dead entries and I then have to write a script to destroy and then redeclare the table at midnight and hope some poor sucker wasn't using the system at the time :lol:

Thanks in advance,

Mecha_Godzilla
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL - question about the MEMORY engine

Post by Christopher »

It sounds like you only need to run a scheduled "ALTER TABLE ENGINE=MEMORY" to free up unused memory -- not delete the whole table.
(#10850)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL - question about the MEMORY engine

Post by mikosiko »

Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only 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, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
will the memory used for a particular row be freed-up when it is deleted IE the memory will be available for another row to use?
The marked paragraph doesn't answer the question?
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: MySQL - question about the MEMORY engine

Post by mecha_godzilla »

Well...yes and no :)

Sentences 3 and 4 in that quote talk about the same thing (what happens when rows are deleted) but there's clearly a distinction being "freeing-up" memory and "reclaiming it" isn't there?

It also says that memory will only be reclaimed when the table is deleted, but that's not the same as rebuilding the table is it?

I understand where you're coming from, but the wording was a bit too ambiguous for me because I need to deploy this in an already-live environment, so I thought I'd better get some wisdom from the pros first :mrgreen:

M_G
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL - question about the MEMORY engine

Post by Christopher »

It seems like a small PHP script (or just some SQL) to insert and delete rows, run the ALTER command ... and actually checking how much memory is being used -- that would be the best what to see what will actually happen.
(#10850)
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: MySQL - question about the MEMORY engine

Post by mecha_godzilla »

Thanks Christopher - that sounds like a good plan so I'll investigate this tomorrow evening and report back with my findings...

M_G
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL - question about the MEMORY engine

Post by Christopher »

That sounds really interesting. That documentation is very ambiguous. I know I would like to know the results and probably many others would as well. It would be great if you could add links to the PHPSEC session handler (I couldn't find it with a quick search). And perhaps give us a recipe for you solution.
(#10850)
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: MySQL - question about the MEMORY engine

Post by mecha_godzilla »

Here's the link to the PHPSEC custom session handler:

http://phpsec.org/projects/guide/5.html

I've seen something similar in one of the O'Reilly books (I don't know whether it was 'inspired' by it or the author just arrived at a very similar solution). I customised my version of this script so that sessions are only started when an SSL connection is present, but otherwise I used it as-is (I figured the PHPSEC know more about these things than I do).

Over the next couple of days I'll put a script together to try and answer my original question - on the face of it the MySQL documentation looked pretty straightforward (to the point where I thought it was stupid to put this post up) but the more I read that paragraph the more ambiguous it seems...or maybe it's just me :crazy:

Thanks again,

M_G
Post Reply