Semi-synchronous replication status in all MySQL production clusters
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Volans
	Apr 4 2016, 10:15 AM

Project Tags

Referenced Files

None

Subscribers

Description

Audit current semi-synchronous replication configuration and status in all MySQL clusters in production.

Related Objects

Mentioned In: T161007: Decouple Mariadb semi-sync replication from $::mw_primary
T132428: db1038 low on available space

Event Timeline

Volans created this task.Apr 4 2016, 10:15 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 4 2016, 10:15 AM

Documentation for the semi-synchronous replication plugin system variables:
https://mariadb.com/kb/en/mariadb/semisynchronous-replication/#system-variables

Documentation for the semi-synchronous replication plugin status variables:
https://mariadb.com/kb/en/mariadb/semisynchronous-replication-plugin-status-variables/

Settings

Timeout

Looks like the timeout for rpl_semi_sync_master_timeout is setup very small in S1 and S3, given that the unit is in milliseconds, while the other shards (S*) use the default value of 10000.

As a result the masters of S1 and S3 are turning off semi-sync replication going back to async very often (10 times per minute on S1, 10 times per second on S3 during the short period analyzed).

I think we should set rpl_semi_sync_master_timeout to a value based on the average wait time in Rpl_semi_sync_master_tx_avg_wait_time (that is in microseconds) for each shard and in any case to a value lower enough that is still acceptable for a transaction to wait that time while committing.

Given that we have only the average wait time is hard to guess a value that will cover most some 90th or 99th percentile.
The approach should be in any case to modify the value and monitor the delta on Rpl_semi_sync_master_no_times and Rpl_semi_sync_master_no_tx to ensure the value is appropriate and of course all the metrics on the query side.
For the ones at 10000 going down to 1000, 100, and potentially ~10 if it doesn't increase too much the Rpl_semi_sync_master_no_times
For the ones at 1 going up to ~10 and see how much it reduce the increase over time of Rpl_semi_sync_master_no_times

How many slaves

Each shard has a very different number of slaves with semi-synchronous replication enabled. We should decide a policy to uniform this.

I can see two general approaches:

Have semi-sync enabled only on 2~3 slaves per shard, on servers with similar specs of the masters that could replace it if needed.
- PRO: run only on _potential masters_; easier to find the best candidate in case of failover; doesn't affect the big slaves that will get most of the traffic;
- CON: more complex to enable (manually, tag/flag on Puppet, etc.)

All slaves have semi-sync enabled:
- PRO: easier to setup, could be done directly in Puppet
- CON: more servers to check to find the most up-to-date in case is necessary; could affect a bit the performances of the big new slaves that will get most of the traffic

EQIAD current status

