Re: [Maria-developers] On the issue of Seconds_behind_master and Parallel Replication

2015-10-15 Thread Kristian Nielsen 
Kristian Nielsen  writes:

> The same problem actually exists in the non-parallel case. In case of a

Just to elaborate on this, here is the binlog from a transaction with some
artificial long-running queries (using INSERT(SLEEP())):

  #151015  9:43:52 server id 1  end_log_pos 1120   GTID 0-1-5
  BEGIN
  #151015  9:43:44 server id 1  end_log_pos 1210   Query  thread_id=3  
exec_time=0  error_code=0
  insert into t1 values (1,0)
  #151015  9:43:44 server id 1  end_log_pos 1308   Query  thread_id=3  
exec_time=1  error_code=0
  insert into t1 values (2, sleep(1))
  #151015  9:43:45 server id 1  end_log_pos 1406   Query  thread_id=3  
exec_time=5  error_code=0
  insert into t1 values (3, sleep(5))
  #151015  9:43:50 server id 1  end_log_pos 1504   Query  thread_id=3  
exec_time=2  error_code=0
  insert into t1 values (4, sleep(2))
  #151015  9:43:52 server id 1  end_log_pos 1531   Xid = 15
  COMMIT/*!*/;

Suppose the slave starts executing this transaction at 9:44:00. The value of
Seconds_Behind_Master will be set to something like the following as each
event is read from the relay log:

  8
  16
  15
  11
  14
  16

Note in particular how the value jumps back and forth during the execution of
the transaction. The time used on the slave is the time the event starts
executing. The time used on the master is inconsistent with this, it is
sometimes the time the transaction ends (BEGIN), sometimes the time the
query ends. When these two values are subtracted, the result is somewhat
fuzzy if the transaction runs for more than a second.

Clearly, I do not have any great desire to jump through hoops and sacrifice
performance in parallel replication to try and match a behaviour that seems
poorly designed from the start, hence the proposed patch.

(I do not have any strong opinion one way or the other on whether to change
the existing behaviour of Seconds_Behind_Master in the non-parallel case.)

 - Kristian.

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] On the issue of Seconds_behind_master and Parallel Replication

2015-10-15 Thread Ian Gilfillan 
From a user's perspective, I like the idea of introducing the change 
for both parallel and non-parallel in 10.1.


On 15/10/2015 08:16, Kristian Nielsen wrote:

It was brought to my attention an issue with parallel replication and the
Seconds_Behind_Master field of SHOW SLAVE STATUS. I have a possible patch
for this, but I wanted to discuss it on the list, as it changes semantics
compared to the non-parallel case.

Each binlog event contains a timestamp (**) of when the event was created on
the master. Whenever the slave SQL thread reads an event from the relay log,
it updates the value of Seconds_Behind_Master to the difference between the
slave's current time and the event's timestamp.

Now in parallel replication, the SQL thread can read a large number of
events from the relay log and queue them in-memory for the worker threads.
So a small value of Seconds_Behind_Master means only that recent events have
been queued - it might still be a long time before the worker threads have
had time to actually execute all the queued events. Apparently the problem
is (justified) user confusion about this queuing delay not being reflected
in Seconds_Behind_Master.

The same problem actually exists in the non-parallel case. In case of a
large transaction, the Seconds_Behind_Master can be small even though there
is still a large amount of execution time remaining for the transaction to
complete on the slave. However, in the non-parallel case, at most one
transaction can be involved. In the parallel case, the problem is amplified
by the potential of thousands of queued transactions awaiting execution.

So how to solve it? Attached is a patch that implements one possible
solution: the Seconds_Behind_Master is only updated after a transaction
commits, with the timestamp of the commit events. This seems more intuitive
anyway. But it does introduce a semantic difference between the non-parallel
and parallel behaviour for Seconds_Behind_Master. The value will in general
be larger on a parallel slave than on a non-parallel slave, for the same
actual slave lag.

Monty suggested changing the behaviour also for non-parallel mode - letting
Seconds_Behind_Master reflect only events actually committed, not just read
from the relay log. This would introduce an incompatible behaviour for
Seconds_Behind_Master, but could perhaps be done for 10.1, if desired. Doing
it in stable 10.0 would be more drastic.

So any opinions on this?

  - Should Seconds_Behind_Master be changed as per above in parallel
replication (from 10.0 on)?

  - If not, any suggestion for another semantics for Seconds_Behind_Master in
parallel replication?

  - If so, should the change to Seconds_Behind_Master also be done in the
non-parallel case in 10.1? What about 10.0?

  - Any comments on the patch?




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp