225 lines
13 KiB
Plaintext
225 lines
13 KiB
Plaintext
include/master-slave.inc
|
|
Warnings:
|
|
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
|
|
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
|
|
[connection master]
|
|
drop table if exists test.t1;
|
|
reset master;
|
|
create table test.t1(a int) engine = XENGINE;
|
|
drop table test.t1;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
slave-bin.000001 # Query # # use `test`; create table test.t1(a int) engine = XENGINE
|
|
slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
|
|
"============ Performance schema on master ============"
|
|
select
|
|
substring(file_name, locate("master-", file_name)) as FILE_NAME,
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_instance
|
|
where file_name like "%master-%" order by file_name;
|
|
FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
master-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
master-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
select * from performance_schema.file_summary_by_instance
|
|
where file_name like "%slave-%" order by file_name;
|
|
FILE_NAME EVENT_NAME OBJECT_INSTANCE_BEGIN COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ SUM_TIMER_READ MIN_TIMER_READ AVG_TIMER_READ MAX_TIMER_READ SUM_NUMBER_OF_BYTES_READ COUNT_WRITE SUM_TIMER_WRITE MIN_TIMER_WRITE AVG_TIMER_WRITE MAX_TIMER_WRITE SUM_NUMBER_OF_BYTES_WRITE COUNT_MISC SUM_TIMER_MISC MIN_TIMER_MISC AVG_TIMER_MISC MAX_TIMER_MISC
|
|
"Expect a master binlog + binlog_index"
|
|
select
|
|
substring(file_name, locate("master-", file_name)) as FILE_NAME,
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_instance
|
|
where event_name like "%binlog%" order by file_name;
|
|
FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
master-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
master-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
select
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_event_name
|
|
where event_name like "%binlog%" order by event_name;
|
|
EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
wait/io/file/sql/binlog_cache NONE NONE NONE NONE
|
|
wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
wait/io/file/sql/binlog_index_cache NONE NONE NONE NONE
|
|
select
|
|
EVENT_NAME,
|
|
if (count_star > 0, "MANY", "NONE") as COUNT_STAR
|
|
from performance_schema.events_waits_summary_global_by_event_name
|
|
where event_name like "%MYSQL_BIN_LOG%"
|
|
and event_name not like "%MYSQL_BIN_LOG::update_cond"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done NONE
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::prep_xids_cond NONE
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids NONE
|
|
"Expect no slave relay log"
|
|
select * from performance_schema.file_summary_by_instance
|
|
where event_name like "%relaylog%" order by file_name;
|
|
FILE_NAME EVENT_NAME OBJECT_INSTANCE_BEGIN COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ SUM_TIMER_READ MIN_TIMER_READ AVG_TIMER_READ MAX_TIMER_READ SUM_NUMBER_OF_BYTES_READ COUNT_WRITE SUM_TIMER_WRITE MIN_TIMER_WRITE AVG_TIMER_WRITE MAX_TIMER_WRITE SUM_NUMBER_OF_BYTES_WRITE COUNT_MISC SUM_TIMER_MISC MIN_TIMER_MISC AVG_TIMER_MISC MAX_TIMER_MISC
|
|
select * from performance_schema.file_summary_by_event_name
|
|
where event_name like "%relaylog%" order by event_name;
|
|
EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ SUM_TIMER_READ MIN_TIMER_READ AVG_TIMER_READ MAX_TIMER_READ SUM_NUMBER_OF_BYTES_READ COUNT_WRITE SUM_TIMER_WRITE MIN_TIMER_WRITE AVG_TIMER_WRITE MAX_TIMER_WRITE SUM_NUMBER_OF_BYTES_WRITE COUNT_MISC SUM_TIMER_MISC MIN_TIMER_MISC AVG_TIMER_MISC MAX_TIMER_MISC
|
|
wait/io/file/sql/relaylog 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
|
|
wait/io/file/sql/relaylog_cache 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
|
|
wait/io/file/sql/relaylog_index 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
|
|
wait/io/file/sql/relaylog_index_cache 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
|
|
select * from performance_schema.events_waits_summary_global_by_event_name
|
|
where event_name like "%MYSQL_RELAY_LOG%"
|
|
and event_name not like "%MYSQL_RELAY_LOG::update_cond"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_done 0 0 0 0 0
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::prep_xids_cond 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log_end_pos 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_xids 0 0 0 0 0
|
|
"============ Performance schema on slave ============"
|
|
select * from performance_schema.file_summary_by_instance
|
|
where file_name like "%master-%" order by file_name;
|
|
FILE_NAME EVENT_NAME OBJECT_INSTANCE_BEGIN COUNT_STAR SUM_TIMER_WAIT MIN_TIMER_WAIT AVG_TIMER_WAIT MAX_TIMER_WAIT COUNT_READ SUM_TIMER_READ MIN_TIMER_READ AVG_TIMER_READ MAX_TIMER_READ SUM_NUMBER_OF_BYTES_READ COUNT_WRITE SUM_TIMER_WRITE MIN_TIMER_WRITE AVG_TIMER_WRITE MAX_TIMER_WRITE SUM_NUMBER_OF_BYTES_WRITE COUNT_MISC SUM_TIMER_MISC MIN_TIMER_MISC AVG_TIMER_MISC MAX_TIMER_MISC
|
|
select
|
|
substring(file_name, locate("slave-", file_name)) as FILE_NAME,
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_instance
|
|
where file_name like "%slave-%"
|
|
and (file_name not like "%slave-relay-bin.0%"
|
|
or file_name like "%slave-relay-bin.000001"
|
|
or file_name like "%slave-relay-bin.000002")
|
|
order by file_name;
|
|
FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
slave-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
slave-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
slave-relay-bin.000001 wait/io/file/sql/relaylog MANY MANY MANY MANY
|
|
slave-relay-bin.000002 wait/io/file/sql/relaylog MANY MANY MANY MANY
|
|
slave-relay-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
"Expect a slave binlog + binlog_index"
|
|
select
|
|
substring(file_name, locate("slave-", file_name)) as FILE_NAME,
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_instance
|
|
where event_name like "%binlog%" order by file_name;
|
|
FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
slave-bin.000001 wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
slave-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
slave-relay-bin.index wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
select
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_event_name
|
|
where event_name like "%binlog%" order by event_name;
|
|
EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
wait/io/file/sql/binlog MANY MANY MANY MANY
|
|
wait/io/file/sql/binlog_cache NONE NONE NONE NONE
|
|
wait/io/file/sql/binlog_index MANY NONE MANY NONE
|
|
wait/io/file/sql/binlog_index_cache NONE NONE NONE NONE
|
|
select
|
|
EVENT_NAME,
|
|
if (count_star > 0, "MANY", "NONE") as COUNT_STAR
|
|
from performance_schema.events_waits_summary_global_by_event_name
|
|
where event_name like "%MYSQL_BIN_LOG%"
|
|
and event_name not like "%MYSQL_BIN_LOG::update_cond"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done NONE
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::prep_xids_cond NONE
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids MANY
|
|
"Expect a slave relay log"
|
|
select
|
|
substring(file_name, locate("slave-", file_name)) as FILE_NAME,
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_instance
|
|
where event_name like "%relaylog%"
|
|
and (file_name not like "%slave-relay-bin.0%"
|
|
or file_name like "%slave-relay-bin.000001"
|
|
or file_name like "%slave-relay-bin.000002")
|
|
order by file_name;
|
|
FILE_NAME EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
slave-relay-bin.000001 wait/io/file/sql/relaylog MANY MANY MANY MANY
|
|
slave-relay-bin.000002 wait/io/file/sql/relaylog MANY MANY MANY MANY
|
|
select
|
|
EVENT_NAME,
|
|
if (count_read > 0, "MANY", "NONE") as COUNT_READ,
|
|
if (count_write > 0,"MANY", "NONE") as COUNT_WRITE,
|
|
if (sum_number_of_bytes_read > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_READ,
|
|
if (sum_number_of_bytes_write > 0, "MANY", "NONE") as SUM_NUMBER_OF_BYTES_WRITE
|
|
from performance_schema.file_summary_by_event_name
|
|
where event_name like "%relaylog%" order by event_name;
|
|
EVENT_NAME COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ SUM_NUMBER_OF_BYTES_WRITE
|
|
wait/io/file/sql/relaylog MANY MANY MANY MANY
|
|
wait/io/file/sql/relaylog_cache NONE NONE NONE NONE
|
|
wait/io/file/sql/relaylog_index MANY NONE NONE NONE
|
|
wait/io/file/sql/relaylog_index_cache NONE NONE NONE NONE
|
|
select
|
|
EVENT_NAME,
|
|
if (count_star > 0, "MANY", "NONE") as COUNT_STAR
|
|
from performance_schema.events_waits_summary_global_by_event_name
|
|
where event_name like "%MYSQL_RELAY_LOG%"
|
|
and event_name not like "%MYSQL_RELAY_LOG::update_cond"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_done NONE
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::prep_xids_cond NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index MANY
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log MANY
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log_end_pos MANY
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_xids MANY
|
|
include/rpl_end.inc
|