概述
本实验将在TiDB数据库中运行耗内存较大的SQL语句，观察其对TiDB Server内存使用情况的影响，对其进行监控和限制。
实验过程
1.使用TiUP bench组件构造包括customer、lineitem、nation等在内的基础表结构以及数据。
[root@node1 ~]# tiup bench tpch -H 192.168.59.27 -P 4000 -D tpch prepare
tiup is checking updates for component bench ...
Starting component `bench`: /root/.tiup/components/bench/v1.9.4/tiup-bench /root/.tiup/components/bench/v1.9.4/tiup-bench tpch -H 192.168.59.27 -P 4000 -D tpch prepare
creating nation
creating region
creating part
creating supplier
creating partsupp
creating customer
creating orders
creating lineitem
generating nation table
generate nation table done
generating region table
generate region table done
generating customers table
generate customers table done
generating suppliers table
generate suppliers table done
generating part/partsupplier tables
[root@node1 ~]# mysql -uroot -P4000 -h192.168.59.28
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 417
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| dm_meta |
| mysql |
| test2 |
| test3 |
| tpch |
+--------------------+
8 rows in set (0.00 sec)
mysql> use tpch
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> show tables;
+----------------+
| Tables_in_tpch |
+----------------+
| customer |
| lineitem |
| nation |
| orders |
| part |
| partsupp |
| region |
| supplier |
+----------------+
8 rows in set (0.00 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 150000 |
+----------+
1 row in set (0.12 sec)2.再建立一张测试表，并且把customer表的数据60次的写入
mysql> CREATE TABLE customer_test (
-> C_CUSTKEY BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
-> C_NAME VARCHAR(25) NOT NULL,
-> C_ADDRESS VARCHAR(40) NOT NULL,
-> C_NATIONKEY BIGINT(20) NOT NULL,
-> C_PHONE CHAR(15) NOT NULL,
-> C_ACCTBAL DECIMAL(15,2) NOT NULL,
-> C_MKTSEGMENT CHAR(10) NOT NULL,
-> C_COMMENT VARCHAR(117) NOT NULL,
-> KEY idx_cnam(C_NAME)
-> );
Query OK, 0 rows affected (0.53 sec)
mysql> exit
Bye
[root@node1 ~]# for i in `seq 60`; do mysql -uroot -P4000 -h192.168.59.28 -e "insert into tpch.customer_test(c_name,c_address,c_nationkey,c_phone,c_acctbal,c_mktsegment,c_comment) select c_name,c_address,c_nationkey,c_phone,c_acctbal,c_mktsegment,c_comment from tpch.customer";done;
[root@node1 ~]# mysql -uroot -P4000 -h192.168.59.28
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5909
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use tpch
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_tpch |
+----------------+
| customer |
| customer_test |
| lineitem |
| nation |
| orders |
| part |
| partsupp |
| region |
| supplier |
+----------------+
9 rows in set (0.00 sec)
mysql> select count(*) from customer_test;
+----------+
| count(*) |
+----------+
| 9000000 |
+----------+
1 row in set (0.00 sec)3.执行测试SQL语句观察内存消耗情况，如下SQL语句使用内存714.2M，磁盘使用为0byts。
mysql> explain analyze select t.c_name,t.c_address,s.c_custkey from tpch.customer_test t,tpch.customer s where t.c_name=s.c_name order by t.c_address;
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:4m59.6s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:4m9.4s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:4m8.6s, loops:8793, build_hash_table:{total:2.31s, fetch:2.12s, build:183.4ms}, probe:{concurrency:5, total:20m48.2s, max:4m9.6s, probe:1m0s, fetch:19m48.2s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:2.08s, loops:148, cop_task: {num: 1, max: 2.2s, proc_keys: 150000, tot_proc: 2.19s, tot_wait: 3ms, rpc_num: 1, rpc_time: 2.2s, copr_cache_hit_ratio: 0.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:1.56s, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, rocksdb: {delete_skipped_count: 0, key_skipped_count: 150000, block: {cache_hit_count: 6, read_count: 503, read_byte: 11.4 MB}}} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:3m56.5s, loops:8795, cop_task: {num: 21, max: 30.6s, min: 6.4s, avg: 20.1s, p95: 30.3s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 4m32.2s, tot_wait: 1m9.1s, rpc_num: 87, rpc_time: 46m36.5s, copr_cache_hit_ratio: 0.00}, backoff{pdRPC: 2.66s, regionMiss: 110ms, tikvRPC: 5.81s} | data:TableFullScan_29 | 355.6 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:22.8s, min:322ms, p80:15s, p95:21.1s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 9000000, total_process_keys_size: 1832025900, total_keys: 9150021, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9150000, block: {cache_hit_count: 20831, read_count: 9254, read_byte: 372.6 MB}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
7 rows in set (4 min 59.68 sec)4.反复执行测试用SQL语句，进行监控
[root@node1 ~]# for i in `seq 10`;do mysql -uroot -P4000 -h192.168.59.28 -e "explain analyze select t.c_name,t.c_address,s.c_custkey from tpch.customer_test t,tpch.customer s where t.c_name=s.c_name order by t.c_address";done;
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:1m50.8s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:59.6s, loops:8792, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:59.3s, loops:8792, build_hash_table:{total:53.9s, fetch:53.8s, build:61ms}, probe:{concurrency:5, total:4m58s, max:59.6s, probe:27.6s, fetch:4m30.4s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:53.8s, loops:148, cop_task: {num: 1, max: 53.8s, proc_keys: 150000, tot_proc: 53.8s, tot_wait: 1ms, rpc_num: 1, rpc_time: 53.8s, copr_cache_hit_ratio: 0.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, rocksdb: {delete_skipped_count: 0, key_skipped_count: 150000, block: {cache_hit_count: 8, read_count: 498, read_byte: 11.1 MB}}} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:1.19s, loops:8795, cop_task: {num: 21, max: 48.1s, min: 534.6ms, avg: 10.1s, p95: 45.9s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 3m13.8s, tot_wait: 5.32s, rpc_num: 21, rpc_time: 3m32.6s, copr_cache_hit_ratio: 0.05}, backoff{regionMiss: 6ms} | data:TableFullScan_29 | 507.2 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:43.2s, min:439ms, p80:6.23s, p95:38.4s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29656, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:5m21s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:4m5.8s, loops:8792, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:4m15.7s, loops:8792, build_hash_table:{total:834ms, fetch:481.1ms, build:352.9ms}, probe:{concurrency:5, total:22m31.5s, max:4m30.3s, probe:5m21.3s, fetch:17m10.2s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:431.8ms, loops:148, cop_task: {num: 1, max: 425.4ms, proc_keys: 0, rpc_num: 1, rpc_time: 425.4ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:4m11.7s, loops:8795, cop_task: {num: 21, max: 39.3s, min: 39.9ms, avg: 19.4s, p95: 34.4s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 5m0.6s, tot_wait: 3.69s, rpc_num: 54, rpc_time: 31m54.4s, copr_cache_hit_ratio: 0.05}, backoff{tikvRPC: 2.6s, regionMiss: 152ms, pdRPC: 33.3s} | data:TableFullScan_29 | 223.8 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:35.2s, min:343ms, p80:29.9s, p95:30.9s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 19921, read_count: 9755, read_byte: 241.4 MB}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
ERROR 9001 (HY000) at line 1: PD server timeout
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:2m2.8s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:1m9s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:1m8.3s, loops:8793, build_hash_table:{total:62.2ms, fetch:2.25ms, build:60ms}, probe:{concurrency:5, total:5m45.4s, max:1m9.1s, probe:57s, fetch:4m48.4s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:511µs, loops:148, cop_task: {num: 1, max: 1.12ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.09ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:55.6s, loops:8795, cop_task: {num: 21, max: 55s, min: 1.52s, avg: 23.5s, p95: 54.3s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 8m6.8s, tot_wait: 3.18s, rpc_num: 114, rpc_time: 9m19.4s, copr_cache_hit_ratio: 0.00}, backoff{pdRPC: 41.2s, tikvRPC: 11.5s, regionScheduling: 288ms, regionMiss: 282ms} | data:TableFullScan_29 | 75.8 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:53.9s, min:308ms, p80:45.6s, p95:53.7s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 9000000, total_process_keys_size: 1832025900, total_keys: 9150021, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9150000, block: {cache_hit_count: 16284, read_count: 13866, read_byte: 313.6 MB}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:1m6.5s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:15.2s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:14.9s, loops:8793, build_hash_table:{total:217.1ms, fetch:61.7ms, build:155.4ms}, probe:{concurrency:5, total:1m16.2s, max:15.2s, probe:38.4s, fetch:37.8s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:41.2ms, loops:148, cop_task: {num: 1, max: 36.1ms, proc_keys: 0, rpc_num: 1, rpc_time: 36.1ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:7.08s, loops:8795, cop_task: {num: 21, max: 14.8s, min: 24.8ms, avg: 6.84s, p95: 14.7s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 2m20s, tot_wait: 356ms, rpc_num: 21, rpc_time: 2m23.7s, copr_cache_hit_ratio: 0.05} | data:TableFullScan_29 | 151.6 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:14.6s, min:803ms, p80:5.96s, p95:8.1s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29682, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:1m5.2s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:13.6s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:13.4s, loops:8793, build_hash_table:{total:213.8ms, fetch:38.7ms, build:175.1ms}, probe:{concurrency:5, total:1m8.2s, max:13.6s, probe:28.3s, fetch:40s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:27.9ms, loops:148, cop_task: {num: 1, max: 10.6ms, proc_keys: 0, rpc_num: 1, rpc_time: 10.6ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:7.61s, loops:8795, cop_task: {num: 21, max: 13.4s, min: 22.4ms, avg: 6.23s, p95: 13s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 2m7.4s, tot_wait: 475ms, rpc_num: 21, rpc_time: 2m10.9s, copr_cache_hit_ratio: 0.05} | data:TableFullScan_29 | 101.1 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:14.6s, min:766ms, p80:3.99s, p95:4.72s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29682, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:1m0.4s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:9.57s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:9.31s, loops:8793, build_hash_table:{total:218.2ms, fetch:124.6ms, build:93.6ms}, probe:{concurrency:5, total:48.1s, max:9.61s, probe:30.6s, fetch:17.4s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:69.5ms, loops:148, cop_task: {num: 1, max: 64.3ms, proc_keys: 0, rpc_num: 1, rpc_time: 64.3ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:3.22s, loops:8795, cop_task: {num: 21, max: 9.31s, min: 378.5ms, avg: 4.44s, p95: 8.93s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 1m20.4s, tot_wait: 8.78s, rpc_num: 21, rpc_time: 1m33.1s, copr_cache_hit_ratio: 0.05} | data:TableFullScan_29 | 126.3 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:14.6s, min:612ms, p80:2.18s, p95:2.89s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29682, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:58.8s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:7.72s, loops:8793, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:7.42s, loops:8793, build_hash_table:{total:394.4ms, fetch:221.8ms, build:172.7ms}, probe:{concurrency:5, total:38.8s, max:7.76s, probe:30.1s, fetch:8.68s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:189.4ms, loops:148, cop_task: {num: 1, max: 186.9ms, proc_keys: 0, rpc_num: 1, rpc_time: 186.9ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:1.38s, loops:8795, cop_task: {num: 21, max: 7.13s, min: 308.3ms, avg: 3.68s, p95: 7.11s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 1m10.7s, tot_wait: 2.12s, rpc_num: 21, rpc_time: 1m17.3s, copr_cache_hit_ratio: 0.05} | data:TableFullScan_29 | 151.6 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:14.6s, min:422ms, p80:2.04s, p95:2.48s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29682, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:59.7s, loops:8791 | tpch.customer_test.c_address | 714.2 MB | 0 Bytes |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:8.13s, loops:8792, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:7.51s, loops:8792, build_hash_table:{total:292.9ms, fetch:103.8ms, build:189.1ms}, probe:{concurrency:5, total:40.9s, max:8.18s, probe:36.1s, fetch:4.78s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:34.1ms, loops:148, cop_task: {num: 1, max: 23.2ms, proc_keys: 0, rpc_num: 1, rpc_time: 23.1ms, copr_cache_hit_ratio: 1.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:53.8s, loops:151} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:389.6ms, loops:8795, cop_task: {num: 21, max: 5.96s, min: 28.9ms, avg: 3.13s, p95: 5.95s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 57.5s, tot_wait: 420ms, rpc_num: 21, rpc_time: 1m5.6s, copr_cache_hit_ratio: 0.05} | data:TableFullScan_29 | 227.4 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:14.6s, min:738ms, p80:1.35s, p95:1.55s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 8872134, total_process_keys_size: 1805998721, total_keys: 9022154, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9022134, block: {cache_hit_count: 29682, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+---------+
5.通过修改参数限制单条SQL语句对于内存的使用
修改参数tidb_expensive_query_time_threshold，使测试SQL可以被记录到tidb.log中，如下：
[root@node1 ~]# mysql -uroot -P4000 -h192.168.59.28
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8443
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use tpch
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show variables like '%tidb_expensive_query_time%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| tidb_expensive_query_time_threshold | 60 |
+-------------------------------------+-------+
1 row in set (0.01 sec)
mysql> set tidb_expensive_query_time_threshold=10;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%tidb_expensive_query_time%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| tidb_expensive_query_time_threshold | 10 |
+-------------------------------------+-------+
1 row in set (0.03 sec)修改参数tidb_mem_quota_query，限制测试SQL的内存使用为107M，此参数只能是session级别
mysql> show variables like '%tidb_mem_quota_query%';
+----------------------+------------+
| Variable_name | Value |
+----------------------+------------+
| tidb_mem_quota_query | 1073741824 |
+----------------------+------------+
1 row in set (0.01 sec)
mysql> set tidb_mem_quota_query=107374100;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%tidb_mem_quota_query%';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| tidb_mem_quota_query | 107374100 |
+----------------------+-----------+
1 row in set (0.01 sec)6.再次执行测试SQL语句，观察内存和磁盘的使用情况，根据如下结果可以看出，之前需要4分多，现在需要9分多，之前disk没有值，现在disk有612.4M的使用。
mysql> explain analyze select t.c_name,t.c_address,s.c_custkey from tpch.customer_test t,tpch.customer s where t.c_name=s.c_name order by t.c_address;
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+----------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+----------+
| Sort_9 | 7947344.56 | 9000000 | root | | time:9m34s, loops:8791 | tpch.customer_test.c_address | 679.0 MB | 612.4 MB |
| └─Projection_12 | 7947344.56 | 9000000 | root | | time:43.4s, loops:8794, Concurrency:5 | tpch.customer_test.c_name, tpch.customer_test.c_address, tpch.customer.c_custkey | 424.1 KB | N/A |
| └─HashJoin_26 | 7947344.56 | 9000000 | root | | time:42.8s, loops:8794, build_hash_table:{total:13.5s, fetch:13.3s, build:109.5ms}, probe:{concurrency:5, total:13m46.4s, max:2m45.3s, probe:11m3.2s, fetch:2m43.2s} | inner join, equal:[eq(tpch.customer.c_name, tpch.customer_test.c_name)] | 4.95 MB | 0 Bytes |
| ├─TableReader_28(Build) | 151024.00 | 150000 | root | | time:13.3s, loops:148, cop_task: {num: 1, max: 13.3s, proc_keys: 150000, tot_proc: 10.2s, tot_wait: 15ms, rpc_num: 1, rpc_time: 13.3s, copr_cache_hit_ratio: 0.00} | data:TableFullScan_27 | 4.87 MB | N/A |
| │ └─TableFullScan_27 | 151024.00 | 150000 | cop[tikv] | table:s | tikv_task:{time:10.2s, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, rocksdb: {delete_skipped_count: 0, key_skipped_count: 150000, block: {cache_hit_count: 2, read_count: 504, read_byte: 11.4 MB}}} | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 9000000.00 | 9000000 | root | | time:20.8s, loops:8795, cop_task: {num: 21, max: 39s, min: 1.96s, avg: 26.6s, p95: 38.9s, max_proc_keys: 448280, p95_proc_keys: 448278, tot_proc: 8m41.2s, tot_wait: 16.6s, rpc_num: 21, rpc_time: 9m19.2s, copr_cache_hit_ratio: 0.00} | data:TableFullScan_29 | 79.5 MB | N/A |
| └─TableFullScan_29 | 9000000.00 | 9000000 | cop[tikv] | table:t | tikv_task:{proc max:38.1s, min:467ms, p80:24.5s, p95:38s, iters:8878, tasks:21}, scan_detail: {total_process_keys: 9000000, total_process_keys_size: 1832025900, total_keys: 9150021, rocksdb: {delete_skipped_count: 0, key_skipped_count: 9150000, block: {cache_hit_count: 3059, read_count: 27066, read_byte: 831.5 MB}}} | keep order:false | N/A | N/A |
+---------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+----------+----------+
7 rows in set (9 min 34.23 sec)「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
