Replicationステップアップ(3)心拍数の詳細のコピー

ドキドキ
心臓の鼓動、この言葉は異なる人の頭の中で、第一反応は異なるものを思い浮かべることができて、例えば医療関係者、思いついたのは“どん~どん~どん~”;linuxネットワークプログラミングの人は、TCPプロトコルの心拍数や、アプリケーション層が独自に構築した心拍数など、クライアントとサービス側のプローブメカニズムかもしれないと考えています.今日述べたMySQLのレプリケーションの鼓動は、アプリケーション層が独自に構築した探査メカニズムに属しています.
TCPプロトコルハートビート
MySQL関連の仕事をするには、どの方向でもTCP関連の知識を理解する必要があります.例えば、次の大きな列の構成などです.

net.ipv4.tcp_congestion_control = cubic
net.ipv4.tcp_dsack = 1
net.ipv4.tcp_early_demux = 1
net.ipv4.tcp_early_retrans = 3
net.ipv4.tcp_ecn = 2
net.ipv4.tcp_ecn_fallback = 1
net.ipv4.tcp_fack = 0
net.ipv4.tcp_fastopen = 1
net.ipv4.tcp_fastopen_blackhole_timeout_sec = 3600
net.ipv4.tcp_fastopen_key = 00000000-00000000-00000000-00000000
net.ipv4.tcp_fin_timeout = 60
net.ipv4.tcp_frto = 2
net.ipv4.tcp_fwmark_accept = 0
net.ipv4.tcp_invalid_ratelimit = 500
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.tcp_l3mdev_accept = 0
net.ipv4.tcp_limit_output_bytes = 262144
net.ipv4.tcp_low_latency = 0
net.ipv4.tcp_max_orphans = 32768
net.ipv4.tcp_max_reordering = 300
net.ipv4.tcp_max_syn_backlog = 256
net.ipv4.tcp_max_tw_buckets = 32768
net.ipv4.tcp_mem = 52533    70045   105066
net.ipv4.tcp_min_rtt_wlen = 300
net.ipv4.tcp_min_tso_segs = 2
net.ipv4.tcp_moderate_rcvbuf = 1
net.ipv4.tcp_mtu_probing = 0
net.ipv4.tcp_no_metrics_save = 0
net.ipv4.tcp_notsent_lowat = 4294967295
net.ipv4.tcp_orphan_retries = 0
net.ipv4.tcp_pacing_ca_ratio = 120
net.ipv4.tcp_pacing_ss_ratio = 200
net.ipv4.tcp_probe_interval = 600
net.ipv4.tcp_probe_threshold = 8
net.ipv4.tcp_recovery = 1

もちろん、TCPハートビートに関する参考は以下の通りです.

net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

TCPハートビートの詳細については、TCPハートビート設定を参照してください.
mysqld心拍数のコピー
クライアントがレプリケーションプロトコルを使用してmasterとレプリケーションリンクを確立すると、masterはdumpスレッドを有効にし、binlogをクライアントに送信します.ここでクライアントとは言いません.slaveとは言いません.説明したいことです.任意のプログラムはmysqldでなくてもmysqld関連のプロトコルを守れば、masterにbinlog dump要求を開始することができます.masterはクライアントのニーズに従います.対応するbinlogを送信します.では、心臓の鼓動をコピーするのは何ですか?主に何に使いますか.
ハートビートのコピーを有効にする
主従レプリケーションのパートナーを構築したことがあります.次のコマンドに詳しいはずです.

change master to master_hosr='',master_user='',master_password='',master_port=13307, ......

しかし、ハートビートの構成オプションを知らない人が多いかもしれませんが、changeコマンドを使用してハートビート間隔を指定すると、以下のようになります.もちろん指定しない場合、心拍数もデフォルトで開き、間隔は15秒です.

MASTER_HEARTBEAT_PERIOD

tcpdumpでバッグをつかむと次のように見えます.

16:23:49.625836 IP localhost.13307 > localhost.39414: Flags [P.], seq 2425355868:2425355914, ack 3652228843, win 350, options [nop,nop,TS val 549495480 ecr 549485480], length 46
16:23:49.625870 IP localhost.39414 > localhost.13307: Flags [.], ack 46, win 350, options [nop,nop,TS val 549495480 ecr 549495480], length 0



16:23:59.626298 IP localhost.13307 > localhost.39414: Flags [P.], seq 46:92, ack 1, win 350, options [nop,nop,TS val 549505480 ecr 549495480], length 46
16:23:59.626335 IP localhost.39414 > localhost.13307: Flags [.], ack 92, win 350, options [nop,nop,TS val 549505480 ecr 549505480], length 0



