Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26196

After enabling the parallel replication slave lag is increasing very high

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.5.11
    • N/A
    • Replication
    • CentOS Linux release 7.7.1908 (Core)

    Description

      Hi Team,

      I have configured the replication from 10.0.38 to 10.5.11. Then I have started the sysbench to load some data. Then I checked the slave lag in my slave server. There is no slave lag and replication is running on single thread.

      Then I have enabled the parallel replication,

      SET GLOBAL slave_parallel_threads=4;
      SET GLOBAL binlog_commit_wait_count=5;
      SET GLOBAL sync_binlog = 1;
      

      Again started the sysbench to load some data. Now my slave lag was increasing slowly.

      [root@ip-172-31-8-142 sysbench]# sysbench oltp_insert.lua --tables=5 --table_size=20000000  --mysql-host=127.0.0.1 --db-driver=mysql --mysql-user=proxy --mysql-password=Proxy@123 --mysql-port=3306 --mysql-db=sbtest  --time=100 --report-interval=10 --threads=2 prepare
      sysbench 1.0.17 (using system LuaJIT 2.0.4)
       
      Initializing worker threads...
       
      Creating table 'sbtest1'...
      Creating table 'sbtest2'...
      Inserting 20000000 records into 'sbtest1'
      Inserting 20000000 records into 'sbtest2'
      

      Here I have attached my config files for both master & slave server.

      Also attached the show slave status output and show process output.

      MariaDB [sbtest]> show slave status\G
      *************************** 1. row ***************************
                      Slave_IO_State: Queueing master event to the relay log
                         Master_Host: 172.31.8.142
                         Master_User: repl
                         Master_Port: 3306
                       Connect_Retry: 60
                     Master_Log_File: mysql-bin.000006
                 Read_Master_Log_Pos: 587112382
                      Relay_Log_File: ip-172-31-15-223-relay-bin.000002
                       Relay_Log_Pos: 3637059
               Relay_Master_Log_File: mysql-bin.000006
                    Slave_IO_Running: Yes
                   Slave_SQL_Running: Yes
                     Replicate_Do_DB: 
                 Replicate_Ignore_DB: 
                  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: 475320641
                     Relay_Log_Space: 115429116
                     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: 398
       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_SSL_Crl: 
                  Master_SSL_Crlpath: 
                          Using_Gtid: Slave_Pos
                         Gtid_IO_Pos: 201-1-11477
             Replicate_Do_Domain_Ids: 
         Replicate_Ignore_Domain_Ids: 
                       Parallel_Mode: optimistic
                           SQL_Delay: 0
                 SQL_Remaining_Delay: NULL
             Slave_SQL_Running_State: Waiting for room in worker thread event queue
                    Slave_DDL_Groups: 0
      Slave_Non_Transactional_Groups: 9941
          Slave_Transactional_Groups: 0
      1 row in set (0.270 sec)
      

      MariaDB [sbtest]> show processlist;
      +-----+-------------+-----------+--------+--------------+------+-----------------------------------------------+------------------+----------+
      | Id  | User        | Host      | db     | Command      | Time | State                                         | Info             | Progress |
      +-----+-------------+-----------+--------+--------------+------+-----------------------------------------------+------------------+----------+
      |   4 | root        | localhost | sbtest | Query        |    0 | starting                                      | show processlist |    0.000 |
      | 181 | system user |           | NULL   | Slave_IO     |   13 | Waiting for master to send event              | NULL             |    0.000 |
      | 183 | system user |           | NULL   | Slave_worker |   13 | Waiting for work from SQL thread              | NULL             |    0.000 |
      | 184 | system user |           | NULL   | Slave_worker |   13 | Waiting for work from SQL thread              | NULL             |    0.000 |
      | 185 | system user |           | NULL   | Slave_worker |    0 | Waiting for prior transaction to commit       | NULL             |    0.000 |
      | 186 | system user |           | NULL   | Slave_worker |    0 | closing tables                                | NULL             |    0.000 |
      | 182 | system user |           | NULL   | Slave_SQL    |   11 | Waiting for room in worker thread event queue | NULL             |    0.000 |
      +-----+-------------+-----------+--------+--------------+------+-----------------------------------------------+------------------+----------+
      7 rows in set (0.000 sec)
      

      MariaDB [sbtest]> show global variables like 'slave%';
      +----------------------------------+-----------------------------------------------+
      | Variable_name                    | Value                                         |
      +----------------------------------+-----------------------------------------------+
      | slave_compressed_protocol        | OFF                                           |
      | slave_ddl_exec_mode              | IDEMPOTENT                                    |
      | slave_domain_parallel_threads    | 0                                             |
      | slave_exec_mode                  | STRICT                                        |
      | slave_load_tmpdir                | /tmp                                          |
      | slave_max_allowed_packet         | 1073741824                                    |
      | slave_net_timeout                | 60                                            |
      | slave_parallel_max_queued        | 131072                                        |
      | slave_parallel_mode              | optimistic                                    |
      | slave_parallel_threads           | 4                                             |
      | slave_parallel_workers           | 4                                             |
      | slave_run_triggers_for_rbr       | NO                                            |
      | slave_skip_errors                | OFF                                           |
      | slave_sql_verify_checksum        | ON                                            |
      | slave_transaction_retries        | 10                                            |
      | slave_transaction_retry_errors   | 1158,1159,1160,1161,1205,1213,1429,2013,12701 |
      | slave_transaction_retry_interval | 0                                             |
      | slave_type_conversions           |                                               |
      +----------------------------------+-----------------------------------------------+
      18 rows in set (0.001 sec)
      

      I have tried to increase the "slave_parallel_max_queued" parameter upto 128 MB. But no luck still the slave is increasing.
      Also changed the "slave_parallel_mode" to conservative mode but no luck.

      Attachments

        1. master_10.0
          1 kB
        2. mysqld.log
          5 kB
        3. op.txt
          197 kB
        4. replication_lag
          124 kB
        5. slave_10.5
          2 kB

        Activity

          People

            Elkin Andrei Elkin
            ponsuresh.pandians Pon Suresh Pandian (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.