Databases Reference
In-Depth Information
These settings are unsafe and incompatible with replication. Replication requires bi-
nary logging and XA support, and in addition—to be as safe as possible—you need
sync_binlog
set to
1
, so the storage engine and the binary log are synchronized. (The
XA support is worthless otherwise, because the binary log might not be “committed”
to disk.) This is one of the reasons we strongly recommend using a RAID controller
with a battery-backed write cache: the cache can speed up the extra
fsync()
calls and
restore performance.
The next chapter goes into more detail on how to configure transaction logging and
binary logging.
External XA Transactions
MySQL can participate in, but not manage, external distributed transactions. It doesn't
support the full XA specification. For example, the XA specification allows connections
to be joined in a single transaction, but that's not possible in MySQL at this time.
External XA transactions are even more expensive than internal ones, due to the added
latency and the greater likelihood of a participant failing. Using XA over a WAN, or
even over the Internet, is a common trap because of unpredictable network perfor-
mance. It's generally best to avoid XA transactions when there's an unpredictable
component, such as a slow network or a user who might not click the “Save” button
for a long time. Anything that delays the commit has a heavy cost, because it's causing
delays not just on one system, but potentially on many.
You can design high-performance distributed transactions in other ways, though. For
instance, you can insert and queue data locally, then distribute it atomically in a much
smaller, faster transaction. You can also use MySQL replication to ship data from one
place to another. We've found that some applications that use distributed transactions
really don't need to use them at all.
That said, XA transactions can be a useful way to synchronize data between servers.
This method works well when you can't use replication for some reason, or when the
updates are not performance-critical.
The MySQL Query Cache
Many database products can cache query execution plans, so the server can skip the
SQL parsing and optimization stages for repeated queries. MySQL can do this in some
circumstances, but it also has a different type of cache (known as the
query cache
) that
stores
complete result sets
for
SELECT
statements. This section focuses on that cache.
The MySQL query cache holds the exact bits that a completed query returned to the
client. When a query cache hit occurs, the server can simply return the stored results
immediately, skipping the parsing, optimization, and execution steps.
