1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
SET @prior_rocksdb_perf_context_level = @@rocksdb_perf_context_level;
SET GLOBAL rocksdb_perf_context_level=3;
CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i)) ENGINE = ROCKSDB;
CREATE TABLE t2 (k INT, PRIMARY KEY (k)) ENGINE = ROCKSDB;
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT WHERE TABLE_NAME = 't1';
TABLE_SCHEMA TABLE_NAME PARTITION_NAME STAT_TYPE VALUE
test t1 NULL USER_KEY_COMPARISON_COUNT #
test t1 NULL BLOCK_CACHE_HIT_COUNT #
test t1 NULL BLOCK_READ_COUNT #
test t1 NULL BLOCK_READ_BYTE #
test t1 NULL BLOCK_READ_TIME #
test t1 NULL BLOCK_CHECKSUM_TIME #
test t1 NULL BLOCK_DECOMPRESS_TIME #
test t1 NULL INTERNAL_KEY_SKIPPED_COUNT #
test t1 NULL INTERNAL_DELETE_SKIPPED_COUNT #
test t1 NULL GET_SNAPSHOT_TIME #
test t1 NULL GET_FROM_MEMTABLE_TIME #
test t1 NULL GET_FROM_MEMTABLE_COUNT #
test t1 NULL GET_POST_PROCESS_TIME #
test t1 NULL GET_FROM_OUTPUT_FILES_TIME #
test t1 NULL SEEK_ON_MEMTABLE_TIME #
test t1 NULL SEEK_ON_MEMTABLE_COUNT #
test t1 NULL SEEK_CHILD_SEEK_TIME #
test t1 NULL SEEK_CHILD_SEEK_COUNT #
test t1 NULL SEEK_IN_HEAP_TIME #
test t1 NULL SEEK_INTERNAL_SEEK_TIME #
test t1 NULL FIND_NEXT_USER_ENTRY_TIME #
test t1 NULL WRITE_WAL_TIME #
test t1 NULL WRITE_MEMTABLE_TIME #
test t1 NULL WRITE_DELAY_TIME #
test t1 NULL WRITE_PRE_AND_POST_PROCESS_TIME #
test t1 NULL DB_MUTEX_LOCK_NANOS #
test t1 NULL DB_CONDITION_WAIT_NANOS #
test t1 NULL MERGE_OPERATOR_TIME_NANOS #
test t1 NULL READ_INDEX_BLOCK_NANOS #
test t1 NULL READ_FILTER_BLOCK_NANOS #
test t1 NULL NEW_TABLE_BLOCK_ITER_NANOS #
test t1 NULL NEW_TABLE_ITERATOR_NANOS #
test t1 NULL BLOCK_SEEK_NANOS #
test t1 NULL FIND_TABLE_NANOS #
test t1 NULL IO_THREAD_POOL_ID #
test t1 NULL IO_BYTES_WRITTEN #
test t1 NULL IO_BYTES_READ #
test t1 NULL IO_OPEN_NANOS #
test t1 NULL IO_ALLOCATE_NANOS #
test t1 NULL IO_WRITE_NANOS #
test t1 NULL IO_READ_NANOS #
test t1 NULL IO_RANGE_SYNC_NANOS #
test t1 NULL IO_LOGGER_NANOS #
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL;
STAT_TYPE VALUE
USER_KEY_COMPARISON_COUNT #
BLOCK_CACHE_HIT_COUNT #
BLOCK_READ_COUNT #
BLOCK_READ_BYTE #
BLOCK_READ_TIME #
BLOCK_CHECKSUM_TIME #
BLOCK_DECOMPRESS_TIME #
INTERNAL_KEY_SKIPPED_COUNT #
INTERNAL_DELETE_SKIPPED_COUNT #
GET_SNAPSHOT_TIME #
GET_FROM_MEMTABLE_TIME #
GET_FROM_MEMTABLE_COUNT #
GET_POST_PROCESS_TIME #
GET_FROM_OUTPUT_FILES_TIME #
SEEK_ON_MEMTABLE_TIME #
SEEK_ON_MEMTABLE_COUNT #
SEEK_CHILD_SEEK_TIME #
SEEK_CHILD_SEEK_COUNT #
SEEK_IN_HEAP_TIME #
SEEK_INTERNAL_SEEK_TIME #
FIND_NEXT_USER_ENTRY_TIME #
WRITE_WAL_TIME #
WRITE_MEMTABLE_TIME #
WRITE_DELAY_TIME #
WRITE_PRE_AND_POST_PROCESS_TIME #
DB_MUTEX_LOCK_NANOS #
DB_CONDITION_WAIT_NANOS #
MERGE_OPERATOR_TIME_NANOS #
READ_INDEX_BLOCK_NANOS #
READ_FILTER_BLOCK_NANOS #
NEW_TABLE_BLOCK_ITER_NANOS #
NEW_TABLE_ITERATOR_NANOS #
BLOCK_SEEK_NANOS #
FIND_TABLE_NANOS #
IO_THREAD_POOL_ID #
IO_BYTES_WRITTEN #
IO_BYTES_READ #
IO_OPEN_NANOS #
IO_ALLOCATE_NANOS #
IO_WRITE_NANOS #
IO_READ_NANOS #
IO_RANGE_SYNC_NANOS #
IO_LOGGER_NANOS #
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT
WHERE TABLE_NAME = 't1'
AND STAT_TYPE in ('INTERNAL_KEY_SKIPPED_COUNT', 'INTERNAL_DELETE_SKIPPED_COUNT');
TABLE_SCHEMA TABLE_NAME PARTITION_NAME STAT_TYPE VALUE
test t1 NULL INTERNAL_KEY_SKIPPED_COUNT 0
test t1 NULL INTERNAL_DELETE_SKIPPED_COUNT 0
SELECT * FROM t1;
i j
1 1
2 2
3 3
4 4
5 5
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT
WHERE TABLE_NAME = 't1'
AND STAT_TYPE in ('INTERNAL_KEY_SKIPPED_COUNT', 'INTERNAL_DELETE_SKIPPED_COUNT');
TABLE_SCHEMA TABLE_NAME PARTITION_NAME STAT_TYPE VALUE
test t1 NULL INTERNAL_KEY_SKIPPED_COUNT 5
test t1 NULL INTERNAL_DELETE_SKIPPED_COUNT 0
SELECT * FROM t1 WHERE j BETWEEN 1 AND 5;
i j
1 1
2 2
3 3
4 4
5 5
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT
WHERE TABLE_NAME = 't1'
AND STAT_TYPE in ('INTERNAL_KEY_SKIPPED_COUNT', 'INTERNAL_DELETE_SKIPPED_COUNT');
TABLE_SCHEMA TABLE_NAME PARTITION_NAME STAT_TYPE VALUE
test t1 NULL INTERNAL_KEY_SKIPPED_COUNT 10
test t1 NULL INTERNAL_DELETE_SKIPPED_COUNT 0
BEGIN;
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t2 VALUES (3), (4);
COMMIT;
SELECT COUNT(*) from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT
WHERE TABLE_NAME = 't2'
AND STAT_TYPE = 'IO_WRITE_NANOS'
AND VALUE > 0;
COUNT(*)
0
SELECT COUNT(*) from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL
WHERE STAT_TYPE = 'IO_WRITE_NANOS' AND VALUE > 0;
COUNT(*)
1
SELECT VALUE INTO @a from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL
WHERE STAT_TYPE = 'IO_WRITE_NANOS';
INSERT INTO t2 VALUES (5), (6), (7), (8);
SELECT COUNT(*) from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT
WHERE TABLE_NAME = 't2'
AND STAT_TYPE = 'IO_WRITE_NANOS'
AND VALUE > 0;
COUNT(*)
1
SELECT VALUE INTO @b from INFORMATION_SCHEMA.ROCKSDB_PERF_CONTEXT_GLOBAL
WHERE STAT_TYPE = 'IO_WRITE_NANOS';
SELECT CASE WHEN @b - @a > 0 THEN 'true' ELSE 'false' END;
CASE WHEN @b - @a > 0 THEN 'true' ELSE 'false' END
true
DROP TABLE t1;
DROP TABLE t2;
SET GLOBAL rocksdb_perf_context_level = @prior_rocksdb_perf_context_level;
|
