Databases Reference
In-Depth Information
Using real tables instead of temporary tables has other benefits, too. For example, it
makes it easier to debug your applications, because you can see the data the applications
are manipulating from another connection. If you used a temporary table, you wouldn't
be able to do that as easily.
Real tables do have some overhead temporary tables don't, however: it's slower to
create them because the
.frm
files associated with these tables must be synced to disk.
You can disable the
sync_frm
option to speed this up, but it's more dangerous.
If you do use temporary tables, you should ensure that the
Slave_open_temp_tables
status variable is
0
before shutting down a replica. If it's not
0
, you're likely to have
problems restarting the replica. The proper procedure is to run
STOP SLAVE
, examine
the variable, and only then shut down the replica. If you examine the variable before
stopping the replica processes, you're risking a race condition.
Not Replicating All Updates
If you misuse
SET SQL_LOG_BIN=0
or don't understand the replication filtering rules,
your replica might not execute some updates that have taken place on the master.
Sometimes you want this for archiving purposes, but it's usually accidental and has bad
consequences.
For example, suppose you have a
replicate_do_db
rule to replicate only the
sakila
database to one of your replicas. If you execute the following commands on the master,
the replica's data will become different from the data on the master:
mysql>
USE test;
mysql>
UPDATE sakila.actor ...
Other types of statements can even cause replication to fail with an error because of
nonreplicated dependencies.
Lock Contention Caused by InnoDB Locking Selects
InnoDB's
SELECT
statements are normally nonlocking, but in certain cases they do ac-
quire locks. In particular,
INSERT ... SELECT
locks all the rows it reads from the source
table by default when using statement-based replication. MySQL needs the locks to
ensure that the statement produces the same result on the replica when it executes
there. In effect, the locks serialize the statement on the master, which matches how the
replica will execute it.
You might encounter lock contention, blocking, and lock wait timeouts because of this
design. One way to alleviate the problems is not to hold a transaction open longer than
needed, so the locks cause less blocking. You can release the locks by committing the
transaction as soon as possible on the master.
It can also help to keep your statements short, by breaking up large statements into
several smaller ones. This is a very effective way to reduce lock contention, and even
