maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

> i don't understand what "only_commits" is, how can it prepare transaction
> "queue up" for group commit
> if only 1 is prepared in parallel ?

Right, so this is an important optimisation, let me try to explain that, and
then I'll answer the other points in a different (shorter) mail for clarity.

Parallel replication in MariaDB works by having the SQL driver thread schedule
each transaction on a pool of worker threads in a round-robin fashion:


   worker1   worker2   worker3   worker4
     T1        T2        T3        T4
     T5        T6        T7        T8
     T9        ...

So in this sense, transactions are always applied in parallel threads when
@@slave_parallel_threads > 0 and @@slave_parallel_mode != none. But one
transaction may need to wait for another at various points to avoid
conflicts. The various modes of parallel replication differ in how they do
these waits.

Considering a transaction to be applied, there are three points of interest
during its replication:

   T:  BEGIN;
       <P1: transaction start>
       INSERT ...
       UPDATE ...
       <P2: just before commit>
       COMMIT;
       <P3: just after commit>

The COMMIT step involves updating the slave GTID position and writing to the
binlog (if --log-slave-updates) as well as the InnoDB commit proper.

So consider now transactions T1 and T2, T1 committed first on the master:

1. If T1 and T2 have different replication domains, and
--slave-parallel-domains is ON, then they are applied in parallel without any
restrictions; T2 does not wait for T1 at any of the points. This is
out-of-order replication, T2 is allowed to commit before T1 on the slave.

2. Otherwise, we have in-order parallel replication, T2 will always commit
after T1 on the slave. Thus, during commit, T2 will wait at its point P3 for
T1 to reach P3 first. This wait is done in a way such that T1 can do a group
commit of both T1 and T2 simultaneously.

3. If we have --slave-parallel-mode=only_commits, then T2 will at point P1
wait for T1 to reach point P2. This means that no query of T2 can run in
parallel with any query of T1. However, all of T2 can run in parallel with the
COMMIT step of T1. In particular, the COMMIT step of T2 can run in parallel
with COMMIT of T1; this is what makes it possible to do group commit in
MariaDB parallel replication, even without running the queries themselves in
parallel.

(If there is an actual row lock conflict between T1 and T2 (maybe they update
the same row), then T2 will wait for T1 inside InnoDB, and group commit will
not be possible. However, as long as T1 has reached its commit step, it is
safe to start T2; T2 will not be able to cause T1 to wait for fail, at most T2
will just have to wait).

4. Consider now --slave-parallel-mode=follow_master_commit. In this case, if
T1 and T2 were group committed together on the master (same commit_id in
GTID), then at point P1 T2 will not wait for T1; T1 and T2 will be allowed to
run in parallel. If we have two different group commits (T1 T2 T3) and (T4 T5)
on the master, then at point P1, T4 and T5 will wait for all of T1, T2, and T3
to reach their point P2. Since T4 and T5 were group-committed together, they
are conflict-free and safe to run in parallel. But we do not know if they
might conflict with T1, T2, or T3, so we make sure those have reached the
COMMIT step first.

5. In --slave-parallel-mode=all_transactions, we relax this even further. T2
will _not_ wait at point P1, even if it has a different commit id from
T1. This is the new optimistic parallel replication mode.

In this case, we might get a conflict, if first T2 modifies a row and then
later T1 needs to modify this row. The result will be a deadlock; T2 gets to
point P3 and waits for T1 to commit, but T1 is inside some query waiting for
T2 to commit and release its row locks. The deadlock is detected, T2 is killed
and rolled back, so that T1 can proceed. We then retry transaction T2. Before
retrying T2, we wait for T1 to reach its point P3 first (the idea is that we
now _know_ that T1 and T2 conflict; so there seems little point in trying to
do them in parallel).

(A conflict can also be detected as a different kind of error, for example
duplicate key violation if T1 deletes a row and T2 inserts the same row. Such
errors are handled the same way).

6. If T2 is not transactional (eg. MyISAM update), then it is not safe to
attempt to run it in parallel with T1. So at point P1 in T2, we wait for T1 to
reach point P3. (It would be enough to wait for all prior transactions to
reach their point P2, but this would be more complex (=costly) to keep track
of, and in optimistic parallel replication we would expect few MyISAM updates,
if any). Note that a following InnoDB T3 could run in parallel with both T1
and T2.

