Memory storage engine (as its name suggests) stores data in database server memory rather than on disk. Disks are slow, memory is fast. So advantages of MEMORY storege engine over any other disk based storage engine are clear:
- reads (SELECT statements) are much faster
- writes are faster too (thanks to InnoDB log buffers not as much as You might think though)
- because writes and reads are done to and from memory there’s no I/O access
- hash indexes are supported (primary key lookups are fast!)
There are some disadvanteges too:
- data stored in MEMORY table is lost on server shutdown
- currently MEMORY storage engine supports only table level locking
- unless You’re using Percona Server 5.5 only fixed row format is supported
- due to above only Percona Server 5.5 supports BLOB and TEXT fields on MEMORY storage engine.
On server shutdown or crash all data in MEMORY table is lost. Wy would anyone need to store data that.. uhm.. does not need to be stored? As it turns out there are situations in which data needs to be saved for a while and it’s not a big thing if it suddenly vanishes. One such application is handlig of visitors sessions – We certainly need to store them somewhere but if our database server crashes nothing bad will happen (without database server Your site most probably is offline anyway). Sessions and various counters are responsible for a lot of database I/O load – each request most probably reads at least one sessions record and writes one session record. So let’s switch to MEMORY storage engine!
Switching to MEMORY storage engine
Zen Cart uses two tables that are ideal candidates for using MEMORY engine: whos_online and sessions. Switching whos_online to MEMORY is very easy and can be done with one line of code:
ALTER TABLE whos_online ENGINE = MEMORY;
Voila! You just offloaded Your database server quite a bit. Unfortunately with whos_online situation is bit more complicated due to fact that it uses BLOB column to store session data and BLOBs are not supported in MEMORY engine in current MySQL release (5.5). To switch sessions to MEMORY engine You will need Percona Server >=5.5. (which I recommend to use anyway as it’s much better than regular MySQL server). Assuming You do have Percona Server 5.5 You can change sessions table as easily as whos_online:
ALTER TABLE sessions ENGINE = MEMORY;
Well, You can use MEMORY on sessions table with one little trick: change type of column sessions.value from MEDIUMBLOB to VARBINARY(65535) to get rid of BLOB type column. Unfortunately it has major consequences: ZenCart does not guarantee that 65kb of binary data for session will be enough (though it’s unlikely that session related data will exceed 4kb), and MEMORY will use whole 65kb of data to store each row in the sessions table. If You have 100 visitors at any given time it’ll take 100*65kb = 6,5Mb of memory instead of optimal 100*2kb = 200kb (assuming average session data size of 2kb). That’s a lot of wasted memory!
If for some reason You can’t use MEMORY storage engine and You still use MyISAM for whos_online or sessions tables switch them to InnoDB now!
You should be aware of few things. First of all amount of memory dedicated to MEMORY storage engine is limited by MySQL variable max_heap_table_size. By default it’s set to 16MB and those 16MB are divided among all MEMORY based tables. You must keep in mind that total size of all of Your MEMORY tables must be well below max_heap_table_size to take into account possible spikes in amount of data stored in them, for example due to website traffic spikes. Fortunately You can change max_heap_table_size in Your my.cnf configuration file.
Second, if You constantly insert rows into MEMORY table You must also remember to purge it from time to time. Fortunately Zen Cart does that for us when it comes to sessions and whos_online tables. As alternative You can also use either cron script or MySQL CREATE EVENT to purge MEMORY table from time to time. Here’s example of such event:
DROP EVENT IF EXISTS PURGE_MEMORY_TABLES;
CREATE EVENT PURGE_MEMORY_TABLES
EVERY 1 MINUTE
ON COMPLETION PRESERVE
DELETE FROM whos_online WHERE time_last_click < (UNIX_TIMESTAMP() - 15 * 60);
DELETE FROM sessions WHERE expiry < (UNIX_TIMESTAMP() - 15 * 60);
Make sure that MySQL event scheduler is running:
Search for `event_scheduler`(it should be ON). If it’s OFF You’ll need to add
event_scheduler = ON
in Your my.ini (usually /etc/my.ini) file. You can also turn it on (until server shutdown) by:
SET GLOBAL event_scheduler = ON;
There are some other things You might want to know about MEMORY engine. I recommend reading MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html