Mysql InnoDb over a raw device in an Active/Passive topology

Question

We have an Active/Passive topology where there are two x86 complexes with a shared raw storage, where only one of the nodes in a given moment has an access to the shared storage (AKA the active node). In case of a a failover in the active node, the passive node initiates a take over and becomes the active node with an access to the shared storage. Each node has its own boot device storage with a filesystem, however the shared storage cannot have a filesystem mounted on it.

We are interested in installing Mysql server on both nodes, where its data resides in the shared storage and only the active node is running the server.

Mysql with InnoDb is capable of running on a raw device, and there is also a guide on how to run Mysql over a cluster similar to our topology. However, in the second example, they do have a filesystem mounted on the shared storage. The filesystem issue raises a major concern:

ib_logfile* still require a file system. So the raw mysql feature is not exactly fully raw. Please correct me if I'm mistaken. Is there a workaround to store those files in the raw storage? We can, however, save the ib_logfiles in the node's boot device and always delete those files before the server is starting, however, this might lead to uncommitted transaction to be partially committed in case of a failure in a middle of a transaction, thus contradicting the whole idea of transactions.

Are there any more files/features that might affect the behaviour of mysql in this topology?

Answer 1

Every installation of mysql compose of 2 directories. 1. Application directory 2. Data directory. The data directory contains all the data of the db. It contains the data files and the log files. The data directory can be on the shared storage and application directory on the local servers, and when you want to switch from active to passive you shutdown(if it didn't crush) the active and start the passive server with permission on the shared storage. Because the log files are in the shared storage the new active server will recover lost transaction. Keep in mind that in this topology the passive server is down, and only when you switch it will be started.

Answer 2

You probably won't like this answer, but here goes...

Don't try to do what you described.

You are protecting against only one thing -- server failure (other than the disk). Meanwhile, disk failure, network failure, earthquake, flood, etc, can destroy the system. Why bother protecting against only one failure scenario?

Back to the tentative plan... If the active server dies, you must prevent it from writing anything more to MySQL's files. Only then can the passive mysqld be turned on. It will (assuming you are using InnoDB) recover what it can from disk. Keep in mind that things on the active were probably waiting to be flushed from RAM.

Or is your goal that a "raw" device is somehow 'better'?

"Raw" is no longer useful. Decades ago, it was a notion that worked well. Today's drives and controllers have virtually eliminated the usefulness of "raw".

Simply "mount" the filesystem and have mysql's files in places that each server know about.

Data and indexes in InnoDB are in 16KB blocks, somewhat organized into 1MB "extents". But as you modify tables, the data gets scattered, thereby eliminating any advantage that "raw" used to provide.

OK, it is true that all the log files (iblog, binlog, slowlog, relaylog, generallog, etc) benefit somewhat from 'serial' access to disk. But if the OS allocates such files somewhat consecutively, then you get the performance of "raw" even on a "filesystem".

InnoDB does not know how to address a raw device.

InnoDB uses RAM heavily to avoid I/O.

If you want HA (High Availability), I recommend Galera, either in MariaDB or PXC.