## Module 3: Using TiKV MVCC In-Memory Engine (IME) to Enhance Query Performance

In this module, participants will first create 2 tables with identical definition, data, and MVCC historical snapshots. Using previously defined PLACEMENT POLICYs, the tables will place Regions with the `LEADER` role on 2 different TiKV nodes: one with the TiKV MVCC in-memory engine(IME) feature enabled and the other without it. Participants will perform both index scans and full table scans on these 2 tables and compare the performance improvement offered by the TiKV MVCC in-memory engine(IME) feature through monitoring observations.

### Step 1. Connect to the TiUP control machine

+ Open two SSH connections to your lab virtual machine, by clicking:
  + [Terminal 1](ssh://monitor1-ssh#1)
  + [Terminal 2](ssh://monitor1-ssh#2)

+ You will now have two active terminal sessions.

### Step 2. Create Tables

+ On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance.

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), create tables `tbl_ime_R1` 和 `tbl_ime_R2` under the `test` database, and understand the DDL statements used for table creation.

  ```sql
  DROP TABLE IF EXISTS test.tbl_ime_R1;
  CREATE TABLE test.tbl_ime_R1 (
  `ID` bigint (20) NOT NULL AUTO_INCREMENT,
  `pID` bigint (20) NOT NULL,
  `cID` bigint (20) NOT NULL,
  `sID` bigint (20) NOT NULL,
  `pState` varchar (100) NOT NULL,
  `cState` tinyint (4) DEFAULT NULL,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  `txt` varchar(64) DEFAULT 'IMETEST',
  PRIMARY KEY (`sID`, `ID`),
  UNIQUE KEY `pcsID` (`pID`, `cID`, `sID`),
  KEY `pcStateIndex` (`pState`, `cState`)
  ) PLACEMENT POLICY = IME_R1;

  DROP TABLE IF EXISTS test.tbl_ime_R2;
  CREATE TABLE test.tbl_ime_R2 (
  `ID` bigint (20) NOT NULL AUTO_INCREMENT,
  `pID` bigint (20) NOT NULL,
  `cID` bigint (20) NOT NULL,
  `sID` bigint (20) NOT NULL,
  `pState` varchar (100) NOT NULL,
  `cState` tinyint (4) DEFAULT NULL,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  `txt` varchar(64) DEFAULT 'IMETEST',
  PRIMARY KEY (`sID`, `ID`),
  UNIQUE KEY `pcsID` (`pID`, `cID`, `sID`),
  KEY `pcStateIndex` (`pState`, `cState`)
  ) PLACEMENT POLICY = IME_R2;

  EXIT;
  ```

  ```output
  mysql> CREATE TABLE test.tbl_ime_R1 (
      -> `ID` bigint (20) NOT NULL AUTO_INCREMENT,
      -> `pID` bigint (20) NOT NULL,
      -> `cID` bigint (20) NOT NULL,
      -> `sID` bigint (20) NOT NULL,
      -> `pState` varchar (100) NOT NULL,
      -> `cState` tinyint (4) DEFAULT NULL,
      -> `created_at` DATETIME,
      -> `updated_at` DATETIME,
      -> `txt` varchar(64) DEFAULT 'IMETEST',
      -> PRIMARY KEY (`sID`, `ID`),
      -> UNIQUE KEY `pcsID` (`pID`, `cID`, `sID`),
      -> KEY `pcStateIndex` (`pState`, `cState`)
      -> ) PLACEMENT POLICY = IME_R1;
  Query OK, 0 rows affected, 5 warnings (0.52 sec)

  mysql> 
  mysql> DROP TABLE IF EXISTS test.tbl_ime_R2;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> CREATE TABLE test.tbl_ime_R2 (
      -> `ID` bigint (20) NOT NULL AUTO_INCREMENT,
      -> `pID` bigint (20) NOT NULL,
      -> `cID` bigint (20) NOT NULL,
      -> `sID` bigint (20) NOT NULL,
      -> `pState` varchar (100) NOT NULL,
      -> `cState` tinyint (4) DEFAULT NULL,
      -> `created_at` DATETIME,
      -> `updated_at` DATETIME,
      -> `txt` varchar(64) DEFAULT 'IMETEST',
      -> PRIMARY KEY (`sID`, `ID`),
      -> UNIQUE KEY `pcsID` (`pID`, `cID`, `sID`),
      -> KEY `pcStateIndex` (`pState`, `cState`)
      -> ) PLACEMENT POLICY = IME_R2;
  Query OK, 0 rows affected, 5 warnings (0.52 sec)

  mysql> 
  mysql> EXIT;
  Bye
  ```

+ Through the PLACEMENT POLICY definitions in the table creation DDL statements, we can understand that:
  + Tables `tbl_ime_R1` use PLACEMENT POLICY `IME_R1`, meaning all Regions with the `LEADER` role are stored on the TiKV node in `region1`, and read/write operations for them default to the TiKV node in `region1`.
  + Tables `tbl_ime_R2` use PLACEMENT POLICY `IME_R2`, meaning all Regions with the `LEADER` role are stored on the TiKV node in `region2`, and read/write operations for them default to the TiKV node in `region2`.
  + The storage layout of tables `tbl_ime_R1` and `tbl_ime_R2` within the TiKV cluster is illustrated in the following diagram:

  ![IME_PART2_TABLES_5.png 70%](https://lab-static.pingcap.com/images/2025/8/20/05cea87a3e752536c404e0a246e6e72afcf7960c.png)

### Step 3. Initialize Data

+ On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance.

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), create two sequence (SEQUENCE) objects: `test.seq_ime_R1` and `test.seq_ime_R2`, for subsequent data insertion into tables `test.tbl_ime_R1` and `test.tbl_ime_R2`.

  ```sql
  DROP SEQUENCE IF EXISTS test.seq_ime_R1;
  CREATE SEQUENCE test.seq_ime_R1;
  DROP SEQUENCE IF EXISTS test.seq_ime_R2;
  CREATE SEQUENCE test.seq_ime_R2;
  EXIT
  ```

  ```output
  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R1;
  Query OK, 0 rows affected (0.51 sec)

  mysql> CREATE SEQUENCE test.seq_ime_R1;
  Query OK, 0 rows affected (0.52 sec)

  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R2;
  Query OK, 0 rows affected (0.51 sec)

  mysql> CREATE SEQUENCE test.seq_ime_R2;
  Query OK, 0 rows affected (0.52 sec)

  mysql> EXIT
  Bye
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), use the `tiup bench` tool to insert 1,000 rows of random data into table `test.tbl_ime_R1`. Each row insertion will be followed by 10 update operations (UPDATE) on that row, generating 10 MVCC snapshots. This process will take approximately 1 minute to complete.

  ```bash
  cat > tbl_ime_query_R1.sql <<EOF
  SELECT @sID := 200001 + FLOOR(RAND() * 99999);
  INSERT INTO test.tbl_ime_R1 (pID, cID, sID, pState, cState, created_at, updated_at) VALUES (1 + FLOOR(RAND() * 99999), 100001 + FLOOR(RAND() * 99999), @sID, ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 29), NOW(), NOW());
  SELECT @ID := NEXTVAL(test.seq_ime_R1);
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  UPDATE test.tbl_ime_R1 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID;
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 1000 --threads 1 --query-files tbl_ime_query_R1.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.40 -P 4000 --count 1000 --threads 1 --query-files tbl_ime_query_R1.sql
  [Current] TBL_IME_QUERY_R1: 0.00s
  [Current] TBL_IME_QUERY_R1: 0.00s
  [Current] TBL_IME_QUERY_R1: 0.00s
  [Current] TBL_IME_QUERY_R1: 0.00s
  [Current] TBL_IME_QUERY_R1: 0.00s
  Finished
  [Summary] TBL_IME_QUERY_R1: 0.00s
  ```

  + On [Terminal 1](ssh://monitor1-ssh#1), use the `tiup bench` tool to insert 1,000 rows of random data into table `test.tbl_ime_R2`. Each row insertion will be followed by 10 update operations (UPDATE) on that row, generating 10 MVCC snapshots. This process will take approximately 1 minute to complete.

  ```bash
  cat > tbl_ime_query_R2.sql <<EOF
  SELECT @sID := 200001 + FLOOR(RAND() * 99999);
  INSERT INTO test.tbl_ime_R2 (pID, cID, sID, pState, cState, created_at, updated_at) VALUES (1 + FLOOR(RAND() * 99999), 100001 + FLOOR(RAND() * 99999), @sID, ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 29), NOW(), NOW());
  SELECT @ID := NEXTVAL(test.seq_ime_R2);
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  UPDATE test.tbl_ime_R2 SET pState = ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), cState = 1 + FLOOR(RAND() * 29) WHERE sID = @sID AND ID = @ID; 
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 1000 --threads 1 --query-files tbl_ime_query_R2.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.40 -P 4000 --count 1000 --threads 1 --query-files tbl_ime_query_R2.sql
  [Current] TBL_IME_QUERY_R2: 0.00s
  [Current] TBL_IME_QUERY_R2: 0.00s
  [Current] TBL_IME_QUERY_R2: 0.00s
  [Current] TBL_IME_QUERY_R2: 0.00s
  [Current] TBL_IME_QUERY_R2: 0.00s
  [Current] TBL_IME_QUERY_R2: 0.00s
  Finished
  [Summary] TBL_IME_QUERY_R2: 0.00s
  ```

### Step 4. Enhance the Performance of Index Scanning

+ On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance.

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), execute the following SQL command to perform a query on table `test.tbl_ime_R1`.

  ```sql
  SELECT sID, ID, txt FROM test.tbl_ime_R1 USE INDEX (pcStateIndex) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  ```

  ```output
  +--------+------+---------+
  | sID    | ID   | txt     |
  +--------+------+---------+
  | 200875 | 1425 | IMETEST |
  | 201896 | 2340 | IMETEST |
  ...
  | 296436 | 2098 | IMETEST |
  | 298352 | 2262 | IMETEST |
  +--------+------+---------+
  50 rows in set (0.01 sec)
  EXIT
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), execute the following SQL command to perform a query on table `test.tbl_ime_R2`.

  ```sql
  SELECT sID, ID, txt FROM test.tbl_ime_R2 USE INDEX (pcStateIndex) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  ```

  ```output
  +--------+-----+---------+
  | sID    | ID  | txt     |
  +--------+-----+---------+
  | 202196 |  91 | IMETEST |
  | 203510 | 375 | IMETEST |
  ...
  | 299728 | 514 | IMETEST |
  | 299902 | 832 | IMETEST |
  +--------+-----+---------+
  50 rows in set (0.00 sec)
  mysql> EXIT
  Bye
  ```

