Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.5.11
-
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.