## Module 4: Using Key Visualizer

+ In this exercise, you will use the sysbench tool to simulate concurrent data insertion and adopt the `AUTO_RANDOM` option to attempt to split hotspots.

### Step 1. Config the `key visualizer` of [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard)

+ In the `key visualizer` of [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard), set the refresh interval to `30s`. The default user name is `root` with no password.

### Step 2. Read the script `oltp_common_1.lua`

+ Pay attention to the `CREATE TABLE` statement. Consider what problems may arise from the method of table creation used.

   ```bash
   cat oltp_common_1.lua 
   ```

   ```output
   cat oltp_common_1.lua 
   ...
   query = string.format([[
   CREATE TABLE sbtest%d(
     id INT(11) NOT NULL AUTO_INCREMENT,
     k INT(11) NOT NULL DEFAULT '0',
     c CHAR(120) NOT NULL DEFAULT '',
     pad CHAR(60) DEFAULT '' NOT NULL,
     PRIMARY KEY (id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin]],
         table_num)

   con:query(query)
   ...
   ```

+ The table created has a clustered index for primary key. It uses `AUTO_INCREMENT` on the primary key column `id`. This may result in hotspot problems during concurrent insertion.

### Step 3. Check the configuration file of sysbench tool

   ```bash
   cat config_lab01
   ```

   ```output
   mysql-host=10.90.1.178 
   mysql-port=4000 
   mysql-user=root 
   mysql-db=sbtest 
   mysql-password=
   time=600 
   threads=32 
   report-interval=10 
   db-driver=mysql
   ```

+ Here are more detailed explanation:
  + time: The running time of the test.
  + thread: The number of concurrent sessions.
  + report-interval: Printing report interval (unit: seconds).
  + db-driver: MySQL compatible syntax.

### Step 4. Create a new database for sysbench testing

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

   ```sql
   CREATE DATABASE sbtest;
   EXIT;
   ```

### Step 5. Run sysbench to simulate the scenario of concurrent data insertion

+ This step may take up to 5 minutes, and during the process, we can monitor whether there are hotspots generated through the traffic visualization chart in [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard).  

   ```bash
   sysbench './oltp_common_1.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   ```

   ```output
    sysbench './oltp_common_1.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Initializing worker threads...

   Creating table 'sbtest1'...
   Inserting 20000000 records into 'sbtest1'
   ```

+ table-size : The number of rows will be inserted to the table.
+ After the completion of the sysbench test, observe the write traffic within 1 hour in the traffic visualization chart in [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard). The following figure is generated with 20,000,000 insertions. Your figure might be different. Consider why this occurs and whether it can be optimized.

   ![30%](https://lab-static.pingcap.com/quick-demo/TableAndIdnexDesignSysBench1_aa_001_20231121.png)
  + Because the table is a clustered index for primary key and uses `AUTO_INCREMENT` to define the primary key, when continuously inserting data, we found that at the same time, the write pressure is concentrated on a certain region, which means there is a hotspot problem.

### Step 6. Clean up the data of `oltp_common_1.lua`

   ```bash
   sysbench './oltp_common_1.lua' --config-file=config_lab01 cleanup
   ```

   ```output
    sysbench './oltp_common_1.lua' --config-file=config_lab01 cleanup
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Dropping table 'sbtest1'...
   ```

### Step 7. Read the script `oltp_common_2.lua`

+ Please pay attention to the `CREATE TABLE` statement.

   ```bash
   cat oltp_common_2.lua 
   ```

   ```output
   ...
   query = string.format([[
   CREATE TABLE sbtest%d(
   id bigINT(11) NOT NULL AUTO_RANDOM(5),
   k INT(11) NOT NULL DEFAULT '0',
   c CHAR(120) NOT NULL DEFAULT '',
   pad CHAR(60) DEFAULT '' NOT NULL,
   PRIMARY KEY (id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin]],
         table_num)

   con:query(query)
   ...
   ```

### Step 8. Run sysbench to simulate the scenario of concurrent data insertion

+ Again, this step may take 5 minutes. During this process, we can monitor whether hotspots are generated by using the traffic visualization graph in the [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard).

   ```bash
   sysbench './oltp_common_2.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   ```

   ```output
   sysbench './oltp_common_2.lua' --config-file=config_lab01 --tables=1 --table-size=5000000 prepare
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Initializing worker threads...

   Creating table 'sbtest1'...
   Inserting 5000000 records into 'sbtest1'
   ```

+ After the completion of the sysbench test, observe the incoming flow within 1 hour in the traffic visualization graph in [TiDB PD Dashboard](http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard). The following figure is generated with 20,000,000 insertions. Your figure might be different. Consider the reasons and compare with the previous test to determine if the hotspot issue has been solved.

   ![30%](https://lab-static.pingcap.com/quick-demo/TableAndIdnexDesignSysBench2_aa_001_20231121.png)

### Step 9. Clean up the data of `oltp_common_2.lua`

   ```bash
   sysbench './oltp_common_2.lua' --config-file=config_lab01 cleanup
   ```

   ```output
    sysbench './oltp_common_2.lua' --config-file=config_lab01 cleanup
   sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

   Dropping table 'sbtest1'...
   ```