南大通用数据库-Gbase-8a-通过审计日志抓取Sql、Trace日志查看执行计划-06

酷酷的乐

于 2024-08-05 09:39:05 发布

阅读量153

点赞数 9

文章标签：数据库分布式 linux 运维

本文链接：https://blog.csdn.net/m0_61155172/article/details/140918132

版权

一、测试环境

名称	值
cpu	Intel® Core™ i5-1035G1 CPU @ 1.00GHz
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	3G
逻辑核数	2
节点1-IP	192.168.142.10
节点2-IP	192.168.142.11

数据库版本

8.6.2.43-R33.132743

二、通过审计日志抓取Sql

（1）开启审计参数

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

gbase> set global log_output = 'table';
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.00)

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

参数介绍：

参数名	描述
long_query_time	单位为：秒，超过这个时间的sql进行记录，0为全记录。
log_output	默认为：file。文件路径在：/opt/gcluster/log/gcluster/gclusterd-audit.log，可能有变化。

audit_log

审计日志开关，0为关闭，1为开。

（2）配置审计测试策略

gbase> create audit policy ap1(enable='y',long_query_time=0,Obj_type='TABLE(VIEW)',Sql_commands='SELECT');
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.01)

gbase> select* from gbase.audit_policy;
+------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
| Name | Enable | Hosts | User | Db | Obj_type    | Object | Sql_commands | Long_query_time | Status |
+------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
| ap1  | Y      |       |      |    | TABLE(VIEW) |        | SELECT       |        0.000000 |        |
+------+--------+-------+------+----+-------------+--------+--------------+-----------------+--------+
1 row in set (Elapsed: 00:00:00.00)

gbase> desc gbase.audit_policy;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| Field           | Type                                                                                                                                                                                                                                                                                                                                                                                                                | Null | Key | Default  | Extra |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
| Name            | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   | PRI |          |       |
| Enable          | enum('Y','N')                                                                                                                                                                                                                                                                                                                                                                                                       | NO   |     | Y        |       |
| Hosts           | varchar(512)                                                                                                                                                                                                                                                                                                                                                                                                        | NO   |     |          |       |
| User            | varchar(16)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Db              | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Obj_type        | enum('','TABLE(VIEW)','PROCEDURE','FUNCTION')                                                                                                                                                                                                                                                                                                                                                                       | NO   |     |          |       |
| Object          | varchar(64)                                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
| Sql_commands    | set('','INSERT','DELETE','UPDATE','LOAD','CREATE_USER','CREATE_DB','CREATE_TABLE','CREATE_VIEW','CREATE_INDEX','CREATE_PROCEDURE','CREATE_FUNCTION','CREATE_EVENT','RENAME_USER','ALTER_DB','ALTER_TABLE','ALTER_PROCEDURE','ALTER_FUNCTION','ALTER_EVENT','DROP_USER','DROP_DB','DROP_TABLE','DROP_VIEW','DROP_INDEX','DROP_PROCEDURE','DROP_FUNCTION','DROP_EVENT','TRUNCATE','GRANT','REVOKE','SELECT','OTHERS') | NO   |     |          |       |
| Long_query_time | decimal(18,6)                                                                                                                                                                                                                                                                                                                                                                                                       | NO   |     | 0.000000 |       |
| Status          | enum('','SUCCESS','FAILED')                                                                                                                                                                                                                                                                                                                                                                                         | NO   |     |          |       |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+----------+-------+
10 rows in set (Elapsed: 00:00:00.00)

（3）查看审计表数据

测试是需要同时打开上面的（1）（2）才可以开启审计。
我们再打开一个终端，执行查询语句，再查看审计表。

gbase> select * from gbase.audit_log where db='abc';
+-----------+--------+---------------------+---------------------+---------------------------+-----+------+---------+-----------------+------+-----+---------------------------------------+-------------------+----------+-------------+-----------+---------+-----------+
| thread_id | taskid | start_time          | end_time            | user_host                 | uid | user | host_ip | query_time      | rows | db  | table_list                            | sql_text          | sql_type | sql_command | operators | status  | conn_type |
+-----------+--------+---------------------+---------------------+---------------------------+-----+------+---------+-----------------+------+-----+---------------------------------------+-------------------+----------+-------------+-----------+---------+-----------+
|        36 | 148044 | 2022-08-11 14:30:21 | 2022-08-11 14:30:21 | root[root] @ localhost [] |   1 | root |         | 00:00:00.012838 |    2 | abc | WRITE: ; READ: `abc`.`abc`; OTHER: ;  | select * from abc | DQL      | SELECT      |           | SUCCESS | CAPI      |
+-----------+--------+---------------------+---------------------+---------------------------+-----+------+---------+-----------------+------+-----+---------------------------------------+-------------------+----------+-------------+-----------+---------+-----------+
1 row in set (Elapsed: 00:00:00.00)

