MEMORY Table is slower than INNODB - Bug or Feature?

I tried to tune an application which uses MySQL to store sessions for multible webservers.

The session table is small about 100 bytes per row.
The session table has lost of insert/deletes and updates.
And updates are mostly done to update "last-page click timestamps".

This setup creates of course a high IO write load for the MySQL server for data which is realtive unimportant and could be kept volatile.

I though "thats easy to fix" and converted the table from type InnoDB to type Memory.

This did remove the disk IO of the table. Nice!

But here comes the gotcha.
The MEMORY table showed up to be significant slower than InnoDB.

I've recorded live SQL-access statements to the table (some million lines) and played these statements several times in parallel (16 threads) against the table.

The Innodb needed 51 secs for this testcase.
But the Memory table needs 130 seconds.

There is 1 key on the table (a unique key on a session md5)
I tried both types of Memory key-types (Hash and INDEX)
The timing for the BTREE is slightly faster 125 seconds.
But in both cases the memory table is significant slower than Innodb.

Isn't this strange?

Everybody would expect that a memory table would have so much less overhead that I should be 10 times faster than Innodb.

This is clear. )
The table does convert to memory very nicely.
I can see that its a memory table afterwards.
And as I mentioned disk IO goes down to zero too.

The problem seems to be a serious bottleneck of the ISAM/MEMORY handler.

Some other details:
The used MySQL version is 5.1.38 with innodb-plugin 1.0.4
So basicly its last month MySQL server version.

The Queries going to the table are only
simple Select (by primary key)
simple Deletes
and simple updates

But lots of these types of queries, as the DB is used as session backend.

The table is a slightly sized tuned version of the original TYPO3 sessions table.
The application is a typical TYPO3 application.
Typo3 per default stores its sessions in a database table.
The original Typo3 engine uses also a blob column but we have dropped it, as this column is mostly used by legacy stuff and not needed by us.

Using this table for session management is normal for typo3.
There are many HUGE Typo based webapplication which run this table structure.
It ugly to see that this issues affects the whole world of them. (

Comment

This could be due to locking granularity, since MEMORY and MyISAM will be locking at the table level and InnoDB will lock at the row level. If this is the case, reducing the number of threads in your test should bring the numbers for all storage engines closer together, and increasing the threads should move them farther apart (though it should flatten out at some point).

If you don't care about persistence of your session data, memcached will outperform MySQL hands down for this type of workload. If you do care about persistence, there's Tokyo Tyrant/Tokyo Cabinet that was written about recently in the Percona blog that may be worth looking into.