GBase 8a数据库集群性能优化之保留执行中间表数据参数gcluster_executor_debug

GBase 8a数据库集群,在执行SQL时会将任务下发到计算节点,有时为了汇总方便,需要使用一些临时中间表做二次计算,在执行完毕后这些临时表会被自动删除。如果对执行过程或操作有怀疑,可以打开gcluster_executor_debug参数,包括中间临时表,以便调试。

参数:

gcluster_executor_debug
默认值为0. 关闭。 1,打开。
支持session级

作用:

SQL执行期间,产生的中间表,会被保留在gnode上。 可以用来分析执行过程是否符合预期,以及每个步骤是否有优化的方法。

样例:

如下开启了trc日志,其中记录了整个gnode上执行的情况,包含了创建的中间表信息。

[gbase@localhost ~]$ gccli testdb

GBase client 8.6.2.43-R23.119607. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> show variables like '%gcluster_executor_debug%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| gcluster_executor_debug | 0     |
+-------------------------+-------+
1 row in set (Elapsed: 00:00:00.01)

gbase> set gcluster_executor_debug=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> set gbase_sql_trace=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select id,count(*) from testdb.human group by id order by count(*) desc limit 2;
+------+----------+
| id   | count(*) |
+------+----------+
|    2 |        2 |
|    1 |        2 |
+------+----------+
2 rows in set (Elapsed: 00:00:00.20)

查看trc日志
其中创建了2个临时表
CREATE TABLE gctmpdb._tmp_rht_1795293194_12_t5_1_1596156045_s_n1
CREATE TABLE gctmpdb._tmp_1795293194_12_t5_2_1596156045_s

[root@localhost gbase]# cat gbase_root_24_20200731084917.trc
/opt/gnode/log/gbase/gbase_root_24_20200731084917.trc
Server Version: 8.6.2.43-R23.119607
Version Comment: 119607
Instance Name: gbase
Session ID: 24
User: root
Time: 20200731084917
GBASE_HOME=/opt/gnode/server/
CPUS: 1
MEM:  3791 MB

2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
CREATE TABLE `gctmpdb`._tmp_rht_1795293194_12_t5_1_1596156045_s_n1 AS SELECT /*::ffff:10.0.2.107_12_5_2020-07-31_08:49:17*/ /*+ TID('1638425') */ `testdb.human`.`id` AS `c2`, COUNT(0) AS `c3` FROM `testdb`.`human_n1` `testdb.human` GROUP BY `testdb.human`.`id` LIMIT 0 ; commit
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] Tables:
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          testdb.human(testdb.human_n1),  31216 rows,     1 DC
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] cnd(0):       FALSE  (0)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          total 1 DC,     found 1 DC to scan(with 0 FULL DC).
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] cnd(0)  FALSE
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          total 1 DC,     found 0 DC after scan(with 0 FULL DC).
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.000s)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(0 rows)
2020-07-31 08:49:17.214 [M:   1M,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(0 rows)
2020-07-31 08:49:17.214 [M:   1M,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.000s)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] ResultSender: send 0 rows.
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] insert done.

