MySQL 5.1 Memory Table

Anyone have any insight on the following:

I am running MySQL 5.1 on a 64bit linux install and have created a couple of Memory table. The documentation for 开发者_JAVA百科MySQL's memory tables states that the RAM will not be "released" until the table is dropped or truncated. I have dropped all of these table, but the RAM usage (from the command top) hasn't gone down a bit, even after several hours (90% on 48GB after dropping the tables). It worked its way up over a couple days with several tables being created and dropped.

Thanks, James

---

It's possible the OS hasn't flushed those pages out of RAM yet. Try using the memory in another process and see if the numbers go down for the MySQL server process.

---

You might want to consider using MyISAM tables in a tmpfs instead. The downside is that you'll have to re-create them on each server restart (memory handler tables persist themselves over a restart).

MyISAM is a lot more space-efficient than the memory engine, and probably just as fast on some workloads (you'll want to test it on a non-production system)

You probably want to try to diagnose exactly what is using the memory - it might not be the memory tables. Unfortunately knowing what's using the memory in MySQL is difficult.

Memory from memory tables should be released, but it is possible that it's not able to release it due to fragmentation, i.e. other kinds of memory being allocated in gaps in between. Normally the "big" allocations in MySQL appear at startup (e.g. innodb buffer pool) but if you've mis-tuned my.cnf, they could happen later too.

Try posting this question with your my.cnf on serverfault.

---

The last time I used a MEMORY table in that was was 4 years ago and I think we solved the problem by using OPTIMIZE TABLE command on it. I can't find documentation online to confirm this way works :S, but give it a try and see if it helps.

Apart from that, Ebay released a patch to make MEMORY table have dynamic rows instead of fixed ones. This means they will take up less memory. It's also (recently) available in Percona server http://www.mysqlperformanceblog.com/2011/09/06/dynamic-row-format-for-memory-tables/