我的书签
添加书签
移除书签先创建测试表
show status 也展示了部分rocksdb引擎的信息
+---------------------------------------+------------------------------------------+| 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_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_DBSTATS || ROCKSDB_DDL || ROCKSDB_LOCKS |+-----------------------------+
- 数据字典相关
show create table INFORMATION_SCHEMA.ROCKSDB_INDEX_FILE_MAP\G*************************** 1. row ***************************Table: ROCKSDB_INDEX_FILE_MAPCreate 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=utf81 row in set (0.00 sec)mysql> show create table INFORMATION_SCHEMA.ROCKSDB_DDL\G*************************** 1. row ***************************Table: ROCKSDB_DDLCreate 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表的数据字典信息
- 事务相关
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: 89STATE: STARTEDNAME:WRITE_COUNT: 2LOCK_COUNT: 2TIMEOUT_SEC: 2WAITING_KEY:WAITING_COLUMN_FAMILY_ID: 0IS_REPLICATION: 0SKIP_TRX_API: 0READ_ONLY: 0HAS_DEADLOCK_DETECTION: 0NUM_ONGOING_BULKLOAD: 0THREAD_ID: 13QUERY: select * from INFORMATION_SCHEMA.ROCKSDB_TRX
其中KEY 0000010780100002表示indexnum:107(263）pk: 80000001(1) 表示(1,’a’,1)这条记录，具体参考
- 统计信息相关
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 |+-------------------------+-------++---------+-------------------------------+----------++---------+-------------------------------+----------+| 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 |+---------+-------------------------------+----------+
- 性能相关
性能统计由参数rocksdb_perf_context_level控制，取值范围如下
enum PerfLevel : unsigned char {kUninitialized = 0, // unknown settingkDisable = 1, // disable perf statskEnableCount = 2, // enable only count statskEnableTimeExceptForMutex = 3, // Other than count stats, also enable time// stats except for mutexeskEnableTime = 4, // enable count and time statskOutOfBounds = 5 // N.B. Must always be the last value!};
示例如下：
- 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.结果主要分为三部分
1）DB Stats
2）Compaction Stats
3）Memory_Stats
show engine rocksdb status\G*************************** 1. row ***************************Type: DBSTATSName: rocksdbStatus:** DB Stats **Uptime(secs): 211548.0 total, 8140.1 intervalCumulative writes: 58 writes, 2097K keys, 58 commit groups, 1.0 writes per commit group, ingest: 0.10 GB, 0.00 MB/sCumulative WAL: 58 writes, 13 syncs, 4.14 writes per sync, written: 0.05 GB, 0.00 MB/sCumulative stall: 00:00:0.000 H:M:S, 0.0 percentInterval writes: 0 writes, 0 keys, 0 commit groups, 0.0 writes per commit group, ingest: 0.00 MB, 0.00 MB/sInterval WAL: 0 writes, 0 syncs, 0.00 writes per sync, written: 0.00 MB, 0.00 MB/sInterval stall: 00:00:0.000 H:M:S, 0.0 percent......(省略)*************************** 3. row ***************************Type: CF_COMPACTIONName: cf_1Status:** 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 0Sum 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 2Int 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 0Uptime(secs): 210665.0 total, 210665.0 intervalFlush(GB): cumulative 0.000, interval 0.000AddFile(GB): cumulative 0.000, interval 0.000AddFile(Total Files): cumulative 0, interval 0AddFile(L0 Files): cumulative 0, interval 0AddFile(Keys): cumulative 0, interval 0Cumulative compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.0 secondsInterval compaction: 0.00 GB write, 0.00 MB/s write, 0.00 GB read, 0.00 MB/s read, 0.0 secondsStalls(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_StatsName: rocksdbStatus:MemTable Total: 93675232MemTable Unflushed: 93673184Table Readers Total: 0Cache Total: 1119Default Cache Capacity: 0
- 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)
显示当前正在运行的事务语句