2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] SUMMARY
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] elapsed time:                00:00:00.000
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] data loaded from storage:       0B,  0s,     0 DC.
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] data decompressed:              0B,  0s.
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] temp space IO stats:
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.214 [M:   1M,   0B,D:   0B] [DC:     0,     0] ======================================================
2020-07-31 08:49:17.239 [M:   1M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT /*::ffff:10.0.2.107_12_5_2020-07-31_08:49:17*/ /*+ TID('1638425') */ `testdb.human`.`id` AS `c2`, COUNT(0) AS `c3` FROM `testdb`.`human_n1` `testdb.human` GROUP BY `testdb.human`.`id` target into server (HOST '::ffff:10.0.2.107', PORT 5050, USER 'root', PASSWORD '', DATABASE 'gctmpdb', TABLE '_tmp_rht_1795293194_12_t5_1_1596156045_s_n1', COMMENT 'group 0, distribution 1, hash_map 0 0 0 @65536' )
2020-07-31 08:49:17.239 [M:   1M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2020-07-31 08:49:17.239 [M:   1M,   0B,D:   0B] [DC:     0,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.239 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(31216 rows)
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] op buffer size: 33554432, tuple width: 12. op buffer can hold 2796202 rows
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] total tuples(per thread): 31216, tuples in op buffer(per thread): 2796202
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] OP buffer big enough, do not use one-pass algorithm
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] begin distinct ratio sampling...
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] finish distinct ratio sampling: less than 2 cells. (time used: 0.000s)
2020-07-31 08:49:17.239 [M:   1M,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(31216 rows)
2020-07-31 08:49:17.255 [M:   2M, 732K,D:   0B] [DC:     1,     0] Start send data to remote server.
2020-07-31 08:49:17.257 [M:   2M, 732K,D:   0B] [DC:     1,     0] gbase_hybrid_store: 1. NoTuplesEachDc: 31213
2020-07-31 08:49:17.257 [M:   2M, 732K,D:   0B] [DC:     1,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.286 [M:   2M, 732K,D:   0B] [DC:     5,     0] TaskQueue: becomes empty <<<<<<<<<<<<<<<<<<<
2020-07-31 08:49:17.286 [M:   2M, 732K,D:   0B] [DC:     5,     0] TqJob finish <<<<<<<<<<<<<<<<<<<
2020-07-31 08:49:17.286 [M:   2M, 732K,D:   0B] [DC:     5,     0] [Memory Statistics (GLOBAL)]: TempTable(0x5f46200)-CompatMem(1), SISRecordDispatcher: peak: 640.00KB, current: 17179869184.00GB, TmpMatBuf: peak: 1024.00KB, current: 0, ByteQueue: peak: 1.18MB, current: 1.12MB
2020-07-31 08:49:17.286 [M:   1M, 732K,D:   0B] [DC:     5,     0] Complete send data to remote server.
2020-07-31 08:49:17.286 [M:   1M, 732K,D:   0B] [DC:     5,     0] produced total 31213 groups.
2020-07-31 08:49:17.286 [M:   1M,  32B,D:   0B] [DC:     5,     0] END Serial Aggregation. (time used: 0.047s)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] ResultSender: send 31213 rows.
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] output result done.

2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] SUMMARY
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] elapsed time:                00:00:00.052
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] data loaded from storage:       0B,  0s,     0 DC.
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] data decompressed:              0B,  0s.
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] temp space IO stats:
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] ======================================================
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] SUMMARY for SIS
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0]
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for total:              0.024 s
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0]
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for materialize:        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for has map    :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for has table  :        0.024 s,        100%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for maxmin:    :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for compress:  :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for send:      :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for recv:      :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for append:    :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] ======================================================
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] SUMMARY for NEW_SIS
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0]
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for total:              0.024 s
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0]
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for hash       :        0.024 s,        100%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for compress   :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] Time for send       :        0 s,    0%
2020-07-31 08:49:17.291 [M:   1M,   0B,D:   0B] [DC:     5,     0] ======================================================
2020-07-31 08:49:17.292 [M:   1M,   0B,D:   0B] [DC:     5,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
CREATE TABLE `gctmpdb`._tmp_1795293194_12_t5_2_1596156045_s AS SELECT /*::ffff:10.0.2.107_12_5_2020-07-31_08:49:17*/ /*+ TID('1638425') */ `_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c2` AS `c4`, cast(SUM_2ND(`_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c3`) as signed) AS `c5` FROM `gctmpdb`._tmp_rht_1795293194_12_t5_1_1596156045_s_n1 GROUP BY `_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c2` LIMIT 0 ; commit
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] Tables:
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          _tmp_rht_1795293194_12_t5_1_1596156045_s_n1(gctmpdb._tmp_rht_1795293194_12_t5_1_1596156045_s_n1),       31213 rows,     1 DC
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] cnd(0):       FALSE  (0)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          total 1 DC,     found 1 DC to scan(with 0 FULL DC).
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] cnd(0)  FALSE
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] T0:          total 1 DC,     found 0 DC after scan(with 0 FULL DC).
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.000s)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2020-07-31 08:49:17.301 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(0 rows)
2020-07-31 08:49:17.302 [M:   1M,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(0 rows)
2020-07-31 08:49:17.302 [M:   1M,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.000s)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] ResultSender: send 0 rows.
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] insert done.

