MyRocks监控信息

xiaoxiao2026-10-08 0

title: MySQL · myrocks · myrocks监控信息

author: 张远

rocksdb本身提供了丰富的监控信息，myrocks通过information_schema下的表和show命令等将这些信息展示出来，下面主要以示例的形式来简单介绍下

先创建测试表

CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT ,PRIMARY KEY(pk) comment 'cf_1', key idx2(b) comment 'cf_2') engine=rocksdb;

SHOW STATUS

show status 也展示了部分rocksdb引擎的信息

show status like '%rock%'; +---------------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------------+------------------------------------------+ | rocksdb_rows_deleted | 0 | | rocksdb_rows_inserted | 1048579 | | rocksdb_rows_read | 3145755 | | rocksdb_rows_updated | 7 | | rocksdb_system_rows_deleted | 0 | | rocksdb_system_rows_inserted | 0 | | rocksdb_system_rows_read | 0 | | rocksdb_system_rows_updated | 0 | | rocksdb_block_cache_add | 16 | | rocksdb_block_cache_data_hit | 76 | | rocksdb_block_cache_data_miss | 6 | | rocksdb_block_cache_filter_hit | 0 | | rocksdb_block_cache_filter_miss | 6 | | rocksdb_block_cache_hit | 76 | | rocksdb_block_cache_index_hit | 0 | | rocksdb_block_cache_index_miss | 6 | | rocksdb_block_cache_miss | 18 | | rocksdb_block_cachecompressed_hit | 0 | | rocksdb_block_cachecompressed_miss | 0 | | rocksdb_bloom_filter_prefix_checked | 0 | | rocksdb_bloom_filter_prefix_useful | 0 | | rocksdb_bloom_filter_useful | 0 | | rocksdb_bytes_read | 13631762 | | rocksdb_bytes_written | 108009584 | | rocksdb_compact_read_bytes | 142 | | rocksdb_compact_write_bytes | 0 | | rocksdb_compaction_key_drop_new | 0 | | rocksdb_compaction_key_drop_obsolete | 4 | | rocksdb_compaction_key_drop_user | 4 | | rocksdb_flush_write_bytes | 7211 | | rocksdb_getupdatessince_calls | 0 | | rocksdb_git_date | %cI | | rocksdb_git_hash | bc5d7b70299b763127f3714055a63ebe7e04ad47 | | rocksdb_l0_num_files_stall_micros | 0 | | rocksdb_l0_slowdown_micros | 0 | | rocksdb_memtable_compaction_micros | 0 | | rocksdb_memtable_hit | 1048593 | | rocksdb_memtable_miss | 1048609 | | rocksdb_no_file_closes | 0 | | rocksdb_no_file_errors | 0 | | rocksdb_no_file_opens | 6 | | rocksdb_num_iterators | 0 | | rocksdb_number_block_not_compressed | 0 | | rocksdb_number_deletes_filtered | 0 | | rocksdb_number_keys_read | 2097202 | | rocksdb_number_keys_updated | 0 | | rocksdb_number_keys_written | 2097220 | | rocksdb_number_merge_failures | 0 | | rocksdb_number_multiget_bytes_read | 0 | | rocksdb_number_multiget_get | 0 | | rocksdb_number_multiget_keys_read | 0 | | rocksdb_number_reseeks_iteration | 0 | | rocksdb_number_sst_entry_delete | 12 | | rocksdb_number_sst_entry_merge | 0 | | rocksdb_number_sst_entry_other | 0 | | rocksdb_number_sst_entry_put | 30 | | rocksdb_number_sst_entry_singledelete | 0 | | rocksdb_number_stat_computes | 0 | | rocksdb_number_superversion_acquires | 21 | | rocksdb_number_superversion_cleanups | 1 | | rocksdb_number_superversion_releases | 1 | | rocksdb_rate_limit_delay_millis | 0 | | rocksdb_snapshot_conflict_errors | 0 | | rocksdb_wal_bytes | 54006676 | | rocksdb_wal_group_syncs | 0 | | rocksdb_wal_synced | 13 | | rocksdb_write_other | 0 | | rocksdb_write_self | 58 | | rocksdb_write_timedout | 0 | | rocksdb_write_wal | 58 | +---------------------------------------+------------------------------------------+

