Should I choose MyISAM for notification tables?

Question

Using Drupal6 with a MariaDB 10 InnoDB engine for all tables, I have noticed that insert queries into notifications_* tables are very inefficient on InnoDB tables. For example, these frequently in my slow_query_log:

INSERT INTO notifications_event (module, type, action, oid, uid, params, created, counter)
VALUES ('XXX', 'XXX', 'XXX', XXX, XXX, 'XXX', XXX, XXX);

INSERT INTO notifications_fields (sid, field, value, intval)
VALUES(XXX, 'XXX', 'XXX', XXX);

QUESTIONS

• Would be a good idea to convert such tables into MyISAM?
• Any downside for using such mix of storage engines?
• Is there a better solution to shorten the insert time?

Answer 1

DISCLAIMER : Pure MySQL Perspective

IMHO, you should leave it as InnoDB. MyISAM is not good in high-write situations. Why ?

LOCKING GRANULARITY

Each time you issue an INSERT, UPDATE, or DELETE query on a MyISAM table

• The query performs a full table lock
• All other queries needing to do the INSERT, UPDATE, or DELETE queries are handled on a first-come first-serve basis

InnoDB has row-level locking. In order to accommodate this, InnoDB caches INSERTs, UPDATEs, and DELETEs in the Buffer Pool, Log buffer, and Undo Logs.

DATA CACHING

MyISAM caches index pages from each MyISAM table's .MYI file in the key buffer.

MyISAM does not cache data. It has to piggyback from the OS for that. You can change the row format of the MyISAM to increase read speed from disk at the expense of double the table's disk usage. Write speed may be adjusted with concurrent INSERTs.

InnoDB caches data and indexes pages in the InnoDB Buffer Pool. You may need a decent size to accommodate data and indexes.

EPILOGUE

You just have to tune some vital settings. Here are some of my past suggestions from the DBA StackExchange.

In favor of InnoDB

• Jan 11, 2013 : Very poor insert performance MySQL / Percona Server
• Dec 06, 2012 : InnoDB insertion faster
• Aug 03, 2012 : Slow insert performance in mysql innodb table for solr indexing
• Jul 04, 2012 : Capabilities of InnoDB INSERT Performance

In favor of MyISAM

• May 03, 2012 : Which is faster, InnoDB or MyISAM?
• Jan 03, 2012 : Optimized my.cnf for high-end and busy server
• Sep 20, 2011 : Best of MyISAM and InnoDB
• Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Although I lean towards InnoDB, I will leave it to you to decide which storage engine you want to use and tune for.

Answer 2

Using SQL database for everything is well known Drupal problem. A lot of data, like notifications and watchdog, does not need SQL anyway, they are just a key-value pairs.

InnoDB uses a much more sophisticated locking strategy. It's perfect for tables you often read and write at the same time, but slower for any single task. MyISAM is simpler, and way faster if you only add occasional and seldom try to read at the same time. I.e. you write to it at night and read it all day.

Joins between different engines are slow, keep that in mind.

If feasible, move Notifications' table to nosql solution. RAM-based if you have a plenty of RAM, just mirrored to disk. It might need hacking the very module, though, so it might end up next to impossible.

Answer 3

You say you have plenty of RAM. If you feel like risking a bit, try MemSQL. It removes main bottleneck - HDD - from the equation. It also claims to be lock-free and optimized for frequent inserts.

And it's developers says transitioning from MySQL is easy as it should be drop-in API-compatible replacement.

Idea taken from syedrakib's question.