Databases Reference
In-Depth Information
single statement if
autocommit
is set, and otherwise a transaction). It's rare to set this
option to anything other than
0
or
1
.
If you don't set
sync_binlog
to
1
, it's likely that a crash will cause your binary log to be
out of sync with your transactional data. This can easily break replication and make
point-in-time recovery impossible. However, the safety provided by setting this option
to
1
comes at high price. Synchronizing the binary log and the transaction log requires
MySQL to flush two files in two distinct locations. This might require a disk seek, which
is relatively slow.
As with the InnoDB log file, placing the binary log on a RAID volume with a battery-
backed write cache can give a huge performance boost. In fact, writing and flushing
the binary logs is actually more expensive than writing and flushing the InnoDB trans-
action logs, because unlike the InnoDB transaction logs, every write to the binary logs
increases their size. That requires a metadata update at the filesystem level for every
write. Thus, setting
sync_binlog=1
can be much more detrimental to performance
than setting
innodb_flush_log_at_trx_commit=1
, especially on network filesystems such
as NFS.
A non-performance-related note on the binary logs: if you want to use the
expire_
logs_days
option to remove old binary logs automatically, don't remove them with
rm
. The server will get confused and refuse to remove them automatically, and
PURGE
MASTER LOGS
will stop working. The solution, should you find yourself entangled in this
situation, is to manually resync the
hostname-bin.index
file with the list of files that still
exist on disk.
We cover RAID in more depth in the next chapter, but it's worth repeating here that
good-quality RAID controllers, with battery-backed write caches set to use the write-
back policy, can handle
thousands
of writes per second and still give you durable stor-
age. The data gets written to a fast cache with a battery, so it will survive even if the
system loses power. When the power comes back, the RAID controller will write
the data from the cache to the disk before making the disk available for use. Thus, a
good RAID controller with a large enough battery-backed write cache can improve
performance dramatically and is a very good investment. Of course, solid-state storage
is another option; we also cover that in the next chapter.
MyISAM I/O Configuration
Let's begin by considering how MyISAM performs I/O for its indexes. MyISAM nor-
mally flushes index changes to disk after every write. If you're going to make many
modifications to a table, however, it might be faster to batch these writes together.
One way to do this is with
LOCK TABLES
, which defers writes until you unlock the tables.
This can be a valuable technique for improving performance, because it lets you control
exactly which writes are deferred and when the writes are flushed to disk. You can defer
writes for precisely the statements you want.
