mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01: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>
97 lines
4.3 KiB
Text
97 lines
4.3 KiB
Text
select * from performance_schema.setup_instruments;
|
|
select * from performance_schema.setup_instruments
|
|
where name like 'Wait/Synch/Mutex/sql/%'
|
|
and name not in ('wait/synch/mutex/sql/DEBUG_SYNC::mutex')
|
|
order by name limit 10;
|
|
NAME ENABLED TIMED
|
|
wait/synch/mutex/sql/Ack_receiver::mutex YES YES
|
|
wait/synch/mutex/sql/Cversion_lock YES YES
|
|
wait/synch/mutex/sql/Delayed_insert::mutex YES YES
|
|
wait/synch/mutex/sql/Event_scheduler::LOCK_scheduler_state YES YES
|
|
wait/synch/mutex/sql/Gtid_index_writer::gtid_index_mutex YES YES
|
|
wait/synch/mutex/sql/gtid_waiting::LOCK_gtid_waiting YES YES
|
|
wait/synch/mutex/sql/hash_filo::lock YES YES
|
|
wait/synch/mutex/sql/HA_DATA_PARTITION::LOCK_auto_inc YES YES
|
|
wait/synch/mutex/sql/LOCK_active_mi YES YES
|
|
wait/synch/mutex/sql/LOCK_after_binlog_sync YES YES
|
|
select * from performance_schema.setup_instruments
|
|
where name like 'Wait/Synch/Rwlock/sql/%'
|
|
and name not in (
|
|
'wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock',
|
|
'wait/synch/rwlock/sql/LOCK_named_pipe_full_access_group')
|
|
order by name limit 10;
|
|
NAME ENABLED TIMED
|
|
wait/synch/rwlock/sql/LOCK_all_status_vars YES YES
|
|
wait/synch/rwlock/sql/LOCK_dbnames YES YES
|
|
wait/synch/rwlock/sql/LOCK_dboptions YES YES
|
|
wait/synch/rwlock/sql/LOCK_grant YES YES
|
|
wait/synch/rwlock/sql/LOCK_rmdir YES YES
|
|
wait/synch/rwlock/sql/LOCK_SEQUENCE YES YES
|
|
wait/synch/rwlock/sql/LOCK_ssl_refresh YES YES
|
|
wait/synch/rwlock/sql/LOCK_system_variables_hash YES YES
|
|
wait/synch/rwlock/sql/LOCK_sys_init_connect YES YES
|
|
wait/synch/rwlock/sql/LOCK_sys_init_slave YES YES
|
|
select * from performance_schema.setup_instruments
|
|
where name like 'Wait/Synch/Cond/sql/%'
|
|
and name not in (
|
|
'wait/synch/cond/sql/COND_open',
|
|
'wait/synch/cond/sql/COND_handler_count',
|
|
'wait/synch/cond/sql/DEBUG_SYNC::cond',
|
|
'wait/synch/cond/sql/COND_socket_listener_active',
|
|
'wait/synch/cond/sql/COND_start_signal_handler')
|
|
order by name limit 10;
|
|
NAME ENABLED TIMED
|
|
wait/synch/cond/sql/Ack_receiver::cond YES YES
|
|
wait/synch/cond/sql/COND_binlog_send YES YES
|
|
wait/synch/cond/sql/COND_flush_thread_cache YES YES
|
|
wait/synch/cond/sql/COND_group_commit_orderer YES YES
|
|
wait/synch/cond/sql/COND_gtid_ignore_duplicates YES YES
|
|
wait/synch/cond/sql/COND_manager YES YES
|
|
wait/synch/cond/sql/COND_parallel_entry YES YES
|
|
wait/synch/cond/sql/COND_prepare_ordered YES YES
|
|
wait/synch/cond/sql/COND_queue_state YES YES
|
|
wait/synch/cond/sql/COND_rpl_thread YES YES
|
|
select * from performance_schema.setup_instruments
|
|
where name='Wait';
|
|
select * from performance_schema.setup_instruments
|
|
where enabled='YES';
|
|
insert into performance_schema.setup_instruments
|
|
set name='FOO', enabled='YES', timed='YES';
|
|
ERROR 42000: INSERT command denied to user 'root'@'localhost' for table `performance_schema`.`setup_instruments`
|
|
update performance_schema.setup_instruments
|
|
set name='FOO';
|
|
ERROR HY000: Invalid performance_schema usage
|
|
update performance_schema.setup_instruments
|
|
set enabled='NO';
|
|
update performance_schema.setup_instruments
|
|
set timed='NO';
|
|
select * from performance_schema.setup_instruments;
|
|
update performance_schema.setup_instruments
|
|
set enabled='YES', timed='YES';
|
|
delete from performance_schema.setup_instruments;
|
|
ERROR 42000: DELETE command denied to user 'root'@'localhost' for table `performance_schema`.`setup_instruments`
|
|
delete from performance_schema.setup_instruments
|
|
where name like 'Wait/Synch/%';
|
|
ERROR 42000: DELETE command denied to user 'root'@'localhost' for table `performance_schema`.`setup_instruments`
|
|
LOCK TABLES performance_schema.setup_instruments READ;
|
|
UNLOCK TABLES;
|
|
LOCK TABLES performance_schema.setup_instruments WRITE;
|
|
UNLOCK TABLES;
|
|
#
|
|
# Bug#13813193 ASSERTION `TABLE->READ_SET ==
|
|
# &TABLE->DEF_READ_SET' FAILED / MYSQL_UPDATE
|
|
#
|
|
UPDATE performance_schema.setup_instruments SET timed='NO'
|
|
ORDER BY RAND();
|
|
update performance_schema.setup_instruments
|
|
set enabled='YES', TIMED='YES';
|
|
#
|
|
# MDEV-25325 column_comment for performance_schema tables
|
|
#
|
|
select column_name, column_comment
|
|
from information_schema.columns
|
|
where table_schema='performance_schema' and table_name='setup_instruments';
|
|
column_name column_comment
|
|
NAME Instrument name
|
|
ENABLED Whether or not the instrument is enabled. It can be disabled, and the instrument will produce no events.
|
|
TIMED Whether or not the instrument is timed. It can be set, but if disabled, events produced by the instrument will have NULL values for the corresponding TIMER_START, TIMER_END, and TIMER_WAIT values.
|