With multi-threaded replication (MTR), a replica can commit
transactions in the same order as the source, or not. This is
determined by sysvar replica_preserve_commit_order
(RPCO). As of MySQL v8.0.27 (released October 2021) it’s ON by
default, but it was OFF by default for several years prior. In
either case, it’s relatively new compared to 20+ years of
single-threaded replication for which commit order was not an
issue or option. But with MTR, it’s important to understand the
affects of RPCO, especially with respect to the focus of this
three-part series: replication lag.
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema binary logs to see why.
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema and binary logs to see why.
Replication being slow—replication lag—is a common complaint, but MySQL replication is actually really fast. Let’s run a controlled experiment and peek inside the Performance Schema and binary logs to see why.
Very often MySQL is much less stable than we realize. In this video I explain how to detect and dianose these MySQL stalls that last for 5-10 seconds or more.
The reason nobody knows about these issues is that they’re:
a) rare
b) intermittent
c) monitoring software can’t catch them
You can use pt-stalk to detect and diagnose such issues. All of the tools I have mentioned in this video are part of Percona Toolkit (no wonder they all start with PT), you can find the toolkit here:
Here’s the main commands I have discussed in this video:
Starting pt-stalk in foreground
# pt-stalk
Starting pt-stalk in background with email notification:
# pt-stalk …[Read more]
Introduction
A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.
Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.
Solutions Suggested By Others
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
DELETE a FROM a INNER JOIN b on a.id=b.id;
DELETE FROM a WHERE id IN (SELECT id FROM b)
The Problem With Suggested Solutions
Solutions above are all fine if the tables are quite small and the …
[Read more]Slave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series – one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:
- Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
- A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a big WHERE clause that doesn't use an index). In this case, the query runs on the master for an hour, which isn't a big problem because it doesn't block other queries. However, when the query moves over to the slaves, all of them start to lag because it plugs up the single …