Skip to content

Show Engine Status

Jump to bottom
Manuel edited this page Sep 25, 2017 · 4 revisions

SHOW ENGINE ROCKSDB STATUS;

Overview

SHOW ENGINE ROCKSDB STATUS provides useful diagnostics and debugging information regarding the RocksDB engine underneath MyRocks.

Sample Output

mysql> show engine rocksdb status\G
*************************** 1. row ***************************
  Type: DBSTATS
  Name: rocksdb
Status: 
** DB Stats **
Uptime(secs): 89725.6 total, 36.8 interval
Cumulative writes: 8 writes, 11 keys, 8 commit groups, 0.9 writes per commit group, ingest: 0.00 GB, 0.00 MB/s
Cumulative WAL: 8 writes, 3 syncs, 2.00 writes per sync, written: 0.00 GB, 0.00 MB/s
Cumulative stall: 00:00:0.000 H:M:S, 0.0 percent
Interval writes: 3 writes, 4 keys, 3 commit groups, 0.8 writes per commit group, ingest: 0.00 MB, 0.00 MB/s
Interval WAL: 3 writes, 0 syncs, 3.00 writes per sync, written: 0.00 MB, 0.00 MB/s
Interval stall: 00:00:0.000 H:M:S, 0.0 percent
** Level 0 read latency histogram (micros):
Count: 5 Average: 2.2000  StdDev: 2.32
Min: 0  Median: 0.8333  Max: 5
Percentiles: P50: 0.83 P75: 4.38 P99: 4.97 P99.9: 5.00 P99.99: 5.00
------------------------------------------------------
[       0,       1 )        3  60.000%  60.000% ############
[       4,       5 )        2  40.000% 100.000% ########


*************************** 2. row ***************************
  Type: CF_COMPACTION
  Name: __system__
Status: 
** Compaction Stats [__system__] **
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      2/0       0.00   0.5      0.0     0.0      0.0       0.0      0.0       0.0   0.0      0.0      0.6         0         1    0.002       0      0
 Sum      2/0       0.00   0.0      0.0     0.0      0.0       0.0      0.0       0.0   1.0      0.0      0.6         0         1    0.002       0      0
 Int      0/0       0.00   0.0      0.0     0.0      0.0       0.0      0.0       0.0   1.0      0.0      0.6         0         1    0.002       0      0
Uptime(secs): 89725.6 total, 89725.6 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

*************************** 3. row ***************************
  Type: CF_COMPACTION
  Name: default
Status: 
** Compaction Stats [default] **
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.4         0         1    0.003       0      0
 Sum      1/0       0.00   0.0      0.0     0.0      0.0       0.0      0.0       0.0   1.0      0.0      0.4         0         1    0.003       0      0
 Int      0/0       0.00   0.0      0.0     0.0      0.0       0.0      0.0       0.0   1.0      0.0      0.4         0         1    0.003       0      0
Uptime(secs): 89725.6 total, 89725.6 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

*************************** 4. row ***************************
  Type: Memory_Stats
  Name: rocksdb
Status: 
MemTable Total: 3768
MemTable Unflushed: 1712
Table Readers Total: 0
Cache Total: 450
Default Cache Capacity: 0
4 rows in set (0.00 sec)

There are 5 distinct parts of the RocksDB engine status.

DB Stats

This section provides information on overall statistics of the databases. Most of the fields are self explanatory but are listed here.

  • Uptime - in seconds, the total time the system has been up.

  • Interval refers to the length of the last statistics collection period.

  • Write Status - Total number of write batches, the total number of keys in those batches, the size of the writes and the write throughput are reported.

  • WAL - On a PUT, the record will also be written to Write Ahead Log. Total number of writes to WAL, the throughput and the number of fsync/fdatasync that happened are also reported.

  • Keys - Total number of keys in the RocksDB engine.

  • Ingest - Total Data ingested and ingestion rate in MBytes per second.

  • Average number of Writes per sync for WAL ( this gives a rough idea of how many writes might be lost on a crash ).

There is information also on stalls encountered in the engine. The above statistics are reported on a cumulative and interval basis.

Read Latency section

This section lists the latency of reads encountered for the different levels of the LSM in RocksDB (Log Structured Merge Tree).

Typical latencies can be in the low microseconds, as keys are found in the memtable and block cache, but if there is a cache miss or a key is found in lower levels, the latencies go up. This is because more number of SST files will need to be read, and more binary searches need to be done.

Column Family Compaction Statistics section.

There is one set of statistics for each column family, including the system column family. Please refer to RocksDB wiki for more information on LSM and Column Families.

The first subsection lists the count and size of the files @ each level for the Column family.

  • Rn(GB) - refers to total read at non output level
  • Rnp1(GB) - refers to read volume at output level.

Followed the levelized output, is general compaction statistics for that column family.

Memory statistics section

  • MemTable Unflushed - Size of memtable that has not been flushed to SST files.
  • Cache Total - Size of block cache

SHOW ENGINE ROCKSDB TRANSACTION STATUS;

Overview

SHOW ENGINE ROCKSDB TRANSACTION STATUS provides useful diagnostics and debugging information related to snapshots and deadlocks of transactions.

Sample Output

mysql> show engine rocksdb transaction status\G
*************************** 1. row ***************************
  Type: rocksdb
  Name:
Status:
============================================================
2017-09-25 14:19:20 ROCKSDB TRANSACTION MONITOR OUTPUT
============================================================
---------
SNAPSHOTS
---------
LIST OF SNAPSHOTS FOR EACH SESSION:
---SNAPSHOT, ACTIVE 40 sec
MySQL thread id 17, OS thread handle 0x7fb344ed2700, query id 54 localhost root
lock count 4, write count 2
insert count 2, update count 0, delete count 0
----------LATEST DETECTED DEADLOCKS----------

*** DEADLOCK PATH
=========================================
TRANSACTIONID: 11
COLUMN FAMILY NAME: default
WAITING KEY: 0000010480000002
LOCK TYPE: EXCLUSIVE
INDEX NAME: PRIMARY
TABLE NAME: test.t
---------------WAITING FOR---------------
TRANSACTIONID: 12
COLUMN FAMILY NAME: default
WAITING KEY: 0000010480000001
LOCK TYPE: EXCLUSIVE
INDEX NAME: PRIMARY
TABLE NAME: test.t
---------------WAITING FOR---------------
TRANSACTIONID: 11
COLUMN FAMILY NAME: default
WAITING KEY: 0000010480000002
LOCK TYPE: EXCLUSIVE
INDEX NAME: PRIMARY
TABLE NAME: test.t

--------TRANSACTIONID: 12 GOT DEADLOCK---------
-----------------------------------------
END OF ROCKSDB TRANSACTION MONITOR OUTPUT
=========================================

1 row in set (0.00 sec)

Snapshots

Displays the current list of active snapshots open. Includes the following information:

  • Thread ID
  • OS handle
  • Query ID
  • Host
  • User
  • Lock count
  • Write count
  • Insert count
  • Update count
  • Delete count

Latest Detected Deadlocks

Displays information about the deadlock cycle formed for the latest detected deadlocks and includes the following pieces of information for each node in the cycle.

  • Transaction ID
  • Column Family Name
  • Waiting Key
  • Lock Type
  • Index Name
  • Table Name

Documentation license here.

Installation

MyRocks

DocStore

MySQL/InnoDB Enhancements

Clone this wiki locally