7. If T2 is DDL, then it is not safe to run in parallel with neither T1, nor a
following T3 (for example, T2 might ALTER from InnoDB to MyISAM a table
modified by T1 or T3). In this case, T2 will at point P1 wait for all prior
transactions to reach their point P2. and T3 will likewise at point P1 wait
for T2 to reach its point P2.

-----------------------------------------------------------------------

So those are the details of how the different modes actually work. 

The optimistic parallel replication approach looks really promising, I
think. It has the potential to use _all_ the parallelism available in the
replication stream, also more than what was possible on the master.

There are two main limitations:

(A) Whenever parallel apply is not possible due to a conflict, we run the risk
of having to rollback and retry a transaction. So if this happens too
frequently, the cost of this might outweight the benefit from parallel apply.

(B) We commit in-order. If we have 10 worker threads, this means that we
cannot start T11 before T1 has completed - all the worker threads will be
occupied with the transactions T1, ..., T10. Thus, while some long-running
transaction T1 is being replicated, we can at most do work N transactions
ahead, where N is the value of --slave-parallel-threads.

What I want to achieve is that optimistic parallel replication will work well
out-of-the-box in most cases, _and_ to provide some way for the DBA to tune it
to overcome limitations (A) and (B), in special cases where it will be
needed. This is the motivation for the extra options (that I agree tend to
look a bit out of place).

For example, an application may have a hot-spot like a single row that is
updated by a lot of transactions. Such row could become even more hot on an
optimistic parallel slave, where retries will increase the contention on the
row (limitation (A)). So we can try to detect such hot rows on the master
(--slave-parallel-wait-if-conflict-on-master). And we can allow the
application to explicitly declare such hot-spot updates
(@@SESSION.replicate_expect_conflicts).

Or imagine a common operation like:

   CREATE TABLE t1 ( ... );
   SET SESSION replicate_expect_conflicts = 1;
   INSERT INTO t1 VALUES (1, ...);
   INSERT INTO t1 VALUES (2, ...);
   INSERT INTO t1 VALUES (3, ...);
   ...

All the inserts are very likely to fail if attempted in parallel with the
CREATE TABLE. If this becomes a bottleneck, it seems useful to have a way for
the application to work around it.

For limitation (B), I provide the --slave-parallel-domains options. If a
long-running operation would block the slave for too long, it can be put into
a different replication domain:

   SET SESSION gtid_domain_id=2;
   ALTER large_table ... ;

Then the operation can run freely in parallel with other operations. But then
the application / DBA is responsible for making sure that the operation has
completed on all slaves before attempting any following operations that might
conflict with it.

> I still prefer "auto" as default,

Indeed, I agree, that is where I want to go as well. I guess I'm just being a
bit humble here; this is new stuff, and frankly not well tested in practice
yet. I feel that I need to start with something that has fine-grained control,
to get some real-life experience about what works - and that will make it
easier for me to understand how an "auto" mode should actually be implemented.

(But I do like your earlier suggestion of just creating the "auto" option now
to mean something - and then that can be later refined. I will try to get that
into the first version somehow).

For me, I'm still at the stage where I want to just understand if the
optimistic approach is even semantically correct, before being ready to truly
consider the finer points of tuning. But it should work - and even if we
manage to find some corner cases where it breaks, it seems it should be
possible to handle by forcing serialisation, similar to the DDL case.

MySQL/MariaDB has the property of fully supporting the 'I' in ACID, unlike
some other popular databases - this is what makes statement-based replication
possible. Given some arbitrary transactions that run in parallel, and assume
that they commit in the order T1, T2, T3, ... Then we know that if we were to
run those transaction _serially_, we would end up with the exact same state of
the database, no matter in what order the operations in T1, T2, T3, ... were
originally run.

On the parallel slave, this means that as long as we commit T1, T2, T3, ... in
the same order as was done on the master, we will get the same results - if
the execution of the transactions is successful. If there is a conflict, we
might get a failure or a deadlock, but silently wrong data is not possible. So
this is what should make the optimistic approach correct, as long as we handle
(with rollback and retry) any failures and deadlocks.

Thanks for reading so far ;-)

 - Kristian.