4

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?
2
  • Is your MariaDB config optimal? What does mysqltuner say?
    – mpdonadio
    Jun 10, 2013 at 0:25
  • Yes, the configuration is close to optimal. There is plenty of ram and Mysqltuner does not much to complaint.
    – alfish
    Jun 10, 2013 at 0:41

3 Answers 3

2

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

In favor of MyISAM

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

1

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.

6
  • Would you please elaborate on how to move notification tables to nosql? I have plenty of unused ram and already use memcached, but in a indiscriminate way.
    – alfish
    Jun 10, 2013 at 16:06
  • If all, or at least most, calls to a database are set and receive by key, you can use MemcacheDB - Memcache's fork/sister that uses RAM for operations, but synchronizes with HDD so it's persistent. You write and read as if it was plain memcached. It should be relatively easy to find all db_select and db_merge. If there's only condition on ID, you're good. As I told - looks like module hacking.
    – Mołot
    Jun 10, 2013 at 20:15
  • I'd love to use MemcacheDB, but I'm not a developer. So appreciate any ready-made solution
    – alfish
    Jun 10, 2013 at 23:58
  • @alfish - sad point is, there are no ready made sql to nosql connectors. SQL layer is the expensive part and any connector would need to implement it. Good point is, if there are no joins, php developer should be able to hack it together in about one day, two with testing. For watchdog it'd be easier, there is a backend support using hooks.
    – Mołot
    Jun 11, 2013 at 6:19
  • I inquired memsql and they said ' 256GB subscription based license is $35000 USD'. This takes this option out of my scope. I don't want to bond the future of my database to such an expensive solution :)
    – alfish
    Jun 11, 2013 at 22:38
1

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.

4
  • Does any Drupaler actually use MemSQL? Does it work as a drop-in integration? I had downloaded and tried but had difficulty to import sql dump.
    – alfish
    Jun 11, 2013 at 9:07
  • @alfish - what difficulty, exactly? I played with it a bit (curiosity) and if I remove all ENGINE declarations and intro / outro sections from my dumps it seems to swallow them pretty all right. Did not connected Drupal to it... yet! ;)
    – Mołot
    Jun 11, 2013 at 9:11
  • While tryin to import a drupal dump, I got 'ERROR 1153 (08S01) at line 8747: Got a packet bigger than 'max_allowed_packet' bytes ' error several times. Despite the fact that I adjusted the parameter to be bigger than the dump file's size. Also I see plenty of 'WARN: Feature...unsupported' in memsqlbin/data/memsql_tracelog
    – alfish
    Jun 11, 2013 at 9:22
  • @alfish it looks like your error about max_allowed_bytes comes from client, you need to set both to a common value. I only encountered unsupported features in intro and outro blocks, but that's only a warnings and shouldn't hurt. Again - maybe it was just my luck, I don't have Drupal on it yet.
    – Mołot
    Jun 11, 2013 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.