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 都是独立的配置信息
select * from INFORMATION_SCHEMA.ROCKSDB_CF_OPTIONS where CF_NAME='cf_1' limit 3;
+---------+-------------------+------------------+
| CF_NAME | OPTION_TYPE | VALUE |
+---------+-------------------+------------------+
| cf_1 | COMPARATOR | RocksDB_SE_v3.10 |
| cf_1 | MERGE_OPERATOR | NULL |
| cf_1 | COMPACTION_FILTER | NULL |
+---------+-------------------+------------------+
SHOW ENGINE ROCKSDB STATUS
show engine rocksdb status.结果主要分为三部分
1）DB Stats
2）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的监控信息也在不断完善中。
