## Module 2: Using TiKV MVCC In-Memory Engine (IME)

In this module, participants will first create 6 tables. Every two tables will use a previously defined PLACEMENT POLICY to place Regions with the LEADER role on the same TiKV node. On each TiKV node, one table will contain a significant amount of MVCC historical snapshot data, while the other table will contain almost no MVCC historical data. These 3 TiKV nodes have different configurations for the TiKV MVCC in-memory engine(IME). Participants will perform scans on all 6 tables and observe the behavior of the differently configured TiKV MVCC in-memory engine(IME). Additionally, participants will modify the TiKV MVCC in-memory engine(IME)'s configuration and observe the resulting effects.

### Step 1. Connect to the TiUP control machine with four terminals

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

+ You will now have four 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_no_ime_R1`, `tbl_ime_R1`, `tbl_no_ime_R2`, `tbl_ime_R2`, `tbl_no_ime_R3`, and `tbl_ime_R3` under the `test` database, and understand the DDL statements used for table creation.

  ```sql
  DROP TABLE IF EXISTS test.tbl_no_ime_R1;
  CREATE TABLE test.tbl_no_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_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_no_ime_R2;
  CREATE TABLE test.tbl_no_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;

  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;

  DROP TABLE IF EXISTS test.tbl_no_ime_R3;
  CREATE TABLE test.tbl_no_ime_R3 (
  `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_R3;

  DROP TABLE IF EXISTS test.tbl_ime_R3;
  CREATE TABLE test.tbl_ime_R3 (
  `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_R3;

  EXIT;
  ```

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

  mysql>   CREATE TABLE test.tbl_no_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.10 sec)

  ...

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

  mysql>   CREATE TABLE test.tbl_ime_R3 (
      ->   `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_R3;
  Query OK, 0 rows affected, 5 warnings (0.07 sec)

  mysql> 
  mysql>   EXIT;
  Bye
  ```

+ Through the PLACEMENT POLICY definitions in the table creation DDL statements, we can understand that:
  + Tables `tbl_no_ime_R1` and `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_no_ime_R2` and `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`.
  + Tables `tbl_no_ime_R3` and `tbl_ime_R3` use PLACEMENT POLICY `IME_R3`, meaning all Regions with the `LEADER` role are stored on the TiKV node in `region3`, and read/write operations for them default to the TiKV node in `region3`.
  + The storage layout of tables `tbl_no_ime_R1`, `tbl_ime_R1`, `tbl_no_ime_R2`, `tbl_ime_R2`, `tbl_no_ime_R3`, and `tbl_ime_R3` within the TiKV cluster is illustrated in the following diagram:

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

### Step 3. Initialize Data

On [Terminal 1](ssh://monitor1-ssh#1), use the `tiup bench` tool to insert `1,000` rows of random data into each of the tables `tbl_no_ime_R1`, `tbl_no_ime_R2`, and `tbl_no_ime_R3`. For usage of the TiUP bench component, please refer to <https://docs.pingcap.com/tidb/stable/tiup-bench/>.

  ```bash
  cat > tbl_no_ime_R1_R2_R3.sql <<EOF
  INSERT INTO test.tbl_no_ime_R1 (pID, cID, sID, pState, cState, created_at, updated_at) VALUES (1 + FLOOR(RAND() * 99999), 100001 + FLOOR(RAND() * 99999), 200001 + FLOOR(RAND() * 99999), ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 29), NOW(), NOW());
  INSERT INTO test.tbl_no_ime_R2 (pID, cID, sID, pState, cState, created_at, updated_at) VALUES (1 + FLOOR(RAND() * 99999), 100001 + FLOOR(RAND() * 99999), 200001 + FLOOR(RAND() * 99999), ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 29), NOW(), NOW());
  INSERT INTO test.tbl_no_ime_R3 (pID, cID, sID, pState, cState, created_at, updated_at) VALUES (1 + FLOOR(RAND() * 99999), 100001 + FLOOR(RAND() * 99999), 200001 + FLOOR(RAND() * 99999), ELT(1 + FLOOR(RAND() * 9), 's1', 's2', 's3', 's4', 's5', 's6', 's7', 's8', 's9', 's0'), 1 + FLOOR(RAND() * 29), NOW(), NOW());
  EOF
  tiup bench rawsql run -H ${HOST_DB1_PRIVATE_IP} -P 4000 --count 1000 --threads 1 --query-files tbl_no_ime_R1_R2_R3.sql
  ```

  ```output
  A new version of bench is available:  -> v1.12.0

      To update this component:   tiup update bench
      To update all components:   tiup update --all

  The component `bench` version  is not installed; downloading from repository.
  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_no_ime_R1_R2_R3.sql
  Finished
  [Summary] TBL_NO_IME_R1_R2_R3: 0.01s
  ```

+ 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 three sequence (SEQUENCE) objects: `test.seq_ime_R1`, `test.seq_ime_R2`, and `test.seq_ime_R3`, for subsequent data insertion into tables `test.tbl_ime_R1`, `test.tbl_ime_R2`, and `test.tbl_ime_R3`.

  ```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;
  DROP SEQUENCE IF EXISTS test.seq_ime_R3;
  CREATE SEQUENCE test.seq_ime_R3;
  EXIT
  ```

  ```output
  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R1;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  mysql> CREATE SEQUENCE test.seq_ime_R1;
  Query OK, 0 rows affected (0.06 sec)

  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R2;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  mysql> CREATE SEQUENCE test.seq_ime_R2;
  Query OK, 0 rows affected (0.06 sec)

  mysql> DROP SEQUENCE IF EXISTS test.seq_ime_R3;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  mysql> CREATE SEQUENCE test.seq_ime_R3;
  Query OK, 0 rows affected (0.06 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 `tbl_ime_R1`. Unlike table `tbl_no_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_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_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_R1.sql
  [Current] TBL_IME_R1: 0.00s
  [Current] TBL_IME_R1: 0.00s
  [Current] TBL_IME_R1: 0.00s
  [Current] TBL_IME_R1: 0.00s
  [Current] TBL_IME_R1: 0.00s
  Finished
  [Summary] TBL_IME_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 `tbl_ime_R2`. Unlike table `tbl_no_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_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_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_R2.sql
  [Current] TBL_IME_R2: 0.00s
  [Current] TBL_IME_R2: 0.00s
  [Current] TBL_IME_R2: 0.00s
  [Current] TBL_IME_R2: 0.00s
  [Current] TBL_IME_R2: 0.00s
  [Current] TBL_IME_R2: 0.00s
  Finished
  [Summary] TBL_IME_R2: 0.00s
  ```

  + On [Terminal 1](ssh://monitor1-ssh#1), use the `tiup bench` tool to insert 1,000 rows of random data into table `tbl_ime_R3`. Unlike table `tbl_no_ime_R3`, 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_R3.sql <<EOF
  SELECT @sID := 200001 + FLOOR(RAND() * 99999);
  INSERT INTO test.tbl_ime_R3 (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_R3);
  UPDATE test.tbl_ime_R3 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_R3 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_R3 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_R3 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_R3 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_R3 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_R3 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_R3 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_R3 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_R3 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_R3.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_R3.sql
  [Current] TBL_IME_R3: 0.00s
  [Current] TBL_IME_R3: 0.00s
  [Current] TBL_IME_R3: 0.00s
  [Current] TBL_IME_R3: 0.00s
  [Current] TBL_IME_R3: 0.00s
  Finished
  [Summary] TBL_IME_R3: 0.00s
  ```

### Step 4. Check Data Distribution

+ 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 statement to view the distribution of tables `tbl_no_ime_R1`, `tbl_ime_R1`, `tbl_no_ime_R2`, `tbl_ime_R2`, `tbl_no_ime_R3`, and `tbl_ime_R3` under schema `test` across the TiKV cluster. In the output:
  + Column `DB_NAME` indicates the schema name.
  + Column `TABLE_NAME` indicates the table name.
  + Column `ADDRESS` indicates the TiKV node address.
  + Column `IS_LEADER` indicates whether it's a LEADER role Region (`1` for yes, `0` for no).
  + Column `IS_LEARNER` indicates whether it's a LEARNER role Region (`1` for yes, `0` for no).
  + Column `REGION COUNT` indicates the number of Regions.

  ```sql
  SELECT
    rs.`DB_NAME`,
    rs.`TABLE_NAME`,
    s.ADDRESS,
    r.`IS_LEADER`,
    r.`IS_LEARNER`,
    COUNT(r.REGION_ID) "REGION COUNT"
  FROM
    INFORMATION_SCHEMA.TIKV_REGION_PEERS r,
    INFORMATION_SCHEMA.TIKV_STORE_STATUS s,
    INFORMATION_SCHEMA.tikv_region_status rs
  WHERE r.STORE_ID = s.STORE_ID
    AND r.`REGION_ID` = rs.`REGION_ID`
    AND rs.`DB_NAME` = 'test'
    AND rs.`TABLE_NAME` IN ('tbl_no_ime_R1', 'tbl_no_ime_R2', 'tbl_no_ime_R3', 'tbl_ime_R1', 'tbl_ime_R2', 'tbl_ime_R3')
    AND r.`IS_LEARNER`=0
  GROUP BY rs.`DB_NAME`,
    rs.`TABLE_NAME`,
    s.`ADDRESS`,
    r.`IS_LEADER`,
    r.`IS_LEARNER`
    ORDER BY r.`IS_LEADER` DESC, rs.`DB_NAME`, s.ADDRESS, rs.`TABLE_NAME`;
  ```

  ```output
  +---------+---------------+-------------------+-----------+------------+--------------+
  | DB_NAME | TABLE_NAME    | ADDRESS           | IS_LEADER | IS_LEARNER | REGION COUNT |
  +---------+---------------+-------------------+-----------+------------+--------------+
  | test    | tbl_ime_R2    | 10.90.3.147:20160 |         1 |          0 |            4 |
  | test    | tbl_no_ime_R2 | 10.90.3.147:20160 |         1 |          0 |            4 |
  | test    | tbl_ime_R1    | 10.90.3.49:20160  |         1 |          0 |            4 |
  | test    | tbl_no_ime_R1 | 10.90.3.49:20160  |         1 |          0 |            4 |
  | test    | tbl_ime_R3    | 10.90.3.51:20160  |         1 |          0 |            4 |
  | test    | tbl_no_ime_R3 | 10.90.3.51:20160  |         1 |          0 |            4 |
  | test    | tbl_ime_R1    | 10.90.3.147:20160 |         0 |          0 |            4 |
  | test    | tbl_ime_R3    | 10.90.3.147:20160 |         0 |          0 |            4 |
  | test    | tbl_no_ime_R1 | 10.90.3.147:20160 |         0 |          0 |            4 |
  | test    | tbl_no_ime_R3 | 10.90.3.147:20160 |         0 |          0 |            4 |
  | test    | tbl_ime_R2    | 10.90.3.49:20160  |         0 |          0 |            4 |
  | test    | tbl_ime_R3    | 10.90.3.49:20160  |         0 |          0 |            4 |
  | test    | tbl_no_ime_R2 | 10.90.3.49:20160  |         0 |          0 |            4 |
  | test    | tbl_no_ime_R3 | 10.90.3.49:20160  |         0 |          0 |            4 |
  | test    | tbl_ime_R1    | 10.90.3.51:20160  |         0 |          0 |            4 |
  | test    | tbl_ime_R2    | 10.90.3.51:20160  |         0 |          0 |            4 |
  | test    | tbl_no_ime_R1 | 10.90.3.51:20160  |         0 |          0 |            4 |
  | test    | tbl_no_ime_R2 | 10.90.3.51:20160  |         0 |          0 |            4 |
  +---------+---------------+-------------------+-----------+------------+--------------+
  18 rows in set (0.02 sec)
  ```

+ From the results of the previous SQL command, we can conclude:
  + Tables `tbl_no_ime_R1` and `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_no_ime_R2` and `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`.
  + Tables `tbl_no_ime_R3` and `tbl_ime_R3` use PLACEMENT POLICY `IME_R3`, meaning all Regions with the `LEADER` role are stored on the TiKV node in `region3`, and read/write operations for them default to the TiKV node in `region3`.

+ The distribution of tables `tbl_no_ime_R1`, `tbl_ime_R1`, `tbl_no_ime_R2`, `tbl_ime_R2`, `tbl_no_ime_R3`, and `tbl_ime_R3` under schema `test` across the TiKV cluster is shown in the following diagram. The reason why `tbl_ime_R1`, `tbl_ime_R2`, and `tbl_ime_R3` have more data than `tbl_no_ime_R1`, `tbl_no_ime_R2`, and `tbl_no_ime_R3` is that they underwent update (UPDATE) operations during insertion, resulting in significant MVCC historical snapshot data.

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

+ On [Terminal 1](ssh://monitor1-ssh#1), execute the two SQL commands below, these two SQL commands perform query operations on table `test.tbl_no_ime_r1` and `test.tbl_ime_r1`. Click to enter.

  ```sql
  SELECT sID, ID, txt FROM test.tbl_no_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;
  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;
  EXIT
  ```

  ```output
  +--------+-----+---------+
  | sID    | ID  | txt     |
  +--------+-----+---------+
  | 203063 | 275 | IMETEST |
  | 203099 | 901 | IMETEST |
  ...
  | 295013 | 475 | IMETEST |
  | 297637 | 920 | IMETEST |
  +--------+-----+---------+
  50 rows in set (0.03 sec)

  mysql> 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;
  +--------+-----+---------+
  | sID    | ID  | txt     |
  +--------+-----+---------+
  | 203120 | 248 | IMETEST |
  | 205100 | 158 | IMETEST |
  ...
  | 296901 | 455 | IMETEST |
  | 297482 | 113 | IMETEST |
  +--------+-----+---------+
  50 rows in set (0.02 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_PART2_DASHBOARD_1.png 70%](https://lab-static.pingcap.com/images/2025/8/19/f5b0606c31a88d5ed4ef29a11154046f4f92f7d9.png)

+ Select the SQL command starting with SELECT `sid`, `id`, `txt` FROM `test`.`tbl_no_ime_r1` WHERE .... This SQL command performs a query operation on table 'test.tbl_no_ime_r1'. Click to enter.

  ![IME_PART2_DASHBOARD_2.png 70%](https://lab-static.pingcap.com/images/2025/8/19/28355b3509e4076a27cde011953fd7ffdd042e86.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. 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 `97`, indicating that the number of rows needing scan filtering is similar to the total number of rows actually stored in the table.

  ![IME_PART2_DASHBOARD_3.png 70%](https://lab-static.pingcap.com/images/2025/8/19/2fd7e5de180aa79f99ce50b3ac69ffd6327aa26a.png)

+ In the `Execution Detail` section, check the `Coprocessor Read` item. Note that `Mean Visible Versions Per Query` is `146` and `Mean Meet Versions Per Query` is `197`. These two values are similar. Since the value of `Mean Meet Versions Per Query` may include MVCC historical snapshot data, this indicates that the query did not scan excessive MVCC historical snapshot data.

  ![IME_PART2_DASHBOARD_4.png 70%](https://lab-static.pingcap.com/images/2025/8/19/69e9763ec43113a737fbd97458b25b3258f92871.png)

+ Return to the previous page and select the SQL command starting with SELECT `sid`, `id`, `txt` FROM `test`.`tbl_ime_r1` WHERE (.... This SQL command performs a query operation on table `test.tbl_ime_r1`. Click to enter.

  ![IME_PART2_DASHBOARD_5.png 70%](https://lab-static.pingcap.com/images/2025/8/19/2d51f054945395ca95606fddc14bb7354523501d.png)

+ In the `Execution Detail` section, navigate to the `Execution Plan` item. Locate the step `Limit_13(Build)` and examine its `Execution Info` information. You will find that `total_process_keys` is `96` and `total_keys` is `1865`, indicating that the number of rows needing scan filtering is much smaller than the total number of rows actually stored in the table. (Your results may vary.)

  ![IME_PART2_DASHBOARD_6.png 70%](https://lab-static.pingcap.com/images/2025/8/19/94b738494b9f66c89fd9d13462c604820a0f082b.png)

+ In the `Execution Detail` section, check the `Coprocessor Read` item. Note that `Mean Visible Versions Per Query` is `146` and `Mean Meet Versions Per Query` is `2.3K`. These two values differ significantly. Since the value of `Mean Meet Versions Per Query` may include MVCC historical snapshot data, this indicates that the query scans excessive MVCC historical snapshot data. (Your results may vary.)

  ![IME_PART2_DASHBOARD_7.png 70%](https://lab-static.pingcap.com/images/2025/8/19/6103fc9a975764597ec03bb44d2a3e54e83a7d35.png)

+ Note: For query operations similar to those on table `test.tbl_ime_r1`, if the `Execution Info` of any operator in the execution plan shows a significant difference between `total_process_keys` and `total_keys`, such query operations are suitable for optimization using the TiKV MVCC in-memory engine feature. Similar results would be observed for queries on tables `tbl_no_ime_R2` vs `tbl_ime_R2` and `tbl_no_ime_R3` vs `tbl_ime_R3`, which are omitted here.

### Step 5. Using the TiKV MVCC In-Memory Engine(IME)

+ To understand the usage of the TiKV MVCC In-Memory Engine(IME), log in to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), navigate to `Home`, then select `tidb-demo`, followed by `tidb-demo-TiKV-Details`. Open the `In Memory Engine` tab. Under this tab, you can view the `Region Cache Hit`, `Region Cache Hit Rate`, and `Region Cache Miss Reason` panels. (`Region Cache Hit` indicates the number of successful data retrievals from the TiKV MVCC In-Memory Engine(IME), `Region Cache Hit Rate` shows the hit rate of the TiKV MVCC In-Memory Engine(IME), and the `Region Cache Miss Reason` panel displays the number of queries on TiKV nodes with the TiKV MVCC In-Memory Engine(IME) enabled that did not utilize it). For details about each panel in the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, please refer to : <https://docs.pingcap.com/tidb/stable/grafana-tikv-dashboard/#in-memory-engine>.

  ![IME_PART2_TiKV_DETAIL_IME_1.png 70%](https://lab-static.pingcap.com/images/2025/8/20/90706bc00ff24f6ca060b5fad2bf527d8b3e165b.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.

  ```bash
  cat > query_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 300000 --threads 1 --query-files query_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 300000 --threads 1 --query-files query_tbl_ime_R1.sql
  [Current] QUERY_TBL_IME_R1: 0.00s
  [Current] QUERY_TBL_IME_R1: 0.00s
  ...
  ```

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

  ```bash
  cat > query_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 300000 --threads 1 --query-files query_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 300000 --threads 1 --query-files query_tbl_ime_R2.sql
  [Current] QUERY_TBL_IME_R2: 0.00s
  [Current] QUERY_TBL_IME_R2: 0.00s
  ...
  ```

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

  ```bash
  cat > query_tbl_ime_R3.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_ime_R3 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 300000 --threads 1 --query-files query_tbl_ime_R3.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 300000 --threads 1 --query-files query_tbl_ime_R3.sql
  [Current] QUERY_TBL_IME_R3: 0.00s
  [Current] QUERY_TBL_IME_R3: 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 currently only query operations on the TiKV node in `region2` are utilizing the TiKV MVCC In-Memory Engine(IME). Its `Region Cache Hit` and `Region Cache Hit Rate` are starting to rise, while `Region Cache Miss Reason` remains unchanged or begins to decline. The TiKV node in `region1` does not show any activity because the TiKV MVCC In-Memory Engine(IME) is not enabled there. Although the TiKV node in `region3` has the TiKV MVCC In-Memory Engine(IME) enabled, its `Region Cache Miss Reason` is increasing, while `Region Cache Hit` and `Region Cache Hit Rate` are not rising, as shown in the following diagram:

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

+ The reason the TiKV node in `region3` is not utilizing the TiKV MVCC In-Memory Engine(IME), despite having it enabled, is that its `in-memory-engine.mvcc-amplification-threshold` is set to `100`, but the actual MVCC read amplification value has not reached `100`, as illustrated below:

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

### Step 6. Config TiKV MVCC In-Memory Engine(IME)

+ 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), query the configuration of all TiKV nodes regarding the TiKV MVCC In-Memory Engine(IME).

  ```sql
  show config where type='tikv' and name like 'in-memory-engine%';
  ```

  ```output
  +------+-------------------+-----------------------------------------------+------------+
  | Type | Instance          | Name                                          | Value      |
  +------+-------------------+-----------------------------------------------+------------+
  | tikv | 10.90.3.49:20160  | in-memory-engine.capacity                     | 1GiB       |
  | tikv | 10.90.3.49:20160  | in-memory-engine.cross-check-interval         | 0s         |
  | tikv | 10.90.3.49:20160  | in-memory-engine.enable                       | false      |
  | tikv | 10.90.3.49:20160  | in-memory-engine.evict-threshold              | null       |
  | tikv | 10.90.3.49:20160  | in-memory-engine.gc-run-interval              | 3m         |
  | tikv | 10.90.3.49:20160  | in-memory-engine.load-evict-interval          | 2m         |
  | tikv | 10.90.3.49:20160  | in-memory-engine.mvcc-amplification-threshold | 5          |
  | tikv | 10.90.3.49:20160  | in-memory-engine.stop-load-threshold          | null       |
  | tikv | 10.90.3.147:20160 | in-memory-engine.capacity                     | 1GiB       |
  | tikv | 10.90.3.147:20160 | in-memory-engine.cross-check-interval         | 0s         |
  | tikv | 10.90.3.147:20160 | in-memory-engine.enable                       | true       |
  | tikv | 10.90.3.147:20160 | in-memory-engine.evict-threshold              | 966367642B |
  | tikv | 10.90.3.147:20160 | in-memory-engine.gc-run-interval              | 3m         |
  | tikv | 10.90.3.147:20160 | in-memory-engine.load-evict-interval          | 2m         |
  | tikv | 10.90.3.147:20160 | in-memory-engine.mvcc-amplification-threshold | 5          |
  | tikv | 10.90.3.147:20160 | in-memory-engine.stop-load-threshold          | 805306369B |
  | tikv | 10.90.3.51:20160  | in-memory-engine.capacity                     | 1GiB       |
  | tikv | 10.90.3.51:20160  | in-memory-engine.cross-check-interval         | 0s         |
  | tikv | 10.90.3.51:20160  | in-memory-engine.enable                       | true       |
  | tikv | 10.90.3.51:20160  | in-memory-engine.evict-threshold              | 966367642B |
  | tikv | 10.90.3.51:20160  | in-memory-engine.gc-run-interval              | 3m         |
  | tikv | 10.90.3.51:20160  | in-memory-engine.load-evict-interval          | 2m         |
  | tikv | 10.90.3.51:20160  | in-memory-engine.mvcc-amplification-threshold | 100        |
  | tikv | 10.90.3.51:20160  | in-memory-engine.stop-load-threshold          | 805306369B |
  +------+-------------------+-----------------------------------------------+------------+
  24 rows in set (0.04 sec)
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), modify the `in-memory-engine.mvcc-amplification-threshold` parameter to `5` for the TiKV node in `region3`. Please configure this according to your current TiKV node IP address.

  ```sql
  set config "10.90.3.51:20160" `in-memory-engine.mvcc-amplification-threshold`=5;
  show config where instance='10.90.3.51:20160' and type='tikv' and name like 'in-memory-engine%';
  ```

  ```output
  mysql> set config "10.90.3.51:20160" `in-memory-engine.mvcc-amplification-threshold`=5;
  Query OK, 0 rows affected (0.02 sec)

  mysql> show config where instance='10.90.3.51:20160' and type='tikv' and name like 'in-memory-engine%';
  +------+------------------+-----------------------------------------------+------------+
  | Type | Instance         | Name                                          | Value      |
  +------+------------------+-----------------------------------------------+------------+
  | tikv | 10.90.3.51:20160 | in-memory-engine.capacity                     | 1GiB       |
  | tikv | 10.90.3.51:20160 | in-memory-engine.cross-check-interval         | 0s         |
  | tikv | 10.90.3.51:20160 | in-memory-engine.enable                       | true       |
  | tikv | 10.90.3.51:20160 | in-memory-engine.evict-threshold              | 966367642B |
  | tikv | 10.90.3.51:20160 | in-memory-engine.gc-run-interval              | 3m         |
  | tikv | 10.90.3.51:20160 | in-memory-engine.load-evict-interval          | 2m         |
  | tikv | 10.90.3.51:20160 | in-memory-engine.mvcc-amplification-threshold | 5          |
  | tikv | 10.90.3.51:20160 | in-memory-engine.stop-load-threshold          | 805306369B |
  +------+------------------+-----------------------------------------------+------------+
  8 rows in set (0.04 sec)
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), in the `In Memory Engine` tab of `tidb-demo-TiKV-Details`, approximately 2 minutes after the previous operation, you will observe that query operations on the TiKV node in `region2` are now utilizing the TiKV MVCC In-Memory Engine(IME). Its `Region Cache Hit` and `Region Cache Hit Rate` will begin to rise, while `Region Cache Miss Reason` will start to decline. The TiKV node in `region1` still shows no activity as the TiKV MVCC In-Memory Engine(IME) remains disabled there, as illustrated below:

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

