## Module 6: TiKV MVCC In-memory Engine (v8.5.0)

Multi-Version Concurrency Control (MVCC) is a critical aspect of modern databases, allowing concurrent access without compromising integrity. TiKV MVCC In-Memory Engine is primarily used to accelerate queries that need to scan a large number of MVCC historical versions. A well optimized query may become a slow query because it scans lots of MVCC versions.

TiKV MVCC in-memory engine is suitable for the following scenarios to improve the query performance:

+ The application that requires querying records that are frequently updated or deleted.
+ The application that requires adjusting [tidb_gc_life_time](https://docs.pingcap.com/tidb/stable/system-variable-reference/#tidb_gc_life_time) to retain historical versions in TiDB for a longer period.

### Step 1. Check the default in-memory-engine settings

+ Connect to TiDB.

  ```bash
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root
  ```

  ```output
  mysql> 
  ```

+ Verify that the TiKV MVCC `in-memory-engine` is turned off by default.

  ```sql
  SHOW CONFIG WHERE type='tikv' AND name='in-memory-engine.enable';
  ```

  ```output
  +------+-------------------+-------------------------+-------+
  | Type | Instance          | Name                    | Value |
  +------+-------------------+-------------------------+-------+
  | tikv | 10.90.1.41:20160  | in-memory-engine.enable | false |
  | tikv | 10.90.1.223:20160 | in-memory-engine.enable | false |
  | tikv | 10.90.1.231:20160 | in-memory-engine.enable | false |
  +------+-------------------+-------------------------+-------+
  3 rows in set (0.01 sec)
  ```

+ Increase the `tidb_gc_life_time` so that the previous versions can be kept during the lifetime of this workshop.

  ```sql
  SET GLOBAL tidb_gc_life_time='1440m';
  ```

### Step 2. Verify the behavior while the in-memory-engine setting is OFF

+ Set up two test tables named `ime_test1` and `ime_test2` with the same schema. Note that there is an index on column `content` for each table.

  ```sql
  DROP TABLE IF EXISTS test.ime_test1;
  DROP TABLE IF EXISTS test.ime_test2;
  CREATE TABLE test.ime_test1 (
      `id` BIGINT AUTO_RANDOM PRIMARY KEY,
      `content` INT,
      `created_at` DATETIME,
      `updated_at` DATETIME,
      `version` INT,
      KEY content_idx (`content`)
  );
  CREATE TABLE test.ime_test2 (
      `id` BIGINT AUTO_RANDOM PRIMARY KEY,
      `content` INT,
      `created_at` DATETIME,
      `updated_at` DATETIME,
      `version` INT,
      KEY content_idx (`content`)
  );
  ```

+ Insert one row into both tables.

  ```sql
  INSERT INTO test.ime_test1 (
      content, created_at, updated_at, version
  ) VALUES (
      FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1
  );
  INSERT INTO test.ime_test2 (
      content, created_at, updated_at, version
  ) VALUES (
      FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1
  );
  SELECT * FROM test.ime_test1;
  SELECT * FROM test.ime_test2;
  EXIT
  ```

+ Expand both table with 32768 rows.

  ```bash
  cat > double.sql <<EOF
  INSERT INTO test.ime_test1 (
      content, created_at, updated_at, version
  )
  SELECT FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1 FROM test.ime_test1;
  INSERT INTO test.ime_test2 (
      content, created_at, updated_at, version
  )
  SELECT FLOOR(1 + (RAND() * 99999999)), NOW(), NOW(), 1 FROM test.ime_test2;
  EOF
  cat > double.sh <<EOF
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root < double.sql
  EOF
  chmod +x double.sh
  for c in {1..15}; do
  ./double.sh
  done;
  ```

+ Generate 100 versions for each row in table `test.ime_test2` by executing update operations on `content` column 100 times. It will take approximately 2 minutes in our lab environment (which is not optimized for performance).

  ```bash
  cat > update_hog.sql <<EOF
  UPDATE test.ime_test2
  SET content = FLOOR(1 + (RAND() * 99999999)), version = version + 1;
  EOF
  cat > update_hog.sh <<EOF
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root < update_hog.sql
  EOF
  chmod +x update_hog.sh
  for c in {1..100}; do
  ./update_hog.sh
  done;
  ```

+ Connect to TiDB again.

  ```bash
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root
  ```

  ```output
  mysql> 
  ```

+ Run two SQL statements that perform a full table scan on the tables `test.ime_test1` and `test.ime_test2`.

  ```sql
  SELECT COUNT(*) FROM test.ime_test1;
  SELECT COUNT(*) FROM test.ime_test2;
  EXIT
  ```

  ```output
  +----------+
  | COUNT(*) |
  +----------+
  |    32768 |
  +----------+
  1 row in set (0.03 sec)

  +----------+
  | COUNT(*) |
  +----------+
  |    32768 |
  +----------+
  1 row in set (1.09 sec)

  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` and `Update`.

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

+ In the top right corner of the Dashboard, select `Columns` and check the menu items `Mean Visible Version Per Query` and `Mean Meet Versions Per Query`, then click the `Query` button.

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

+ From the query results, for the scan of the table `test.ime_test1`, `Mean Visible Version Per Query` is equal to `Mean Meet Versions Per Query`, indicating that there are no MVCC snapshots for processing.
In contrast, for the scan of the table `test.ime_test2`, `Mean Meet Versions Per Query` is significantly higher than `Mean Visible Version Per Query`, suggesting that there is a large amount of MVCC snapshot processing during the scan. This is also evident from the `Total Latency` and `Mean Latency` columns, which show that for the same number of rows scanned, the execution times differ, with a amount of time consumed by the scanning of MVCC snapshots for the table `test.ime_test2`.

  ![95%](https://lab-static.pingcap.com/quick-demo/ime-off-dashboard-sql-5.png)

+ Click on the [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) and log in using the username and password (both are "admin"). Navigate to `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine`, scroll down to the panels about `Region Cache Hit`，`Region Cache Hit Rate`，`Region Cache Miss Reason`，`Memory Usage` and `Region Count`.

  ![90%](https://lab-static.pingcap.com/quick-demo/ime-off-grafana-no-data-1.png)

+ Currently, they both show `No data`, because the TiKV MVCC In-memory Engine is not enabled in the current database cluster. Change the `Absolute time range` to `Last 5 minutes`.

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

+ Change the `Refresh time` to `10s`.

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

### Step 3. Enable the TiKV MVCC In-memory Engine

+ Return to [Terminal 1](ssh://monitor1-ssh#1) , use `tiup cluster edit-config` to open the TiDB cluster configuration in `vi`.

  ```bash
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup cluster edit-config tidb-demo
  ```

+ To enable the TiKV MVCC In-memory Engine, set `in-memory-engine.capacity` to `1GiB`, `in-memory-engine.enable` to `true`, and `in-memory-engine.mvcc-amplification-threshold` to `100` for ALL THREE `tikv_servers`.

+ Copy the following section below each TiKV's `os: linux` entry, then use `:wq` to save and quit.

  ```yaml
    config:
      in-memory-engine.capacity: 1GiB
      in-memory-engine.enable: true
      in-memory-engine.mvcc-amplification-threshold: 50
  ```

  ```output
  ...
  tikv_servers:
  - host: 10.90.1.41
    ssh_port: 22
    port: 20160
    status_port: 20180
    deploy_dir: /tidb-deploy/tikv-20160
    data_dir: /tidb-data/tikv-20160
    log_dir: /tidb-deploy/tikv-20160/log
    arch: amd64
    os: linux
    config:
      in-memory-engine.capacity: 1GiB
      in-memory-engine.enable: true
      in-memory-engine.mvcc-amplification-threshold: 50
  - host: 10.90.1.223
    ssh_port: 22
    port: 20160
    status_port: 20180
    deploy_dir: /tidb-deploy/tikv-20160
    data_dir: /tidb-data/tikv-20160
    log_dir: /tidb-deploy/tikv-20160/log
    arch: amd64
    os: linux
    config:
      in-memory-engine.capacity: 1GiB
      in-memory-engine.enable: true
      in-memory-engine.mvcc-amplification-threshold: 50
  - host: 10.90.1.231
    ssh_port: 22
    port: 20160
    status_port: 20180
    deploy_dir: /tidb-deploy/tikv-20160
    data_dir: /tidb-data/tikv-20160
    log_dir: /tidb-deploy/tikv-20160/log
    arch: amd64
    os: linux
    config:
      in-memory-engine.capacity: 1GiB
      in-memory-engine.enable: true
      in-memory-engine.mvcc-amplification-threshold: 50
  ...
  ```

+ Answer `y` to the prompt.

  ```output
  Please check change highlight above, do you want to apply the change? [y/N]:(default=N) y
  Applying changes...
  Applied successfully, please use `tiup cluster reload tidb-demo [-N <nodes>] [-R <roles>]` to reload config.
  ```

+ Run `tiup cluster reload` to reload the config.

  ```bash
  tiup cluster reload tidb-demo --yes
  ```

  ```output
  ...
  Reloaded cluster `tidb-demo` successfully
  ```

+ After the config is reloaded, connect to TiDB.

  ```bash
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root
  ```

  ```output
  mysql> 
  ```

+ Verify that the TiKV MVCC `in-memory-engine` is turned on.

  ```sql
  SHOW CONFIG WHERE type='tikv' AND name='in-memory-engine.enable';
  EXIT
  ```

  ```output
  +------+-------------------+-------------------------+-------+
  | Type | Instance          | Name                    | Value |
  +------+-------------------+-------------------------+-------+
  | tikv | 10.90.1.41:20160  | in-memory-engine.enable | true  |
  | tikv | 10.90.1.223:20160 | in-memory-engine.enable | true  |
  | tikv | 10.90.1.231:20160 | in-memory-engine.enable | true  |
  +------+-------------------+-------------------------+-------+
  3 rows in set (0.02 sec)

  Bye
  ```

### Step 4. Verify the behavior while the in-memory-engine setting is ON

+ Click on the [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) and log in using the username and password (both are "admin"). Navigate to `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine`, scroll down to the panels about `Region Cache Hit`，`Region Cache Hit Rate`，`Region Cache Miss Reason`，`Memory Usage` and `Region Count`. Now, these panels no longer display `No data`，however, the values for `Region Cache Hit`, `Region Cache Hit Rate`, `Region Cache Miss Reason`, `Memory Usage`, and `Region Count` are very low, almost zero, because there are still no queries for MVCC snapshots currently.

  ![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-no-query-1.png)

+ Return to [Terminal 1](ssh://monitor1-ssh#1) , execute 10,000 scans on the `test.ime_test1` table via the command line, while monitoring the `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine` panel in the [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) during the scanning process.

  ```bash
  for i in `seq 10000`;
  do mysql -uroot -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -e "SELECT COUNT(*) FROM test.ime_test1;";
  done;
  ```

+ During the scanning process of the `test.ime_test1` table, it can be observed from the `tidb-demo-TikV-Details` > `In Memory Engine` panel that there is little change in both `Region Cache Hit` and `Region Cache Hit Rate`. (The `Region Cache Hit` indicates the number of times data is successfully retrieved from the Region cache, while the `Region Cache Hit Rate` represents the hit rate of the Region cache.) The reason for their minimal changes is that the `test.ime_test1` table itself does not have MVCC historical snapshots.

  ![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-ime-test1.png)

+ After the 10,000 scans on the `test.ime_test1` table finish, return to [Terminal 1](ssh://monitor1-ssh#1) , execute another 50,000 scans on the `test.ime_test2` table via the command line, while monitoring the `Home` > `tidb-demo` > `tidb-demo-TikV-Details` > `In Memory Engine` panel in the [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) again during the scanning process.

  ```bash
  for i in `seq 50000`;
  do mysql -uroot -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -e "SELECT COUNT(*) FROM test.ime_test2;";
  done;
  ```

+ During the scanning process of the `test.ime_test2` table, it can be observed from the `tidb-demo-TikV-Details` > `In Memory Engine` panel that there are significant changes in both `Region Cache Hit` and `Region Cache Hit Rate`. The reason for this is that the `test.ime_test2` table itself contains a large number of MVCC historical snapshots. The TiKV MVCC in-memory engine caches the latest written MVCC versions in memory and implements an MVCC GC mechanism independent of TiDB. As a result, a substantial amount of data is read from the cache of the TiKV MVCC in-memory engine during the scanning process.

![90%](https://lab-static.pingcap.com/quick-demo/ime-on-grafana-ime-test2.png)

+ The TiKV MVCC In-memory Engine may sometimes not cache data in a timely manner; in this case, you can try repeating the steps above, scanning the `ime_test2` table again and observing the monitoring panel.