GBase 8a通过集群日志查看执行计划、各阶段整体耗时和各个节点的耗时做性能排查

GBase 8a提供了执行计划，以及不同的日志级别，现实整体各个节点耗时，以及每个节点的耗时，来方便用户进行性能排查，本文介绍详细的分析方法。

环境

2节点虚拟机集群

[gbase@rh6-1 ~]$ gcadmin
CLUSTER STATE:  ACTIVE
CLUSTER MODE:   NORMAL

=================================================================
|             GBASE COORDINATOR CLUSTER INFORMATION             |
=================================================================
|   NodeName   |     IpAddress     |gcware |gcluster |DataState |
-----------------------------------------------------------------
| coordinator1 |    10.0.2.201     | OPEN  |  OPEN   |    0     |
-----------------------------------------------------------------
=============================================================
|              GBASE DATA CLUSTER INFORMATION               |
=============================================================
|NodeName |     IpAddress     |gnode |syncserver |DataState |
-------------------------------------------------------------
|  node1  |    10.0.2.201     | OPEN |   OPEN    |    0     |
-------------------------------------------------------------
|  node2  |    10.0.2.202     | OPEN |   OPEN    |    0     |
-------------------------------------------------------------

简单的单列表，50万行

gbase> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "id" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (Elapsed: 00:00:00.06)

打开参数

其中profiling参数是可以直观的查看执行过程每个阶段的整体耗时。重点关注其中的Sending task to gnodes部分耗时。

gcluster_log_level参数7是可以查看到每个节点的具体SQL以及每个节点的耗时。

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

gbase> set gcluster_log_level=7;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

执行计划

可以看到分成了3大部分。第0部分REDIST(id)是根据id列做重分布。第1部分[GATHER]是将各个节点的数据汇总，第2部分[RESULT]是返回结果。

gbase> explain select id,count(*) from t1 group by id order by count(*) desc limit 10;
+----+--------------+-----------+---------+-------------------------------------------------+-----------------+
| ID | MOTION       | OPERATION | TABLE   | CONDITION                                       | NO STAT Tab/Col |
+----+--------------+-----------+---------+-------------------------------------------------+-----------------+
| 02 | [RESULT]     |  Step     | <01>    |                                                 |                 |
|    |