Debian Codegraph @

mysql-test/suite/ndbcrunch/replicate_mysqld2.inc

raw
1 #echo MYSQLD1_PORT $MYSQLD1_PORT;
2 #echo MYSQLD1_SOCK $MYSQLD1_SOCK;
3
4 #query_vertical SHOW MASTER STATUS;
5 #SHOW REPLICAS; # list of replicas currently registered with the source
6
7 #echo MYSQLD2_PORT $MYSQLD2_PORT;
8 #echo MYSQLD2_SOCK $MYSQLD2_SOCK;
9
10 --echo # Save source binlog position
11 let $source_file= query_get_value(SHOW MASTER STATUS, File, 1);
12 let $source_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
13 #echo source_file: $source_file;
14 #echo source_pos: $source_pos;
15
16 --echo ## Connect to second mysqld, then start replicating from first mysqld
17 --connect(mysqld2,127.0.0.1,root,,test,$MYSQLD2_PORT,)
18
19 eval
20 CHANGE REPLICATION SOURCE TO
21 SOURCE_HOST='127.0.0.1',
22 SOURCE_PORT=$MYSQLD1_PORT;
23
24 #query_vertical SHOW REPLICA STATUS;
25
26 START REPLICA IO_THREAD
27 USER = 'root'
28 PASSWORD = '';
29
30 --echo #
31 --echo # Wait for IO thread to fetch all binlog
32 --echo #
33 let $wait = 1;
34 while ($wait) {
35 sleep 0.1;
36 #query_vertical SHOW REPLICA STATUS;
37 let $io_file= query_get_value(SHOW REPLICA STATUS, Source_Log_File, 1);
38 let $io_pos= query_get_value(SHOW REPLICA STATUS, Read_Source_Log_Pos, 1);
39 #echo io_file: $io_file;
40 #echo io_pos: $io_pos;
41
42 if ($io_file == $source_file) {
43 if ($io_pos == $source_pos) {
44 --echo The IO thread has fetched all binlog!
45 let $wait = 0;
46 }
47 }
48 }
49
50 --disable_query_log
51 CREATE TEMPORARY TABLE counters (name varchar(100));
52 INSERT INTO counters VALUES
53 ('Ndb_api_bytes_sent_count_replica'), /* number of bytes sent */
54 ('Ndb_api_bytes_received_count_replica'), /* number of bytes received */
55 ('Ndb_api_pk_op_count_replica'), /* primary key operations */
56 ('Ndb_api_trans_commit_count_replica'), /* commits in NDB */
57 ('Ndb_api_wait_exec_complete_count_replica'), /* roundtrips to NDB */
58
59 ('Ndb_api_wait_nanos_count_replica'), /* wait for NDB */
60 ('Ndb_replica_trans_apply_nanos'), /* time applying */
61 ('Ndb_replica_trans_gap_nanos'); /* wait for server */
62
63 CREATE TEMPORARY TABLE stats (
64 name varchar(64) PRIMARY KEY,
65 start BIGINT,
66 end BIGINT);
67
68 # All replica counters are zero before replica has started, thus it's necessary
69 # to first start the replica and then wait for counters to start updating
70 #SELECT * FROM performance_schema.global_status
71 # WHERE VARIABLE_NAME IN (SELECT name from counters) AND
72 # VARIABLE_VALUE != 0;
73
74 --enable_query_log
75
76 START REPLICA SQL_THREAD;
77
78 # Wait for replica to start updating counters
79 # NOTE! The replica uses "copy out" for its counter values
80 let $wait = 1;
81 while ($wait) {
82 if (`SELECT VARIABLE_VALUE FROM performance_schema.global_status
83 WHERE VARIABLE_NAME = 'Ndb_api_trans_commit_count_replica'`) {
84 --echo Detected first replica commit;
85 let $wait = 0;
86
87 # Also wait for Ndb_replica_trans_apply_nanos which are slower to
88 # start updating (NOTE! usually need several epochs before first
89 # measurement shows, should only need two?)
90 #if (`SELECT VARIABLE_VALUE FROM performance_schema.global_status
91 # WHERE VARIABLE_NAME = 'Ndb_replica_trans_apply_nanos'`) {
92 # --echo Detected Ndb_replica_trans_apply_nanos measurement;
93 # let $wait = 0;
94 #}
95 }
96 if ($wait) {
97 sleep 0.1;
98 }
99 }
100
101 # Collect counters
102 let $start_time = `SELECT NOW(6)`;
103 --disable_query_log ONCE
104 INSERT INTO stats
105 SELECT VARIABLE_NAME AS name, VARIABLE_VALUE as start, 0 AS end
106 FROM performance_schema.global_status
107 WHERE VARIABLE_NAME IN (SELECT name from counters);
108 #SELECT * FROM stats;
109
110 --echo #
111 --echo # Wait for SQL thread to apply binlog
112 --echo #
113 let $wait = 1;
114 while ($wait) {
115 sleep 0.1;
116 #query_vertical SHOW REPLICA STATUS;
117 let $sql_file= query_get_value(SHOW REPLICA STATUS, Relay_Source_Log_File, 1);
118 let $sql_pos= query_get_value(SHOW REPLICA STATUS, Exec_Source_Log_Pos, 1);
119 #echo sql_file: $sql_file;
120 #echo sql_pos: $sql_pos;
121
122 if ($sql_file == $source_file) {
123 if ($sql_pos == $source_pos) {
124 --echo The SQL thread has fetched all binlog!
125 let $wait = 0;
126 }
127 }
128 }
129
130 STOP REPLICA;
131
132 # Collect end counters
133 let $end_time = `SELECT NOW(6)`;
134 --disable_query_log ONCE
135 UPDATE stats,
136 (SELECT * FROM performance_schema.global_status
137 WHERE VARIABLE_NAME IN (SELECT name from counters)) AS b
138 SET end=b.VARIABLE_VALUE WHERE name=b.VARIABLE_NAME;
139 #SELECT * FROM stats;
140
141 if ($CRUNCH_STATS)
142 {
143 # Dump the collected stats
144 SELECT * FROM stats;
145
146 # #########################
147 # Calculate and print stats
148
149 # Sample period
150 let $sample_period = `
151 SELECT TIMEDIFF('$end_time', '$start_time') as 'elapsed time'`;
152 echo sample_period: $sample_period;
153
154 # transactions_applied (epochs)
155 let $transactions_applied = `SELECT end-start FROM stats
156 WHERE name = 'Ndb_api_trans_commit_count_replica'`;
157 echo transactions_applied: $transactions_applied;
158
159 # bytes
160 let $bytes_sent = `SELECT (end-start) / 1024 / 1024 FROM stats
161 WHERE name = 'Ndb_api_bytes_sent_count_replica'`;
162 echo bytes_sent (MB): $bytes_sent;
163
164 # apply_time
165 let $apply_time = `
166 SELECT (end-start) / 1000000 AS 'transaction apply time (ms)'
167 FROM stats WHERE name = 'Ndb_replica_trans_apply_nanos'`;
168 echo apply_time(ms): $apply_time;
169
170 # gap_time
171 let $gap_time = `
172 SELECT (end-start) / 1000000 AS 'inter-transaction gap (ms)'
173 FROM stats WHERE name = 'Ndb_replica_trans_gap_nanos'`;
174 echo gap_time(ms): $gap_time;
175
176 # total_time = apply_time+gap_time (~ Sample period)
177 # let $total_time = `SELECT $apply_time + $gap_time`;
178 # echo total_time (ms): $total_time;
179
180 # api_wait_time
181 let $api_wait_time = `
182 SELECT (end-start) / 1000000 AS 'NdbApi wait time per transaction'
183 FROM stats WHERE name = 'Ndb_api_wait_nanos_count_replica'`;
184 echo api_wait_time (ms): $api_wait_time;
185 echo;
186
187 #
188 # Averages
189 #
190 #avg_apply_time = apply_time / transactions_applied
191 # let $avg_apply_time = `
192 # SELECT $apply_time / $transactions_applied`;
193 # echo avg_apply_time (ms): $avg_apply_time;
194
195 #avg_gap_time = gap_time / transactions_applied
196 # let $avg_gap_time = `
197 # SELECT $gap_time / $transactions_applied`;
198 # echo avg_gap_time (ms): $avg_gap_time;
199
200 #avg_api_work_time = api_wait_time / transactions_applied
201 let $avg_api_work_time = `
202 SELECT $api_wait_time / $transactions_applied`;
203 echo avg_api_work_time (ms): $avg_api_work_time;
204 echo;
205
206 #
207 # Percent
208 #
209 #apply_api_wait_percent = 100 * (api_wait_time / apply_time)
210 # let $apply_api_wait_percent = `
211 # SELECT 100* ($api_wait_time / $apply_time)`;
212 # echo apply_api_wait_percent: $apply_api_wait_percent;
213
214 #apply_work_percent = 100 - apply_api_wait_percent
215 # let $apply_work_percent = `
216 # SELECT 100 - $apply_api_wait_percent`;
217 # echo apply_work_percent: $apply_work_percent;
218
219 }
220
221 connection default;
222