GBase 8a集群的show profile目前只能提供每个阶段的耗时，但因为实际执行在数据节点，所以没有准确的CPU和磁盘IO读写信息。而在数据节点上，可以通过show profile all查看到相对真实的CPU，磁盘读写次数等资源使用信息。
show profiles
查看所有缓冲的Query信息show profile CPU,BLOCK IO;
如上应是最常用的信息。
目录导航
语法
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}参数
profiling参数，默认是关闭，仅支持session级设置。
gbase> set profiling=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
使用样例
注意，磁盘IO，指真实的从物理磁盘读取和写入，如果操作系统有缓冲，将不会被记录。为了更真实的评估磁盘IO,需要做冷数据，清理掉操作系统缓冲和数据库的缓冲。
清理缓冲
清理操作系统缓冲
[root@gbase_rh7_001 ~]# echo 3 > /proc/sys/vm/drop_caches
[root@gbase_rh7_001 ~]#清理数据库缓冲
gbase> release cache;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)查询语句
查看已经有得queryID show profiles
gbase> show profiles;
+----------+--------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+--------------+-------------------------------------------------------------------+
| 1 | 0.00109450 | show variables like '%profiling%' |
| 2 | 0.00016775 | select @@profiling |
| 3 | 0.00218100 | use test |
| 4 | 0.00897825 | select * from hdfs_load where numericcol = '111' |
| 5 | 0.00007625 | show profile for select * from hdfs_load where numericcol = '111' |
| 6 | 260.01173450 | select *,sleep(300) from hdfs_load where numericcol = '111' |
| 7 | 5.02966700 | select *,sleep(300) from hdfs_load where numericcol = '111' |
| 8 | 0.02451600 | select * from hdfs_load where numericcol = '111' |
+----------+--------------+-------------------------------------------------------------------+
8 rows in set (Elapsed: 00:00:00.00)样例
将只有读取IO部分，如下看 Block_ops_in。
gbase> select id,count(*) from t1_n1 group by id order by count(*) desc limit 11;
+-------+----------+
| id | count(*) |
+-------+----------+
| 25142 | 720 |
| 22546 | 715 |
| 5467 | 710 |
| 3482 | 704 |
| 22827 | 703 |
| 29405 | 700 |
| 17247 | 700 |
| 16492 | 699 |
| 31178 | 697 |
| 21055 | 695 |
| 22810 | 693 |
+-------+----------+
11 rows in set (Elapsed: 00:00:04.90)
gbase> show profile all;
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps |
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| parse | 0.000243 | 0.000251 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| init | 4.896292 | 4.789630 | 0.027203 | 30 | 462 | 79056 | 0 | 0 | 0 | 0 | 90 | 0 |
| query end | 0.000107 | 0.000102 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| commit end | 0.000081 | 0.000082 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| freeing items | 0.000070 | 0.000067 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| cleaning up | 0.000028 | 0.000027 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
6 rows in set (Elapsed: 00:00:00.00)
导出少量数据
涉及到读写。大量的磁盘读，计算以及少量的磁盘写入。
gbase> select id,count(*) from t1_n1 group by id order by count(*) desc limit 11 into outfile '/home/gbase/1.txt' writemode by overwrites;
Query OK, 11 rows affected (Elapsed: 00:00:04.63)
gbase> show profile all;
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps |
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| parse | 0.000260 | 0.000255 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 |
| init | 4.629179 | 4.532016 | 0.031844 | 26 | 434 | 79024 | 8 | 0 | 0 | 0 | 12 | 0 |
| query end | 0.000164 | 0.000156 | 0.000001 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| commit end | 0.000099 | 0.000000 | 0.001012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| freeing items | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| cleaning up | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
6 rows in set (Elapsed: 00:00:00.00)
gbase>
DML语句
会有读写信息，可以看到，全表复制时的读写几乎相当。
gbase> create table a like t1_n1;
Query OK, 0 rows affected (Elapsed: 00:00:00.18)
gbase> insert into a select * from t1_n1;
Query OK, 20000005 rows affected (Elapsed: 00:00:23.84)
Records: 20000005 Duplicates: 0 Warnings: 0 Total: 20000005
gbase> show profile all;
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
| parse | 0.000144 | 0.000139 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 |
| checking permissions | 0.000174 | 0.000172 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 0 |
| init | 20.409096 | 18.462448 | 1.101342 | 114 | 1906 | 921282 | 916936 | 0 | 0 | 1 | 206776 | 0 |
| query end | 0.064205 | 0.000473 | 0.000009 | 5 | 0 | 0 | 16 | 0 | 0 | 0 | 1 | 0 |
| commit testdb.a | 3.366323 | 0.006193 | 0.046511 | 39 | 3 | 0 | 0 | 0 | 0 | 0 | 26 | 0 |
| commit end | 0.000108 | 0.000100 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| freeing items | 0.000066 | 0.000064 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| cleaning up | 0.000026 | 0.000026 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+
8 rows in set (Elapsed: 00:00:00.00)