MyISAM tables use table-level locking. Based on your traffic estimates, you have close to 200 writes per second. With MyISAM, only one of these could be in progress at any time. You have to make sure that your hardware can keep up with these transaction to avoid being overrun, i.e., a single query can take no more than 5ms.
That suggests to me you would need a storage engine which supports row-level locking, i.e., InnoDB.
On the other hand, it should be fairly trivial to write a few simple scripts to simulate the load with each storage engine, then compare the results.
There are some very well-known differences between Innodb and MyISAM e.g. Innodb supports transactions, MyISAM does not etc. However, I am goint to list some not-so-well-known differences here for my readers. Please feel free to suggest more through comments below. Everything should be true for MySQL server versions 5.1 or before.
That suggests to me you would need a storage engine which supports row-level locking, i.e., InnoDB.
On the other hand, it should be fairly trivial to write a few simple scripts to simulate the load with each storage engine, then compare the results.
There are some very well-known differences between Innodb and MyISAM e.g. Innodb supports transactions, MyISAM does not etc. However, I am goint to list some not-so-well-known differences here for my readers. Please feel free to suggest more through comments below. Everything should be true for MySQL server versions 5.1 or before.
Innodb vs MyISAM:
- Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
- Innodb never fragments short rows.
- Innodb does not have separate index files so they do not have to be opened.
- Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.
- There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
- By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
- MyISAM requires a file descriptor open for each client for the same table data file. Index file descriptors are shared b/w clients on the other hand. This causes a high usage of open files and causes the database to reach the open files limit allowed for the process (ulimit for Linux external to MySQL or open_files_limit variable inside MySQL).
- MyISAM can possibly cause information_schema to respond way too late (not confirmed through Innodb yet).
- For MyISAM tables, table header has to be modified each time the table is opened. (
You must be registered for see links)
- In some cases, MyISAM supports building idexes by sorting (after ALTERs).
- MyISAM databases can be read from readonly media eg. CD.