I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:

  • one MySQL instance will be a producer
  • one MySQL will be read only consumer

On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.

http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html

  • 2
    What is not clear from "Permitting multiple MySQL servers to access a common data directory over NFS is a very bad idea." ? – ypercubeᵀᴹ Apr 24 '14 at 13:26
  • I just want to check if it is technically possible or not, recommendation is other story. specially my application assured write/read will not conflict – HRK Apr 24 '14 at 13:28
  • 1
    I wouldn't even think about it, in all honesty – Philᵀᴹ Apr 24 '14 at 13:42
up vote 1 down vote accepted

What you are asking for is impossible at this time. Why ???

First, take a look at the InnoDB Architecture

InnoDB Architecture

The InnoDB Buffer Pool and the InnoDB Log Buffer are designed to operate on a single system tablespace (a.k.a. ibdata1). I wrote about this back on Oct 03, 2013 : Is there any way to use different InnoDB settings for different databases on the same server?

What you are looking for currently exists in Oracle RAC.

Oracle RAC

Forms a Cluster with these characteristics

  • Each Oracle RAC server runs a local instance of Oracle's RDBMS
  • Each Oracle RAC server has its own Log Buffer
  • Each Oracle RAC server shares access with other Oracle RAC servers
  • All Oracle RAC server connect to the same set of database files

InnoDB

InnoDB Storage Engine for MySQL

  • InnoDB only has one Log Buffer, and only one thread that connects to the Log Buffer
  • InnoDB does not allow multiple Log Buffer threads
  • When you start mysqld (an instance of MySQL) and its InnoDB Storage Engine attempts to connect to ibdata1, the InnoDB Storage Engine has to fail to start up if another Log Buffer thread is already open from another mysqld process.

Epilogue

Please read my earlier post for 2 alternatives. A third alternative would be to switch to MySQL Cluster (using the NDB Storage Engine). That way, you can have one or more Data Nodes shared amongst multiple SQL Nodes.

UPDATE 2014-04-24 16:55 EDT

@ypercube made the following comment

Your linked question (and answer) is about 1 server. This question is about 2 servers that share the same data directory. The two do not seem relevant.

In theory, you could set up multiple instances of mysqld on a single server. However, each mysqld instance must point to a unique system tablespace (ibdata1). in light of this fact, when it comes to multiple mysqld instances, whether they come from one DB server or multiple DB Servers:

  • Only one mysqld instance can attain an exclusive log buffer thread on a system tablespace
  • Only one mysqld instance can attain an exclusive insert buffer thread on a system tablespace

This is why I referred to my Oct 03, 2013 post. I also mentioned the comparison to Oracle RAC.

Your Actual Question

I want to run two separate MySQL instances on two separate machines, but using same data directory over NFS, is it possible with InnoDB Storage Engine?

InnoDB is not designed to have shared log buffers or shared insert buffers. The answer is no. I don't think Oracle will ever change the InnoDB Storage Engine to do this. If they did, many people will drop Oracle RAC and MySQL Cluster in favor of InnoDB for budgetary and/or foolhardy reasons.

  • Your linked question (and answer) is about 1 server. This question is about 2 servers that share the same data directory. The two do not seem relevant. – ypercubeᵀᴹ Apr 24 '14 at 20:20
  • mysql also supports per-table tablespace, what if both servers configured to use per-table tablespace then what will be the scenario ? in per table design, as per my knowledge ibdata1 will no longer store data it just store meta data i guess – HRK Apr 25 '14 at 6:17
  • That's correct. You can place .ibd files on different disks. Even if you did, ibdata1 is still reason multiple mysqld processes cannot access one ibdata1. – RolandoMySQLDBA Apr 25 '14 at 7:30
  • Also, some of the modified pages of a .ibd file are still in the buffer pool at any given moment. So the problem of having two processes read the same .ibd file is similar to the problem of two processes reading the same ibdata1 file. – Bill Karwin Apr 25 '14 at 15:14

I just want to check if it is technically possible or not, recommendation is other story. specially my application assured write/read will not conflict

As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.

Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.

MyISAM doesn't have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you'll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.

But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That's a more common practice, and it works well.

Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.

  • well, replication is always an option and that is what normal practice in most of the scenario, I was just scanning other possibilities and suddenly this idea comes to my mind, so I rushed here to get expert advise. – HRK Apr 25 '14 at 6:22
  • @HRK, no problem, it's worth asking. But the fact is that two mysqld processes reading the same data files doesn't work. – Bill Karwin Apr 25 '14 at 15:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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