`S1`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1     | -- very low value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         | Value registered after ~30m, if different, with change rate
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 8             | -- 8 slaves
| Rpl_semi_sync_master_net_avg_wait_time     | 300           |
| Rpl_semi_sync_master_net_wait_time         | 4502460095806 | 4503033662156 (295196/s)
| Rpl_semi_sync_master_net_waits             | 14975544325   | 14977683612 (1101/s)
| Rpl_semi_sync_master_no_times              | 29773904      | 29774251 (10.7/m)
| Rpl_semi_sync_master_no_tx                 | 42863661      | 42864283 (19.2/m)
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 518           |
| Rpl_semi_sync_master_tx_wait_time          | 1855951739105 | 1856083931664 (68035/s)
| Rpl_semi_sync_master_tx_waits              | 3582806860    | 3583144224 (174/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 18896415      | 18898569 (1.1/s)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 3375033145    | 3375352931 (164.6/s)
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
15 rows in set (0.00 sec)

`S2`

MariaDB PRODUCTION s2 localhost (none) > show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value | Value registered after ~30m, if different, with change rate
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 | -- default value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | OFF   | -- not a semi-sync slave of codfw master
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
7 rows in set (0.00 sec)

MariaDB PRODUCTION s2 localhost (none) > SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         |
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 3             | - 3 slaves
| Rpl_semi_sync_master_net_avg_wait_time     | 310           |
| Rpl_semi_sync_master_net_wait_time         | 500797957369  | 500943726452 (72234/s)
| Rpl_semi_sync_master_net_waits             | 1613723063    | 1614190261 (232/s)
| Rpl_semi_sync_master_no_times              | 1             |
| Rpl_semi_sync_master_no_tx                 | 16444988      | -- not changed, probably old offset
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1497          |
| Rpl_semi_sync_master_tx_wait_time          | 1165223192619 | 1165388706819 (82019/s)
| Rpl_semi_sync_master_tx_waits              | 777920348     | 778140942 (109/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 6504498       | 6504732 (7.0/m)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 640976114     | 641179427 (6045/s)
| Rpl_semi_sync_slave_status                 | OFF           | -- not a semi-sync slave of codfw master
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
16 rows in set (0.00 sec)

`S3`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1     | -- very low value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         | Value registered after ~30m, if different, with change rate
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 1             | -- only 1 slave
| Rpl_semi_sync_master_net_avg_wait_time     | 318           |
| Rpl_semi_sync_master_net_wait_time         | 1726378837758 | 1726422544263 (21701/s)
| Rpl_semi_sync_master_net_waits             | 5422125643    | 5422279585 (76.4/s)
| Rpl_semi_sync_master_no_times              | 92771417      | 92791048 (9.7/s)
| Rpl_semi_sync_master_no_tx                 | 1403209539    | 1403239746 (15.0/s)
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 530           |
| Rpl_semi_sync_master_tx_wait_time          | 1122082887611 | 1122182996954 (49707/s)
| Rpl_semi_sync_master_tx_waits              | 2116437992    | 2116579942 (70.5/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 61862997      | 61869525 (3.2/s)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 2060650255    | 2060782862 (65.8/s)
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
15 rows in set (0.00 sec)

`S4`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 | -- default value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         | Value registered after ~30m, if different, with change rate
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 2             | -- only 2 slaves
| Rpl_semi_sync_master_net_avg_wait_time     | 350           |
| Rpl_semi_sync_master_net_wait_time         | 1911247721935 | 1911355042974 (54121/s)
| Rpl_semi_sync_master_net_waits             | 5450290214    | 5450681796 (197.5/s)
| Rpl_semi_sync_master_no_times              | 0             |
| Rpl_semi_sync_master_no_tx                 | 0             |
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 890           |
| Rpl_semi_sync_master_tx_wait_time          | 2363452913292 | 2363620354363 (84438/s)
| Rpl_semi_sync_master_tx_waits              | 2655356039    | 2655574777 (110.3/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 15045879      | 15046896 (0.51/s)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 2467744134    | 2467948560 (103.1/s)
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
15 rows in set (0.00 sec)

`S5`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 | -- default value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+----------------+
| Variable_name                              | Value          | Value registered after ~30m, if different, with change rate
+--------------------------------------------+----------------+
| Rpl_semi_sync_master_clients               | 5              | -- 5 slaves
| Rpl_semi_sync_master_net_avg_wait_time     | 358            |
| Rpl_semi_sync_master_net_wait_time         | 2374821347421  | 2375101909217 (145519/s)
| Rpl_semi_sync_master_net_waits             | 6621293848     | 6622101762 (419/s)
| Rpl_semi_sync_master_no_times              | 1              |
| Rpl_semi_sync_master_no_tx                 | 481541010      | -- not changed, probably old offset
| Rpl_semi_sync_master_status                | ON             |
| Rpl_semi_sync_master_timefunc_failures     | 0              |
| Rpl_semi_sync_master_tx_avg_wait_time      | 4254           |
| Rpl_semi_sync_master_tx_wait_time          | 14632961328193 | 14633043606017 (42675/s)
| Rpl_semi_sync_master_tx_waits              | 3439108811     | 3439288502 (93.2/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 121952810      | 121958280 (2.84/s)
| Rpl_semi_sync_master_wait_sessions         | 0              |
| Rpl_semi_sync_master_yes_tx                | 3010390613     | 3010564981 (909.44/s)
| Rpl_status                                 | AUTH_MASTER    |
+--------------------------------------------+----------------+
15 rows in set (0.00 sec)

`S6`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 | -- default value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         | Value registered after ~30m, if different, with change rate
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 1             | -- only 1 slave
| Rpl_semi_sync_master_net_avg_wait_time     | 323           |
| Rpl_semi_sync_master_net_wait_time         | 1137743488252 | 1137793507415 (26298/s)
| Rpl_semi_sync_master_net_waits             | 3516161732    | 3516322404 (84.5/s)
| Rpl_semi_sync_master_no_times              | 1             |
| Rpl_semi_sync_master_no_tx                 | 301           | -- not changed, probably old offset
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1292          |
| Rpl_semi_sync_master_tx_wait_time          | 2723774470840 | 2724055483870 (147746/s)
| Rpl_semi_sync_master_tx_waits              | 2106621177    | 2106794633 (91.2/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 7172838       | 7173499 (20.9/m)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 1982626134    | 1982787001 (84.6/s)
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
15 rows in set (0.01 sec)

`S7`

mysql:root@localhost [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 | -- default value
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)

mysql:root@localhost [(none)]> SHOW GLOBAL STATUS like 'rpl%';
+--------------------------------------------+---------------+
| Variable_name                              | Value         | Value registered after ~30m, if different, with change rate
+--------------------------------------------+---------------+
| Rpl_semi_sync_master_clients               | 2             | -- only 2 slaves
| Rpl_semi_sync_master_net_avg_wait_time     | 318           |
| Rpl_semi_sync_master_net_wait_time         | 1653717401590 | 1653824600938 (57364/s)
| Rpl_semi_sync_master_net_waits             | 5192398316    | 5192770578 (198.9/s)
| Rpl_semi_sync_master_no_times              | 0             |
| Rpl_semi_sync_master_no_tx                 | 0             |
| Rpl_semi_sync_master_status                | ON            |
| Rpl_semi_sync_master_timefunc_failures     | 0             |
| Rpl_semi_sync_master_tx_avg_wait_time      | 740           |
| Rpl_semi_sync_master_tx_wait_time          | 2048101314613 | 2048216836915 (61711/s)
| Rpl_semi_sync_master_tx_waits              | 2765250449    | 2765462063 (113.0/s)
| Rpl_semi_sync_master_wait_pos_backtraverse | 83244391      | 83252648 (4.41/s)
| Rpl_semi_sync_master_wait_sessions         | 0             |
| Rpl_semi_sync_master_yes_tx                | 2620688795    | 2620887829 (106.3/s)
| Rpl_status                                 | AUTH_MASTER   |
+--------------------------------------------+---------------+
15 rows in set (0.00 sec)

Volans triaged this task as Low priority.Apr 5 2016, 10:47 PM

Mentioned in SAL [2016-04-12T08:00:22Z] <volans> Set rpl_semi_sync_master_timeout=100 on db1038 T131753 (filling up erro log)

S3 master db1038 was filling up the error log quickly with messages:

160412  8:00:32 [Warning] Timeout waiting for reply of binlog (file: db1038-bin.002707, pos: 548532377), semi-sync up to file db1038-bin.002707, position 548531787.
160412  8:00:32 [Note] Semi-sync replication switched OFF.
160412  8:00:32 [Note] Semi-sync replication switched ON with slave (server_id: 171970584) at (db1038-bin.002707, 548533286)
160412  8:00:32 [Warning] Timeout waiting for reply of binlog (file: db1038-bin.002707, pos: 548543269), semi-sync up to file db1038-bin.002707, position 548541830.
160412  8:00:32 [Note] Semi-sync replication switched OFF.
160412  8:00:32 [Note] Semi-sync replication switched ON with slave (server_id: 171970584) at (db1038-bin.002707, 548543269)

I've set the rpl_semi_sync_master_timeout to 100 (ms) and the logs stopped immediately. S3 has only one semi-sync server, I'll look at it's status now.

Volans mentioned this in T132428: db1038 low on available space.Apr 12 2016, 9:20 AM

For reference, S3 only semi-sync slave is db1035

• jcrespo raised the priority of this task from Low to Medium.Apr 15 2016, 11:04 AM

Mentioned in SAL [2016-05-03T13:00:02Z] <volans> Change runtime semi-synchronous replication on eqiad core DBs (s1-s7) to match configured value T131753

Mentioned in SAL [2016-05-03T15:15:37Z] <volans> Change runtime semi-synchronous replication on eqiad x1,es2,es3 to match configured value T131753

Eqiad coredb cluster done: s1-s7, x1, es2-es3

I'll re-check their stats in few days to ensure that the choosen value for rpl_semi_sync_master_timeout of 100 milliseconds is ok and it doesn't make the masters to switch to async too often.

For reference here the current values:

volans@db1057:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 8           |
| Rpl_semi_sync_master_net_avg_wait_time     | 344         |
| Rpl_semi_sync_master_net_wait_time         | 343642200   |
| Rpl_semi_sync_master_net_waits             | 996187      |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 147         |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 548         |
| Rpl_semi_sync_master_tx_wait_time          | 86897742    |
| Rpl_semi_sync_master_tx_waits              | 158355      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 45          |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 152438      |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1018:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 9           |
| Rpl_semi_sync_master_net_avg_wait_time     | 351         |
| Rpl_semi_sync_master_net_wait_time         | 1348776256  |
| Rpl_semi_sync_master_net_waits             | 3840207     |
| Rpl_semi_sync_master_no_times              | 7           |
| Rpl_semi_sync_master_no_tx                 | 724         |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 569         |
| Rpl_semi_sync_master_tx_wait_time          | 421612428   |
| Rpl_semi_sync_master_tx_waits              | 740118      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 538         |
| Rpl_semi_sync_master_wait_sessions         | 1           |
| Rpl_semi_sync_master_yes_tx                | 703233      |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1075:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 7           |
| Rpl_semi_sync_master_net_avg_wait_time     | 395         |
| Rpl_semi_sync_master_net_wait_time         | 811681597   |
| Rpl_semi_sync_master_net_waits             | 2053447     |
| Rpl_semi_sync_master_no_times              | 2           |
| Rpl_semi_sync_master_no_tx                 | 4979        |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 526         |
| Rpl_semi_sync_master_tx_wait_time          | 168569417   |
| Rpl_semi_sync_master_tx_waits              | 319991      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 84          |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 310364      |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1042:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 6           |
| Rpl_semi_sync_master_net_avg_wait_time     | 386         |
| Rpl_semi_sync_master_net_wait_time         | 444880802   |
| Rpl_semi_sync_master_net_waits             | 1149841     |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 1999        |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 651         |
| Rpl_semi_sync_master_tx_wait_time          | 167397193   |
| Rpl_semi_sync_master_tx_waits              | 256918      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 100         |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 239253      |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1049:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 4           |
| Rpl_semi_sync_master_net_avg_wait_time     | 343         |
| Rpl_semi_sync_master_net_wait_time         | 630169114   |
| Rpl_semi_sync_master_net_waits             | 1833227     |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 469         |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 699         |
| Rpl_semi_sync_master_tx_wait_time          | 386652257   |
| Rpl_semi_sync_master_tx_waits              | 552916      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 727         |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 491434      |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1050:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 4           |
| Rpl_semi_sync_master_net_avg_wait_time     | 346         |
| Rpl_semi_sync_master_net_wait_time         | 120609498   |
| Rpl_semi_sync_master_net_waits             | 348356      |
| Rpl_semi_sync_master_no_times              | 2           |
| Rpl_semi_sync_master_no_tx                 | 299         |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 711         |
| Rpl_semi_sync_master_tx_wait_time          | 69227590    |
| Rpl_semi_sync_master_tx_waits              | 97282       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 8           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 95292       |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1041:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 4           |
| Rpl_semi_sync_master_net_avg_wait_time     | 322         |
| Rpl_semi_sync_master_net_wait_time         | 101297713   |
| Rpl_semi_sync_master_net_waits             | 313702      |
| Rpl_semi_sync_master_no_times              | 2           |
| Rpl_semi_sync_master_no_tx                 | 186         |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 669         |
| Rpl_semi_sync_master_tx_wait_time          | 56312501    |
| Rpl_semi_sync_master_tx_waits              | 84082       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 5           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 82808       |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@db1031:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |
| Rpl_semi_sync_master_net_avg_wait_time     | 330         |
| Rpl_semi_sync_master_net_wait_time         | 8550346     |
| Rpl_semi_sync_master_net_waits             | 25852       |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 51          |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 573         |
| Rpl_semi_sync_master_tx_wait_time          | 15115415    |
| Rpl_semi_sync_master_tx_waits              | 26373       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 1           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 25853       |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@es1015:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 2           |
| Rpl_semi_sync_master_net_avg_wait_time     | 452         |
| Rpl_semi_sync_master_net_wait_time         | 15711448    |
| Rpl_semi_sync_master_net_waits             | 34697       |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 42          |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 611         |
| Rpl_semi_sync_master_tx_wait_time          | 10876759    |
| Rpl_semi_sync_master_tx_waits              | 17781       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 17647       |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

volans@es1019:~$ sudo -i mysql --skip-ssl -e "SHOW STATUS LIKE '%rpl%'"
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 2           |
| Rpl_semi_sync_master_net_avg_wait_time     | 452         |
| Rpl_semi_sync_master_net_wait_time         | 13864463    |
| Rpl_semi_sync_master_net_waits             | 30619       |
| Rpl_semi_sync_master_no_times              | 2           |
| Rpl_semi_sync_master_no_tx                 | 42          |
| Rpl_semi_sync_master_status                | ON          |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 572         |
| Rpl_semi_sync_master_tx_wait_time          | 9003071     |
| Rpl_semi_sync_master_tx_waits              | 15723       |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 15585       |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+

Volans moved this task from In progress to Pending comment on the DBA board.May 3 2016, 3:53 PM

Confirmed also status of codfw at runtime is all semi-sync OFF as intented.

Status update for eqiad after 48h:

volans@neodymium:~$ sudo salt -C 'G@mysql_role:master and G@site:eqiad' cmd.run 'mysql --defaults-file=.my.cnf -e "SHOW STATUS LIKE \"Rpl_semi_sync_master_no_%\""'
db1057.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	8
    Rpl_semi_sync_master_no_tx	1001
db1031.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	1
    Rpl_semi_sync_master_no_tx	51
db1041.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	84
    Rpl_semi_sync_master_no_tx	2080
es1019.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	2
    Rpl_semi_sync_master_no_tx	42
db1049.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	6
    Rpl_semi_sync_master_no_tx	484
es1015.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	1
    Rpl_semi_sync_master_no_tx	42
db1075.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	18
    Rpl_semi_sync_master_no_tx	5167
db1042.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	6
    Rpl_semi_sync_master_no_tx	2062
db1018.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	16
    Rpl_semi_sync_master_no_tx	1008
db1050.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	60
    Rpl_semi_sync_master_no_tx	1533

As you can see s6 and s7 master are the ones that switched the most back to async ~65-80 times in 48h, followed by s2 and s3 ~17 times. I think we could increase the rpl_semi_sync_master_timeout to 200ms for s6 and s7 and it's acceptable for the others.
@jcrespo what do you think?

Another datapoint after ~9 days:

volans@neodymium:~$ sudo salt -C 'G@mysql_role:master and G@site:eqiad' cmd.run 'mysql --defaults-file=.my.cnf -e "SHOW STATUS LIKE \"Rpl_semi_sync_master_no_%\""'
db1075.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	95
    Rpl_semi_sync_master_no_tx	6153
es1019.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	3
    Rpl_semi_sync_master_no_tx	43
es1015.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	1
    Rpl_semi_sync_master_no_tx	42
db1031.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	4
    Rpl_semi_sync_master_no_tx	70
db1057.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	78
    Rpl_semi_sync_master_no_tx	2059
db1042.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	29
    Rpl_semi_sync_master_no_tx	2213
db1049.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	36
    Rpl_semi_sync_master_no_tx	2535
db1050.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	194
    Rpl_semi_sync_master_no_tx	6778
db1041.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	316
    Rpl_semi_sync_master_no_tx	7846
db1018.eqiad.wmnet:
    Variable_name	Value
    Rpl_semi_sync_master_no_times	186
    Rpl_semi_sync_master_no_tx	20726

I do not see a huge issue with s6 and s7, that is probably the hardware and it will be replaced soon.

I'm resolving it for now, situation looks stable.

Volans mentioned this in T161007: Decouple Mariadb semi-sync replication from $::mw_primary.Mar 23 2017, 7:02 PM

Semi-synchronous replication status in all MySQL production clustersClosed, ResolvedPublicActions