MySQL Memory Table, Memcached or anything else?

A dataset growing currently >1 million which requires constant lookup / updation of user specific data. lookin开发者_Python百科g for fastest and scalable option with high TPS. Memcache/memcacheddb vs mysql memory tables are a big confusion for implementation and scaling options. Can any one provide proper scaling / tps and performance information for which one to land on?

Does the integrity of this data matter? If it does, you can immediately rule out memcached and MySQL memory tables, as neither one is persisted to durable storage. memcachedb is at least persisted, but it doesn't make the same sorts of reliability guarantees that a normal (R)DBMS would.

If you've got a large dataset, you don't scale it by throwing hardware at it. Since you didn't say what's your growth rate, it's difficult to suggest anything.

If you need to scale writes - you partition your table. If you need to scale reads - you create master > multiple slaves replication cluster.

Also, there's an engine called TokuDB available for MySQL - more info at www.tokutek.com. It's extremely fast for certain things (updates, hot index addition and similar) but not that excellent when it comes to mass updating. It's worth checking out.