16:24:09.627078 IP localhost.13307 > localhost.39414: Flags [P.], seq 92:138, ack 1, win 350, options [nop,nop,TS val 549515480 ecr 549505480], length 46
16:24:09.627113 IP localhost.39414 > localhost.13307: Flags [.], ack 138, win 350, options [nop,nop,TS val 549515480 ecr 549515480], length 0



16:24:19.627594 IP localhost.13307 > localhost.39414: Flags [P.], seq 138:184, ack 1, win 350, options [nop,nop,TS val 549525480 ecr 549515480], length 46
16:24:19.627628 IP localhost.39414 > localhost.13307: Flags [.], ack 184, win 350, options [nop,nop,TS val 549525480 ecr 549525480], length 0



16:24:29.628152 IP localhost.13307 > localhost.39414: Flags [P.], seq 184:230, ack 1, win 350, options [nop,nop,TS val 549535480 ecr 549525480], length 46
16:24:29.628186 IP localhost.39414 > localhost.13307: Flags [.], ack 230, win 350, options [nop,nop,TS val 549535480 ecr 549535480], length 0

ハートビート間隔の表示
mysqlライブラリのslave_を表示します.master_infoテーブルでは、現在のレプリケーションチャネルの心拍数間隔を表示できます.

*************************** 4. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.001836
        Master_log_pos: 298191716
                  Host: 100.107.14.181
             User_name: *********
         User_password: ************
                  Port: **********
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 15      /*15 */
                  Bind:
    Ignored_server_ids: 0
                  Uuid:**************
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 1
          Channel_name: rrxdebt4
           Tls_version:

showコマンドで表示することもできます.

mysql> show global status like '%heart%';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Slave_heartbeat_period    | 15.000              |
| Slave_last_heartbeat      | 2018-07-15 14:21:47 |
| Slave_received_heartbeats | 578024              |
+---------------------------+---------------------+
3 rows in set (0.00 sec)

mysqldコピー心拍数は何ですか?
一言:クライアントは心拍数を使ってホストが生存しているかどうか、ネットワークがスムーズであるかどうかを確認します.クライアントはbinlogの受信者として、masterから送信されたbinlogを長時間受信していなければ、ネットワークが隔離されているのか、masterが本当に書き込まれていないのかを確認できないため、コピー関係を確立する際に、コピー心拍を指定し、時間間隔を指定し、指定した時間間隔内にmasterが書き込まれていなければ、クライアントに心拍数を送信し、masterがまだ生きていることをクライアントに伝えます.
slave_net_timeout ?
スレーブのパラメータslave_net_timeout、どういう意味ですか?まずドキュメントを見て、次のようにします.

The number of seconds to wait for more data from a master/slave connection before aborting the read. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE commands.

大まかな意味は、読み出し操作を中断するまでの待ち時間であり、単位は秒である.したがって、このパラメータの設定がheartbeatより小さい場合、どのような問題が発生しますか?問題は,masterの書き込みが頻繁でない場合,ioスレッド中断,再接続の問題が頻発する可能性があることである.
拡張
ioスレッドがデータ読み込みを待つ方法
ioスレッドが新しいデータをどのように待つかを見てみましょう