+ Go to the [TiDB Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) and log in with the username `root`, note that a password is not required at the moment. Navigate to `SQL Statements` panel, change the display time to `Recent 5 min`.

  ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-time-1.png)

+ Select the database as `test`.

  ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-db-2.png)

+ Set the displayed operations to `Select`.

  ![IME_PART3_DASHBOARD_INDEX_1.png 70%](https://lab-static.pingcap.com/images/2025/8/20/22d376120199a59922852df88503485e6e666be4.png)

+ Select the recently executed SQL command for querying table `test.tbl_ime_R1` (execution count: 1) and click to enter.

  ![IME_PART3_DASHBOARD_INDEX_2.png 70%](https://lab-static.pingcap.com/images/2025/8/20/a80f9ac47d2356173879fc28ef998fac0821c560.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. Based on the operator `IndexLookUp_14`, it is determined that this query uses an index scanning. Locate the operator `Limit_13(Build)` and examine its `Execution Info` information. You will find that `total_process_keys` is `96` and `total_keys` is `2193`, indicating that the number of rows requiring scan filtering is much smaller than the total number of rows actually stored. Therefore, this query is suitable for optimization using the TiKV MVCC In-Memory Engine to improve query performance. (Your results may vary.)

  ![IME_PART3_DASHBOARD_INDEX_3.png 70%](https://lab-static.pingcap.com/images/2025/8/20/9db33fbe6b450f2d5797c07c7d739218b087d975.png)

+ Select the recently executed SQL command for querying table `test.tbl_ime_R2` (execution count: 1) and click to enter.

  ![IME_PART3_DASHBOARD_INDEX_4.png 70%](https://lab-static.pingcap.com/images/2025/8/20/5de60326bf7ec54e6f7dd7cb2706e70bede561ff.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. You will observe that its execution plan is identical to the previous SQL command, both utilizing the index scanning operator, as shown below:

  ![IME_PART3_DASHBOARD_INDEX_5.png 70%](https://lab-static.pingcap.com/images/2025/8/20/7110b4053fa2dc3145d44e578fa54d9b2bff182a.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_ime_R1`. The scan will run continuously; please do not wait for it to complete and proceed immediately to the next step.(The scanning will last about 3~5 minutes.)

  ```bash
  cat > index_scan_tbl_ime_R1.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_ime_R1 USE INDEX (pcStateIndex) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 100000 --threads 1 --query-files index_scan_tbl_ime_R1.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.40 -P 4000 --count 100000 --threads 1 --query-files index_scan_tbl_ime_R1.sql
  [Current] INDEX_SCAN_TBL_IME_R1: 0.00s
  [Current] INDEX_SCAN_TBL_IME_R1: 0.00s
  ...
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000),and set the monitoring time range to the last 15 minutes, while changing the refresh rate to 5 seconds.

  ![IME_PART3_TiKV_DETAIL_INDEX_1.png 70%](https://lab-static.pingcap.com/images/2025/8/20/de118edc706857e83407e65968e1a7be7eca79dc.png)

+ In the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, observe through the `Region Cache Hit`, `Region Cache Hit Rate`, and `Region Cache Miss Reason` panels that the TiKV node in `region1` does not currently have the TiKV MVCC In-Memory Engine enabled (thus no activity is shown), and the current query is not utilizing the TiKV MVCC In-Memory Engine, as illustrated below:

  ![IME_PART3_TiKV_DETAIL_INDEX_2.png 70%](https://lab-static.pingcap.com/images/2025/8/20/4c1bf579870186fd2737072fda121448992c9473.png)

+ In the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe through the `Total Ops Details by CF (Index Scan)` panel the rate of various events during the index scanning process for each Column Family (CF) in RocksDB within the coprocessor of the TiKV node in `region1`. For example, the `write-next` event occurs at approximately 400K ops/m or higher, and the `write-processed_keys` event occurs at approximately 20.0K ops/m or higher, as shown below:(The `Total Ops Details by CF (Index Scan)` panel displays the number of events per second sent to each Column Family (CF) in RocksDB during index read operations, allowing users to monitor the storage layer workload during index scanning.)

  ![IME_PART3_TiKV_DETAIL_INDEX_3.png 70%](https://lab-static.pingcap.com/images/2025/8/20/ad42056f7be0ad77f4d4aad46c40b631bb421b15.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe through the `99% gRPC message duration` panel that the current 99th percentile execution latency for `coprocessor` is approximately around `1ms`, as shown below (you can click the `coprocessor` label in the legend for easier viewing): (The `99% gRPC message duration` panel displays the 99th percentile latency of gRPC message processing per message type in TiKV. This metric represents the maximum time taken to process 99% of gRPC messages, making it a critical indicator for identifying communication bottlenecks between TiDB and TiKV components. Monitoring this metric helps detect abnormal latency spikes in specific message types (such as coprocessor, kv_commit) that could impact overall cluster performance.）

  ![IME_PART3_TiKV_DETAIL_INDEX_4.png 70%](https://lab-static.pingcap.com/images/2025/8/20/d73a6268beaffed0ea5a8c2d3062bd9b6cca4d03.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), wait for the scanning on table `test.tbl_ime_R1` to complete, or press**CTRL-C**to terminate the scanning.

  ```output
  ...
  [Current] INDEX_SCAN_TBL_IME_R1: 0.00s
  [Current] INDEX_SCAN_TBL_IME_R1: 0.00s
  Finished
  [Summary] INDEX_SCAN_TBL_IME_R1: 0.00s
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe that the rates of related events in the `Total Ops Details by CF (Index Scan)` panel begin to decline, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_5.png 70%](https://lab-static.pingcap.com/images/2025/8/20/5fca4b2f57d7b76f7dfa22c740709c6e8a77af14.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe that the 99th percentile execution latency for `coprocessor` in the `99% gRPC message duration` panel begins to decline, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_6.png 70%](https://lab-static.pingcap.com/images/2025/8/20/31bc2eb6a9131c047094ea3d43541b8cb423a9f0.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_ime_R2`. The scanning will run continuously; please do not wait for it to complete and proceed immediately to the next step.(The scanning will last about 3~5 minutes.)

  ```bash
  cat > index_scan_tbl_ime_R2.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_ime_R2 USE INDEX (pcStateIndex) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 100000 --threads 1 --query-files index_scan_tbl_ime_R2.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.40 -P 4000 --count 100000 --threads 1 --query-files index_scan_tbl_ime_R2.sql
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  ...
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, observe through the `Region Cache Hit`, `Region Cache Hit Rate`, and `Region Cache Miss Reason` panels that the TiKV node in `region2` is currently utilizing the TiKV MVCC In-Memory Engine, with the `Region Cache Hit Rate` approaching `100%`, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_7.png 70%](https://lab-static.pingcap.com/images/2025/8/20/8e57d8b9c4b106f92d77df6a98976e798f7166c5.png)

+ In the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe through the `Total Ops Details by CF (Index Scan)` panel the rate of various events during the index scanning process for each Column Family (CF) in RocksDB within the coprocessor of the TiKV node in `region2`. For example, the `write-next` and the `write-processed_keys` events occurs at approximately 30K ops/m to 40K ops/m. Compared to the previous query on the TiKV node in `region1` (which did not use the TiKV MVCC In-Memory Engine), the occurrence rate of the `write-next` event is significantly reduced, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_8.png 70%](https://lab-static.pingcap.com/images/2025/8/20/1cbcfc000927b6e01c90c234f986a9c239439c75.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe through the `99% gRPC message duration` panel that the current 99th percentile execution latency for `coprocessor` is approximately around `395μs`. This is also reduced compared to the previous query on the TiKV node in `region1` (without the TiKV MVCC In-Memory Engine). Simultaneously, in the `gRPC message count` panel, the frequency of `coprocessor` has increased, as shown below (you can click the `coprocessor` label in the legend for easier viewing):

  ![IME_PART3_TiKV_DETAIL_INDEX_9.png 70%](https://lab-static.pingcap.com/images/2025/8/20/16b5a73834e7fc9557a72ec387cc73a31b115566.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), wait for the scanning on table `test.tbl_ime_R2` to complete, or press**CTRL-C**to stop scanning.

  ```output
  ...
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  Finished
  [Summary] INDEX_SCAN_TBL_IME_R2: 0.00s
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe that the rates of related events in the `Total Ops Details by CF (Index Scan)` panel begin to decline, allowing a comparison of the RocksDB scanning characteristics between the two queries, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_10.png 70%](https://lab-static.pingcap.com/images/2025/8/20/1b0c334b77e4293542a525f0f3b1ac1307959394.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe that the 99th percentile execution latency for `coprocessor` in the `99% gRPC message duration` panel begins to decline, allowing a comparison of the 99th percentile execution latency between the two queries, as shown below:

  ![IME_PART3_TiKV_DETAIL_INDEX_11.png 70%](https://lab-static.pingcap.com/images/2025/8/20/d9abcad18eeb6866189449368ff1fba09fd27fdc.png)

### Step 5. Enhance the Performance of Table Scanning

+ On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance.

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), execute the following SQL command to perform a query on table `test.tbl_ime_R1`.

  ```sql
  SELECT sID, ID, txt FROM test.tbl_ime_R1 IGNORE INDEX (pcStateIndex, pcsID) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  ```

  ```output
  +--------+------+---------+
  | sID    | ID   | txt     |
  +--------+------+---------+
  | 200208 | 3411 | IMETEST |
  | 200228 | 1097 | IMETEST |
  ...
  | 202804 |   46 | IMETEST |
  | 202820 | 2306 | IMETEST |
  +--------+------+---------+
  50 rows in set (0.01 sec)
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), execute the following SQL command to perform a query on table `test.tbl_ime_R2`.

  ```sql
  SELECT sID, ID, txt FROM test.tbl_ime_R2 IGNORE INDEX (pcStateIndex, pcsID) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  EXIT
  ```

  ```output
  +--------+-----+---------+
  | sID    | ID  | txt     |
  +--------+-----+---------+
  | 200693 | 554 | IMETEST |
  | 200694 | 200 | IMETEST |
  ...
  | 214020 | 978 | IMETEST |
  | 214956 | 644 | IMETEST |
  +--------+-----+---------+
  50 rows in set (0.00 sec)

  mysql> EXIT
  Bye
  ```

+ Go to the [TiDB Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard) and log in with the username `root`, note that a password is not required at the moment. Navigate to `SQL Statements` panel, change the display time to `Recent 5 min`.

  ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-time-1.png)

+ Select the database as `test`.

  ![60%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-db-2.png)

+ Set the displayed operations to `Select`.

  ![IME_PART3_DASHBOARD_TABLE_1.png 70%](https://lab-static.pingcap.com/images/2025/8/20/398f59c3dc85baace38caa0341f4c143c2cafc19.png)

+ Select the recently executed SQL command for querying table `test.tbl_ime_R1` (execution count: 1) and click to enter.

  ![IME_PART3_DASHBOARD_TABLE_2.png 70%](https://lab-static.pingcap.com/images/2025/8/20/a064e6e3cf0b6052ab97c490e7b2e6b33cce3103.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. Based on the operator `TableFullScan_10`, it is determined that this query uses a full table scan (Note: there may be currently two execution plans; the one using a full table scan was just executed). Locate the operator `Limit_12` and examine its `Execution Info` information. You will find that `total_process_keys` is `224` and `total_keys` is `1793`, indicating that the number of rows requiring scan filtering is much smaller than the total number of rows actually stored. Therefore, this query is suitable for optimization using the TiKV MVCC In-Memory Engine to improve query performance. (Your results may vary.)

  ![IME_PART3_DASHBOARD_TABLE_3.png 70%](https://lab-static.pingcap.com/images/2025/8/20/c18939929796ede4565f4f3be0ad7edaa4b26267.png)

+ Select the recently executed SQL command for querying table `test.tbl_ime_R2` (execution count: 1) and click to enter.

  ![IME_PART3_DASHBOARD_TABLE_4.png 70%](https://lab-static.pingcap.com/images/2025/8/20/28cfe70c689c307a5f48b8508bd14ffa0e732538.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. You will observe that its execution plan is identical to the previous SQL command, both utilizing the full table scan operator (Note: there may be currently two execution plans; the one using a full table scan was just executed), as shown below:

  ![IME_PART3_DASHBOARD_TABLE_5.png 70%](https://lab-static.pingcap.com/images/2025/8/20/ff1c6801cc846790d2ad88d2de7502a9dbf73704.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_ime_R1`. The scan will run continuously; please do not wait for it to complete and proceed immediately to the next step.(The scanning will last about 3~5 minutes.)

  ```bash
  cat > table_scan_tbl_ime_R1.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_ime_R1 IGNORE INDEX (pcStateIndex, pcsID) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 100000 --threads 1 --query-files table_scan_tbl_ime_R1.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.1.40 -P 4000 --count 100000 --threads 1 --query-files table_scan_tbl_ime_R1.sql
  [Current] TABLE_SCAN_TBL_IME_R1: 0.00s
  [Current] TABLE_SCAN_TBL_IME_R1: 0.00s
  ...
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, observe through the `Region Cache Hit`, `Region Cache Hit Rate`, and `Region Cache Miss Reason` panels that the TiKV node in `region1` does not currently have the TiKV MVCC In-Memory Engine enabled (thus no activity is shown), and the current query is not utilizing the TiKV MVCC In-Memory Engine, as illustrated below:

  ![IME_PART3_TiKV_DETAIL_TABLE_2.png 70%](https://lab-static.pingcap.com/images/2025/8/20/eb160f376e4eeeeba5942385b06d4c0aebfa0309.png)

+ In the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe through the `Total Ops Details by CF (Table Scan)` panel the rate of various events during the table scan process for each Column Family (CF) in RocksDB within the coprocessor of the TiKV node in `region1`. For example, the `write-next` event occurs at approximately 400K ops/m or higher, the `write-processed_keys` event occurs at approximately 70K ops/m or higher, and the `write-seek` event occurs at approximately 60K ops/m or higher, as shown below:(The `Total Ops Details by CF (Table Scan)` panel displays the number of events per second sent to each Column Family (CF) in RocksDB during table read operations, allowing users to monitor the storage layer workload during table scanning.)

  ![IME_PART3_TiKV_DETAIL_TABLE_3.png 70%](https://lab-static.pingcap.com/images/2025/8/20/e0871b4eed23ac0ef4ca21749d51d3bfcaab912c.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe through the `99% gRPC message duration` panel that the current 99th percentile execution latency for `coprocessor` is approximately around `1ms`, as shown below (you can click the `coprocessor` label in the legend for easier viewing):

  ![IME_PART3_TiKV_DETAIL_TABLE_4.png 70%](https://lab-static.pingcap.com/images/2025/8/20/99e898e6e46febbe133885ff09f910b22828db8a.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), wait for the scanning on table `test.tbl_ime_R1` to complete, or press**CTRL-C**to terminate the scanning.

  ```output
  ...
  [Current] TABLE_SCAN_TBL_IME_R1: 0.00s
  [Current] TABLE_SCAN_TBL_IME_R1: 0.00s
  Finished
  [Summary] TABLE_SCAN_TBL_IME_R1: 0.00s
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe that the rates of related events in the `Total Ops Details by CF (Table Scan)` panel begin to decline, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_5.png 70%](https://lab-static.pingcap.com/images/2025/8/20/34e236c12adf4de7dd80c37b59dc82a0df74ee9d.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe that the 99th percentile execution latency for `coprocessor` in the `99% gRPC message duration` panel begins to decline, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_6.png 70%](https://lab-static.pingcap.com/images/2025/8/20/4b45d2214274ece8dabdb0816a2eab112c042c0f.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_ime_R2`. The scanning will run continuously; please do not wait for it to complete and proceed immediately to the next step.(The scanning will last about 3~5 minutes.)

  ```bash
  cat > table_scan_tbl_ime_R2.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_ime_R2 IGNORE INDEX (pcStateIndex, pcsID) WHERE ((pState = 's1') OR (pState = 's2') OR (pState = 's3')) OR ((pState = 's3' AND cState = 10) OR (pState = 's3' AND cState = 20)) AND NOT (sID = 200001 AND ID = 1097) AND NOT (sID = 0 AND ID = 0) LIMIT 50;
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 100000 --threads 1 --query-files table_scan_tbl_ime_R2.sql
  ```

  ```output
  Starting component bench: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench rawsql run -H 10.90.2.164 -P 4000 --count 100000 --threads 1 --query-files table_scan_tbl_ime_R2.sql
  [Current] TABLE_SCAN_TBL_IME_R2: 0.00s
  [Current] TABLE_SCAN_TBL_IME_R2: 0.00s
  ...
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, observe through the `Region Cache Hit`, `Region Cache Hit Rate`, and `Region Cache Miss Reason` panels that the TiKV node in `region2` is currently utilizing the TiKV MVCC In-Memory Engine, with the `Region Cache Hit Rate` approaching `100%`, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_7.png 70%](https://lab-static.pingcap.com/images/2025/8/20/8377b214517b9c937a309c8e7e7e4b82c1ed7093.png)

+ In the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe through the `Total Ops Details by CF (Table Scan)` panel the rate of various events during the table scan process for each Column Family (CF) in RocksDB within the coprocessor of the TiKV node in `region2`. For example, the `write-next` and the `write-processed_keys` events occurs at approximately 90K ops/m to 150K ops/m, and the `write-seek` event occurs at approximately 450 ops/m. Compared to the previous query on the TiKV node in `region1` (which did not use the TiKV MVCC In-Memory Engine), the occurrence rates of related events are significantly reduced, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_8.png 70%](https://lab-static.pingcap.com/images/2025/8/20/d36cf2c3da511834019a24fc061701776e5ed39b.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe through the `99% gRPC message duration` panel that the current 99th percentile execution latency for `coprocessor` is approximately around `743μs`. This is also reduced compared to the previous query on the TiKV node in `region1` (without the TiKV MVCC In-Memory Engine). Simultaneously, in the `gRPC message count` panel, the frequency of `coprocessor` has increased, as shown below (you can click the `coprocessor` label in the legend for easier viewing):

  ![IME_PART3_TiKV_DETAIL_TABLE_9.png 70%](https://lab-static.pingcap.com/images/2025/8/20/5859487265ca34e1409ed6eea1b1e3d2cfab982b.png)

+ On [Terminal 2](ssh://monitor1-ssh#2), wait for the scanning on table `test.tbl_ime_R2` to complete, or press**CTRL-C**to stop scanning.

  ```output
  ...
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  [Current] INDEX_SCAN_TBL_IME_R2: 0.00s
  Finished
  [Summary] INDEX_SCAN_TBL_IME_R2: 0.00s
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `Coprocessor Detail` tab of `tidb-demo-TiKV-Details`, observe that the rates of related events in the `Total Ops Details by CF (Table Scan)` panel begin to decline, allowing a comparison of the RocksDB scan characteristics between the two queries, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_10.png 70%](https://lab-static.pingcap.com/images/2025/8/20/24e71cc077ee387f4b8c980b398a1d8f1257f541.png)

+ In the `gRPC` tab of `tidb-demo-TiKV-Details`, observe that the 99th percentile execution latency for `coprocessor` in the `99% gRPC message duration` panel begins to decline, allowing a comparison of the 99th percentile execution latency between the two queries, as shown below:

  ![IME_PART3_TiKV_DETAIL_TABLE_11.png 70%](https://lab-static.pingcap.com/images/2025/8/20/e0de9ea57d7ae9cd97c65f6fd545ccfd57fb9989.png)

### Step 6. Cleanup Data

+ On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance.

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```output
  mysql> 
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), clean up all data used in this module.

  ```sql
  DROP TABLE IF EXISTS test.tbl_ime_R1;
  DROP TABLE IF EXISTS test.tbl_ime_R2;

  DROP SEQUENCE IF EXISTS test.seq_ime_R1;
  DROP SEQUENCE IF EXISTS test.seq_ime_R2;

  EXIT
  ```

  ```output
  mysql> DROP TABLE IF EXISTS test.tbl_ime_R1;
  Query OK, 0 rows affected (0.51 sec)

  mysql> DROP TABLE IF EXISTS test.tbl_ime_R2;
  Query OK, 0 rows affected (0.52 sec)

  mysql> 
  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R1;
  Query OK, 0 rows affected (0.51 sec)

  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R2;
  Query OK, 0 rows affected (0.51 sec)

  mysql> 
  mysql> EXIT
  Bye
  ```

+ This is the end of Module 3.