数据库内核月报 - 2017 / 03

MySQL · myrocks · myrocks监控信息

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 status\G结果节选

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的监控信息也在不断完善中。