南大通用GBase 8a通过show profile查看占用的CPU,磁盘读写IO等信息

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)

参考