Tim.Igoe.me.uk

When creating a new table in your MySQL (or any other for that matter) database, it is important that you use the right table type. Using the wrong table, or not optimising it properly can lead to all sorts of performance problems down the line.

When creating tables, if you don't specify a table type, most likely is it will be created as a MyISAM type. For most things this is more than acceptable, however, problems start when a lot of writing back to the table is required.

Recently, I have been trying to optimise a few high traffic sites, the biggest bottleneck I found was the session information that I chose to store in the database. Why the database you may ask. To enable multiple web servers to share session data, I needed a central place to put it, where it could be accessed and kept up to date, no matter which front end web server was handing the request. The database seemed like a logical choice.

MyISAM is a great all round table type, it doesn't have many features but this does benefit it when it comes to performance. However, the biggest problem with MyISAM tables is the fact when they are locked for writing (INSERT or UPDATE) the entire table is locked. For small tables, or low to medium usage tables, this will never cause a problem. But for tables that are read and written constantly it can be problematic.

Enter InnoDB, this table type allows us to perform row level locking, but at a performance cost due to all the extra features InnoDB supports over MyISAM. This is much better for a heavy read and write table, but, in my case there is another solution that is even better.

Memory based tables, as session data doesn't need to be kept for ever, in actual fact, doesn't really need to be kept for more than 15-20 minutes after a user has left the page I can use a memory based table. Memory tables are very like MyISAM tables, in terms of table level locking, but they do have limits on what field types can be used (No BLOB or TEXT type fields), however, for session data this should be fine.

One major downside to Memory based tables is that if I restart MySQL, I will lose the current content of the memory based table, but sessions are constantly re-created anyway so this isn't a problem, I just needed to make my session code able to re-create a timed out session.

Having the much faster access to the session data by using a Memory based table reduced the overall load of the database server, and increased the sites responsiveness.

It just shows, it is very important to make sure you are using the right database table type, and field types when creating your databases.