+ On [Terminal 1](ssh://monitor1-ssh#1), attempt to modify the `in-memory-engine.enable` parameter to `true` for the TiKV node in `region1`. Please configure this according to your current TiKV node IP address.

  ```sql
  set config "10.90.3.49:20160" `in-memory-engine.in-memory-engine.enable`=true;
  show config where instance='10.90.3.49:20160' and type='tikv' and name like 'in-memory-engine%';
  ```

  ```output
  mysql> set config "10.90.3.49:20160" `in-memory-engine.in-memory-engine.enable`=true;
  Query OK, 0 rows affected, 1 warning (0.02 sec)

  mysql> show config where instance='10.90.3.49:20160' and type='tikv' and name like 'in-memory-engine%';
  +------+------------------+-----------------------------------------------+-------+
  | Type | Instance         | Name                                          | Value |
  +------+------------------+-----------------------------------------------+-------+
  | tikv | 10.90.3.49:20160 | in-memory-engine.capacity                     | 1GiB  |
  | tikv | 10.90.3.49:20160 | in-memory-engine.cross-check-interval         | 0s    |
  | tikv | 10.90.3.49:20160 | in-memory-engine.enable                       | false |
  | tikv | 10.90.3.49:20160 | in-memory-engine.evict-threshold              | null  |
  | tikv | 10.90.3.49:20160 | in-memory-engine.gc-run-interval              | 3m    |
  | tikv | 10.90.3.49:20160 | in-memory-engine.load-evict-interval          | 2m    |
  | tikv | 10.90.3.49:20160 | in-memory-engine.mvcc-amplification-threshold | 5     |
  | tikv | 10.90.3.49:20160 | in-memory-engine.stop-load-threshold          | null  |
  +------+------------------+-----------------------------------------------+-------+
  8 rows in set (0.04 sec)
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1), disconnect the client session.

  ```sql
  EXIT
  ```

  ```output
  mysql> EXIT
  Bye
  ```

+ The `in-memory-engine.enable` parameter for the TiKV node in `region1` remains `false` because the TiKV MVCC In-Memory Engine(IME) feature cannot be modified online for TiKV nodes. Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), navigate to the `In Memory Engine` tab in `tidb-demo-TiKV-Details`. You will observe that query operations on both the `region2` and `region3` TiKV nodes are utilizing the TiKV MVCC In-Memory Engine(IME) feature, while the `region1` TiKV node is not using it. Note: Your results may differ from the lab guide; the `Region Cache Hit Rate` for queries on `region2` and `region3` TiKV nodes may not necessarily reach 100%.

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

+ The usage status of the TiKV MVCC In-Memory Engine(IME) across TiKV nodes in `region1`, `region2`, and `region3` is illustrated below:

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

+ On [Terminal 2](ssh://monitor1-ssh#2), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_ime_R1`.

  ```output
  ...
  [Current] QUERY_TBL_IME_R1: 0.00s
  [Current] QUERY_TBL_IME_R1: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23009)
  Finished
  [Summary] QUERY_TBL_IME_R1: 0.00s
  [Summary] QUERY_TBL_IME_R1_ERR: 0.00s
  ...
  ```

+ On [Terminal 3](ssh://monitor1-ssh#3), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_ime_R2`.

  ```output
  [Current] QUERY_TBL_IME_R2: 0.00s
  [Current] QUERY_TBL_IME_R2: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23314)
  Finished
  [Summary] QUERY_TBL_IME_R2: 0.00s
  [Summary] QUERY_TBL_IME_R2_ERR: 0.00s
  ```

+ On [Terminal 4](ssh://monitor1-ssh#4), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_ime_R3`.

  ```output
  [Current] QUERY_TBL_IME_R3: 0.00s
  [Current] QUERY_TBL_IME_R3: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23495)
  [ec2-user@ip-10-90-4-217 ~]$ Finished
  [Summary] QUERY_TBL_IME_R3: 0.00s
  ```

+ On [Terminal 2](ssh://monitor1-ssh#2), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_no_ime_R1`. The scan will run continuously; please do not wait for completion and proceed immediately to the next step.

  ```bash
  cat > query_tbl_no_ime_R1.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_no_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 300000 --threads 1 --query-files query_tbl_no_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 300000 --threads 1 --query-files query_tbl_no_ime_R1.sql
  [Current] QUERY_TBL_NO_IME_R1: 0.00s
  [Current] QUERY_TBL_NO_IME_R1: 0.00s
  ...
  ```

+ On [Terminal 3](ssh://monitor1-ssh#3), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_no_ime_R2`. The scan will run continuously; please do not wait for completion and proceed immediately to the next step.

  ```bash
  cat > query_tbl_no_ime_R2.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_no_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 300000 --threads 1 --query-files query_tbl_no_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 300000 --threads 1 --query-files query_tbl_no_ime_R2.sql
  [Current] QUERY_TBL_NO_IME_R2: 0.00s
  [Current] QUERY_TBL_NO_IME_R2: 0.00s
  ...
  ```

+ On [Terminal 4](ssh://monitor1-ssh#4), use the `tiup bench` tool to perform a continuous scan on table `test.tbl_no_ime_R3`. The scan will run continuously; please do not wait for completion and proceed immediately to the next step.

  ```bash
  cat > query_tbl_no_ime_R3.sql <<EOF
  SELECT sID, ID, txt FROM test.tbl_no_ime_R3 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 300000 --threads 1 --query-files query_tbl_no_ime_R3.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 300000 --threads 1 --query-files query_tbl_no_ime_R3.sql
  [Current] QUERY_TBL_NO_IME_R3: 0.00s
  [Current] QUERY_TBL_NO_IME_R3: 0.00s
  ...
  ```

+ Return to [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000), navigate to 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 currently, aside from the `region1` TiKV node (which remains disabled and shows no activity), query operations on both the `region2` and `region3` TiKV nodes are not utilizing the TiKV MVCC In-Memory Engine(IME). Their `Region Cache Hit` and `Region Cache Hit Rate` remain low without increase, while `Region Cache Miss Reason` rises and maintains high values. This occurs because the MVCC read amplification values for tables `test.tbl_no_ime_R2` and `test.tbl_no_ime_R3` do not exceed the threshold value of `5` set in the `in-memory-engine.mvcc-amplification-threshold` parameter for the TiKV nodes in `region2` and `region3`, as shown below:

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

+ On [Terminal 2](ssh://monitor1-ssh#2), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_no_ime_R1`.

  ```output
  ...
  [Current] QUERY_TBL_NO_IME_R1: 0.00s
  [Current] QUERY_TBL_NO_IME_R1: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23800)
  Finished
  [Summary] QUERY_TBL_NO_IME_R1: 0.00s
  [Summary] QUERY_TBL_NO_IME_R1_ERR: 0.00s
  ...
  ```

+ On [Terminal 3](ssh://monitor1-ssh#3), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_no_ime_R2`.

  ```output
  [Current] QUERY_TBL_NO_IME_R2: 0.00s
  [Current] QUERY_TBL_NO_IME_R2: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23768)
  Finished
  [Summary] QUERY_TBL_NO_IME_R2: 0.00s
  [Summary] QUERY_TBL_NO_IME_R2_ERR: 0.00s
  ```

+ On [Terminal 4](ssh://monitor1-ssh#4), press**CTRL-C**to stop the `tiup bench` tool from scanning table `test.tbl_no_ime_R3`.

  ```output
  [Current] QUERY_TBL_NO_IME_R3: 0.00s
  [Current] QUERY_TBL_NO_IME_R3: 0.00s
  ^C
  Got signal [interrupt] to exit.
  Got signal interrupt (Component: bench ; PID: 23809)
  [ec2-user@ip-10-90-4-217 ~]$ Finished
  [Summary] QUERY_TBL_NO_IME_R3: 0.00s
  ```

### Step 7. 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_no_ime_R1;
  DROP TABLE IF EXISTS test.tbl_no_ime_R2;
  DROP TABLE IF EXISTS test.tbl_no_ime_R3;

  DROP TABLE IF EXISTS test.tbl_ime_R1;
  DROP TABLE IF EXISTS test.tbl_ime_R2;
  DROP TABLE IF EXISTS test.tbl_ime_R3;

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

  EXIT
  ```

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

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

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

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

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

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

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

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

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

  mysql>   EXIT;
  Bye
  ```

+ This is the end of Module 2.