INFORMATION_SCHMEA

information_schema下rocksdb相关的表如下

select table_name from INFORMATION_SCHEMA.tables where table_name like '%rock%'; +-----------------------------+ | table_name | +-----------------------------+ | ROCKSDB_PERF_CONTEXT | | ROCKSDB_GLOBAL_INFO | | ROCKSDB_COMPACTION_STATS | | ROCKSDB_INDEX_FILE_MAP | | ROCKSDB_CF_OPTIONS | | ROCKSDB_PERF_CONTEXT_GLOBAL | | ROCKSDB_CFSTATS | | ROCKSDB_TRX | | ROCKSDB_DBSTATS | | ROCKSDB_DDL | | ROCKSDB_LOCKS | +-----------------------------+ 数据字典相关 show create table INFORMATION_SCHEMA.ROCKSDB_INDEX_FILE_MAP\G *************************** 1. row *************************** Table: ROCKSDB_INDEX_FILE_MAP Create Table: CREATE TEMPORARY TABLE `ROCKSDB_INDEX_FILE_MAP` ( `COLUMN_FAMILY` int(4) NOT NULL DEFAULT '0', `INDEX_NUMBER` int(4) NOT NULL DEFAULT '0', `SST_NAME` varchar(193) NOT NULL DEFAULT '', `NUM_ROWS` bigint(8) NOT NULL DEFAULT '0', `DATA_SIZE` bigint(8) NOT NULL DEFAULT '0', `ENTRY_DELETES` bigint(8) NOT NULL DEFAULT '0', `ENTRY_SINGLEDELETES` bigint(8) NOT NULL DEFAULT '0', `ENTRY_MERGES` bigint(8) NOT NULL DEFAULT '0', `ENTRY_OTHERS` bigint(8) NOT NULL DEFAULT '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table INFORMATION_SCHEMA.ROCKSDB_DDL\G *************************** 1. row *************************** Table: ROCKSDB_DDL Create Table: CREATE TEMPORARY TABLE `ROCKSDB_DDL` ( `TABLE_SCHEMA` varchar(193) NOT NULL DEFAULT '', `TABLE_NAME` varchar(193) NOT NULL DEFAULT '', `PARTITION_NAME` varchar(193) DEFAULT NULL, `INDEX_NAME` varchar(193) NOT NULL DEFAULT '', `COLUMN_FAMILY` int(4) NOT NULL DEFAULT '0', `INDEX_NUMBER` int(4) NOT NULL DEFAULT '0', `INDEX_TYPE` smallint(2) NOT NULL DEFAULT '0', `KV_FORMAT_VERSION` smallint(2) NOT NULL DEFAULT '0', `CF` varchar(193) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8

例如查询t1表的数据字典信息

select d.*,i.* from INFORMATION_SCHEMA.ROCKSDB_INDEX_FILE_MAP i,INFORMATION_SCHEMA.ROCKSDB_DDL d where i.INDEX_NUMBER=d.INDEX_NUMBER\G *************************** 1. row *************************** TABLE_SCHEMA: test TABLE_NAME: t1 PARTITION_NAME: NULL INDEX_NAME: PRIMARY COLUMN_FAMILY: 2 INDEX_NUMBER: 263 INDEX_TYPE: 1 KV_FORMAT_VERSION: 11 CF: cf_1 COLUMN_FAMILY: 2 INDEX_NUMBER: 263 SST_NAME: 000039.sst NUM_ROWS: 2 DATA_SIZE: 42 ENTRY_DELETES: 0 ENTRY_SINGLEDELETES: 0 ENTRY_MERGES: 0 ENTRY_OTHERS: 0 *************************** 2. row *************************** TABLE_SCHEMA: test TABLE_NAME: t1 PARTITION_NAME: NULL INDEX_NAME: idx2 COLUMN_FAMILY: 3 INDEX_NUMBER: 264 INDEX_TYPE: 2 KV_FORMAT_VERSION: 11 CF: cf_2 COLUMN_FAMILY: 3 INDEX_NUMBER: 264 SST_NAME: 000040.sst NUM_ROWS: 2 DATA_SIZE: 45 ENTRY_DELETES: 0 ENTRY_SINGLEDELETES: 0 ENTRY_MERGES: 0 ENTRY_OTHERS: 0 2 rows in set (0.00 sec) 事务相关 begin; select * from INFORMATION_SCHEMA.ROCKSDB_LOCKS; INSERT INTO t1 (a,b) VALUES (1,'a'); select * from INFORMATION_SCHEMA.ROCKSDB_LOCKS; +------------------+----------------+------------------+------+ | COLUMN_FAMILY_ID | TRANSACTION_ID | KEY | MODE | +------------------+----------------+------------------+------+ | 2 | 14 | 0000010780000001 | X | +------------------+----------------+------------------+------+ select * from INFORMATION_SCHEMA.ROCKSDB_TRX\G *************************** 1. row *************************** TRANSACTION_ID: 89 STATE: STARTED NAME: WRITE_COUNT: 2 LOCK_COUNT: 2 TIMEOUT_SEC: 2 WAITING_KEY: WAITING_COLUMN_FAMILY_ID: 0 IS_REPLICATION: 0 SKIP_TRX_API: 0 READ_ONLY: 0 HAS_DEADLOCK_DETECTION: 0 NUM_ONGOING_BULKLOAD: 0 THREAD_ID: 13 QUERY: select * from INFORMATION_SCHEMA.ROCKSDB_TRX

其中KEY 0000010780100002表示indexnum:107(263）pk: 80000001(1) 表示(1,'a',1)这条记录，具体参考myrocks记录格式分析

统计信息相关 select * from INFORMATION_SCHEMA.ROCKSDB_GLOBAL_INFO; +--------------+--------------+-----------------------------------------+ | TYPE | NAME | VALUE | +--------------+--------------+-----------------------------------------+ | BINLOG | FILE | mysql-bin.000003 | | BINLOG | POS | 18957545 | | BINLOG | GTID | b89fb268-0b22-11e7-a0ce-2c44fd7a5210:27 | | MAX_INDEX_ID | MAX_INDEX_ID | 264 | | CF_FLAGS | 0 | default [0] | | CF_FLAGS | 1 | __system__ [0] | | CF_FLAGS | 2 | cf_1 [0] | | CF_FLAGS | 3 | cf_2 [0] | +--------------+--------------+-----------------------------------------+ select * from INFORMATION_SCHEMA.ROCKSDB_DBSTATS; +-------------------------+-------+ | STAT_TYPE | VALUE | +-------------------------+-------+ | DB_BACKGROUND_ERRORS | 0 | | DB_NUM_SNAPSHOTS | 0 | | DB_OLDEST_SNAPSHOT_TIME | 0 | | DB_BLOCK_CACHE_USAGE | 1119 | +-------------------------+-------+ select * from INFORMATION_SCHEMA.ROCKSDB_CFSTATS where CF_NAME='cf_1'; +---------+-------------------------------+----------+ | CF_NAME | STAT_TYPE | VALUE | +---------+-------------------------------+----------+ | cf_1 | NUM_IMMUTABLE_MEM_TABLE | 0 | | cf_1 | MEM_TABLE_FLUSH_PENDING | 0 | | cf_1 | COMPACTION_PENDING | 0 | | cf_1 | CUR_SIZE_ACTIVE_MEM_TABLE | 44739520 | | cf_1 | CUR_SIZE_ALL_MEM_TABLES | 44739520 | | cf_1 | NUM_ENTRIES_ACTIVE_MEM_TABLE | 1048574 | | cf_1 | NUM_ENTRIES_IMM_MEM_TABLES | 0 | | cf_1 | NON_BLOCK_CACHE_SST_MEM_USAGE | 0 | | cf_1 | NUM_LIVE_VERSIONS | 1 | +---------+-------------------------------+----------+ 性能相关

INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL 是全局的性能信息，而INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT是以表为单位的性能信息。

性能统计由参数rocksdb_perf_context_level控制，取值范围如下

enum PerfLevel : unsigned char { kUninitialized = 0, // unknown setting kDisable = 1, // disable perf stats kEnableCount = 2, // enable only count stats kEnableTimeExceptForMutex = 3, // Other than count stats, also enable time // stats except for mutexes kEnableTime = 4, // enable count and time stats kOutOfBounds = 5 // N.B. Must always be the last value! };

示例如下：

select * from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL; +---------------------------------+-------------+ | STAT_TYPE | VALUE | +---------------------------------+-------------+ | USER_KEY_COMPARISON_COUNT | 565061179 | | BLOCK_CACHE_HIT_COUNT | 26 | | BLOCK_READ_COUNT | 2 | | BLOCK_READ_BYTE | 145 | | BLOCK_READ_TIME | 684522 | | BLOCK_CHECKSUM_TIME | 8380 | | BLOCK_DECOMPRESS_TIME | 10825 | | INTERNAL_KEY_SKIPPED_COUNT | 3371079 | | INTERNAL_DELETE_SKIPPED_COUNT | 0 | | GET_SNAPSHOT_TIME | 2409821566 | | GET_FROM_MEMTABLE_TIME | 68354733245 | | GET_FROM_MEMTABLE_COUNT | 4194309 | | GET_POST_PROCESS_TIME | 3421224444 | | GET_FROM_OUTPUT_FILES_TIME | 8016972510 | | SEEK_ON_MEMTABLE_TIME | 277621 | | SEEK_ON_MEMTABLE_COUNT | 33 | | SEEK_CHILD_SEEK_TIME | 1700582 | | SEEK_CHILD_SEEK_COUNT | 54 | | SEEK_IN_HEAP_TIME | 101201 | | SEEK_INTERNAL_SEEK_TIME | 2019275 | | FIND_NEXT_USER_ENTRY_TIME | 3997301676 | | WRITE_WAL_TIME | 410899041 | | WRITE_MEMTABLE_TIME | 23580852751 | | WRITE_DELAY_TIME | 0 | | WRITE_PRE_AND_POST_PROCESS_TIME | 1117611 | | DB_MUTEX_LOCK_NANOS | 237804 | | DB_CONDITION_WAIT_NANOS | 0 | | MERGE_OPERATOR_TIME_NANOS | 0 | | READ_INDEX_BLOCK_NANOS | 0 | | READ_FILTER_BLOCK_NANOS | 0 | | NEW_TABLE_BLOCK_ITER_NANOS | 1109437 | | NEW_TABLE_ITERATOR_NANOS | 308214 | | BLOCK_SEEK_NANOS | 1290004508 | | FIND_TABLE_NANOS | 0 | | IO_THREAD_POOL_ID | 102 | | IO_BYTES_WRITTEN | 54016973 | | IO_BYTES_READ | 145 | | IO_OPEN_NANOS | 0 | | IO_ALLOCATE_NANOS | 0 | | IO_WRITE_NANOS | 116163102 | | IO_READ_NANOS | 664547 | | IO_RANGE_SYNC_NANOS | 0 | | IO_LOGGER_NANOS | 0 | +---------------------------------+-------------+ select * from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT where table_name='t1' limit 1; +--------------+------------+----------------+---------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | STAT_TYPE | VALUE | +--------------+------------+----------------+---------------------------+-----------+ | test | t1 | NULL | USER_KEY_COMPARISON_COUNT | 565060904 | +--------------+------------+----------------+---------------------------+-----------+ COMPACTION相关 select * from INFORMATION_SCHEMA.ROCKSDB_COMPACTION_STATS where CF_NAME='cf_1' limit 3; +---------+-------+-----------+-------+ | CF_NAME | LEVEL | TYPE | VALUE | +---------+-------+-----------+-------+ | cf_1 | L0 | AvgSec | 0 | | cf_1 | L0 | CompCount | 2 | | cf_1 | L0 | CompSec | 0 | +---------+-------+-----------+-------+

具体可以参考下节SHOW ENGINE ROCKSDB STATUS。

参数配置

每个column family 都是独立的配置信息

SHOW ENGINE ROCKSDB STATUS

show engine rocksdb status.结果主要分为三部分1）DB Stats2）Compaction Stats 3）Memory_Stats

show engine rocksdb statusG结果节选

show engine rocksdb status\G *************************** 1. row *************************** Type: DBSTATS Name: rocksdb Status: ** DB Stats ** Uptime(secs): 211548.0 total, 8140.1 interval Cumulative writes: 58 writes, 2097K keys, 58 commit groups, 1.0 writes per commit group, ingest: 0.10 GB, 0.00 MB/s Cumulative WAL: 58 writes, 13 syncs, 4.14 writes per sync, written: 0.05 GB, 0.00 MB/s Cumulative stall: 00:00:0.000 H:M:S, 0.0 percent Interval writes: 0 writes, 0 keys, 0 commit groups, 0.0 writes per commit group, ingest: 0.00 MB, 0.00 MB/s Interval WAL: 0 writes, 0 syncs, 0.00 writes per sync, written: 0.00 MB, 0.00 MB/s Interval stall: 00:00:0.000 H:M:S, 0.0 percent ......(省略) *************************** 3. row *************************** Type: CF_COMPACTION Name: cf_1 Status: ** Compaction Stats [cf_1] ** Level Files Size(MB} Score Read(GB} Rn(GB} Rnp1(GB} Write(GB} Wnew(GB} Moved(GB} W-Amp Rd(MB/s} Wr(MB/s} Comp(sec} Comp(cnt} Avg(sec} KeyIn KeyDrop ---------------------------------------------------------------------------------------------------------------------------------------------------------- L0 1/0 0.00 0.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.3 0 2 0.004 0 0 Sum 1/0 0.00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.0 0.1 0.2 0 3 0.004 2 2 Int 0/0 0.00 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0.000 0 0 Uptime(secs): 210665.0 total, 210665.0 interval Flush(GB): cumulative 0.000, interval 0.000 AddFile(GB): cumulative 0.000, interval 0.000 AddFile(Total Files): cumulative 0, interval 0 AddFile(L0 Files): cumulative 0, interval 0 AddFile(Keys): cumulative 0, interval 0 Cumulative compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.0 seconds Interval compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.0 seconds Stalls(count): 0 level0_slowdown, 0 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 0 slowdown for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 0 total count ......(省略) *************************** 6. row *************************** Type: Memory_Stats Name: rocksdb Status: MemTable Total: 93675232 MemTable Unflushed: 93673184 Table Readers Total: 0 Cache Total: 1119 Default Cache Capacity: 0 6 rows in set (0.00 sec) DB Stats

其中：Interval stall: 此值受max_write_buffer_number，level0_slowdown_writes_trigger、soft_pending_compaction_bytes_limit等参数的影响，具体参考（SetupDelay）

Compaction Stats

其中 Rn(GB} = bytes_read_non_output_levels / kGB Rnp1(GB} = bytes_read_output_level / kGB W-Amp = bytes_written/bytes_read_non_output_levels

此部分内容与 INFORMATION_SCHEMA.ROCKSDB_COMPACTION_STATS有部分重合。

Memory_Stats

MemTable Total: 对应DB::Properties::kSizeAllMemTables MemTable Unflushed：对应DB::Properties::kCurSizeAllMemTables Table Readers Total: 对应DB::Properties::kEstimateTableReadersMem Cache Total: 表示已使用的内存 Default Cache Capacity: 使用默认blockcache的总量(basetable没有指定blockcache时使用默认的8M的blockcache)

SHOW ENGING ROKSDB TRANCTION STATUS

显示当前正在运行的事务语句

show engine rocksdb transaction status\G *************************** 1. row *************************** Type: SNAPSHOTS Name: rocksdb Status: ============================================================ 2017-03-20 07:49:22 ROCKSDB TRANSACTION MONITOR OUTPUT ============================================================ --------- SNAPSHOTS --------- LIST OF SNAPSHOTS FOR EACH SESSION: ---SNAPSHOT, ACTIVE 5 sec MySQL thread id 12, OS thread handle 0x7fd23a1d0700, query id 187 127.0.0.1 root Searching rows for update update t1 set b='cc' where a=2 lock count 72822, write count 2 ----------------------------------------- END OF ROCKSDB TRANSACTION MONITOR OUTPUT =========================================

总结

以上粗略介绍了myrocks的监控信息，具体还需要在实践中灵活运用。myrocks的监控信息也在不断完善中。

最新回复(0)