2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] SUMMARY
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] elapsed time:                00:00:00.001
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] data loaded from storage:       0B,  0s,     0 DC.
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] data decompressed:              0B,  0s.
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] temp space IO stats:
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.302 [M:   1M,   0B,D:   0B] [DC:     0,     0] ======================================================
2020-07-31 08:49:17.304 [M:   1M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT /*::ffff:10.0.2.107_12_5_2020-07-31_08:49:17*/ /*+ TID('1638425') */ `_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c2` AS `c4`, cast(SUM_2ND(`_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c3`) as signed) AS `c5` FROM `gctmpdb`._tmp_rht_1795293194_12_t5_1_1596156045_s_n1 GROUP BY `_tmp_rht_1795293194_12_t5_1_1596156045_s_n1`.`c2` ORDER BY `c5` DESC LIMIT 2 target into server (HOST '::ffff:10.0.2.107', PORT 5050, USER 'root', PASSWORD '', DATABASE 'gctmpdb', TABLE '_tmp_1795293194_12_t5_2_1596156045_s', COMMENT '' )
2020-07-31 08:49:17.304 [M:   1M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2020-07-31 08:49:17.305 [M:   1M,   0B,D:   0B] [DC:     0,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.305 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(31213 rows)
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] op buffer size: 33554432, tuple width: 16. op buffer can hold 2097152 rows
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] total tuples(per thread): 31213, tuples in op buffer(per thread): 2097152
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] OP buffer big enough, do not use one-pass algorithm
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] begin distinct ratio sampling...
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] finish distinct ratio sampling: less than 2 cells. (time used: 0.000s)
2020-07-31 08:49:17.305 [M:   1M,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(31213 rows)
2020-07-31 08:49:17.353 [M:   3M, 732K,D:   0B] [DC:     1,     2] produced 31210 groups, 3 rows left.
2020-07-31 08:49:17.354 [M:   3M, 732K,D:   0B] [DC:     1,     2] produced total 31213 groups.
2020-07-31 08:49:17.354 [M:   3M,  32B,D:   0B] [DC:     1,     2] END Serial Aggregation. (time used: 0.049s)
2020-07-31 08:49:17.355 [M:   3M,   0B,D:   0B] [DC:     1,     2] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.355 [M:   3M,   0B,D:   0B] [DC:     1,     2] BEGIN Sort(31213 rows)
2020-07-31 08:49:17.355 [M:   3M,   0B,D:   0B] [DC:     1,     2] get DOP = 1: idle=2, parallel_degree=0, max_parallel=2
2020-07-31 08:49:17.355 [M:   3M,  64B,D:   0B] [DC:     1,     2] limit sorter initialized, total_bytes: 22, key_bytes: 9.
2020-07-31 08:49:17.392 [M:   4M,  64B,D:   0B] [DC:     7,     2] Start send data to remote server.
2020-07-31 08:49:17.393 [M:   4M,  64B,D:   0B] [DC:     7,     2] gbase_hybrid_store: 1. NoTuplesEachDc: 2
2020-07-31 08:49:17.394 [M:   5M,  64B,D:   0B] [DC:     9,     2] TaskQueue: becomes empty <<<<<<<<<<<<<<<<<<<
2020-07-31 08:49:17.394 [M:   5M,  64B,D:   0B] [DC:     9,     2] TqJob finish <<<<<<<<<<<<<<<<<<<
2020-07-31 08:49:17.395 [M:   4M,  64B,D:   0B] [DC:    10,     2] [Memory Statistics (GLOBAL)]: TempTable(0x623e000)-CompatMem(1), SISRecordDispatcher: peak: 640.00KB, current: 17179869184.00GB, TmpMatBuf: peak: 1024.00KB, current: 0, ByteQueue: peak: 1.18MB, current: 1.12MB
2020-07-31 08:49:17.395 [M:   3M,  64B,D:   0B] [DC:    10,     2] Complete send data to remote server.
2020-07-31 08:49:17.395 [M:   3M,  64B,D:   0B] [DC:    10,     2] END Sort. (time used: 0.040s)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] ResultSender: send 2 rows.
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] output result done.

