All geek questions in one place

MEMORY (HEAP) vs. InnoDB in read and write environment

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

He needs a small table (less than 10,000 rows), which will be loaded with a lot of load (scanning) and writing (update and some insertion / deletion). I'm really talking about 10,000 updates or options per second. These statements will only be executed on a few (less than 10) open mysql connections.

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

My thoughts:

  • Both mechanisms are likely to serve SELECT directly from memory, since even InnoDB will cache the entire table. How about UPDATE? (Innodb_flush_log_at_trx_commit?)

  • My main concern is the lock behavior: InnoDB row lock and MEMORY table lock. Will this be a bottleneck in the implementation of MEMORY?

Thanks for your thoughts!

+3
source share
2 answers

If you really need to have such concurrent updates, he is pretty sure that innodb will work better since HEAP tables only have table-level locks and not row-level locks like Innodb.

If you are starting from scratch, I will explore using MySQL 5.5 or Percona XtraDB, as they both contain many scalability improvements over MySQL 5.1.

+5
source

. InnoDB MVCC http://en.wikipedia.org/wiki/Multiversion_concurrency_control, .

, - ? MEMORY, .

, ? - memcached redis?

0

Source: https://habr.com/ru/post/1744022/

More articles:


All Articles