Databases Reference
In-Depth Information
mysql>
SELECT NOW_USEC()
+----------------------------+
| NOW_USEC() |
+----------------------------+
| 2007-10-23 10:41:10.743917 |
+----------------------------+
This lets us measure replication speed by inserting the value of
NOW_USEC()
into a table
on the master, then comparing it to the value on the replica.
We measured the delay by setting up two instances of MySQL on the same server to
avoid inaccuracies caused by the clock. We configured one instance as a replica of the
other, then ran the following queries on the master instance:
mysql>
CREATE TABLE test.lag_test(
->
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->
now_usec VARCHAR(26) NOT NULL
->
);
mysql>
INSERT INTO test.lag_test(now_usec) VALUES( NOW_USEC() );
We used a
VARCHAR
column because MySQL's built-in time types can't store times with
subsecond resolution (although some of its time functions can do subsecond calcula-
tions). All that remained was to compare the difference between the replica and the
master. We decided to use a Federated table to help.
24
On the replica, we ran:
mysql>
CREATE TABLE test.master_val (
->
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->
now_usec VARCHAR(26) NOT NULL
->
) ENGINE=FEDERATED
->
CONNECTION='mysql://user:pass@127.0.0.1/test/lag_test',;
A simple join and the
TIMESTAMPDIFF()
function show the microseconds of lag between
the time the query executed on the master and on the replica:
mysql>
SELECT m.id, TIMESTAMPDIFF(FRAC_SECOND, m.now_usec, s.now_usec) AS usec_lag
->
FROM test.lag_test as s
->
INNER JOIN test.master_val AS m USING(id);
+----+----------+
| id | usec_lag |
+----+----------+
| 1 | 476 |
+----+----------+
We inserted 1,000 rows into the master with a Perl script, with a 10-millisecond delay
between row insertions to prevent the master and replica instances from fighting each
other for CPU time. We then built a temporary table containing the lag of each event:
mysql>
CREATE TABLE test.lag AS
>
SELECT TIMESTAMPDIFF(FRAC_SECOND, m.now_usec, s.now_usec) AS lag
->
FROM test.master_val AS m
->
INNER JOIN test.lag_test as s USING(id);
Next, we grouped the results by lag time to see what the most frequent lag times were:
24. By the way, this is the only time that some of the authors have used the Federated engine.
