Databases Reference
In-Depth Information
sync_master_info
,
sync_relay_log
, and
sync_relay_log_info
These options, available in MySQL 5.5 and newer, correct longstanding problems
with replicas: they don't sync their status files to disk, so if the server crashes it can
be anyone's guess what the replica's position relative to the master actually was,
and there can be corruption in the relay logs. These options make replicas much
more likely to be recoverable after a crash. They are not enabled by default, because
they cause extra
fsync()
operations on replicas, which can slow them down. We
suggest enabling these options if you have decent hardware, and disabling them if
there is a problem with replication that you can trace to latency caused by
fsync()
.
There's a less intrusive way to do this in Percona Server, enabled with the
innodb_overwrite_relay_log_info
option. This makes InnoDB store the replica-
tion position in the InnoDB transaction logs, which is fully transactional and
doesn't require any extra
fsync()
operations. During crash recovery, InnoDB will
check the replication metadata files and update them to have the correct position
if they're out of date.
Advanced InnoDB Settings
Recall our discussion of InnoDB's history in
Chapter 1
: it was first built in, then avail-
able in two versions, and now the newer version of the engine is once again built into
the server. The newer InnoDB code has more features and is much more scalable. If
you're using MySQL 5.1, you should configure MySQL explicitly to ignore the old
version of InnoDB and use the newer version. It will improve server performance
greatly. You'll need to enable the
ignore_builtin_innodb
option, and then configure
the
plugin_load
option to enable InnoDB as a plugin. Consult the InnoDB manual for
the exact syntax for your platform.
19
Several options are available in the newer version of InnoDB, once you've enabled it.
Some of these are quite important for server performance, and there are also a couple
of safety and sanity options:
innodb
This rather innocuous-looking option is actually very important. If you set its value
to
FORCE
, the server will not be able to start unless InnoDB can start. If you use
InnoDB as your default storage engine, this is definitely what you want. You do
not want the server to start when InnoDB fails because of some error such as a
misconfiguration, because a badly behaved application could then connect to the
server and cause who knows what harm and confusion. It's much better for the
server to fail as a whole, which will force you to look at the error log instead of
believing that the server started okay.
19. In Percona Server, there's only one version of InnoDB and it's built in, so you don't need to disable one
version and load another one to replace it.
