Hi Kristian,

Thank you for your quick response. Please see my answers inline.
Kristian Nielsen
29 Jun 2015 11:18

So if I understand correctly, what is compared here is the value of some
InnoDB statistics between two MySQL 5.6 servers each running a single
replication SQL thread, and a MariaDB 10.0 server running two replication
SQL threads (multi-source replication).

I do not have much experience with interpreting InnoDB mutex wait
statistics, hopefully some with more experience on this can contribute. But
it does seem somewhat expected that a server with two threads has a much
higher potential for mutex contention (mutex rounds and os waits) than a
server using only a single thread, right?
Yes, that's correct. Two Oracle MySQL 5.6 slave with log_slave_updates serve as the sources of the "merged" slave running MariaDB. I would expect some higher contention I was surprised by the multiplied factor since the two thread are running hypothetically isolated on different databases on the same servers so other than some global mutexes shouldn't be any contention on the threads.

Please correct me if I'm wrong but if I understand it correctly the spin waits indicate that roughly the expected number of lockings happens on the target slave (source1 + source2). Order of magnitude higher mutex rounds means it took longer to acquire those locks.

Did you try comparing the numbers when only one thread is running on the
MariaDB slave (eg. stopping first one of the multisource connections, then
the other) ?
Yes. We let it run for a while stopped one thread and the other. The graphs below show the status when we stopped one source completely (the server itself). The results roughly the same. The spin waits are aligned (sometimes even lower) while the mutex round and os waits are multiplied.



Did you try comparing the configurations of the three servers for any
relevant differences?
The server configurations were the same except of course MariaDB specific things driven by our puppet configuration.

What are the corresponding statistics on the original masters generating the
load?
I can't really compare the master metrics because the write happens parallel therefore the spin waits are much higher although the OS waits seems roughly equally high like what we experienced on the slave.
Did you try to determine which individual mutexes are mostly contributing to
the differences (just total number of mutex waits is a somewhat crude
statistics which might be hard to interpret)?
Yes, I did. We don't track that so I cannot look it back. The server are running different test now but I can set it up again and see. If I remember correctly nothing really stood out.
Do you have any indication that these differences are causing problems with
performance, or are you just curious to understand them?
Not really. We saw it being a bit slower in replication but in spite of the multiple runs there were no statistically significant difference. We had the sources get behind in replication with 60-3600 seconds and start replication from there. The target was keeping up with both upstreams with only 1-2 seconds delay.

We were only stressing the slave with the upstream slaves catching up. Do you think it would worth to setup two true master and test with natural writes on them and see how they replicate to the multi-sourced slave?

Best regards,
--
Károly Nagy
System engineer

Booking.com BV
Rembrandt Square Office,
Herengracht 597, 1017 CE Amsterdam
Direct +31 (0)20 715 8403
Karoly Nagy
29 Jun 2015 10:51
Dear MariaDB developers,

I'm Károly Nagy working for Booking.com currently testing multi-source replication functionality of MariaDB. Kristian suggested I should reach out to you on this mailing list regarding my questions.

We're seeing very high and fluctuating mutex contentions while replicating from two sources (Oracle MySQL 5.6) to a single MariaDB slave. You can see that on the graphs below. The spin waits are relatively [1] aligned but the mutex rounds [2] are 5-10 times higher than it is on the two sources combined together and not consistent. The sources have a relatively constant pattern while the target has dips around 2.5k and spikes up to 8k. The os waits are in completely different order of magnitude [3].
 
The scenario where values were captured:
Is this behavior expected?

Could you give us some insights on why we could see these results?

If there is any more information you need please let me know. Thank you for your help in advance!

Every metric is on /10 seconds basis.

[1] Mutex spin waits



[2] Mutex rounds



[3] Mutex OS waits




Best regards,
_______________________________________________
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
Karoly Nagy
5 Jun 2015 16:10
Dear MariaDB developers,

I'm Károly Nagy working for Booking.com currently testing multi-source replication functionality of MariaDB. Kristian suggested I should reach out to you on this mailing list regarding my questions.

We're seeing very high and fluctuating mutex contentions while replicating from two sources (Oracle MySQL 5.6) to a single MariaDB slave. You can see that on the graphs below. The spin waits are relatively [1] aligned but the mutex rounds [2] are 5-10 times higher than it is on the two sources combined together and not consistent. The sources have a relatively constant pattern while the target has dips around 2.5k and spikes up to 8k. The os waits are in completely different order of magnitude [3].
 
The scenario where values were captured:
Is this behavior expected?

Could you give us some insights on why we could see these results?

If there is any more information you need please let me know. Thank you for your help in advance!

Every metric is on /10 seconds basis.

[1] Mutex spin waits



[2] Mutex rounds



[3] Mutex OS waits




Best regards,