Thread 74 (Thread 0x7fc2ac271700 (LWP 25290)):
#0  0x00007fc2e823674d in poll () at ../sysdeps/unix/syscall-template.S:84
#1  0x0000000001ed8303 in vio_io_wait (vio=0x7fc250010d00, event=VIO_IO_EVENT_READ, timeout=30000) at /data/mysql-server-explain_ddl/vio/viosocket.c:786
#2  0x0000000001ed6f69 in vio_socket_io_wait (vio=0x7fc250010d00, event=VIO_IO_EVENT_READ) at /data/mysql-server-explain_ddl/vio/viosocket.c:77
#3  0x0000000001ed7054 in vio_read (vio=0x7fc250010d00, buf=0x7fc250010f20 "*", size=16384) at /data/mysql-server-explain_ddl/vio/viosocket.c:132
#4  0x0000000001ed7238 in vio_read_buff (vio=0x7fc250010d00, buf=0x7fc250014f50 "", size=4) at /data/mysql-server-explain_ddl/vio/viosocket.c:166
#5  0x000000000152151d in net_read_raw_loop (net=0x7fc25000e1f0, count=4) at /data/mysql-server-explain_ddl/sql/net_serv.cc:672
#6  0x0000000001521721 in net_read_packet_header (net=0x7fc25000e1f0) at /data/mysql-server-explain_ddl/sql/net_serv.cc:762
#7  0x0000000001521803 in net_read_packet (net=0x7fc25000e1f0, complen=0x7fc2ac270c18) at /data/mysql-server-explain_ddl/sql/net_serv.cc:822
#8  0x00000000015219ba in my_net_read (net=0x7fc25000e1f0) at /data/mysql-server-explain_ddl/sql/net_serv.cc:899
#9  0x000000000174650f in cli_safe_read_with_ok (mysql=0x7fc25000e1f0, parse_ok=0 '\000', is_data_packet=0x0) at /data/mysql-server-explain_ddl/sql-common/client.c:1055
#10 0x0000000001746882 in cli_safe_read (mysql=0x7fc25000e1f0, is_data_packet=0x0) at /data/mysql-server-explain_ddl/sql-common/client.c:1188
#11 0x000000000191d4bc in read_event (mysql=0x7fc25000e1f0, mi=0x561ebe0, suppress_warnings=0x7fc2ac270d71) at /data/mysql-server-explain_ddl/sql/rpl_slave.cc:4455
#12 0x0000000001920fdb in handle_slave_io (arg=0x561ebe0) at /data/mysql-server-explain_ddl/sql/rpl_slave.cc:5713
#13 0x0000000001e4a621 in pfs_spawn_thread (arg=0x7fc2340d4d50) at /data/mysql-server-explain_ddl/storage/perfschema/pfs.cc:2188
#14 0x00007fc2e8dad6ba in start_thread (arg=0x7fc2ac271700) at pthread_create.c:333
#15 0x00007fc2e824241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

pollメカニズムを見る必要があるかもしれません.

POLL(2)                                                                                  Linux Programmer's Manual                                                                                 POLL(2)

NAME
       poll, ppoll - wait for some event on a file descriptor

SYNOPSIS
       #include 

       int poll(struct pollfd *fds, nfds_t nfds, int timeout);

       #define _GNU_SOURCE         /* See feature_test_macros(7) */
       #include 
       #include 

       int ppoll(struct pollfd *fds, nfds_t nfds,
               const struct timespec *tmo_p, const sigset_t *sigmask);

