mirror of
https://github.com/MariaDB/server.git
synced 2025-04-05 23:05:34 +02:00
d039346a7a
Improve the performance of slave connect using B+-Tree indexes on each binlog file. The index allows fast lookup of a GTID position to the corresponding offset in the binlog file, as well as lookup of a position to find the corresponding GTID position. This eliminates a costly sequential scan of the starting binlog file to find the GTID starting position when a slave connects. This is especially costly if the binlog file is not cached in memory (IO cost), or if it is encrypted or a lot of slaves connect simultaneously (CPU cost). The size of the index files is generally less than 1% of the binlog data, so not expected to be an issue. Most of the work writing the index is done as a background task, in the binlog background thread. This minimises the performance impact on transaction commit. A simple global mutex is used to protect index reads and (background) index writes; this is fine as slave connect is a relatively infrequent operation. Here are the user-visible options and status variables. The feature is on by default and is expected to need no tuning or configuration for most users. binlog_gtid_index On by default. Can be used to disable the indexes for testing purposes. binlog_gtid_index_page_size (default 4096) Page size to use for the binlog GTID index. This is the size of the nodes in the B+-tree used internally in the index. A very small page-size (64 is the minimum) will be less efficient, but can be used to stress the BTree-code during testing. binlog_gtid_index_span_min (default 65536) Control sparseness of the binlog GTID index. If set to N, at most one index record will be added for every N bytes of binlog file written. This can be used to reduce the number of records in the index, at the cost only of having to scan a few more events in the binlog file before finding the target position Two status variables are available to monitor the use of the GTID indexes: Binlog_gtid_index_hit Binlog_gtid_index_miss The "hit" status increments for each successful lookup in a GTID index. The "miss" increments when a lookup is not possible. This indicates that the index file is missing (eg. binlog written by old server version without GTID index support), or corrupt. Signed-off-by: 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-%" 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
|