如果上面设置的是

set global log_output = 'file';

在/opt/gcluster/log/gcluster/gclusterd-audit.log里面看到审计的Sql

# Threadid=36;
# Taskid=148096;
# Time: 220811 14:52:37
# End_time: 220811 14:52:37
# User@Host: root[root] @ localhost []
# UID: 1
# Query_time: 0.006730 Rows: 2
# use czg;
# Tables: WRITE: ; READ: `czg`.`czg`; OTHER: ; ;
# SET timestamp=1660200757;
# Sql_text: select * from czg;
# Sql_type: DQL;
# Sql_command: SELECT;
# Status: SUCCESS;
# Connect Type: CAPI;

（4）抓取完改回参数，清理策略

gbase> drop audit policy ap1;
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.01)

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

三、通过Trace日志查看执行计划

（1）修改数据库参数

gbase> set global gbase_sql_trace_level=15;
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.01)

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

gbase> select * from czg;
+------+------+
| a    | b    |
+------+------+
|    1 | abc  |
|    2 | asd  |
+------+------+
2 rows in set (Elapsed: 00:00:00.19)

（2）查看日志

日志可能分布在每个数据节点，里面会有一个完整的TRACE日志。
如果安装了C3包可以使用如下命令，快速定位是哪个文件，大的文件就是：

cexec data: 'ls -lrt /opt/gnode/log/gbase/*.trc|tail -n1 '

名称	描述
路径	/opt/gnode/log/gbase/
文件名	例如：gbase_root_26_20220811150150.trc

如果没有配置需要我们去每个节点的这个路径下去寻找。
日志样式如下：

/opt/gnode/log/gbase/gbase_root_26_20220811150150.trc
Server Version: 8.6.2.43-R33.132743
Version Comment: 132743
Instance Name: gbase
Session ID: 26
User: root
Time: 20220811150150
GBASE_HOME=/opt/gnode/server/
CPUS: 2
MEM:  2945 MB


2022-08-11 15:01:50.323 [M: 128B,   0B,D:   0B] [DC:     2,     0] _gbase_file_sync_level value, old: 1, new: 1
2022-08-11 15:01:50.323 [M: 128B,   0B,D:   0B] [DC:     2,     0] sync_frm value, old: 1, new: 1
2022-08-11 15:01:50.323 [M: 128B,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2022-08-11 15:01:50.323 [M: 128B,   0B,D:   0B] [DC:     0,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 2, attr count = 2, record size = 308, page size = 65536
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 2, attr count = 2, record size = 11, page size = 65536
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 2, attr count = 2, record size = 11, page size = 65536
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0]
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0] BEGIN Materialization(2 rows, page size: 65536)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 2, attr count = 2, record size = 11, page size = 65536
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     0,     0] need not materialize here, materialize later
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] Send 2 rows already
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 65536, attr count = 2, record size = 11, page size = 65536
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] ResultSender: send 0 rows.
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] output result done.

2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] SUMMARY
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] elapsed time:                00:00:00.000
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] data loaded from storage:       0B,  0s,     0 DC.
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] data decompressed:              0B,  0s.
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] temp space IO stats:
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2022-08-11 15:01:50.324 [M: 128B,   0B,D:   0B] [DC:     2,     0]

酷酷的乐

关注

9
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
南大通用数据库-Gbase-8a-通过审计日志抓取Sql、Trace日志查看执行计划-06

在/opt/gcluster/log/gcluster/gclusterd-audit.log里面看到审计的Sql。日志可能分布在每个数据节点，里面会有一个完整的TRACE日志。测试是需要同时打开上面的（1）（2）才可以开启审计。如果没有配置需要我们去每个节点的这个路径下去寻找。我们再打开一个终端，执行查询语句，再查看审计表。二、通过审计日志抓取Sql。（2）配置审计测试策略。
复制链接

扫一扫