DESCRIPTION
       poll() performs a similar task to select(2): it waits for one of a set of file descriptors to become ready to perform I/O.

       The set of file descriptors to be monitored is specified in the fds argument, which is an array of structures of the following form:

           struct pollfd {
               int   fd;         /* file descriptor */
               short events;     /* requested events */
               short revents;    /* returned events */
           };

       The caller should specify the number of items in the fds array in nfds.

       The  field  fd  contains a file descriptor for an open file.  If this field is negative, then the corresponding events field is ignored and the revents field returns zero.  (This provides an easy
       way of ignoring a file descriptor for a single poll() call: simply negate the fd field.  Note, however, that this technique can't be used to ignore file descriptor 0.)

       The field events is an input parameter, a bit mask specifying the events the application is interested in for the file descriptor fd.  This field may be specified as zero, in which case the  only
       events that can be returned in revents are POLLHUP, POLLERR, and POLLNVAL (see below).

       The  field  revents  is an output parameter, filled by the kernel with the events that actually occurred.  The bits returned in revents can include any of those specified in events, or one of the
       values POLLERR, POLLHUP, or POLLNVAL.  (These three bits are meaningless in the events field, and will be set in the revents field whenever the corresponding condition is true.)

       If none of the events requested (and no error) has occurred for any of the file descriptors, then poll() blocks until one of the events occurs.

       The timeout argument specifies the number of milliseconds that poll() should block waiting for a file descriptor to become ready.  The call will block until either:

       *  a file descriptor becomes ready;

       *  the call is interrupted by a signal handler; or

       *  the timeout expires.

       Note that the timeout interval will be rounded up to the system clock granularity, and kernel scheduling delays mean that the blocking interval may overrun by a small amount.  Specifying a  nega‐
       tive value in timeout means an infinite timeout.  Specifying a timeout of zero causes poll() to return immediately, even if no file descriptors are ready.

dumpスレッドがハートビートを送信する方法
dumpスレッドはbinlogサイトの更新を待機し,心拍間隔が設定されている場合は待機タイムアウト後にheartbeat eventを送信する.実は最近のbinlog file name+posです.

Thread 24 (Thread 0x7fbd44050700 (LWP 6474)):
#0  pthread_cond_timedwait@@GLIBC_2.3.2 () at ../sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S:225
#1  0x0000000001985490 in native_cond_timedwait (cond=0x2db7fa0 <mysql_bin_log+1824>, mutex=0x2db7ee8 <mysql_bin_log+1640>, abstime=0x7fbd4404de50) at /data/mysql-server-explain_ddl/include/thr_cond.h:129
#2  0x00000000019857f6 in safe_cond_timedwait (cond=0x2db7fa0 <mysql_bin_log+1824>, mp=0x2db7ec0 <mysql_bin_log+1600>, abstime=0x7fbd4404de50, file=0x224ec38 "/data/mysql-server-explain_ddl/sql/binlog.cc", line=7683) at /data/mysql-server-explain_ddl/mysys/thr_cond.c:88
#3  0x00000000018d9126 in my_cond_timedwait (cond=0x2db7fa0 <mysql_bin_log+1824>, mp=0x2db7ec0 <mysql_bin_log+1600>, abstime=0x7fbd4404de50, file=0x224ec38 "/data/mysql-server-explain_ddl/sql/binlog.cc", line=7683) at /data/mysql-server-explain_ddl/include/thr_cond.h:180
#4  0x00000000018d96c3 in inline_mysql_cond_timedwait (that=0x2db7fa0 <mysql_bin_log+1824>, mutex=0x2db7ec0 <mysql_bin_log+1600>, abstime=0x7fbd4404de50, src_file=0x224ec38 "/data/mysql-server-explain_ddl/sql/binlog.cc", src_line=7683) at /data/mysql-server-explain_ddl/include/mysql/psi/mysql_thread.h:1229
#5  0x00000000018eb4ba in MYSQL_BIN_LOG::wait_for_update_bin_log (this=0x2db7880 <mysql_bin_log>, thd=0x7fbcfc000950, timeout=0x7fbd4404de50) at /data/mysql-server-explain_ddl/sql/binlog.cc:7683
#6  0x000000000190e4ca in Binlog_sender::wait_with_heartbeat (this=0x7fbd4404e5d0, log_pos=919) at /data/mysql-server-explain_ddl/sql/rpl_binlog_sender.cc:620
#7  0x000000000190e3f7 in Binlog_sender::wait_new_events (this=0x7fbd4404e5d0, log_pos=919) at /data/mysql-server-explain_ddl/sql/rpl_binlog_sender.cc:599
#8  0x000000000190e058 in Binlog_sender::get_binlog_end_pos (this=0x7fbd4404e5d0, log_cache=0x7fbd4404e020) at /data/mysql-server-explain_ddl/sql/rpl_binlog_sender.cc:365
#9  0x000000000190bd00 in Binlog_sender::send_binlog (this=0x7fbd4404e5d0, log_cache=0x7fbd4404e020, start_pos=123) at /data/mysql-server-explain_ddl/sql/rpl_binlog_sender.cc:313
#10 0x000000000190b8d4 in Binlog_sender::run (this=0x7fbd4404e5d0) at /data/mysql-server-explain_ddl/sql/rpl_binlog_sender.cc:225
#11 0x0000000001909470 in mysql_binlog_send (thd=0x7fbcfc000950, log_ident=0x7fbd4404f230 "", pos=4, slave_gtid_executed=0x7fbd4404f000, flags=0) at /data/mysql-server-explain_ddl/sql/rpl_master.cc:412
#12 0x0000000001909307 in com_binlog_dump_gtid (thd=0x7fbcfc000950, packet=0x7fbcfc0098a1 "", packet_length=86) at /data/mysql-server-explain_ddl/sql/rpl_master.cc:396
#13 0x000000000161b15c in dispatch_command (thd=0x7fbcfc000950, com_data=0x7fbd4404fe00, command=COM_BINLOG_DUMP_GTID) at /data/mysql-server-explain_ddl/sql/sql_parse.cc:1677
#14 0x00000000016194aa in do_command (thd=0x7fbcfc000950) at /data/mysql-server-explain_ddl/sql/sql_parse.cc:999
#15 0x000000000175ba57 in handle_connection (arg=0x4d71eb0) at /data/mysql-server-explain_ddl/sql/conn_handler/connection_handler_per_thread.cc:300
#16 0x0000000001e49759 in pfs_spawn_thread (arg=0x5012a50) at /data/mysql-server-explain_ddl/storage/perfschema/pfs.cc:2188
#17 0x00007fbd67c746ba in start_thread (arg=0x7fbd44050700) at pthread_create.c:333
#18 0x00007fbd6710941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109