-
南大通用数据库-Gbase-8a-学习-05-通过审计日志抓取Sql、Trace日志查看执行计划
一、测试环境
名称 值 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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
参数介绍：
参数名 描述 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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
（3）查看审计表数据
我测试是需要同时打开上面的（1）（2）才可以开启审计。
我们再打开一个终端，执行查询语句，再查看审计表。gbase> select * from gbase.audit_log where db='czg'; +-----------+--------+---------------------+---------------------+---------------------------+-----+------+---------+-----------------+------+-----+---------------------------------------+-------------------+----------+-------------+-----------+---------+-----------+ | 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 | czg | WRITE: ; READ: `czg`.`czg`; OTHER: ; | select * from czg | DQL | SELECT | | SUCCESS | CAPI | +-----------+--------+---------------------+---------------------+---------------------------+-----+------+---------+-----------------+------+-----+---------------------------------------+-------------------+----------+-------------+-----------+---------+-----------+ 1 row in set (Elapsed: 00:00:00.00)- 1
- 2
- 3
- 4
- 5
- 6
- 7
如果上面设置的是
set global log_output = 'file';- 1
在/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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
（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)- 1
- 2
- 3
- 4
- 5
三、通过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 | czg | | 2 | zxj | +------+------+ 2 rows in set (Elapsed: 00:00:00.19)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
（2）查看日志
日志可能分布在每个数据节点，里面会有一个完整的TRACE日志。
如果安装了C3包可以使用如下命令，快速定位是哪个文件，大的文件就是：cexec data: 'ls -lrt /opt/gnode/log/gbase/*.trc|tail -n1 '- 1
名称 描述 路径 /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] SQL Statement: SELECT /*::ffff:192.168.142.10_36_54_2022-08-11_15:01:50*/ /*+ TID('148113') */ `czg.czg`.`a` AS `a`, `czg.czg`.`b` AS `b` FROM `czg`.`czg_n1` `czg.czg` 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: 2, 0] before opt:select "czg"."czg.czg"."a" AS "a","czg"."czg.czg"."b" AS "b" from "czg"."czg_n1" "czg.czg" 2022-08-11 15:01:50.323 [M: 128B, 0B,D: 0B] [DC: 2, 0] after opt:select "czg"."czg.czg"."a" AS "a","czg"."czg.czg"."b" AS "b" from "czg"."czg_n1" "czg.czg" 2022-08-11 15:01:50.323 [M: 128B, 0B,D: 0B] [DC: 0, 0] SQL Statement: SELECT /*::ffff:192.168.142.10_36_54_2022-08-11_15:01:50*/ /*+ TID('148113') */ `czg.czg`.`a` AS `a`, `czg.czg`.`b` AS `b` FROM `czg`.`czg_n1` `czg.czg` 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] ======================================================- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
-
相关阅读:
软考高级信息系统项目管理师系列论文十六：论信息系统项目质量管理
驱动开发：内核解锁与强删文件
彩虹商城知识付费程序
JS点击按钮获取列表信息，纯JS代码创建表格，如何引入CSS框架，＜svg＞标签
视频如何转换成音频？音视频转换，4个方法
使用Java构建RESTful API：实现灵活、可扩展的Web服务
软件保护工具VMProtect将许可系统集成到应用程序（8）：硬件锁定
运筹学-单纯形法-代码实现（包含做题的每一步骤）
使用mysql语句操作数据库
【leetcode】三数之和
- 原文地址：https://blog.csdn.net/qq_45111959/article/details/126284983
