######### multi_source.sh and related files obtained from: https://github.com/datacharmer/mysql-replication-samples/tree/master/multi_source -- Extracted binary tarball and kept /export/umesh/sandbox/mysql-sandbox-master with name mysql-5.7.17 [umshastr@hod03]/export/umesh/sandbox/mysql-sandbox-master:tar -zxvf /export/umesh/sandbox/mysql-sandbox-master/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz [umshastr@hod03]/export/umesh/sandbox/mysql-sandbox-master:mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql-5.7.17 [umshastr@hod03]/export/umesh/sandbox/mysql-sandbox-master: ./multi_source.sh mysql-5.7.17 mysql ALL-MASTERS ################## -- Terminal 1 [umshastr@hod03]/export/umesh/sandbox/mysql-sandbox-master: ./multi_source.sh mysql-5.7.17 mysql ALL-MASTERS installing node 1 installing node 2 installing node 3 installing node 4 group directory installed in $HOME/sandboxes/multi_msb_mysql-5_7_17 # server: 1: # server: 2: # server: 3: # server: 4: # option 'master-info-repository=table' added to node1 configuration file # option 'relay-log-info-repository=table' added to node1 configuration file # option 'gtid_mode=ON' added to node1 configuration file # option 'enforce-gtid-consistency' added to node1 configuration file # option 'master-info-repository=table' added to node2 configuration file # option 'relay-log-info-repository=table' added to node2 configuration file # option 'gtid_mode=ON' added to node2 configuration file # option 'enforce-gtid-consistency' added to node2 configuration file # option 'master-info-repository=table' added to node3 configuration file # option 'relay-log-info-repository=table' added to node3 configuration file # option 'gtid_mode=ON' added to node3 configuration file # option 'enforce-gtid-consistency' added to node3 configuration file # option 'master-info-repository=table' added to node4 configuration file # option 'relay-log-info-repository=table' added to node4 configuration file # option 'gtid_mode=ON' added to node4 configuration file # option 'enforce-gtid-consistency' added to node4 configuration file # executing "stop" on /home/umshastr/sandboxes/multi_msb_mysql-5_7_17 executing "stop" on node 1 executing "stop" on node 2 executing "stop" on node 3 executing "stop" on node 4 # executing "start" on /home/umshastr/sandboxes/multi_msb_mysql-5_7_17 executing "start" on node 1 . sandbox server started executing "start" on node 2 . sandbox server started executing "start" on node 3 . sandbox server started executing "start" on node 4 . sandbox server started # Setting topology ALL-MASTERS # node node1 -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14419, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2' -------------- -------------- START SLAVE for channel 'node2' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14420, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3' -------------- -------------- START SLAVE for channel 'node3' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14421, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4' -------------- -------------- START SLAVE for channel 'node4' -------------- # node node2 -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14418, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1' -------------- -------------- START SLAVE for channel 'node1' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14420, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3' -------------- -------------- START SLAVE for channel 'node3' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14421, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4' -------------- -------------- START SLAVE for channel 'node4' -------------- # node node3 -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14418, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1' -------------- -------------- START SLAVE for channel 'node1' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14419, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2' -------------- -------------- START SLAVE for channel 'node2' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14421, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node4' -------------- -------------- START SLAVE for channel 'node4' -------------- # node node4 -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14418, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node1' -------------- -------------- START SLAVE for channel 'node1' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14419, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node2' -------------- -------------- START SLAVE for channel 'node2' -------------- -------------- CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=14420, MASTER_USER='rsandbox', MASTER_PASSWORD='rsandbox', MASTER_AUTO_POSITION=1 for channel 'node3' -------------- -------------- START SLAVE for channel 'node3' -------------- ‘/export/umesh/sandbox/mysql-sandbox-master/test_all_masters_replication.sh’ -> ‘/home/umshastr/sandboxes/multi_msb_mysql-5_7_17/test_all_masters_replication.sh’ [umshastr@hod03]/export/umesh/sandbox/mysql-sandbox-master: cd $HOME/sandboxes/multi_msb_mysql-5_7_17 [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n1 -e "stop slave for channel 'node3'; reset slave all for channel 'node3';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n1 -e "stop slave for channel 'node4'; reset slave all for channel 'node4';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n2 -e "stop slave for channel 'node3'; reset slave all for channel 'node3';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n2 -e "stop slave for channel 'node4'; reset slave all for channel 'node4';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n3 -e "stop slave for channel 'node4'; reset slave all for channel 'node4';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n4 -e "stop slave for channel 'node3'; reset slave all for channel 'node3';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n4 -e "stop slave for channel 'node2'; reset slave all for channel 'node2';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n4 -e "stop slave for channel 'node1'; reset slave all for channel 'node1';" [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: for i in {1..3}; do echo "binlog_rows_query_log_events=1" >> node$i/my.sandbox.cnf ; done [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: for i in {1..3}; do echo "performance-schema-instrument='memory%=ON' " >> node$i/my.sandbox.cnf ; done [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: for i in {1..3}; do echo "log_slave_updates=1" >> node$i/my.sandbox.cnf ; done [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./restart_all # executing "stop" on /home/umshastr/sandboxes/multi_msb_mysql-5_7_17 executing "stop" on node 1 executing "stop" on node 2 executing "stop" on node 3 executing "stop" on node 4 # executing "start" on /home/umshastr/sandboxes/multi_msb_mysql-5_7_17 executing "start" on node 1 . sandbox server started executing "start" on node 2 . sandbox server started executing "start" on node 3 . sandbox server started executing "start" on node 4 . sandbox server started [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./use_all "SELECT * FROM performance_schema.replication_connection_status\G"; # server: 1: *************************** 1. row *************************** CHANNEL_NAME: node2 GROUP_NAME: SOURCE_UUID: 00014419-2222-2222-2222-222222222222 THREAD_ID: NULL SERVICE_STATE: CONNECTING COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 2003 LAST_ERROR_MESSAGE: error connecting to master 'rsandbox@127.0.0.1:14419' - retry-time: 60 retries: 1 LAST_ERROR_TIMESTAMP: 2017-03-09 10:20:18 # server: 2: *************************** 1. row *************************** CHANNEL_NAME: node1 GROUP_NAME: SOURCE_UUID: 00014418-1111-1111-1111-111111111111 THREAD_ID: 26 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 # server: 3: *************************** 1. row *************************** CHANNEL_NAME: node1 GROUP_NAME: SOURCE_UUID: 00014418-1111-1111-1111-111111111111 THREAD_ID: 26 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row *************************** CHANNEL_NAME: node2 GROUP_NAME: SOURCE_UUID: 00014419-2222-2222-2222-222222222222 THREAD_ID: 27 SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 # server: 4: [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./use_all "select * from sys.memory_global_total"; # server: 1: total_allocated 324.57 MiB # server: 2: total_allocated 324.56 MiB # server: 3: total_allocated 324.70 MiB # server: 4: total_allocated 131.31 MiB [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ps aux|grep multi_msb_mysql5_7_17|grep .sock|awk '{print "vsz: "$5/1024" MB","rss: "$6/1024" MB",$19}' [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ps aux|grep multi_msb_mysql-5_7_17|grep .sock|awk '{print "vsz: "$5/1024" MB","rss: "$6/1024" MB",$19}' vsz: 1534.56 MB rss: 172.938 MB --port=14418 vsz: 1470.34 MB rss: 172.934 MB --port=14419 vsz: 1534.56 MB rss: 173.191 MB --port=14420 vsz: 1277.54 MB rss: 172.953 MB --port=14421 [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n1 -e "create database sbtest1;" -- Terminal 2 [umshastr@hod03]~/bugs/sysbench: sysbench/sysbench --num-threads=16 --max-requests=300000 --db-driver=mysql --test=/home/umshastr/bugs/sysbench/sysbench/tests/db/oltp.lua --mysql-table-engine=InnoDB --mysql-db=sbtest1 --mysql-user=msandbox --mysql-password=msandbox --oltp-table-size=1000000 --mysql-socket=/tmp/mysql_sandbox14418.sock prepare sysbench 0.5: multi-threaded system evaluation benchmark Creating table 'sbtest1'... Inserting 1000000 records into 'sbtest1' [umshastr@hod03]~/bugs/sysbench: [umshastr@hod03]~/bugs/sysbench: sysbench/sysbench --num-threads=16 --max-requests=300000 --db-driver=mysql --test=/home/umshastr/bugs/sysbench/sysbench/tests/db/oltp.lua --mysql-table-engine=InnoDB --mysql-db=sbtest1 --mysql-user=msandbox --mysql-password=msandbox --oltp-table-size=1000000 --mysql-socket=/tmp/mysql_sandbox14418.sock run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Random number generator seed is 0 and will be ignored Threads started! OLTP test statistics: queries performed: read: 4205670 write: 1201031 other: 600583 total: 6007284 transactions: 300178 (1550.08 per sec.) deadlocks: 227 (1.17 per sec.) read/write requests: 5406701 (27919.49 per sec.) other operations: 600583 (3101.33 per sec.) General statistics: total time: 193.6533s total number of events: 300178 total time taken by event execution: 3097.8003s response time: min: 3.29ms avg: 10.32ms max: 4091.63ms approx. 95 percentile: 36.39ms Threads fairness: events (avg/stddev): 18761.1250/31.44 execution time (avg/stddev): 193.6125/0.00 [umshastr@hod03]~/bugs/sysbench: -- Terminal 1 -- Wait for replication to catch up [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./use_all -e "show slave status\G"|grep Seconds_Behind_Master Seconds_Behind_Master: 0 Seconds_Behind_Master: 0 Seconds_Behind_Master: 0 Seconds_Behind_Master: 0 [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./use_all "select * from sys.memory_global_total"; # server: 1: total_allocated 340.86 MiB # server: 2: total_allocated 335.73 MiB # server: 3: total_allocated 1.35 GiB # server: 4: total_allocated 131.31 MiB [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ps aux|grep multi_msb_mysql-5_7_17|grep .sock|awk '{print "vsz: "$5/1024" MB","rss: "$6/1024" MB",$19}' vsz: 2947.37 MB rss: 344.883 MB --port=14418 vsz: 1790.6 MB rss: 300.223 MB --port=14419 vsz: 2931.65 MB rss: 1512.35 MB --port=14420 vsz: 1277.54 MB rss: 173.277 MB --port=14421 [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n3 -e 'select event_name, high_number_of_bytes_used/1024/1024 high_number_of_bytes_used_MB from performance_schema.memory_summary_global_by_event_name order by high_number_of_bytes_used desc limit 10;' +-----------------------------------------------------------------------------+------------------------------+ | event_name | high_number_of_bytes_used_MB | +-----------------------------------------------------------------------------+------------------------------+ | memory/sql/Log_event | 1041.97299862 | | memory/innodb/buf_buf_pool | 131.06250000 | | memory/innodb/log0log | 32.00839996 | | memory/sql/XID | 19.00152588 | | memory/performance_schema/events_statements_history_long | 13.65661621 | | memory/performance_schema/events_statements_summary_by_digest.tokens | 9.76562500 | | memory/performance_schema/events_statements_history_long.tokens | 9.76562500 | | memory/performance_schema/events_statements_history_long.sqltext | 9.76562500 | | memory/performance_schema/table_handles | 9.06250000 | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.66992188 | +-----------------------------------------------------------------------------+------------------------------+ [umshastr@hod03]~/sandboxes/multi_msb_mysql-5_7_17: ./n3 -e 'select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes limit 10;' +-----+--------------------------+---------+------------+-----------+------------+-----------------+ | tid | user | ccu | ca | caa | cma | total_allocated | +-----+--------------------------+---------+------------+-----------+------------+-----------------+ | 28 | sql/slave_sql | 3429150 | 962.99 MiB | 294 bytes | 953.67 MiB | 18.45 GiB | | 1 | sql/main | 28263 | 189.39 MiB | 6.86 KiB | 131.06 MiB | 221.06 MiB | | 25 | sql/slave_sql | 775419 | 97.61 MiB | 132 bytes | 88.29 MiB | 26.72 GiB | | 48 | msandbox@localhost | 54 | 721.79 KiB | 13.37 KiB | 256.00 KiB | 755.51 KiB | | 26 | sql/slave_io | 532 | 610.49 KiB | 1.15 KiB | 528.01 KiB | 10.71 MiB | | 27 | sql/slave_io | 527 | 610.22 KiB | 1.16 KiB | 528.01 KiB | 10.70 MiB | | 24 | innodb/dict_stats_thread | 34 | 1.10 KiB | 33 bytes | 1.10 KiB | 7.35 MiB | | 23 | innodb/buf_dump_thread | 1 | 32 bytes | 32 bytes | 32 bytes | 480.95 KiB | | 3 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | | 4 | innodb/io_log_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | +-----+--------------------------+---------+------------+-----------+------------+-----------------+