rm -rf master bin/mysqld --defaults-file=./master1.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/master -v bin/mysqld --defaults-file=./master1.cnf 2>&1 & rm -rf slave bin/mysqld --defaults-file=./slave1.cnf --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/slave -v bin/mysqld --defaults-file=./slave1.cnf 2>&1 & -- Setup replication -- on master mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table norm_table(c1 int auto_increment primary key, c2 int, c3 varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> create table part_table(c1 int auto_increment primary key, c2 int, c3 varchar(20)); Query OK, 0 rows affected (0.00 sec) -- Run sysbench ##conf [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: cat master1.cnf slave1.cnf [mysqld] port=3306 socket=/tmp/mysql_master.sock basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/master interactive_timeout = 300 wait_timeout = 300 character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 log-output=file slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/master/slow.log log-error =/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/master/error.log log_warnings = 2 pid-file = /export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/master/mysql.pid long_query_time = 1 log-slow-slave-statements = 1 log-slave-updates=1 gtid_mode=on enforce-gtid-consistency=on binlog_format = row server-id = 1 log-bin = master-bin binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 10 skip_slave_start = 0 max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates relay_log=relay-log-1 table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 master-info-repository=TABLE relay-log-info-repository=TABLE log_bin_trust_function_creators=1 innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_page_size=16384 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1 innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT [mysqld] port=3307 socket=/tmp/mysql_slave.sock basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15 datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/slave interactive_timeout = 300 wait_timeout = 300 character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 log-output=file slow_query_log = 1 slow_query_log_file = /export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/slave/slow.log log-error =/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/slave/error.log log_warnings = 2 pid-file = /export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15/slave/mysql.pid long_query_time = 1 log-slow-slave-statements = 1 log-slave-updates=1 gtid_mode=on enforce-gtid-consistency=on binlog_format = row server-id = 2 log-bin = slave-bin binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 10 skip_slave_start = 0 max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates relay_log=relay-log-1 table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 master-info-repository=TABLE relay-log-info-repository=TABLE log_bin_trust_function_creators=1 innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_page_size=16384 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1 innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: [umshastr@hod03]~/bugs/sysbench: bin/sysbench --test=/home/umshastr/bugs/sysbench/sysbench/tests/db/xa.lua --mysql-user=root --mysql-db=test --mysql-socket=/tmp/mysql_master.sock --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=360 --num-threads=10 --max-requests=0 --percentile=99 run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 10 Report intermediate results every 10 second(s) Random number generator seed is 0 and will be ignored Threads started! [ 10s] threads: 10, tps: 0.00, reads/s: 6233.61, writes/s: 49870.14, response time: 3.95ms (99%) [ 20s] threads: 10, tps: 0.00, reads/s: 6442.81, writes/s: 51542.84, response time: 3.84ms (99%) [ 30s] threads: 10, tps: 0.00, reads/s: 6693.50, writes/s: 53547.89, response time: 3.87ms (99%) [ 40s] threads: 10, tps: 0.00, reads/s: 6638.10, writes/s: 53103.58, response time: 3.78ms (99%) [ 50s] threads: 10, tps: 0.00, reads/s: 6712.90, writes/s: 53704.61, response time: 3.74ms (99%) [ 60s] threads: 10, tps: 0.00, reads/s: 6663.50, writes/s: 53306.61, response time: 3.76ms (99%) [ 70s] threads: 10, tps: 0.00, reads/s: 6767.40, writes/s: 54141.20, response time: 3.57ms (99%) [ 80s] threads: 10, tps: 0.00, reads/s: 6737.20, writes/s: 53896.10, response time: 3.58ms (99%) [ 90s] threads: 10, tps: 0.00, reads/s: 6679.80, writes/s: 53440.19, response time: 3.61ms (99%) [ 100s] threads: 10, tps: 0.00, reads/s: 6756.40, writes/s: 54050.62, response time: 3.55ms (99%) [ 110s] threads: 10, tps: 0.00, reads/s: 6747.30, writes/s: 53977.09, response time: 3.60ms (99%) [ 120s] threads: 10, tps: 0.00, reads/s: 6746.60, writes/s: 53974.30, response time: 3.57ms (99%) [ 130s] threads: 10, tps: 0.00, reads/s: 6755.10, writes/s: 54039.41, response time: 3.59ms (99%) [ 140s] threads: 10, tps: 0.00, reads/s: 6726.30, writes/s: 53811.60, response time: 3.57ms (99%) [ 150s] threads: 10, tps: 0.00, reads/s: 6763.00, writes/s: 54105.20, response time: 3.60ms (99%) -- kill slave [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: ps aux|grep mysqld umshastr 19729 114 0.1 9293828 380528 pts/7 Sl 12:02 22:16 bin/mysqld --defaults-file=./master1.cnf umshastr 20983 72.1 0.9 5232152 2393920 pts/7 Sl 12:14 4:59 bin/mysqld --defaults-file=./slave1.cnf umshastr 21600 0.0 0.0 112632 972 pts/7 S+ 12:21 0:00 grep --color=auto mysqld [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: kill -9 20983 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: bin/mysqld --defaults-file=./slave1.cnf 2>&1 & [6] 21604 [5] Killed bin/mysqld --defaults-file=./slave1.cnf 2>&1 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: ps aux|grep mysqld umshastr 19729 130 0.1 9293828 393564 pts/7 Sl 12:02 25:59 bin/mysqld --defaults-file=./master1.cnf umshastr 21604 88.2 0.1 3003840 457508 pts/7 Sl 12:22 0:14 bin/mysqld --defaults-file=./slave1.cnf umshastr 21638 0.0 0.0 112632 968 pts/7 S+ 12:22 0:00 grep --color=auto mysqld [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: -- restart slave and check slave status *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 606610180 Relay_Log_File: relay-log-1.000010 Relay_Log_Pos: 4475 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1440 Last_Error: Error 'XAER_DUPID: The XID already exists' on query. Default database: 'test'. Query: 'XA START X'39303737',X'',1' Skip_Counter: 0 Exec_Master_Log_Pos: 488897436 Relay_Log_Space: 117717422 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1440 Last_SQL_Error: Error 'XAER_DUPID: The XID already exists' on query. Default database: 'test'. Query: 'XA START X'39303737',X'',1' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 222d0845-8c75-11e6-9751-0010e05f3e06 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 161007 12:22:18 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 222d0845-8c75-11e6-9751-0010e05f3e06:1237424-1547468 Executed_Gtid_Set: 222d0845-8c75-11e6-9751-0010e05f3e06:1-1237434 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)