Databases Reference
In-Depth Information
I/O of the data. But unless you have many hard drives (20 or so), or flash storage, you
should think carefully before doing this.
The real advantage of separating the binary log and data files is the reduced likelihood
of losing both your data and your log files in the event of a crash. Separating them is
good practice if you don't have a battery-backed write cache on your RAID controller.
But if you have a battery backup unit, a separate volume isn't needed as often as you
might think. Performance is rarely a determining factor. This is because even though
there are lots of writes to transaction logs, most of them are small. As a result, the RAID
cache will usually merge the requests together, and you'll typically get just a couple of
sequential physical write requests per second. This usually won't interfere with the
random I/O to your data files, unless you're really saturating the RAID controller over-
all. The general logs, which have sequential asynchronous writes and low load, can also
share a volume with the data comfortably.
There's another way to look at it, though, which a lot of people don't consider. Does
placing logs on separate volumes improve performance? Typically, yes—but is it worth
it? The answer is frequently no.
Here's why: it's
expensive
to dedicate hard drives to transaction logs. Suppose you have
six hard drives. The obvious choices are to place all six into one RAID volume, or split
them into four for the data and two for the transaction logs. If you do this, though,
you've reduced the number of drives available for the data files by a third, which is a
significant decrease; also, you're dedicating two drives to a possibly trivial workload
(assuming that your RAID controller has a battery-backed write cache).
On the other hand, if you have many hard drives, dedicating some to the transaction
logs is proportionately less expensive and can be beneficial. If you have a total of 30
hard drives, for example, you can ensure that the log writes are as fast as possible by
dedicating 2 drives (configured as a RAID 1 volume) to the logs. For extra performance,
you might also dedicate some write cache space for this RAID volume in the RAID
controller.
Cost effectiveness isn't the only consideration. Another reason why you might want to
keep InnoDB data and transaction logs on the same volume is that this strategy lets you
use LVM snapshots for lock-free backups. Some filesystems allow consistent
multivolume snapshots, and for those filesystems it might not be a big deal, but it's
something to keep in mind for ext3. (You can also use Percona XtraBackup for lock-
free backups; see
Chapter 15
for more on this topic.)
If you have enabled
sync_binlog
, binary logs are similar to transaction logs in terms of
performance. However, it's actually a
good
idea to store binary logs on a different vol-
ume from your data—it's safer to have them stored separately, so they can survive even
if the data is lost. That way, you can use them for point-in-time recovery. This consid-
eration doesn't apply to the InnoDB transaction logs, because they're useless without
the data files; you can't apply transaction logs to last night's backup. (This distinction
