[DRBD-user] drbd+mysql+innodb

Felix Frank ff at mpexnet.de
Thu Jun 13 15:15:48 CEST 2013

Note: "permalinks" may not be as permanent as we would like,
direct links of old sources may well be a few messages off.


Hi,

On 06/13/2013 02:59 PM, Robinson, Eric wrote:
> Thanks for the good input, especially from Paul and Dirk. It may help
> people to understand that we have approximately 500 instances of MySQL
> distributed across a dozen servers in 6 pacemaker+drbd clusters. Each
> cluster consists of 2 nodes, with 40 instances of MySQL per node. Each
> node is primary for its own drbd device and replicates to the other
> node. If a node fails, the surviving node becomes primary for both drbd
> devices and temporarily runs all 80 MySQL instances. Running that many
> MySQL instances on one server has never been a significant problem for
> us and performance has been good. However, if we switch to innodb, then
> the disk and memory footprint will change and I don't think the same
> model could be maintained.

you've been doing this with MyISAM? You got lucky then. Your work loads
are likely not very write heavy.

The problem is that with MyISAM, there is no safe crash recovery, and a
drbd secondary is only crash-complete at any given time. If your server
crashes at a bad moment, you loose MyISAM data, on both your nodes.

> Secondly, we backup each mysql instance at night by performing two
> rsyncs of the live running database to a separate archive server.The
> first rsync copies the changes that have occurred during the production
> day. Then we do a 'flush tables with read lock' and perform a second
> rsync, which gets the changes that occurred while the first rsync was in
> progress. The databases are read locked for about 1 second, often less.
> In this manner, we get a 100% binary backup of the databases, but the
> system remains accessible to users 24x7. This is way faster and more
> efficient than use mysqldump. However, again, this technique will
> apparently not work with innodb, for some reason that I still do not
> understand. Apparently there is no 'flush tables with read lock' in
> innodb, or if there is, it still does not work the way it does with
> myisam, which is what I am struggling to understand.

InnoDB streamlines its disk writes quite a bit. Writes get commited to
logfiles first, so the tablespace needs not be rebuilt after every
single transactional commit etc. So I guess as long as the database is
running, there is room for fuzziness among in-memory representation(s)
of the dataset and what's on disk.

Of course, you do gain the ability to take a crash-complete image of the
mysql data, e.g. by severing drbd replication and reading from the
secondary machine.

HTH,
Felix



More information about the drbd-user mailing list