2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] SUMMARY
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] elapsed time:                00:00:00.091
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] data loaded from storage:     122K,  0s,     2 DC.
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] data decompressed:            168K,  0s.
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] temp space IO stats:
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] ======================================================
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] SUMMARY for SIS
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2]
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for total:              0.001 s
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2]
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for materialize:        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for has map    :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for has table  :        0.001 s,        100%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for maxmin:    :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for compress:  :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for send:      :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for recv:      :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for append:    :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] ======================================================
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] SUMMARY for NEW_SIS
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2]
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for total:              0.001 s
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2]
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for hash       :        0.001 s,        100%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for compress   :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] Time for send       :        0 s,    0%
2020-07-31 08:49:17.396 [M:   1M,   0B,D:   0B] [DC:    10,     2] ======================================================
2020-07-31 08:49:17.398 [M:   1M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT /*::ffff:10.0.2.107_12_5_2020-07-31_08:49:17*/ /*+ TID('1638425') */ `_tmp_1795293194_12_t5_2_1596156045_s`.`c4` AS `id`, `_tmp_1795293194_12_t5_2_1596156045_s`.`c5` AS `count(*)` FROM `gctmpdb`._tmp_1795293194_12_t5_2_1596156045_s ORDER BY `count(*)` DESC LIMIT 2
2020-07-31 08:49:17.398 [M:   1M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     0,     0] BEGIN Sort(2 rows)
2020-07-31 08:49:17.399 [M:   1M,  32B,D:   0B] [DC:     0,     0] limit sorter initialized, total_bytes: 9, key_bytes: 0.
2020-07-31 08:49:17.399 [M:   2M,  32B,D:   0B] [DC:     2,     1] Send 2 rows already
2020-07-31 08:49:17.399 [M:   1M,  32B,D:   0B] [DC:     2,     1] END Sort. (time used: 0.000s)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] ResultSender: send 0 rows.
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] output result done.

2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] SUMMARY
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] elapsed time:                00:00:00.000
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] data loaded from storage:      36B,  0s,     1 DC.
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] data decompressed:              8K,  0s.
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] temp space IO stats:
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2020-07-31 08:49:17.399 [M:   1M,   0B,D:   0B] [DC:     2,     1] ======================================================
[root@localhost gbase]#

查看gnode里的中间表数据

[gbase@localhost ~]$ gncli gctmpdb

GBase client 8.6.2.43-R23.119607. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> show tables;
+---------------------------------------------+
| Tables_in_gctmpdb                           |
+---------------------------------------------+
| _tmp_1795293194_12_t5_2_1596156045_s        |
| _tmp_rht_1795293194_12_t5_1_1596156045_s_n1 |
+---------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select count(*) from ._tmp_rht_1795293194_12_t5_1_1596156045_s_n1;
+----------+
| count(*) |
+----------+
|    31213 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select count(*) from _tmp_1795293194_12_t5_2_1596156045_s;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from _tmp_1795293194_12_t5_2_1596156045_s;
+------+------+
| c4   | c5   |
+------+------+
|    2 |    2 |
|    1 |    2 |
+------+------+
2 rows in set (Elapsed: 00:00:00.00)

GBase 8a数据库集群性能优化之保留执行中间表数据参数gcluster_executor_debug》有1条评论

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注