mirror of
https://github.com/MariaDB/server.git
synced 2025-02-23 05:43:08 +01:00
25b5c63905
This commit adds 3 new status variables to 'show all slaves status':
- Master_last_event_time ; timestamp of the last event read from the
master by the IO thread.
- Slave_last_event_time ; Master timestamp of the last event committed
on the slave.
- Master_Slave_time_diff: The difference of the above two timestamps.
All the above variables are NULL until the slave has started and the
slave has read one query event from the master that changes data.
- Added information_schema.slave_status, which allows us to remove:
- show_master_info(), show_master_info_get_fields(),
send_show_master_info_data(), show_all_master_info()
- class Sql_cmd_show_slave_status.
- Protocol::store(I_List<i_string_pair>* str_list) as it is not
used anymore.
- Changed old SHOW SLAVE STATUS and SHOW ALL SLAVES STATUS to
use the SELECT code path, as all other SHOW ... STATUS commands.
Other things:
- Xid_log_time is set to time of commit to allow slave that reads the
binary log to calculate Master_last_event_time and
Slave_last_event_time.
This is needed as there is not 'exec_time' for row events.
- Fixed that Load_log_event calculates exec_time identically to
Query_event.
- Updated RESET SLAVE to reset Master/Slave_last_event_time
- Updated SQL thread's update on first transaction read-in to
only update Slave_last_event_time on group events.
- Fixed possible (unlikely) bugs in sql_show.cc ...old_format() functions
if allocation of 'field' would fail.
Reviewed By:
Brandon Nesterenko <brandon.nesterenko@mariadb.com>
Kristian Nielsen <knielsen@knielsen-hq.org>
209 lines
12 KiB
Text
209 lines
12 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
drop table if exists test.t1;
|
|
connection slave;
|
|
reset master;
|
|
create table test.t1(a int);
|
|
drop table test.t1;
|
|
include/show_binlog_events.inc
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
slave-bin.000001 # Gtid # # GTID #-#-#
|
|
slave-bin.000001 # Query # # use `test`; create table test.t1(a int)
|
|
slave-bin.000001 # Gtid # # GTID #-#-#
|
|
slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
|
|
connection 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-%" and file_name not like "%sql/share%" 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.000001.idx wait/io/file/sql/gtid_index NONE MANY NONE MANY
|
|
master-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY
|
|
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 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 "%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 MANY MANY MANY
|
|
wait/io/file/sql/binlog_index_cache NONE NONE NONE NONE
|
|
wait/io/file/sql/binlog_state 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"
|
|
and event_name not like "%MYSQL_BIN_LOG::COND_binlog_background_thread%"
|
|
and event_name not like "%MYSQL_BIN_LOG::COND_xid_list"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_bin_log_updated MANY
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_queue_busy NONE
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_relay_log_updated NONE
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_background_thread MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xid_list MANY
|
|
"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_bin_log_updated 0 0 0 0 0
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_queue_busy 0 0 0 0 0
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_relay_log_updated 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_binlog_end_pos 0 0 0 0 0
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index 0 0 0 0 0
|
|
connection slave;
|
|
"============ 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.000001.idx wait/io/file/sql/gtid_index NONE MANY NONE MANY
|
|
slave-bin.index wait/io/file/sql/binlog_index MANY MANY MANY MANY
|
|
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/relaylog_index MANY MANY MANY MANY
|
|
"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 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 "%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 MANY MANY MANY
|
|
wait/io/file/sql/binlog_index_cache NONE NONE NONE NONE
|
|
wait/io/file/sql/binlog_state 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"
|
|
and event_name not like "%MYSQL_BIN_LOG::COND_binlog_background_thread%"
|
|
and event_name not like "%MYSQL_BIN_LOG::COND_xid_list"
|
|
order by event_name;
|
|
EVENT_NAME COUNT_STAR
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_bin_log_updated NONE
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_queue_busy NONE
|
|
wait/synch/cond/sql/MYSQL_BIN_LOG::COND_relay_log_updated NONE
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_background_thread MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY
|
|
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xid_list 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
|
|
slave-relay-bin.index wait/io/file/sql/relaylog_index 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 MANY MANY MANY
|
|
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_bin_log_updated NONE
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_queue_busy NONE
|
|
wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_relay_log_updated MANY
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_binlog_end_pos NONE
|
|
wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index MANY
|
|
include/stop_slave.inc
|