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

Posted by Johannes on Stack Overflow See other posts from Stack Overflow or by Johannes
Published on 2010-05-04T16:53:36Z Indexed on 2010/05/04 21:08 UTC
Read the original article Hit count: 241

Filed under:

mysql

|

memory

|

innodb

|

heap

|

lock

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:

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?)
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!

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about memory

SQL Server and Hyper-V Dynamic Memory Part 2

as seen on SQL Blog - Search for 'SQL Blog'
Part 1 of this series was an introduction and overview of Hyper-V Dynamic Memory. This part looks at SQL Server memory management and how the SQL engine responds to changing OS memory conditions. Part 2: SQL Server Memory Management As with any Windows process, sqlserver.exe has a virtual… >>> More
SQL Server and Hyper-V Dynamic Memory - Part 1

as seen on SQL Blog - Search for 'SQL Blog'
SQL and Dynamic Memory Blog Post Series Hyper-V Dynamic Memory is a new feature in Windows Server 2008 R2 SP1 that allows the memory assigned to guest virtual machines to vary according to demand. Using this feature with SQL Server is supported, but how well does it work in… >>> More
SQL Server and Hyper-V Dynamic Memory - Part 1

as seen on SQL Blog - Search for 'SQL Blog'
SQL and Dynamic Memory Blog Post Series Hyper-V Dynamic Memory is a new feature in Windows Server 2008 R2 SP1 that allows the memory assigned to guest virtual machines to vary according to demand. Using this feature with SQL Server is supported, but how well does it work in… >>> More
Python Memory leak - Solved, but still puzzled

as seen on Stack Overflow - Search for 'Stack Overflow'
Dear everyone, I have successfully debugged my own memory leak problems. However, I have noticed some very strange occurence. for fid, fv in freqDic.iteritems(): outf.write(fid+"\t") #ID for i, term in enumerate(domain): #Vector tfidf = self.tf(term… >>> More
SQL Server and Hyper-V Dynamic Memory Part 3

as seen on SQL Blog - Search for 'SQL Blog'
In parts 1 and 2 of this series we looked at the basics of Hyper-V Dynamic Memory and SQL Server memory management. In this part Serdar looks at configuration guidelines for SQL Server memory management. Part 3: Configuration Guidelines for Hyper-V Dynamic Memory and SQL Server Now that… >>> More