# Clustered and Non-Clustered Indexes Considerations for Performance ### Overview + In these exercises, you will create both clustered and non-clustered indexes for primary key tables and perform related settings to avoid hotspots. You will create a clustered index for primary key table, attempting to split the table regions to avoid hotspot issues. Finally, we will practice how to detect hotspots through monitoring and avoid hotspot issues by modifying the table structure. ### Notes + The following experiments are designed to run on TiDB v7.5.0. Please install and deploy the TiDB v7.5.0 version database before the exercises. If you use an earlier version of TiDB database, the results may not be consistent with the guide. + Many of the steps require that you type commands in a terminal window. You might find it convenient to launch a terminal window and keep it open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`tidb>`" in your terminal window is SQL command prompt. + In these exercises, we need 10 machines connected in the network. The `Role` and `IP Address` (represented by environment variables) are as follows: | `Node Role` | `IP Address` | | :----------------- | :-------------------------------------------------------- | | `Monitor 1` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Control Machine` | `${HOST_CM_PRIVATE_IP}, ${HOST_CM_PUBLIC_IP}` | | `TiDB server 1` | `${HOST_DB1_PRIVATE_IP}, ${HOST_DB1_PUBLIC_IP}` | | `TiDB server 2` | `${HOST_DB2_PRIVATE_IP}, ${HOST_DB2_PUBLIC_IP}` | | `TiKV server 1` | `${HOST_KV1_PRIVATE_IP}, ${HOST_KV1_PUBLIC_IP}` | | `TiKV server 2` | `${HOST_KV2_PRIVATE_IP}, ${HOST_KV2_PUBLIC_IP}` | | `TiKV server 3` | `${HOST_KV3_PRIVATE_IP}, ${HOST_KV3_PUBLIC_IP}` | | `PD server 1` | `${HOST_PD1_PRIVATE_IP}, ${HOST_PD1_PUBLIC_IP}` | | `PD server 2` | `${HOST_PD2_PRIVATE_IP}, ${HOST_PD2_PUBLIC_IP}` | | `PD server 3` | `${HOST_PD3_PRIVATE_IP}, ${HOST_PD3_PUBLIC_IP}` | | `TiFlash server 1` | `${HOST_TIFLASH1_PRIVATE_IP}, ${HOST_TIFLASH1_PUBLIC_IP}` | | `Alertmanager` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Grafana` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | | `Prometheus` | `${HOST_MONITOR1_PRIVATE_IP}, ${HOST_MONITOR1_PUBLIC_IP}` | ### Assumptions + You are given the user credentials (for example: the private key) of the exercise environment. + You are given access to 10 machines that have been set up. + Your environment has TiDB cluster deployed and running. ## Exercise 1:Create both clustered and non-clustered indexes for primary key tables and perform related settings to avoid hotspots. ### Overview + In this exercise, you will create both clustered and non-clustered indexes for primary key tables and perform related settings to avoid hotspots. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. One of your colleagues has created a table `orders1` in the TiDB database. This table will have a high concurrency of insertions in the application. The definition is as follows: ```sql USE test; CREATE TABLE orders1 ( id INT(20) unsigned AUTO_INCREMENT NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id) ); ``` Please point out the performance issues that may occur with this table, and rebuild the `orders1` table with the requirement that the `orders1` table is a clustered index for primary key table. 2. Insert data into the `orders1` table that you just created five times and observe the data distribution. 3. Another one of your colleagues has created a table `orders2` in the TiDB database. This table will have a high concurrency of insertions in the application. The definition is as follows: ```sql USE test; CREATE TABLE orders2 ( id INT(20) NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL ); ``` Please point out the performance issues that may occur with this table, and rebuild the `orders2` table with the requirement that the `orders2` table is a non-clustered index for primary key table. 4. Insert data into the `orders2` table that you just created five times and observe the data distribution. 5. Clean up the `orders1` table and `orders2` table. ### Solution 0. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Enter the following command at the terminal prompt and receive the results shown: ``` ssh -A -o ServerAliveInterval=30 -o ServerAliveCountMax=5 ec2-user@ ... , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| AL2 End of Life is 2025-06-30. ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` Connect to TiDB. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. (if you don't know how to get the `${HOST_DB1_PRIVATE_IP}` please ask your instructor for help). Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p mysql> use test; Database changed mysql> prompt tidb> PROMPT set to 'tidb>' ``` 1. Observing the original `orders1` table: ```sql USE test; CREATE TABLE orders1 ( id INT(20) unsigned AUTO_INCREMENT NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id) ); ``` + Identify the major issues: 1. Due to the use of AUTO_INCREMENT for the primary key, it is recommended to change AUTO_INCREMENT to AUTO_RANDOM to avoid hotspot issues caused by inserted data. 2. Due to the large amount of data insertion, it is recommended to split the table in advance during table creation. + Rebuild the `orders1` table, since we need to use AUTO_RANDOM instead of AUTO_INCREMENT, the `id` column needs to be changed from `int` type to `bigint` type. Execute the following command in the terminal and you will receive the results shown: ```sql USE test; CREATE TABLE orders1 ( id bigINT(20) unsigned AUTO_RANDOM NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id) CLUSTERED ); SHOW CREATE TABLE orders1\G ``` ``` tidb>USE test; Database changed tidb>CREATE TABLE orders1 -> ( id bigINT(20) unsigned AUTO_RANDOM NOT NULL , -> code VARCHAR(30) NOT NULL, -> order_no VARCHAR(200) NOT NULL DEFAULT '', -> status INT(4) NOT NULL, -> cancle_flag INT(4) DEFAULT NULL, -> create_user VARCHAR(50) DEFAULT NULL, -> update_user VARCHAR(50) DEFAULT NULL, -> create_time DATETIME DEFAULT NULL, -> update_time DATETIME DEFAULT NULL, -> PRIMARY KEY (id) CLUSTERED -> ); Query OK, 0 rows affected, 1 warning (0.53 sec) tidb>SHOW CREATE TABLE orders1\G *************************** 1. row *************************** Table: orders1 Create Table: CREATE TABLE `orders1` ( `id` bigINT(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `code` VARCHAR(30) NOT NULL, `order_no` VARCHAR(200) NOT NULL DEFAULT '', `status` INT(4) NOT NULL, `cancle_flag` INT(4) DEFAULT NULL, `create_user` VARCHAR(50) DEFAULT NULL, `update_user` VARCHAR(50) DEFAULT NULL, `create_time` DATETIME DEFAULT NULL, `update_time` DATETIME DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.01 sec) ``` + Check the Region distribution of the `orders1` table and manually split it. ```sql SHOW TABLE orders1 REGIONS\G SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; SHOW TABLE orders1 REGIONS\G ``` ``` tidb>SHOW TABLE orders1 REGIONS\G *************************** 1. row *************************** REGION_ID: 34 START_KEY: t_94_ END_KEY: t_281474976710649_ LEADER_ID: 139 LEADER_STORE_ID: 2 PEERS: 35, 139, 186 SCATTERING: 0 WRITTEN_BYTES: 273 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 1 row in set (0.01 sec) tidb>SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; +--------------------+----------------------+ | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO | +--------------------+----------------------+ | 15 | 1 | +--------------------+----------------------+ 1 row in set (0.48 sec) tidb>SHOW TABLE orders1 REGIONS\G *************************** 1. row *************************** REGION_ID: 244 START_KEY: t_94_ END_KEY: t_94_r_576460752303423487 LEADER_ID: 246 LEADER_STORE_ID: 2 PEERS: 245, 246, 247 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 2. row *************************** REGION_ID: 248 START_KEY: t_94_r_576460752303423487 END_KEY: t_94_r_1152921504606846974 LEADER_ID: 251 LEADER_STORE_ID: 5 PEERS: 249, 250, 251 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 3. row *************************** REGION_ID: 252 START_KEY: t_94_r_1152921504606846974 END_KEY: t_94_r_1729382256910270461 LEADER_ID: 253 LEADER_STORE_ID: 1 PEERS: 253, 254, 255 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 4. row *************************** REGION_ID: 256 START_KEY: t_94_r_1729382256910270461 END_KEY: t_94_r_2305843009213693948 LEADER_ID: 257 LEADER_STORE_ID: 1 PEERS: 257, 258, 259 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 5. row *************************** REGION_ID: 260 START_KEY: t_94_r_2305843009213693948 END_KEY: t_94_r_2882303761517117435 LEADER_ID: 262 LEADER_STORE_ID: 2 PEERS: 261, 262, 263 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 6. row *************************** REGION_ID: 264 START_KEY: t_94_r_2882303761517117435 END_KEY: t_94_r_3458764513820540922 LEADER_ID: 267 LEADER_STORE_ID: 5 PEERS: 265, 266, 267 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 7. row *************************** REGION_ID: 268 START_KEY: t_94_r_3458764513820540922 END_KEY: t_94_r_4035225266123964409 LEADER_ID: 270 LEADER_STORE_ID: 2 PEERS: 269, 270, 271 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 8. row *************************** REGION_ID: 272 START_KEY: t_94_r_4035225266123964409 END_KEY: t_94_r_4611686018427387896 LEADER_ID: 273 LEADER_STORE_ID: 1 PEERS: 273, 274, 275 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 9. row *************************** REGION_ID: 276 START_KEY: t_94_r_4611686018427387896 END_KEY: t_94_r_5188146770730811383 LEADER_ID: 279 LEADER_STORE_ID: 5 PEERS: 277, 278, 279 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 10. row *************************** REGION_ID: 280 START_KEY: t_94_r_5188146770730811383 END_KEY: t_94_r_5764607523034234870 LEADER_ID: 281 LEADER_STORE_ID: 1 PEERS: 281, 282, 283 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 11. row *************************** REGION_ID: 284 START_KEY: t_94_r_5764607523034234870 END_KEY: t_94_r_6341068275337658357 LEADER_ID: 286 LEADER_STORE_ID: 2 PEERS: 285, 286, 287 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 12. row *************************** REGION_ID: 288 START_KEY: t_94_r_6341068275337658357 END_KEY: t_94_r_6917529027641081844 LEADER_ID: 291 LEADER_STORE_ID: 5 PEERS: 289, 290, 291 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 13. row *************************** REGION_ID: 292 START_KEY: t_94_r_6917529027641081844 END_KEY: t_94_r_7493989779944505331 LEADER_ID: 293 LEADER_STORE_ID: 1 PEERS: 293, 294, 295 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 14. row *************************** REGION_ID: 296 START_KEY: t_94_r_7493989779944505331 END_KEY: t_94_r_8070450532247928818 LEADER_ID: 299 LEADER_STORE_ID: 5 PEERS: 297, 298, 299 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 15. row *************************** REGION_ID: 300 START_KEY: t_94_r_8070450532247928818 END_KEY: t_94_r_8646911284551352305 LEADER_ID: 302 LEADER_STORE_ID: 2 PEERS: 301, 302, 303 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 16. row *************************** REGION_ID: 34 START_KEY: t_94_r_8646911284551352305 END_KEY: t_281474976710649_ LEADER_ID: 139 LEADER_STORE_ID: 2 PEERS: 35, 139, 186 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 16 rows in set (0.08 sec) ``` 2. Insert data into the created `orders1` table five times and observe the distribution of the data. Enter the following command at the terminal prompt and receive the results shown: ```sql BEGIN; INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); COMMIT; SELECT id, code, status FROM orders1; SELECT SLEEP(5); SHOW TABLE orders1 REGIONS\G ``` ``` tidb>BEGIN; Query OK, 0 rows affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>COMMIT; Query OK, 0 rows affected (0.00 sec) tidb>SELECT id, code, status FROM orders1; +---------------------+----------------------+--------+ | id | code | status | +---------------------+----------------------+--------+ | 1152921504606846977 | b1f55f9268fb31cec7f5 | 0 | | 1152921504606846978 | c3fd54abaab48504539a | 3 | | 1152921504606846979 | 92c1b99e02e4e646b0b5 | 0 | | 1152921504606846980 | 67af6b9ceb8e758c5794 | 9 | | 1152921504606846981 | ed834b9ef841a67d8edb | 8 | +---------------------+----------------------+--------+ 5 rows in set (0.01 sec) tidb>SELECT SLEEP(5); +----------+ | SLEEP(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) tidb>SHOW TABLE orders1 REGIONS\G *************************** 1. row *************************** REGION_ID: 436 START_KEY: t_100_ END_KEY: t_100_r_576460752303423487 LEADER_ID: 437 LEADER_STORE_ID: 1 PEERS: 437, 438, 439 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 2. row *************************** REGION_ID: 440 START_KEY: t_100_r_576460752303423487 END_KEY: t_100_r_1152921504606846974 LEADER_ID: 442 LEADER_STORE_ID: 2 PEERS: 441, 442, 443 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 3. row *************************** REGION_ID: 444 START_KEY: t_100_r_1152921504606846974 END_KEY: t_100_r_1729382256910270461 LEADER_ID: 447 LEADER_STORE_ID: 5 PEERS: 445, 446, 447 SCATTERING: 0 WRITTEN_BYTES: 27 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 4. row *************************** REGION_ID: 448 START_KEY: t_100_r_1729382256910270461 END_KEY: t_100_r_2305843009213693948 LEADER_ID: 449 LEADER_STORE_ID: 1 PEERS: 449, 450, 451 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 5. row *************************** REGION_ID: 452 START_KEY: t_100_r_2305843009213693948 END_KEY: t_100_r_2882303761517117435 LEADER_ID: 454 LEADER_STORE_ID: 2 PEERS: 453, 454, 455 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 6. row *************************** REGION_ID: 456 START_KEY: t_100_r_2882303761517117435 END_KEY: t_100_r_3458764513820540922 LEADER_ID: 459 LEADER_STORE_ID: 5 PEERS: 457, 458, 459 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 7. row *************************** REGION_ID: 460 START_KEY: t_100_r_3458764513820540922 END_KEY: t_100_r_4035225266123964409 LEADER_ID: 461 LEADER_STORE_ID: 1 PEERS: 461, 462, 463 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 8. row *************************** REGION_ID: 464 START_KEY: t_100_r_4035225266123964409 END_KEY: t_100_r_4611686018427387896 LEADER_ID: 466 LEADER_STORE_ID: 2 PEERS: 465, 466, 467 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 9. row *************************** REGION_ID: 468 START_KEY: t_100_r_4611686018427387896 END_KEY: t_100_r_5188146770730811383 LEADER_ID: 471 LEADER_STORE_ID: 5 PEERS: 469, 470, 471 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 10. row *************************** REGION_ID: 472 START_KEY: t_100_r_5188146770730811383 END_KEY: t_100_r_5764607523034234870 LEADER_ID: 474 LEADER_STORE_ID: 2 PEERS: 473, 474, 475 SCATTERING: 0 WRITTEN_BYTES: 27 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 11. row *************************** REGION_ID: 476 START_KEY: t_100_r_5764607523034234870 END_KEY: t_100_r_6341068275337658357 LEADER_ID: 478 LEADER_STORE_ID: 2 PEERS: 477, 478, 479 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 12. row *************************** REGION_ID: 480 START_KEY: t_100_r_6341068275337658357 END_KEY: t_100_r_6917529027641081844 LEADER_ID: 482 LEADER_STORE_ID: 2 PEERS: 481, 482, 483 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 13. row *************************** REGION_ID: 484 START_KEY: t_100_r_6917529027641081844 END_KEY: t_100_r_7493989779944505331 LEADER_ID: 485 LEADER_STORE_ID: 1 PEERS: 485, 486, 487 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 14. row *************************** REGION_ID: 488 START_KEY: t_100_r_7493989779944505331 END_KEY: t_100_r_8070450532247928818 LEADER_ID: 491 LEADER_STORE_ID: 5 PEERS: 489, 490, 491 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 15. row *************************** REGION_ID: 492 START_KEY: t_100_r_8070450532247928818 END_KEY: t_100_r_8646911284551352305 LEADER_ID: 493 LEADER_STORE_ID: 1 PEERS: 493, 494, 495 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 16. row *************************** REGION_ID: 34 START_KEY: t_100_r_8646911284551352305 END_KEY: t_281474976710649_ LEADER_ID: 139 LEADER_STORE_ID: 2 PEERS: 35, 139, 186 SCATTERING: 0 WRITTEN_BYTES: 2056 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 16 rows in set (0.08 sec) ``` + Although the AUTO_RANDOM attribute has been specified for the primary key in the `orders1` table, it is found that the generated primary key values are still continuous. Moreover, by observing the `WRITTEN_BYTES` parameter in the `SHOW TABLE orders1 REGIONS\G` command, it is noticed that all the data has been inserted into the same region. Please note that the `SHOW TABLE orders1 REGIONS\G` data has a certain update time, so the command `SELECT sleep(5);` has been used here to wait. Next, we will `TRUNCATE` the data in the `orders1` table and insert new data again. Please note that after `TRUNCATE`, there will only be 1 region left in the `orders1` table, so it needs to be manually splited. ```sql TRUNCATE TABLE orders1; SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); SELECT id, code, status FROM orders1; ``` ``` tidb>TRUNCATE TABLE orders1; Query OK, 0 rows affected (0.54 sec) tidb>SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; +--------------------+----------------------+ | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO | +--------------------+----------------------+ | 15 | 1 | +--------------------+----------------------+ 1 row in set (0.06 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 tidb>SELECT id, code, status FROM orders1; +---------------------+----------------------+--------+ | id | code | status | +---------------------+----------------------+--------+ | 5188146770730811393 | 4341550b1717c9e42c33 | 6 | | 5188146770730811394 | 8d5687b2a3fa0cbcc039 | 3 | | 5188146770730811395 | 87424ab86c4189f14488 | 3 | | 5188146770730811396 | 3952300c307266d9da4e | 2 | | 5188146770730811397 | 901e4f0fb4e0bcfd98bc | 9 | +---------------------+----------------------+--------+ 5 rows in set (0.01 sec) ``` + The conclusion is that as long as the insertion statements are within the same transaction, regardless of whether AUTO_RANDOM is specified or not, the primary key values will be continuous and there is still a possibility of hotspots. Below, we will perform the insertion operation in different transactions. ```sql TRUNCATE TABLE orders1; SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; SET autocommit=ON; INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); SELECT id, code, status FROM orders1; ``` ``` tidb>TRUNCATE TABLE orders1; Query OK, 0 rows affected (0.53 sec) tidb>SPLIT TABLE orders1 BETWEEN(0) AND (9223372036854775807) REGIONS 16; +--------------------+----------------------+ | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO | +--------------------+----------------------+ | 15 | 1 | +--------------------+----------------------+ 1 row in set (0.06 sec) tidb>SET autocommit=ON; Query OK, 0 rows affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.02 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders1(code, status) VALUES(SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>SELECT id, code, status FROM orders1; +----------------------+----------------------+--------+ | id | code | status | +----------------------+----------------------+--------+ | 6341068275337658369 | a96328e33dd6b7ad2f15 | 8 | | 13835058055282163715 | 586ab8cf9fb194b5e4ca | 2 | | 14411518807585587205 | 21105132b2384ccab63b | 7 | | 8646911284551352324 | a80b9c0196c4e9256fd3 | 6 | | 7493989779944505346 | 3f43c82a9308a1467b85 | 2 | +----------------------+----------------------+--------+ 5 rows in set (0.02 sec) ``` + When different transactions perform insertion, we found that the values of the primary key column are random. 3. Investigate the original `orders2` table: ```sql USE test; CREATE TABLE orders2 ( id INT(20) NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL ); ``` + The main issue found is that, due to the table being non-clustered index for primary key, it will lead to a write hotspot problem. To avoid this, SHARD_ROW_ID_BITS and PRE_SPLIT_REGIONS can be used."。 + Rebuild the `orders2` table，Enter the following command at the terminal prompt and receive the results shown: ```sql USE test; CREATE TABLE orders2 ( id INT(20) NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL ) SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3; SHOW CREATE TABLE orders2\G ``` ``` tidb>USE test; Database changed tidb>CREATE TABLE orders2 -> ( id INT(20) NOT NULL , -> code VARCHAR(30) NOT NULL, -> order_no VARCHAR(200) NOT NULL DEFAULT '', -> status INT(4) NOT NULL, -> cancle_flag INT(4) DEFAULT NULL, -> create_user VARCHAR(50) DEFAULT NULL, -> update_user VARCHAR(50) DEFAULT NULL, -> create_time DATETIME DEFAULT NULL, -> update_time DATETIME DEFAULT NULL -> ) SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3; Query OK, 0 rows affected (0.54 sec) tidb>SHOW CREATE TABLE orders2\G *************************** 1. row *************************** Table: orders2 Create Table: CREATE TABLE `orders2` ( `id` INT(20) NOT NULL, `code` VARCHAR(30) NOT NULL, `order_no` VARCHAR(200) NOT NULL DEFAULT '', `status` INT(4) NOT NULL, `cancle_flag` INT(4) DEFAULT NULL, `create_user` VARCHAR(50) DEFAULT NULL, `update_user` VARCHAR(50) DEFAULT NULL, `create_time` DATETIME DEFAULT NULL, `update_time` DATETIME DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 */ 1 row in set (0.00 sec) ``` + Confirm `orders2` table is non-clustered index for primary key. Enter the following command at the terminal prompt and receive the results shown: ```sql SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'orders2'; ``` ``` tidb>SELECT TIDB_PK_TYPE FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'orders2'; +--------------+ | TIDB_PK_TYPE | +--------------+ | NONCLUSTERED | +--------------+ 1 row in set (0.02 sec) ``` + Check the distribution of regions of the `orders2` table. ```sql SHOW TABLE orders2 REGIONS\G ``` ``` tidb>SHOW TABLE orders2 REGIONS\G *************************** 1. row *************************** REGION_ID: 748 START_KEY: t_106_ END_KEY: t_106_r_1152921504606846976 LEADER_ID: 750 LEADER_STORE_ID: 2 PEERS: 749, 750, 751 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 2. row *************************** REGION_ID: 752 START_KEY: t_106_r_1152921504606846976 END_KEY: t_106_r_2305843009213693952 LEADER_ID: 754 LEADER_STORE_ID: 2 PEERS: 753, 754, 755 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 3. row *************************** REGION_ID: 756 START_KEY: t_106_r_2305843009213693952 END_KEY: t_106_r_3458764513820540928 LEADER_ID: 758 LEADER_STORE_ID: 2 PEERS: 757, 758, 759 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 4. row *************************** REGION_ID: 760 START_KEY: t_106_r_3458764513820540928 END_KEY: t_106_r_4611686018427387904 LEADER_ID: 762 LEADER_STORE_ID: 2 PEERS: 761, 762, 763 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 5. row *************************** REGION_ID: 764 START_KEY: t_106_r_4611686018427387904 END_KEY: t_106_r_5764607523034234880 LEADER_ID: 766 LEADER_STORE_ID: 2 PEERS: 765, 766, 767 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 6. row *************************** REGION_ID: 768 START_KEY: t_106_r_5764607523034234880 END_KEY: t_106_r_6917529027641081856 LEADER_ID: 769 LEADER_STORE_ID: 1 PEERS: 769, 770, 771 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 7. row *************************** REGION_ID: 772 START_KEY: t_106_r_6917529027641081856 END_KEY: t_106_r_8070450532247928832 LEADER_ID: 774 LEADER_STORE_ID: 2 PEERS: 773, 774, 775 SCATTERING: 0 WRITTEN_BYTES: 27 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 8. row *************************** REGION_ID: 34 START_KEY: t_106_r_8070450532247928832 END_KEY: t_281474976710649_ LEADER_ID: 139 LEADER_STORE_ID: 2 PEERS: 35, 139, 186 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 8 rows in set (0.05 sec) ``` + We can see that due to setting `PRE_SPLIT_REGIONS` to 3, the `orders2` table has 8 empty regions available for insertion at the time of creation. 4. Insert data into the newly created `orders2` table five times and observe the distribution of the data. Enter the following command at the terminal prompt and receive the results shown: ```sql BEGIN; INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); COMMIT; SELECT id, code, status FROM orders2; SELECT _tidb_rowid, id, code, status FROM orders2; SELECT sleep(5); SHOW TABLE orders2 REGIONS\G ``` ``` tidb>BEGIN; Query OK, 0 rows affected (0.00 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>COMMIT; Query OK, 0 rows affected (0.00 sec) tidb>SELECT id, code, status FROM orders2; +----+----------------------+--------+ | id | code | status | +----+----------------------+--------+ | 1 | 9cb0ad99d83a62915d6b | 4 | | 1 | 329dadd330d53bbe8aeb | 3 | | 2 | 999ca3ad7c2c61f61777 | 5 | | 2 | 74760a7e6f1f3a16ef64 | 7 | | 9 | 1e264cb3f696c5d9b94c | 2 | +----+----------------------+--------+ 5 rows in set (0.01 sec) tidb>SELECT _tidb_rowid, id, code, status FROM orders2; +---------------------+----+----------------------+--------+ | _tidb_rowid | id | code | status | +---------------------+----+----------------------+--------+ | 5764607523034234881 | 1 | 9cb0ad99d83a62915d6b | 4 | | 5764607523034234882 | 1 | 329dadd330d53bbe8aeb | 3 | | 5764607523034234883 | 2 | 999ca3ad7c2c61f61777 | 5 | | 5764607523034234884 | 2 | 74760a7e6f1f3a16ef64 | 7 | | 5764607523034234885 | 9 | 1e264cb3f696c5d9b94c | 2 | +---------------------+----+----------------------+--------+ 5 rows in set (0.00 sec) tidb>SELECT sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) tidb>SHOW TABLE orders2 REGIONS\G *************************** 1. row *************************** REGION_ID: 748 START_KEY: t_106_ END_KEY: t_106_r_1152921504606846976 LEADER_ID: 750 LEADER_STORE_ID: 2 PEERS: 749, 750, 751 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 2. row *************************** REGION_ID: 752 START_KEY: t_106_r_1152921504606846976 END_KEY: t_106_r_2305843009213693952 LEADER_ID: 754 LEADER_STORE_ID: 2 PEERS: 753, 754, 755 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 3. row *************************** REGION_ID: 756 START_KEY: t_106_r_2305843009213693952 END_KEY: t_106_r_3458764513820540928 LEADER_ID: 758 LEADER_STORE_ID: 2 PEERS: 757, 758, 759 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 4. row *************************** REGION_ID: 760 START_KEY: t_106_r_3458764513820540928 END_KEY: t_106_r_4611686018427387904 LEADER_ID: 762 LEADER_STORE_ID: 2 PEERS: 761, 762, 763 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 5. row *************************** REGION_ID: 764 START_KEY: t_106_r_4611686018427387904 END_KEY: t_106_r_5764607523034234880 LEADER_ID: 766 LEADER_STORE_ID: 2 PEERS: 765, 766, 767 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 6. row *************************** REGION_ID: 768 START_KEY: t_106_r_5764607523034234880 END_KEY: t_106_r_6917529027641081856 LEADER_ID: 769 LEADER_STORE_ID: 1 PEERS: 769, 770, 771 SCATTERING: 0 WRITTEN_BYTES: 654 READ_BYTES: 870 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 7. row *************************** REGION_ID: 772 START_KEY: t_106_r_6917529027641081856 END_KEY: t_106_r_8070450532247928832 LEADER_ID: 774 LEADER_STORE_ID: 2 PEERS: 773, 774, 775 SCATTERING: 0 WRITTEN_BYTES: 27 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 8. row *************************** REGION_ID: 34 START_KEY: t_106_r_8070450532247928832 END_KEY: t_281474976710649_ LEADER_ID: 139 LEADER_STORE_ID: 2 PEERS: 35, 139, 186 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 8 rows in set (0.05 sec) ``` + You have noticed that although the `SHARD_ROW_ID_BITS` property of the `orders2` table is specified, the generated hidden column `_tidb_rowid` is still continuous. Furthermore, by observing the `WRITTEN_BYTES` in `SHOW TABLE orders2 REGIONS\G`, it is found that all data is inserted in the same region. Please note that as the data in `SHOW TABLE orders2 REGIONS\G` may have a certain updating time, the code uses `SELECT sleep(5);` to wait. Next, we will `TRUNCATE` the data in `orders2` and insert new data. Note that after `TRUNCATE`, our `orders2` table is not the same as `orders1`. Due to the fact that `orders2` specifies the `PRE_SPLIT_REGIONS=3` table property, it still has 8 default regions, so it does not need to be manually split again. ```sql TRUNCATE TABLE orders2; INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); SELECT id, code, status FROM orders2; SELECT _tidb_rowid, id, code, status FROM orders2; ``` ``` tidb>TRUNCATE TABLE orders2; Query OK, 0 rows affected (0.54 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)), (9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 tidb>SELECT id, code, status FROM orders2; +----+----------------------+--------+ | id | code | status | +----+----------------------+--------+ | 1 | 71f193f2ed99f16bed50 | 4 | | 1 | 83664e98ca7e5991ac06 | 0 | | 2 | 79639074670bc1b9dfaf | 0 | | 2 | 446775a4c9850a10ce95 | 8 | | 9 | 1a2e539cc7aac470f468 | 4 | +----+----------------------+--------+ 5 rows in set (0.00 sec) tidb>SELECT _tidb_rowid, id, code, status FROM orders2; +---------------------+----+----------------------+--------+ | _tidb_rowid | id | code | status | +---------------------+----+----------------------+--------+ | 1152921504606846977 | 1 | 71f193f2ed99f16bed50 | 4 | | 1152921504606846978 | 1 | 83664e98ca7e5991ac06 | 0 | | 1152921504606846979 | 2 | 79639074670bc1b9dfaf | 0 | | 1152921504606846980 | 2 | 446775a4c9850a10ce95 | 8 | | 1152921504606846981 | 9 | 1a2e539cc7aac470f468 | 4 | +---------------------+----+----------------------+--------+ 5 rows in set (0.01 sec) ``` + The conclusion is that as long as the insert statements are within the same transaction, regardless of whether the `SHARD_ROW_ID_BITS` property of the table is specified or not, the generated hidden column `_tidb_rowid` will still be continuous, and there may still be a possibility of hotspot. Below we will perform the insert operations in different transactions. ```sql TRUNCATE TABLE orders2; SET autocommit=ON; INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); SELECT id, code, status FROM orders2; SELECT _tidb_rowid, id, code, status FROM orders2; ``` ``` tidb>TRUNCATE TABLE orders2; Query OK, 0 rows affected (0.53 sec) tidb>SET autocommit=ON; Query OK, 0 rows affected (0.00 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.03 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(1, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(2, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders2(id, code, status) VALUES(9, SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10)); Query OK, 1 row affected (0.00 sec) tidb>SELECT id, code, status FROM orders2; +----+----------------------+--------+ | id | code | status | +----+----------------------+--------+ | 9 | dbb9004292d00a8a0059 | 8 | | 1 | 219cf3b5989d4bf0973c | 7 | | 2 | e2d02bf318cca2040c71 | 4 | | 1 | 3c32ed407a200ed13f5c | 1 | | 2 | 74301f4a88de6434abd3 | 7 | +----+----------------------+--------+ 5 rows in set (0.00 sec) tidb>SELECT _tidb_rowid, id, code, status FROM orders2; +---------------------+----+----------------------+--------+ | _tidb_rowid | id | code | status | +---------------------+----+----------------------+--------+ | 8070450532247928837 | 9 | dbb9004292d00a8a0059 | 8 | | 2305843009213693953 | 1 | 219cf3b5989d4bf0973c | 7 | | 1729382256910270467 | 2 | 74301f4a88de6434abd3 | 7 | | 7493989779944505346 | 1 | 3c32ed407a200ed13f5c | 1 | | 4611686018427387908 | 2 | e2d02bf318cca2040c71 | 4 | +---------------------+----+----------------------+--------+ 5 rows in set (0.00 sec) ``` + When different transactions perform inserts, we found that the automatically generated hidden column `_tidb_rowid` is not continuous. 5. Clean up table `orders1` and `orders2`. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE orders1; DROP TABLE orders2; ``` ``` tidb>DROP TABLE orders1; Query OK, 0 rows affected (0.53 sec) tidb>DROP TABLE orders2; Query OK, 0 rows affected (0.52 sec) ``` ## Exercise 2: Create a clustered index for primary key table, and attempt to split the table regions to avoid hotspot issues. ### Overview + In this exercise, we will create a clustered index for primary key table. After inserting data into the table, we will manually split the regions of the table to prevent hotspots in read and write operations. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks 1. Connect to TiDB and create a new Clustered table `orders`. 2. Insert 10 rows into the table `orders`. 3. Split the table `orders`. 4. Drop table table `orders`. ### Solution 0. In terminal 1, connect to TiDB and change the current database to `test`. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p ``` ```sql USE test; ``` ``` tidb> USE test; Database changed ``` 1. Connect to TiDB and create a new clustered index for primary key table `orders`. Enter the following command at the terminal prompt and receive the results shown: ```sql USE test; CREATE TABLE orders ( id bigINT(20) unsigned AUTO_RANDOM NOT NULL , code VARCHAR(30) NOT NULL, order_no VARCHAR(200) NOT NULL DEFAULT '', status INT(4) NOT NULL, cancle_flag INT(4) DEFAULT NULL, create_user VARCHAR(50) DEFAULT NULL, update_user VARCHAR(50) DEFAULT NULL, create_time DATETIME DEFAULT NULL, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id) CLUSTERED ); SHOW CREATE TABLE orders\G ``` ``` tidb>USE test; Database changed tidb>CREATE TABLE orders -> ( id bigINT(20) unsigned AUTO_RANDOM NOT NULL , -> code VARCHAR(30) NOT NULL, -> order_no VARCHAR(200) NOT NULL DEFAULT '', -> status INT(4) NOT NULL, -> cancle_flag INT(4) DEFAULT NULL, -> create_user VARCHAR(50) DEFAULT NULL, -> update_user VARCHAR(50) DEFAULT NULL, -> create_time DATETIME DEFAULT NULL, -> update_time DATETIME DEFAULT NULL, -> PRIMARY KEY (id) CLUSTERED -> ); Query OK, 0 rows affected, 1 warning (0.54 sec) tidb>SHOW CREATE TABLE orders\G *************************** 1. row *************************** Table: orders Create Table: CREATE TABLE `orders` ( `id` bigINT(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `code` VARCHAR(30) NOT NULL, `order_no` VARCHAR(200) NOT NULL DEFAULT '', `status` INT(4) NOT NULL, `cancle_flag` INT(4) DEFAULT NULL, `create_user` VARCHAR(50) DEFAULT NULL, `update_user` VARCHAR(50) DEFAULT NULL, `create_time` DATETIME DEFAULT NULL, `update_time` DATETIME DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) ``` 2. Insert 10 rows into the table `orders`. Enter the following command at the terminal prompt and receive the results shown: ```sql INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Jones', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Garcia', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Davis', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Adams', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Edwards', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Anderson', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Morgan', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Clark', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Smith', now()); INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Tom', now()); SELECT COUNT(*) FROM orders; SELECT code, create_user FROM orders; ``` ``` tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Jones', now()); Query OK, 1 row affected (0.02 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Garcia', now()); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Davis', now()); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Adams', now()); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Edwards', now()); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Anderson', now()); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Morgan', now()); Query OK, 1 row affected (0.01 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Clark', now()); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Smith', now()); Query OK, 1 row affected (0.00 sec) tidb>INSERT INTO orders(code, order_no, status, cancle_flag, create_user, create_time) VALUES(CONCAT('CODE_', LPAD(ROUND(RAND() * 1000), 3, 0)), SUBSTRING(MD5(RAND()),1,20), FLOOR(RAND()*10), FLOOR(RAND()*10), 'Tom', now()); Query OK, 1 row affected (0.00 sec) tidb>SELECT COUNT(*) FROM orders; +----------+ | COUNT(*) | +----------+ | 10 | +----------+ 1 row in set (0.01 sec) tidb>SELECT code, create_user FROM orders; +----------+-------------+ | code | create_user | +----------+-------------+ | CODE_525 | Edwards | | CODE_502 | Davis | | CODE_224 | Morgan | | CODE_370 | Jones | | CODE_525 | Smith | | CODE_832 | Tom | | CODE_189 | Anderson | | CODE_483 | Clark | | CODE_963 | Garcia | | CODE_257 | Adams | +----------+-------------+ 10 rows in set (0.00 sec) ``` 3. Split the table `orders` to prevent from reading and writing hotspot. Enter the following command at the terminal prompt and receive the results shown: ```sql SHOW TABLE orders REGIONS\G SPLIT TABLE orders BETWEEN (0) and (9223372036854775807) regions 4; SHOW TABLE orders REGIONS\G ``` ``` tidb>SHOW TABLE orders REGIONS\G *************************** 1. row *************************** REGION_ID: 28 START_KEY: t_110_ END_KEY: t_281474976710649_ LEADER_ID: 187 LEADER_STORE_ID: 2 PEERS: 29, 187, 261 SCATTERING: 0 WRITTEN_BYTES: 0 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 1 row in set (0.01 sec) tidb>SPLIT TABLE orders BETWEEN (0) and (9223372036854775807) regions 4; +--------------------+----------------------+ | TOTAL_SPLIT_REGION | SCATTER_FINISH_RATIO | +--------------------+----------------------+ | 3 | 1 | +--------------------+----------------------+ 1 row in set (0.28 sec) tidb>SHOW TABLE orders REGIONS\G *************************** 1. row *************************** REGION_ID: 426 START_KEY: t_110_ END_KEY: t_110_r_2305843009213693951 LEADER_ID: 427 LEADER_STORE_ID: 1 PEERS: 427, 428, 429 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 2. row *************************** REGION_ID: 430 START_KEY: t_110_r_2305843009213693951 END_KEY: t_110_r_4611686018427387902 LEADER_ID: 433 LEADER_STORE_ID: 5 PEERS: 431, 432, 433 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 3. row *************************** REGION_ID: 434 START_KEY: t_110_r_4611686018427387902 END_KEY: t_110_r_6917529027641081853 LEADER_ID: 436 LEADER_STORE_ID: 2 PEERS: 435, 436, 437 SCATTERING: 0 WRITTEN_BYTES: 39 READ_BYTES: 0 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: *************************** 4. row *************************** REGION_ID: 28 START_KEY: t_110_r_6917529027641081853 END_KEY: t_281474976710649_ LEADER_ID: 187 LEADER_STORE_ID: 2 PEERS: 29, 187, 261 SCATTERING: 0 WRITTEN_BYTES: 1624 READ_BYTES: 2290 APPROXIMATE_SIZE(MB): 1 APPROXIMATE_KEYS: 0 SCHEDULING_CONSTRAINTS: SCHEDULING_STATE: 4 rows in set (0.01 sec) ``` 4. Clean up the `orders` table. Enter the following command at the terminal prompt and receive the results shown: ```sql DROP TABLE orders; ``` ``` tidb>DROP TABLE orders; Query OK, 0 rows affected (0.53 sec) ``` ## Exercise 3: Monitor and resolve hotspots in concurrent write operations ### Overview + 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. ### Duration + This exercise should take you approximately 40 minutes to complete. ### Tasks 1. Install the sysbench tool. 2. Check the sysbench script `oltp_common_1.lua` provided in this exercise, and find the `CREATE TABLE` statement. What are the characteristics of this table? What performance problems may it cause? 3. Edit the configuration file of Sysbench. 4. Enter the TiDB database and create the sbtest database for testing purposes. 5. Start sysbench to simulate concurrent insertion by multiple sessions. 6. Use the TiDB Dashboard tool to monitor whether there are hotspots generated and why. 7. Check the sysbench script `oltp_common_2.lua` provided in this exercise, find the `CREATE TABLE` statement, and consider whether optimizing the table structure can avoid performance issues. 8. Clean up the previous experimental data. 9. Start sysbench again to simulate concurrent insertion by multiple sessions. 10. Use TiDB Dashboard tool again to monitor and solve the hotspot issue. Why? 11. Clean up experimental data. ### Solution 0. Login as user `ec2-user` on `Control Machine` (`${HOST_MONITOR1_PUBLIC_IP}`). Install the binary of MySQL 5.7.x in your server. Enter the following command at the terminal prompt and receive the results shown: ``` ssh -A -o ServerAliveInterval=30 -o ServerAliveCountMax=5 ec2-user@ ... , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| AL2 End of Life is 2025-06-30. ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ``` $ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz ... Resolving downloads.mysql.com (downloads.mysql.com)... 23.15.25.107, 2600:1413:5000:181::2e31, 2600:1413:5000:187::2e31 Connecting to downloads.mysql.com (downloads.mysql.com)|23.15.25.107|:443... connected. ... ``` ``` $ tar -xvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisam_ftdump mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisamchk mysql-5.7.38-linux-glibc2.12-x86_64/bin/myisamlog ... mysql-5.7.38-linux-glibc2.12-x86_64/support-files/mysql.server mysql-5.7.38-linux-glibc2.12-x86_64/docs/INFO_BIN mysql-5.7.38-linux-glibc2.12-x86_64/docs/INFO_SRC ``` ``` $ sudo mv mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql $ sudo chown -R mysql.mysql /usr/local/mysql/ ``` 1. Install the sysbench tool. Enter the following command at the terminal prompt and receive the results shown: ``` wget https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.17 sudo mv 1.0.17 sysbench-1.0.17.tar.gz sudo tar -xzvf sysbench-1.0.17.tar.gz -C /usr/local/ cd /usr/local/sysbench-1.0.17/ sudo yum install m4 autoconf automake libtool sudo yum install automake libtool -y sudo ./autogen.sh cd vi .bash_profile source .bash_profile cd /usr/local/sysbench-1.0.17/ sudo ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib sudo make -j sudo make install sysbench --version ``` ``` $ wget https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.17 --2023-11-21 09:17:37-- https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.17 Resolving codeload.github.com (codeload.github.com)... 192.30.255.120 Connecting to codeload.github.com (codeload.github.com)|192.30.255.120|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/x-gzip] Saving to: ‘1.0.17’ [ <=> ] 1,509,044 7.82MB/s in 0.2s 2023-11-21 09:17:37 (7.82 MB/s) - ‘1.0.17’ saved [1509044] $ sudo mv 1.0.17 sysbench-1.0.17.tar.gz $ sudo tar -xzvf sysbench-1.0.17.tar.gz -C /usr/local/ sysbench-1.0.17/ sysbench-1.0.17/.gitignore sysbench-1.0.17/.travis.yml ... sysbench-1.0.17/third_party/luajit/luajit/src/vm_x86.dasc sysbench-1.0.17/third_party/luajit/luajit/src/xb1build.bat sysbench-1.0.17/third_party/luajit/luajit/src/xedkbuild.bat $ cd /usr/local/sysbench-1.0.17/ $ sudo yum install m4 autoconf automake libtool Loaded plugins: extras_suggestions, langpacks, priorities, update-motd amzn2-core | 3.6 kB 00:00:00 58 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ... perl-Thread-Queue.noarch 0:3.02-2.amzn2 Complete! $ sudo ./autogen.sh ./autogen.sh: running `libtoolize --copy --force' libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'. libtoolize: copying file `config/ltmain.sh' ... Libtoolized with: libtoolize (GNU libtool) 2.4.2 Automade with: automake (GNU automake) 1.13.4 Configured with: autoconf (GNU Autoconf) 2.69 $ cd $ vi .bash_profile ... PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/sysbench-1.0.17 ... $ source .bash_profile $ cd /usr/local/sysbench-1.0.17/ $ sudo ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking target system type... x86_64-unknown-linux-gnu ... CK_LIBS : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.a configure flags : `===============================================================================` $ sudo make -j Making all in doc make[1]: Entering directory `/usr/local/sysbench-1.0.17/doc' Making all in xsl ... make[1]: Entering directory `/usr/local/sysbench-1.0.17' make[1]: Nothing to be done for `all-am'. make[1]: Leaving directory `/usr/local/sysbench-1.0.17' $ sudo make install Making install in doc make[1]: Entering directory `/usr/local/sysbench-1.0.17/doc' Making install in xsl ... make[2]: Nothing to be done for `install-data-am'. make[2]: Leaving directory `/usr/local/sysbench-1.0.17' make[1]: Leaving directory `/usr/local/sysbench-1.0.17' $ sysbench --version sysbench 1.0.17 ``` 2. Please examine the test script `oltp_common_1.lua` provided in this exercise and search for the keyword `CREATE TABLE`, (by type `/` followed by `CREATE TABLE`). Consider what problems may arise from the method of table creation used. Enter the following command at the terminal prompt and receive the results shown: ``` $ vi oltp_common_1.lua ``` ``` $ vi 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 is a clustered index for primary key, using `AUTO_INCREMENT` on the primary key column `id`. This may result in hotspot problems during concurrent insertion. 3. Edit the configuration file of sysbench tool, enter the following command at the terminal prompt and receive the results shown: ``` $ vi config_new ``` ``` $ vi config_new mysql-host= mysql-port=4000 mysql-user=root mysql-db=sbtest mysql-password= time=600 threads=32 report-interval=10 db-driver=mysql ``` + 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. 4. Connect to TiDB and create a new database named `sbtest` for sysbench testing, and exit. Enter the following command at the terminal prompt and receive the results shown: ``` $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root -p ``` ```sql CREATE DATABASE sbtest; EXIT; ``` ``` tidb>CREATE DATABASE sbtest; Query OK, 0 rows affected (0.14 sec) tidb>EXIT Bye ``` 5. Start sysbench to simulate the scenario of concurrent data insertion. This step may take up to 10 minutes, and during the process, we can monitor whether there are hotspots generated through the traffic visualization chart in TiDB Dashboard. Enter the following command at the terminal prompt and receive the results shown: ``` sysbench './oltp_common_1.lua' --config-file=config_new --tables=1 --table-size=20000000 prepare ``` ``` $ sysbench './oltp_common_1.lua' --config-file=config_new --tables=1 --table-size=20000000 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. 6. After the completion of the sysbench test, observe the write traffic within 1 hour in the traffic visualization chart in TiDB Dashboard as shown in the figure below. Consider why this occurs and whether it can be optimized. + 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. 7. Check the `oltp_common_2.lua` script provided in this exercise's sysbench test, search for the keyword `CREATE TABLE` inside, and think about the differences in table creation methods from before? Enter the following command at the terminal prompt and receive the results shown: ``` vi oltp_common_2.lua ``` ``` $ vi oltp_common_2.lua ... 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) ... ``` 8. Clean up the data of `oltp_common_1.lua`. Enter the following command at the terminal prompt and receive the results shown: ``` sysbench './oltp_common_1.lua' --config-file=config_new cleanup ``` ``` $ sysbench './oltp_common_1.lua' --config-file=config_new cleanup sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Dropping table 'sbtest1'... ``` 9. Restart sysbench and simulate concurrent data insertion. This step may take 10 minutes. During this process, we can monitor whether hotspots are generated by using the traffic visualization graph in the TiDB Dashboard. Enter the following command at the terminal prompt and receive the results shown: ``` sysbench './oltp_common_2.lua' --config-file=config_new --tables=1 --table-size=20000000 prepare ``` ``` sysbench './oltp_common_2.lua' --config-file=config_new --tables=1 --table-size=20000000 prepare sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Initializing worker threads... Creating table 'sbtest1'... Inserting 20000000 records into 'sbtest1' ``` 10. After the completion of the sysbench test, observe the incoming flow within 1 hour in the traffic visualization graph in TiDB Dashboard as shown in the following picture. Please consider the reasons and compare with the previous test to determine if the hotspot issue has been solved. + Change the `AUTO_INCREMENT` attribute of the primary key to `AUTO_RANDOM`, so that the data will not be concentrated in the same region when inserting data, effectively solving the hotspot issue. 11. Clean up the data of `oltp_common_2.lua`. Enter the following command at the terminal prompt and receive the results shown: ``` sysbench './oltp_common_2.lua' --config-file=config_new cleanup ``` ``` $ sysbench './oltp_common_2.lua' --config-file=config_new cleanup sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Dropping table 'sbtest1'... ``` ## End of Exercises