Best Practices With MySQL Memory Tables to Minimize Memory Footprint of mysqld!

Amazon If you use MySQL memory database tables (with table engine set to memory) you need to be very careful. You may feel that your web server is running a lot more faster than before and indulge in your achievements inordinately but you need to learn the following best practices before your MySQL daemon hurts you

What happened to my server?

My web server used to use non memory tables and it runs slowly. So I changed it to use memory tables and managed to achieve a big reduction in the running time. I happily thought my server was in a great shape and there's nothing to worry about.

Boy was I wrong One day I realize my server was returning 502 Bad Gateway error. I quickly found that the problem was the PHP FastCGI process was killed for no reason (more story at Why Does PHP Fast CGI Process Keep Dying?. I quickly fixed the problem by restarting the PHP FastCGI process and everything was back to normal.

I realized that this happened once every couple of days. So I got concerned. When I ran top command on my server and sorted the rows by memory % (by pressing Shift >) I saw the following horrible screenshot My MySQL daemon was consuming more than half of the total memory on the server!



I began taking steps to fix this issue, and ended up reducing the memory consumption to a mere 8.7% as shown in the following screenshot! Read on to see how this is done.

I assume you copy the content of a source table to the memory table at MySQL daemon startup, and you may modify the content of the source table but not the memory table.

1. Make sure your varchar() columns have the minimum necessary sizes

If you use varchar() make sure its size is the minimum size you need. If you aren't sure simply run a query on your MySQL database to find out the maximum length each column of your table occupies. The following is an example query assuming table entry has two columns: column1, column2.

SELECT
'column1', Max(CHAR_LENGTH(column1)) AS Max,
'column2', Max(CHAR_LENGTH(column2)) AS Max
FROM entry;

Then simply change the column's size in the memory table to match the results of running the query. You don't need to change the source table.

The number in varchar() is the number of characters, even if it is a UTF8 character. For example I can fill a varchar(3) column with H你好.

2. Get rid of outliers in your memory table

Suppose the query yields a big size, say 2055, on some column simply look at the second max size. If the second max size is very far from 2055, say 500, that means you have an outlier in the column in your table. Simply track down in the source table and edit the target row's target column to be smaller. You certainly don't want to sacrifice performance just for this one outlier.

You don't need to worry about data getting truncated because you should NEVER edit memory tables. You should instead change the content of the source table, and simply instruct MySQL daemon to copy data from the source table to the memory table when the daemon boots up.

3. Minimize number of columns in the memory table

If you don't need to use a column just don't put it in the memory table structure. You can preserve the column in the source table just in case you'd like to use it in the future. Your MySQL daemon startup script will simply copy the content of only needed columns from the source table to the memory table.

If you do all the best practices mentioned in this article you can be rest assured your memory table will be running fast and lean.

Related Articles
Why Does PHP Fast CGI Process Keep Dying?
Save MySQL Daemon Process's Memory Usage By Making VARCHAR Columns Smaller!

If you have any questions let me know and I will do my best to help you!