MySQL主从同步 问题定位

    xiaoxiao2021-03-25  210

    MySQL主从同步: 1、查看同步状态 SHOW SLAVE STATUS;    保持同步标识Seconds_Behind_Master =0  如果值很大说明延迟很大  优化mysql slave的同步速度   2、如何判断是io thread慢还是 sql thread慢呢?    有个方法,观察show slave status\G , [root@szwlan3 ~]# mysql -uxxx -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.3.0.99                   Master_User: repluser                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000009           Read_Master_Log_Pos: 776231834                Relay_Log_File: mysql-relay-log.000022                 Relay_Log_Pos: 4095928         Relay_Master_Log_File: mysql-bin.000009              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB: mysql            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 776231176               Relay_Log_Space: 4096759               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                   Master_UUID: 78a67bec-65e2-11e6-ab3b-002590295e86              Master_Info_File: /home/mysqldata/master.info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Searching rows for update            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0 1 row in set (0.01 sec) mysql> 测试环境: Red Hat Enterprise Linux Server release 6.3 (Santiago) Server version: 5.6.22-log MySQL Community Server (GPL) 我搭建了1主3从的环境,准备测试MHA架构,过程中发现,测试并发插入的时候,从库1可以跟上,从库2,3跟不上 如何判断是io thread慢还是 sql thread慢呢,有个方法,观察show slave status\G , 判断3个参数(参数后面的值是默认空闲时候的正常值): Slave_IO_State: Waiting for master to send event Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Seconds_Behind_Master: 0 1.sql thread慢的表现: Seconds_Behind_Master越来越大 Slave_SQL_Running_State: Reading event from the relay log 2.io thread慢的表现: Seconds_Behind_Master为0 Slave_SQL_Running_State: 显示正常值 Slave_IO_State:显示忙碌状态 而我观察到的值是 Slave_IO_State: Waiting for master to send event Seconds_Behind_Master: 313 Slave_SQL_Running_State: Reading event from the relay log 因此推断是sql thread慢 为啥只有slave2,3慢,而slave1可以跟上呢,开始怀疑是参数配置的差异,比对/etc/my.cnf后发现,配置无差异 因此排除这个原因,后来用dstat观察,发现繁忙时候,slave的IO写速度上不去 slave1: $ dstat ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 0 0 100 0 0 0|9308B 11k| 0 0 | 3B 3B| 63 63 3 4 54 40 0 0| 88k 10M| 45k 9438B| 0 0 |1857 2579 3 3 59 35 0 1| 80k 7552k| 40k 8486B| 0 0 |1675 2307 3 3 56 38 0 0| 72k 7824k| 42k 8816B| 0 0 |1727 2348 3 4 52 41 0 1| 96k 9688k| 49k 10k| 0 0 |2029 2874 3 4 54 39 0 0| 96k 8880k| 45k 9410B| 0 0 |1905 2674 3 3 53 40 0 1| 96k 9776k| 58k 10k| 0 0 |1935 2671 3 3 58 36 0 0| 64k 7848k| 40k 8420B| 0 0 |1724 2357 3 5 52 40 0 1| 96k 8936k| 49k 10k| 0 0 |1948 2680 3 4 51 42 0 1| 96k 9400k| 49k 10k| 0 0 |1988 2760 3 4 52 41 0 0| 88k 9752k| 49k 10k| 0 0 |2058 2868 4 4 51 41 0 1| 96k 9680k| 49k 9938B| 0 0 |1990 2750 3 3 59 35 0 0| 80k 7632k| 39k 8288B| 0 0 |1668 2275 3 4 52 42 0 1| 80k 8504k| 46k 9146B| 0 0 |1860 2523 3 4 51 42 0 0| 80k 8496k| 43k 8684B| 0 0 |1882 2516 2 3 65 30 0 0| 64k 5976k| 30k 6440B| 0 0 |1326 1802 3 4 53 40 0 1| 72k 8360k| 59k 10k| 0 0 |1859 2538 3 4 51 42 0 1| 96k 8840k| 53k 10k| 0 0 |1958 2648 2 4 51 43 0 0| 72k 7352k| 40k 7760B| 0 0 |1633 2219 3 4 51 42 0 1| 88k 7920k| 31k 6770B| 0 0 |1767 2373 3 3 54 40 0 0| 80k 8528k| 40k 8750B| 0 0 |1859 2549 slave2: ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 2 1 50 47 0 1|8192B 1168k| 55k 10k| 0 0 | 533 771 1 1 51 48 0 0|8192B 1048k| 33k 7046B| 0 0 | 427 622 1 1 51 48 0 0|8192B 1080k| 58k 9806B| 0 0 | 500 709 1 1 50 48 0 0| 0 1864k| 51k 8486B| 0 0 | 502 669 1 2 51 47 0 0|8192B 1120k| 42k 8156B| 0 0 | 496 674 1 1 51 47 0 0|8192B 1160k| 32k 6350B| 0 0 | 467 655 1 2 51 47 0 0| 0 1288k| 50k 10k| 0 0 | 563 797 1 1 51 47 0 0|8192B 1200k| 43k 8486B| 0 0 | 493 728 2 1 50 47 0 0|8192B 1024k| 45k 8816B| 0 0 | 481 659 1 1 50 48 0 0|8192B 1248k| 49k 9450B| 0 0 | 517 772 1 1 50 48 0 0| 0 1264k| 47k 9146B| 0 0 | 516 756 1 2 50 47 0 1|8192B 1144k| 50k 10k| 0 0 | 520 765 1 1 51 48 0 0|8192B 1200k| 51k 8156B| 0 0 | 484 716 1 2 50 48 0 0|8192B 968k| 50k 9278B| 0 0 | 470 684 1 1 50 48 0 0|8192B 1128k| 39k 7892B| 0 0 | 476 679 1 1 51 47 0 0| 0 1248k| 45k 9476B| 0 0 | 523 760 1 2 50 48 0 0|8192B 1448k| 41k 7826B| 0 0 | 552 805 1 1 50 48 0 0|8192B 1120k| 44k 8090B| 0 0 | 470 692 slave3: ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 1 1 50 49 0 0|8192B 1328k|1167B 170B| 0 0 | 385 515 1 1 51 48 0 0|8192B 1128k| 754B 170B| 0 0 | 325 449 1 1 50 49 0 0| 0 920k| 474B 314B| 0 0 | 279 381 0 1 50 49 0 0|8192B 664k|1633B 170B| 0 0 | 226 291 1 1 50 49 0 0|8192B 1200k|1250B 170B| 0 0 | 353 475 1 1 50 48 0 0| 0 1432k|1632B 170B| 0 0 | 402 551 1 1 51 48 0 0| 16k 1752k|1045B 170B| 0 0 | 487 664 1 1 50 48 0 0|8192B 1648k| 12k 170B| 0 0 | 461 636 1 1 51 48 0 0| 0 1272k| 886B 170B| 0 0 | 380 501 1 1 50 49 0 0|8192B 1000k|1023B 170B| 0 0 | 300 400 1 1 50 48 0 0|8192B 1096k| 747B 170B| 0 0 | 332 442 1 1 50 48 0 0|8192B 1448k|1003B 170B| 0 0 | 416 557 1 1 50 48 0 0| 0 1592k|1174B 170B| 0 0 | 450 614 1 1 51 48 0 0|8192B 1416k|1028B 170B| 0 0 | 404 552 0 1 50 49 0 0|8192B 1128k|1031B 170B| 0 0 | 331 447 1 1 51 48 0 0|8192B 1160k|1185B 170B| 0 0 | 340 458 1 1 50 49 0 0| 0 1120k| 633B 170B| 0 0 | 326 453 1 0 50 49 0 0|8192B 656k|8886B 170B| 0 0 | 221 288 1 1 50 49 0 0|8192B 1128k|1619B 170B| 0 0 | 335 451 slave1可以达到每秒9M的写入IO,而slave2,3只能达到每秒1M多,IO性能差很多,后来分析了下存储,发现是有很大差异的,也印证了我的推测 那么问题来了,要如何优化IO性能比较差的slave呢,其实很简单,修改两个参数 mysql> set global sync_binlog=20 ; Query OK, 0 rows affected (0.00 sec) mysql> set global innodb_flush_log_at_trx_commit=2;Query OK, 0 rows affected (0.00 sec) innodb_flush_log_at_trx_commit 如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。 如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去. 如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。 注意: 由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。 sync_binlog sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。 当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。 注意: 如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。 而且mysql服务默认是autocommit打开的 修改参数后,slave2,3也一样可以跟上slave1的速度了 slave2,3: ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq| read writ| recv send| in out | int csw 3 2 94 2 0 0| 32k 80k| 49k 10k| 0 0 |1042 658 3 2 94 1 0 1| 32k 72k| 49k 10k| 0 0 |1258 964 2 2 95 2 0 0| 32k 72k| 44k 9146B| 0 0 |1126 882 2 1 95 2 0 0| 32k 72k| 41k 8486B| 0 0 | 959 659 2 2 96 1 0 0| 32k 72k| 47k 9476B| 0 0 |1153 841 2 2 95 2 0 0| 24k 72k| 39k 8090B| 0 0 | 866 504 2 2 96 1 0 0| 24k 72k| 42k 7562B| 0 0 | 908 663 2 1 95 2 0 0| 40k 72k| 52k 10k| 0 0 |1084 685 3 1 94 2 0 1| 40k 80k| 54k 11k| 0 0 |1204 873 2 2 96 1 0 0| 16k 32k| 30k 6044B| 0 0 | 846 802 2 1 97 1 0 0| 24k 32k| 35k 7760B| 0 0 |1059 888 2 1 95 3 0 0| 32k 856k| 44k 9278B| 0 0 | 943 551 2 1 94 3 0 0| 32k 104k| 42k 8618B| 0 0 | 986 704 2 1 96 1 0 0| 24k 72k| 34k 7034B| 0 0 | 863 682 2 2 95 2 0 0| 32k 64k| 45k 8684B| 0 0 |1052 750 2 2 90 7 0 0| 24k 416k| 38k 7166B| 0 0 | 906 722 3 2 93 2 0 1| 32k 80k| 57k 10k| 0 0 |1069 829 3 2 94 1 0 0| 32k 72k| 42k 8486B| 0 0 |1076 942 2 1 96 1 0 0| 24k 72k| 37k 7496B| 0 0 | 859 575 2 2 94 2 0 1| 32k 64k| 43k 8684B| 0 0 |1138 1011 3 2 94 1 0 0| 32k 72k| 42k 9014B| 0 0 |1099 782 2 3 94 2 0 0| 32k 72k| 50k 10k| 0 0 |1332 1359 2 2 95 2 0 0| 24k 72k| 34k 6902B| 0 0 | 921 799 2 2 94 2 0 0| 40k 72k| 55k 11k| 0 0 |1318 1016 1 2 96 2 0 0| 32k 80k| 41k 8882B| 0 0 |1020 719 而且我观察到slave2,3的写入数量减少了两个数量级,从1M多下降到70k

    innodb_flush_log_at_trx_commit=2

    http://blog.csdn.net/fengbangyue/article/details/6401564

    转载请注明原文地址: https://ju.6miu.com/read-4456.html

    最新回复(0)