database - Mysql storage engine for log table

Question

What is the best storage engine to use for a log table on mysql?

The table will have a couple if id fields (ints) a date field, a varchar for the message level and a text field with the message.

It will log all sort of things, user actions, system events etc.. It is expected to grow huge quickly.

Records in it will never be modified and rarely accessed. When accessed the should be sortable by date level and id.

An interesting option is the archive engine. It meets all criteria but has one big drawback, not only can't records be deleted, the table can't even be purged. The only way to clear the table is to delete it and recreate it.

Any suggestions?

Answer 1

Well, according to your question, InnoDB should do the work because:

1. It's scalability is a lot better than MyISAM
2. It's row-locking, therefore if you are going to have more writes than selects, it fits better.
3. Finally, since you said that they will be rarely accessed, there is no need for MyISAM since it is better on select queries.

Check this for more information

EDIT

Well, in the comment you ask about other engines. Here is a full list of engines. Among them, as you said archive has a disadvantage, the others do not fit to your request. Here is a quotation from MySQL website:

InnoDB has been designed for maximum performance when processing large data volumes. 
Its CPU efficiency is probably not matched by any other disk-based relational database
engine.

So basically:

1. If you're going to use MEMORY don't as you said you won't access data a lot and your table will grow too much. You will need a lot of RAM for that and when you reboot all data will be lost.
2. If you're going to use MyISAM don't as it is designed for tables which select queries are more frequent than insert and update.
3. As for archive, that's your choice. Here is a comparison between MyISAM and archive for a log table. I would stick to InnoDB though.
4. I won't even mention Merge, Blackhole, Example and other engines. (I don't have much knowledge on CSV engine but as far as I've read, that is a not an approriate engine for this kind of table.

To be honest, I used to spend a lot of time before making an important move in coding. I researched for hours, maybe for days about an issue to see which way is the most appropriate. I'll tell you what, researching is good, but after a point if it prevents you from working then you should stop, drink a coffee, and make your choice right away. Therefore, just try the most appropriate one to you and as you will experience you will find even a better way, by trying yourself. I mean, I don't believe that Facebook was designed for such a volume, but as it grew, they continued to change the structure accordingly. That's what I believe though, may not be the reality :) Anyways, hope that info helps you.

EDIT 2013

Below you'll find brief descriptions for built-in MySQL storage engines.

MyISAM

These tables include additional optimizations, such as advanced cache and indexing mechanisms, which provide fast access to data. Using table-level locking, the MyISAM storage engine provides for concurrent operations. When read performance is a concern, generally, MyISAM is the choice.

Memory

Also called heap tables, memory tables are ideal for fast retrievel of frequently used data that is rarely altered (such as country codes, zip codes or other lookup tables). As the name suggests, data is stored in memory and hence access is much faster than data stored in disks. One significant restriction for using memory is that data is valid during MySQL session. When it crashes, or shuts down data is lost.

InnoDB

One case where you will have to use this storage engine is when you need to use foreign keys or transactions. InnoDB is more concurrent than MyISAM since it provides row-level locking. The storage engine is highly reliable. Another case when you will want to use this storage is when you have more writes than reads. When you frequently write data into the table try using this storage is it is more concurrent than MyISAM.

Archive

It is designed for storing large amounts of data in a compressed format. One of the use cases of this storage engine is to store archival or historical data or security logs. The table uses no indexes so for daily data retrieval and storage it is not a good choice. It's row-level locking and data is uncompressed on the fly when demanded. Moreover, altering the table is not possible.

Merge

Merge is used to 'merge' partitioned tables which reside on the same machine. When you split a large table into several smaller tables, and access them simultaneously using a merge-table, the biggest benefit is its speed. Searches and sorts will execure quicker since there is less data in tables.