MEMORY(HEAP) vs. InnoDB in a Read and Write Environment

Question

I want to program a real-time application using MySQL.

It needs a small table (less than 10000 rows) that will be under heavy read (scan) and write (update and some insert/delete) load. I am really speaking of 10000 updates or selects per second. These statements will be executed on only a few (less than 10) open mysql connections.

The table is small and does not contain any data that needs to be stored on disk. So I ask which is faster: InnoDB or MEMORY (HEAP)?

My thoughts are:

1. Both engines will probably serve SELECTs directly from memory, as even InnoDB will cache the whole table. What about the UPDATEs? (innodb_flush_log_at_trx_commit?)

2. My main concern is the locking behavior: InnoDB row lock vs. MEMORY table lock. Will this present the bottleneck in the MEMORY implementation?

Thanks for your thoughts!

Answer 1

If you're really having to have that much concurrent updates, it's almost certain that innodb will perform better, as HEAP tables only have table-level locks, not row-level locks like Innodb.

If you're starting from scratch I would investigate using MySQL 5.5 or Percona's XtraDB as they both contain many scalability improvements over the stock MySQL 5.1.

Answer 2

Thanks!

Of what improvements in MySQL 5.5 are you thinking?

Is it just performing better on the same table definitions, or does the design have to be significantly different. If yes, how do I find out which parts should be implemented differently in 5.5. compared to 5.1? Any directions?

Answer 3

It's not just a question of row locks - InnoDB also has MVCC http://en.wikipedia.org/wiki/Multiversion_concurrency_control so the readers won't even block writers.

But I think your question is missing the all important detail - what sort of data are you storing? If you need to be able to recover post-crash MEMORY is not an option.

If you don't need to recover post crash, then why are you using a database? Why not use something like memcached or redis?

Answer 4

I don't need post crash recovery. The reason why I wnated to use a database server is, because I need to join this table (A) with two other read-only tables (B and C) and perform a complicated query with ORDER BY. Basically I match two rows from table A based on static information they are referencing in tables B and C. I thought thats what SQL and databases are for? its not just a temporary storage. But of course I am open for ideas!

Notes: 1. Tables B and C are 50 MB and can be also stored as MEMORY as they have static information that can be filled at startup. 2. I thought that the constant writes to table A will block the reads, which are basically table scan, if the table only can be locked as a whole (with MEMORY engine)

Thanks for your thoughts!