数据库日志管理
数据库日志管理¶
日志记录方式¶
SeaboxSQL的数据库日志管理系统是一套高度灵活的机制，它允许通过不同的方法记录和存储数据库服务器的运行信息，以支持问题诊断和系统监控,审计等多个功能。主要的日志记录方式包括标准错误（stderr）、CSV 文件格式的日志（csvlog）、系统日志（syslog）。
推荐以日志收集器为实现基础的CSV日志方式：
当选择将日志输出到标准错误或CSV文件时，SeaboxSQL允许这些日志通过一个后台进程，称为日志收集器，被重定向到磁盘上的指定文件。这个过程中，日志收集器将捕获所有重定向到标准错误的消息，并将它们存储到预设的日志文件中。这种方式的一个优势是提供了日志文件的持续管理，包括文件的创建和轮转，这有助于避免单个日志文件变得过大。
同时CSV格式的日志输出特别适合于后期分析，因为这种格式可以被方便地导入到各种数据库或表格处理软件中。
SeaboxSQL的日志系统设计考虑到了高负载情况下的性能和可靠性，确保在系统负载极高时不丢失日志消息。这对于保证在复杂的生产环境中追踪和诊断问题至关重要。同时，用户可以指定日志文件存储的具体目录，无论是绝对路径还是相对于数据库集群数据目录的路径，从而在系统架构中提供灵活的日志管理策略。
以下配置参数控制这日志记录方式(详细的参数说明参见参考手册)
log_destination: 设置日志输出目的地，如 stderr 和 csvlog。logging_collector: 启用后台日志收集器，将 stderr 日志信息重定向到文件中。log_directory: 指定日志文件存储的目录。log_filename: 配置日志文件的命名模式。log_file_mode: 设置日志文件的权限模式。
日志记录内容¶
- CSV日志保证了每条SQL执行的信息由一条日志记录，为了记录比较完善的信息，同时增加了固定列，其中包括：资源信息，系统状态信息，内置审计等功能。
- 增加了扩展区，扩展区提供8个text类型的字符串字段，用于记录扩展内容。同时增加global_detail,session_detail,stat_detail(系统状态信息)，query_detail等预定义字段。 以下的表格描述了当前csv日志列格式 ，
| cidx | name | type | desc |
|---|---|---|---|
| 0 | log_time | timestamp | 日志时间戳 |
| with time | |||
| zone | |||
| 1 | log_type | text | 日志的类别，分成如下几个类型 T_PLAIN(普通日志) T_START(server启动) T_SHUTDOWN(server关闭) T_LOGIN（用户登路 注意需要配合log_connections=on) T_LOGOUT(用户登出,注意需要配置log_disconnnections=on)T_FINISH(无SQL的错误日志) T_QUERY(每条SQL默认会打印一条，当log_duration=on时，成功执行SQL会打印一条日志。记录这条SQL的执行状态信息，其中根据配置参数(sdlog_classes)可能包含资源统计信息，系统状态信息，对象审计相关信息 出错SQL会记录一条错误详细信息日志，同时会根据配置项(sdlog_classes中包含"SYSINFO")，而打印整个系统状态信息 |
| 2 | tag_global | text | 占位标签，此标签后面一般为server全局的状态信息 |
| 3 | num_conns | int | 当前连接数 |
| 4 | num_sess_active | bigint | 活跃的session数量 |
| 5 | num_sess_queued | bigint | 排队的session数量 |
| 6 | global_mem | bigint | 当前实例使用内存总量 |
| 7 | sess_mem | bigint | 当前session使用的内存字节数 |
| 8 | tag_session | text | 占位标签，此标签后面为当前session状态信息 |
| 9 | user_id | bigint | 用户id |
| 10 | user_name | text | 用户名 |
| 11 | database_name | text | 数据库名称 |
| 12 | process_id | int | 进程ID |
| 13 | connection_from | text | 客户端连接信息 |
| 14 | session_id | text | session id |
| 15 | session_line_num | int | session的日志序列号 |
| 16 | sc_db_id | int | 标识记录此条日志的coordinator,如果配置sdlog_archive_base_dbid,则 |
| 计算公式为((sdlog_archive_base_dbid * 10000) + | |||
| dbid)，否则值为dbid | |||
| 17 | sc_session_id | text | 集群的session_id |
| 18 | sc_command_count | int | session已经执行query的数量 |
| 19 | sc_slice | int | slice |
| 20 | command_tag | text | 命令标签，ps进程会显示这部分 |
| 21 | session_start_time | timestamp | session开始时间戳 |
| with time | |||
| zone | |||
| 22 | virtual_transaction_ | text | 虚拟事务ID |
| id | |||
| 23 | transaction_id | text | 普通事务ID |
| 24 | sc_dist_tranx_id | text | 分布式事务ID |
| 25 | local_tranx_id | text | 本地事务ID |
| 26 | sub_tranx_id | text | 子事务ID |
| 27 | tag_resource | text | 占位标签，此标签后面为当前session的资源使用信息log_exetended_class必须包含 |
| “RESOURCE”时，以下的stat_开头的资源列才会输出正确值，否则为空，这些信息同时还依赖sd | |||
| _stat_activity_enable=on配置参数，否则输出为0 | |||
| 28 | stat_cpu | bigint | cpu百分比（集群:取所有executor的累计值） |
| 29 | stat_mem_peak | bigint | 当前session使用内存峰值(集群:取所有executor的最大值) |
| 30 | stat_read | bigint | 磁盘读（字节)(累加值) |
| 31 | stat_write | bigint | 磁盘写(字节)(累加值) |
| 32 | stat_tmp_write | bigint | 算子产生的的临时文件写（字节)(累加值) |
| 33 | stat_ic_recv | bigint | 网络收(字节）(累加值) |
| 34 | stat_ic_send | bigint | 网络发(字节)(累加值) |
| 35 | stat_ic_retry | bigint | 网络重试(累加值) |
| 36 | stat_sblk_hit | bigint | 该语句造成的共享块缓冲命中总数(累加值) |
| 37 | stat_sblk_r | bigint | 该语句读取的共享块的总数(累加值) |
| 38 | stat_sblk_dt | bigint | 该语句弄脏的共享块的总数(累加值) |
| 39 | stat_sblk_w | bigint | 该语句写入的共享块的总数(累加值) |
| 40 | stat_tblk_r | bigint | 该语句读取的临时块的总数(累加值) |
| 41 | stat_tblk_w | bigint | 该语句写入的临时块的总数(累加值) |
| 42 | stat_blk_r_time | float8 | 该语句花在读取块上的总时间，以毫秒计（如果track_io_timing 被启用，否则为零(累加值) |
| 43 | stat_blk_w_time | float8 | 该语句花在写入块上的总时间，以毫秒计（如果track_io_timing 被启用，否则为零 |
| 44 | tag_statement | text | 占位标签，此标签后面为SQL语句的状态信息 |
| 45 | duration | float8 | 执行时间(ms) |
| 46 | query_start_time | timestamp | SQL开始时间 |
| with time | |||
| zone | |||
| 47 | state | text | 此语句的状态信息，当前认为elevel为WARNING，ERROR，FATAL时设置为"error" |
| ，否则设置为OK | |||
| 48 | error_severity | text | 错误严重性 |
| 49 | sql_state_code | text | 错误码 |
| 50 | message | text | 错误消息 |
| 51 | detail | text | 错误消息详情 |
| 52 | hint | text | 提示消息 |
| 53 | context | text | 错误上下文 |
| 54 | processed_rows | bigint | 当前语句处理行,一般DML语句 影响这列 |
| 55 | scanned_rows | bigint | 当前语句扫描行数，一般select语句，影响这列 |
| 56 | query_id | text | query模板化的后的hash值 |
| 57 | query | text | 此语句的SQL |
| 58 | query_pos | int | 当query不是存储过程或函数时， query中导致错误的字符位置 |
| 59 | query_plan | text | SQL的计划 |
| 60 | costs | float8 | SQL的计划的代价 |
| 61 | internal_query | text | 导致错误的内部查询，内部查询是一个存储过程或函数中的一个语句 |
| 62 | internal_query_pos | int | 内部查询中导致错误的字符位置 |
| 63 | objects | text | 对象涉及日志记录 |
| 64 | parameters | text | 语句参数 |
| 65 | location | text | 源代码中错误的位置（如果log_error_verbosity=VERBOSE时才会指定） |
| 66 | application_name | text | session级别的应用名称 |
| 67 | query_step | text | 标识扩展查询的阶段：prepare/parse/bind/execute/，非扩展查询标记为None |
| 68 | backend_type | text | 标识后端类型,类型包括如下：autovacuum launcher/autovacuum |
| worker/client backend/background worker/background | |||
| writer/checkpointer/startup/walreceiver/walsender/ | |||
| walwriter/client backend(mpp executor)/client | |||
| backend(mpp follower) | |||
| 69 | sql_log_id | text | sql命令在集群中的唯一标识，格式如下：实例启动时间(长整形数字)-集群sessionid(sc_s |
| ession_id)-session的命令序列号(sc_command_count) | |||
| 70 | query_level | text | 查询层次,函数中的查询此字段会大于0 |
| 71 | log_flags | text | 日志标记，可以多个以空格分隔，当前支持标记：AUDIT(Objects列为审计日志),OBJECT_CLIP（objects列对象有截断) |
| 72 | tag_extension | text | 占位标签，此标签后面为扩展显示内容.当前扩展内容用户登出时使用 |
| 73 | msg_extra0 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 74 | msg_extra1 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 75 | msg_extra2 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 76 | msg_extra3 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 77 | msg_extra4 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 78 | msg_extra5 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 79 | msg_extra6 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 80 | msg_extra7 | text | 当log_type = T_LOGIN/T_LOGOUT时 |
| 使用三个字段msg_0(用户名),msg_1(数据库名),msg_2(应用名) | |||
| 81 | global_detail | text | 预留字段 |
| 82 | session_detail | text | 预留字段 |
| 83 | stat_detail | text | log_exetended_class必须包含“SYSINFO”时， |
| 且此条日志级别大于warning, 此列才会输出， | |||
| 输出系统状态信息且以json字符串的形式显示:包括进程数，内存和swap的当前状态 | |||
| 84 | query_detail | text | 预留字段 |
以下配置参数控制日志内容(详细的参数说明参见参考手册)
log_min_messages: 设置日志记录的最低消息级别，如 ERROR 或 WARNING。log_statements: 记录那些SQL语句被记录log_min_error_statement: 控制在从那个消息级别开始打印SQL语句被记录。log_min_duration_statement: 记录执行时间超过指定毫秒数的 SQL 语句。log_connections: 记录客户端连接事件。log_disconnections: 记录会话断开事件。log_duration: 开启此参数会记录每个语句的执行时间sdlog_classes: 设置日志记录的类别范围，例如 'ALL' 代表记录所有类别，可选项包括对象、资源、系统信息等。sdlog_sysinfo_min_messages: 定义记录系统信息的最低日志级别，如 'warning'，当日志级别达到或高于此设置时记录系统信息。object_log:指定记录那些操作。object_internal_object: 开启时，记录不常见系统内部对象，例如类型，约束等，关闭时只记录常用用户对象：例如数据库，表，函数等object_log_substatement: 开启时，记录子语句和子语句序号，并以子语句为分类记录对象。关闭时，只记录对象和其操作。object_substatement_size: 记录子语句时，子语句最长字节限制，如果超过此值，则会按照字符集截断到此参数配置。object_max_substatements: 记录最大子语句数，一条SQL涉及的子语句大于此值时，大于的子语句和涉及的对象将不记录。object_log_catalog: 设置是否记录对系统目录的访问object_log_relation:是否记录SELECT 或 DML 语句中引用的每个关系（如表、视图等）。object_log_parameter: 是否记录SQL查询中的参数。object_role: 授权角色，当设置此角色时，数据库对象进行SELECT或DML操作时，只有授权角色有相应权限的对象才会被记录。
日志归档功能¶
日志归档功能：以日志文件为归档单位，远程加载到审计库，调用预定义的SQL文件，执行不同的目标表归档，简单功能框图如下：
以下配置参数控制这日志内容(详细的参数说明参见参考手册)
-
sdlog_archive: 控制是否启用日志归档，通过Python脚本将日志从本地导入到远端的日志数据库中 -
sdlog_archive_conninfo: 设置日志归档时远端数据库的连接信息，如主机、端口、用户名和密码，数据库名称。 -
sdlog_archive_base_dbid: 区分每个不同的数据库的不同coordinator，例如数据库A设置当前值为1，则数据库A中的coordinator0的计算公式为sdlog_archive_base_dbid * 10000 + dbid，计算下来则为10001,此值会记录在日志中作为一列存在。 -
sdlog_archive_target_tables: 设置自行运行的SQL函数文件，这些文件保存在share/seaboxsql/sdlog_archive_config/default目录中,以xxxx.sql的形式存在，并根据用户具体的现场情况，选配以上参数，同时提供share/seaboxsql/sdlog_archive_config/custome目录,此目录下可自定义的新的功能，目录中提供了增加sdlog_test的例子: sdlog_test_init.sql用于定义表，函数等需要初始化化的内容，sdlog_test.sql则会在每个日志文件增加时需要执行的操作。修改配置sdlog_archive_target_tables参数增加sdlog_test，则就可以启动sdlog_test相关文件的执行。 -
sdlog_archive_slow_min_duration: 设置记录慢查询的最短时间阈值，只有大于等于这个时间的SQL查询才会被记录。
日志归档功能生成的表信息¶
- 默认情况下，日志归功能会在归档库中生成如下表，也可以通过sdlog_archive_target_tables参数修改生成的表.
- sdlog，sdlog_error,sdlog_session ,sdlog.slow 的表结构都是相同的，且和CSV日志列完全对应。内容参见上面描述过的日志记录内容
| 配置 | 类型 | 说明 |
|---|---|---|
| sdlog.sdlog | 物理分区表 | 日志原始记录 |
| sdlog.sdlog_slow | 物理分区表 | SQL执行时间大于等于1s(通过sdlog_archive_slow_min_duration配置)的日志记录 |
| sdlog.sdlog_error | 物理分区表 | 报错或告警的日志信息 |
| sdlog.sdlog_session | 物理分区表 | session的开始和结束日志信息 |
| sdlog.vw_audit | 视图 | 对象的审计信息，向上兼容sdaudit功能 |
| sdlog.vw_audit_event | 视图 | 审计信息，向上兼容sdaudit功能 |
日志推荐配置¶
- 涉及日志的配置参数很多, 这里针对单机和集群推荐常用配置参数
- 同时介绍日志归档功能的推荐配置参数，适用于有日志归档需求的用户
单机的配置参数修改¶
- 日志推荐配置: 修改seaboxsql.conf配置文件中如下内容,然后重起生效。
# 修改以下配置参数 log_connections = on log_disconnections = on log_destination = 'csvlog' logging_collector = on # 日志记录中的资源列依赖以下这个配置参数，所以必须配置其为ON sd_stat_activity_enable = on # 每条SQl全量信息依赖log_duration=on配置参数项，所以必须设置为ON log_duration = on - 日志归档推荐配置: 修改seaboxsql.conf配置文件中如下内容。然后重起生效
# 开启日志归档功能
sdlog_archive = on
# 归档库的连接字符串，
# host=IP:PORT 远端归档库IP地址和监听端口(确保远端归档库监听所有端口： listen_addresses="*"）
# init_user=superuser/sd_dba 初始化远端归档库用户，需要superuser权限的用户，或者sd_dba用户，此用户默认会设置sd_sao_etl有写入和修改归档库所有表权限，sd_sao为读归档库内所有表权限，sd_sao_owner为所有归档库内对象的所有者。
# 也可以通过user_sao_etl,user_sao，user_sao_owner三个参数个性化的指定用户
# 确保归档库中的sd_hba.conf中增加sd_sao_etl,初始化远端归档库用户的开放访问权限,使用trust认证方式 (sd_dba用户用来创建sdlog schema) 以下是建议修改的例子
#host audit sd_sao_etl 192.168.0.7/32 trust
#host audit sd_dba 192.168.0.7/32 trust
#dbname : 归档库名称(需要提前在数据库中创建)
sdlog_archive_conninfo='host=192.168.0.7:16666 init_user=superuser/sd_dba dbname=audit'
# 区分不同数据库的不同coordinator实例，多集群日志归档到一个远端数据库时，每个集群需要配置不同数值
sdlog_archive_base_dbid=10000
集群配置信息¶
- 日志推荐配置： 通过执行以下配置命令
seaboxconfig -c sd_stat_activity_enable -v on
seaboxconfig -c log_connections -v on
seaboxconfig -c log_disconnections -v on
seaboxconfig -c log_duration -v on
seaboxconfig -c logging_collector -v on
seaboxconfig -c log_destination -v 'csvlog'
seabox stop -a
seabox start -a
- 日志归档推荐配置： 通过执行以下配置命令
seaboxconfig -c sdlog_archive_conninfo -v 'host=192.168.0.7:16666 init_user=sd_dba dbname=audit'
seaboxconfig -c sdlog_archive -v on
seaboxconfig -c sdlog_archive_base_dbid -v 10000
seabox notify reload
验证配置生效¶
- 验证日志记录
-- 验证日志记录是否正常，创建简单的表t1,然后在csv日志中会记录如下一条日志
ssql -p 9700 seaboxsql
ssql (21.2.2.233)
Type "help" for help.
seaboxsql=# create table t1(c1 int);
CREATE TABLE
-- csv中记录的create table日志
2024-06-12 17:43:39.810 CST,T_QUERY,GLOBAL:,1,0,0,8388608,8388608,SESSION:,"10","cl","seaboxsql",57121,"[local]",66696dc1.df21,4,1,1000000000000025,2,-1,"CREATE TABLE",2024-06-12 17:43:29.174 CST,6/0,0,dx60000,x0,sx0,RESOURCE:,0,8388608,0,2392064,0,0,0,0,247,55,29,2,0,0,0.000,0.000,STATEMENT:,100.374,2024-06-12 17:43:39.710 CST,ok,LOG,00000,"duration: 100.338 ms",,,,0,0,6346922172813343316,"create table t1(c1 int);",,,0.00,,,,,,"ssql",none,"client backend",2bdacea967202-38d7ea4c68019-2,EXTENSION:,,,,,,,,,,,,
- 验证归档数据库记录
-- 验证日志归档是否正常，创建简单的表t2,然后手动强制切换日志文件，查看远端归档库audit.sdlog.sdlog表存在这条日志记录
ssql -p 9700 seaboxsql
ssql (21.2.2.233)
Type "help" for help.
seaboxsql=# create table t2(c1 int);
CREATE TABLE
seaboxsql=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
-- 登录归档数据库 ,过滤object 匹配t2对象，查找到日志记录
ssql -p 16666 audit
ssql (21.2.2.233)
Type "help" for help.
audit=# select * from sdlog.sdlog where objects like '%t2%';
log_time | log_type | tag_global | num_conns | num_sess_active | num_sess_queued | global_mem | sess_mem | tag_session | user_id | user_name | database_name | process_id | connection_from | session_id | session_line_num | sc_db_id | sc_session_id | sc_command_count | sc_slice | command_tag | session_start_time | virtual_transaction_id | transaction_id | sc_dist_tranx_id | local_tranx_id | sub_tranx_id | tag_resource | stat_cpu | stat_mem_peak | stat_read | stat_write | stat_tmp_write | stat_ic_recv | stat_ic_send | stat_ic_retry | stat_sblk_hit | stat_sblk_r | stat_sblk_dt | stat_sblk_w | stat_tblk_r | stat_tblk_w | stat_blk_r_time | stat_blk_w_time | tag_statement | duration | query_start_time | state | error_severity | sql_state_code | message | detail | hint | context | processed_rows | scanned_rows | query_id | query | query_pos | query_plan | costs | internal_query | internal_query_pos | objects | parameters | location | application_name | action | backend_type | sql_log_id | tag_extension | msg_extra0 | msg_extra1 | msg_extra2 | msg_extra3 | msg_extra4 | msg_extra5 | msg_extra6 | msg_extra7 | global_detail | session_detail | stat_detail | query_detail
----------------------------+----------+------------+-----------+-----------------+-----------------+------------+----------+-------------+---------+-----------+---------------+------------+-----------------+---------------+------------------+-----------+------------------+------------------+----------+--------------+----------------------------+------------------------+----------------+------------------+----------------+--------------+--------------+----------+---------------+-----------+------------+----------------+--------------+--------------+---------------+---------------+-------------+--------------+-------------+-------------+-------------+-----------------+-----------------+---------------+----------+----------------------------+-------+----------------+----------------+---------------------+--------+------+---------+----------------+--------------+---------------------+--------------------------+-----------+------------+-------+----------------+--------------------+------------------------------------------------------------+------------+----------+------------------+--------+----------------+-------------------------------+---------------+------------+------------+------------+------------+------------+------------+------------+------------+---------------+----------------+-------------+--------------
2024-06-13 11:15:38.281+08 | T_QUERY | GLOBAL: | 1 | 0 | 0 | 8388608 | 8388608 | SESSION: | 10 | cl | seaboxsql | 60702 | [local] | 666a6455.ed1e | 4 | 100000001 | 1000000000003211 | 2 | -1 | CREATE TABLE | 2024-06-13 11:15:33.181+08 | 6/0 | 0 | dx60016 | x0 | sx0 | RESOURCE: | 0 | 8388608 | 65536 | 1433600 | 0 | 0 | 0 | 0 | 291 | 0 | 19 | 0 | 0 | 0 | 0 | 0 | STATEMENT: | 84.42 | 2024-06-13 11:15:38.197+08 | ok | LOG | 00000 | duration: 84.384 ms | | | | 0 | 0 | 3664417766048368402 | create table t2(c1 int); | | | 0 | | || | | ssql | none | client backend | 2bdacea967202-38d7ea4c68c8b-2 | EXTENSION: | | | | | | | | | | | |
(1 row)
内置对象审计功能¶
- 兼容之前的sdaudit提供的审计视图，建立vw_audit和vw_audit_event。
- 日志中内置了对象审计功能，这里以例子的方式，说明几个相对重要功能，其他功能参见参考手册中参数描述即可。
以下是审计功能涉及的配置参数(详细的参数说明参见参考手册)
sdaudit_log:指定记录那些操作。sdaudit_log_substatement: 开启时，记录子语句和子语句序号，并以子语句为分类记录对象。关闭时，只记录对象和其操作。sdaudit_log_catalog: 设置是否记录对系统目录的访问sdaudit_log_relation:是否记录SELECT 或 DML 语句中引用的每个关系（如表、视图等）。sdaudit_log_parameter: 是否记录SQL查询中的参数。sdaudit_role: 审计授权角色，当设置此角色时，数据库对象进行SELECT或DML操作时，只有授权角色有相应权限的对象才会被记录
指定记录那些操作(sdaudit_log)¶
- 支持如下几种选项,同时支持补集操作,例如-read就是除去read选项
READ（读取操作，如 SELECT 和 COPY） WRITE（写入操作，如 INSERT、UPDATE、DELETE 等） FUNCTION（函数调用和 DO 块 ROLE（与角色和权限相关的语句，如 GRANT、REVOKE 等 DDL（不包括在 ROLE 类别中的所有 DDL） MISC（杂项命令，如 DISCARD、FETCH 等） MISC_SET（杂项 SET 命令，如 SET ROLE） ALL（以上所有类别）
-- 如下的例子 说明记录除了select操作的所有操作.
set sdaudit_log = 'all,-read';
drop table if exists sdlog_log_object;
NOTICE: table "sdlog_log_object" does not exist, skipping
create table sdlog_log_object (c1 int);
alter table sdlog_log_object add column c2 int;
insert into sdlog_log_object(c1) values(1);
update sdlog_log_object set c2 = 1 where c1 = 1;
delete from sdlog_log_object where c1 = 1;
select * from sdlog_log_object;
drop table sdlog_log_object;
-- 强制切换日志文件
select pg_rotate_logfile();
-- 登录远端归档库，通过vw_audit可以查询到sdlog_log_object的操作记录，但是没有select 操作。
SELECT
*
FROM (
SELECT
database_name,
json ->> 'object' AS obj_name,
json ->> 'type' AS obj_type,
json -> 'commands' AS actions,
regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g') AS query,
processed_rows,
scanned_rows,
parameters
FROM (
SELECT
database_name,
jsonb_array_elements_text(objects::jsonb)::json AS json,
parameters,
processed_rows,
scanned_rows,
query
FROM
sdlog.sdlog where command_tag !='MPPEXEC UTILITY')) b
WHERE
database_name = 'sdlog_test_db'
and obj_name like '%sdlog_log_object%'
and obj_type = 'table'
ORDER BY
database_name,
obj_name,
query;
database_name | obj_name | obj_type | actions | query | processed_rows | scanned_rows | parameters
---------------+-------------------------+----------+------------------+--------------------------------------------------+----------------+--------------+------------
sdlog_test_db | public.sdlog_log_object | table | ["ALTER TABLE"] | alter table sdlog_log_object add column c2 int; | 0 | 0 |
sdlog_test_db | public.sdlog_log_object | table | ["CREATE TABLE"] | create table sdlog_log_object (c1 int); | 0 | 0 |
sdlog_test_db | public.sdlog_log_object | table | ["DELETE"] | delete from sdlog_log_object where c1 = 1; | 1 | 0 |
sdlog_test_db | public.sdlog_log_object | table | ["DROP TABLE"] | drop table sdlog_log_object; | 0 | 0 |
sdlog_test_db | public.sdlog_log_object | table | ["INSERT"] | insert into sdlog_log_object(c1) values(1); | 1 | 0 |
sdlog_test_db | public.sdlog_log_object | table | ["UPDATE"] | update sdlog_log_object set c2 = 1 where c1 = 1; | 1 | 0 |
(6 rows)
指定记录是否记录parameter(sdaudit_log_parameter)¶
set sdaudit_log_parameter=on;
drop table if exists sdlog_log_parameter;
NOTICE: table "sdlog_log_parameter" does not exist, skipping
create table sdlog_log_parameter (c1 text);
PREPARE sdlog_stmt(varchar(10)) AS INSERT INTO sdlog_log_parameter (c1) VALUES ($1);
EXECUTE sdlog_stmt(1);
EXECUTE sdlog_stmt('1,ta",2');
EXECUTE sdlog_stmt(null);
reset sdaudit_log_parameter;
SELECT
parameters,
processed_rows,
scanned_rows,
query
FROM
sdlog.sdlog
WHERE
query like '%EXECUTE sdlog_stmt%' and query not like '% sc_toolkit.sdlog_statement%' and command_tag !='MPPEXEC UTILITY'
ORDER BY
query;
parameters | processed_rows | scanned_rows | query
------------+----------------+--------------+--------------------------------
1,ta",2 | 1 | 0 | EXECUTE sdlog_stmt('1,ta",2');
1 | 1 | 0 | EXECUTE sdlog_stmt(1);
| 1 | 0 | EXECUTE sdlog_stmt(null);
(3 rows)
记录子语句信息(sdaudit_log_substatement)¶
- 一旦开启时，记录子语句和子语句序号，并以子语句为分类记录对象。关闭时，只记录对象和其操作。
- 如下的例子是开启sdaudit_log_substatement=on后，执行一个函数，可以看到函数内部的子语句和子语句序号
sdlog_test_db=# select query,jsonb_array_elements_text(objects::jsonb)::json AS json from sdlog.sdlog where query like '%select log_test();%' and objects is not null ;
query | json
--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select log_test(); | {"type": "", "object": "", "commands": ["SELECT"], "audit_type": "SESSSION", "audit_class": "READ", "substatement": "select log_test();", "substatementid": 1}
select log_test(); | {"type": "function", "object": "public.log_test", "commands": ["EXECUTE"], "audit_type": "SESSION", "audit_class": "FUNCTION", "substatement": "select log_test();", "substatementid": 2}
select log_test(); | {"type": "table", "object": "public.t_sdlog_test", "commands": ["INSERT"], "audit_type": "SESSION", "audit_class": "WRITE", "substatement": "insert into t_sdlog_test select i from (select i, pg_sleep(0.001) from generate_series(1,500)i)", "substatementid": 3}
select log_test(); | {"type": "", "object": "", "commands": ["SELECT"], "audit_type": "SESSSION", "audit_class": "READ", "substatement": "SELECT log_test_level1()", "substatementid": 4}
select log_test(); | {"type": "function", "object": "public.log_test_level1", "commands": ["EXECUTE"], "audit_type": "SESSION", "audit_class": "FUNCTION", "substatement": "SELECT log_test_level1()", "substatementid": 5}
select log_test(); | {"type": "table", "object": "public.t_sdlog_test", "commands": ["INSERT"], "audit_type": "SESSION", "audit_class": "WRITE", "substatement": "insert into t_sdlog_test select i from (select i, pg_sleep(0.001) from generate_series(1,500)i) t", "substatementid": 6}
select log_test(); | {"type": "", "object": "", "commands": ["SELECT"], "audit_type": "SESSSION", "audit_class": "READ", "substatement": "SELECT pg_sleep(3)", "substatementid": 7}
select log_test(); | {"type": "", "object": "", "commands": ["CHECKPOINT"], "audit_type": "SESSSION", "audit_class": "MISC", "substatement": "checkpoint", "substatementid": 8}
(8 rows)
记录relation(sdaudit_log_relation)¶
- 开启时，不受sdaudit_role的限制，直接记录SELECT或DML涉及的对象，如上面例子中的object字段。
记录系统表对象sdaudit_log_catalog)¶
- 开启时，会记录pg_catalog系统schema下的对象
审计角色控制对象审计访问权限(sdaudit_role)¶
如果配置了sdaudit_role , 则只有此角色有访问权限的对象操作才会被记录。没有权限的操作不记录对象审计，只记录SQL
-- 设置涉及角色为sd_sao_etl 记录sdlog_role_sd_sao_etl的select操作涉及的对象, sdlog_role_sd_sao_etl的其他操作不记录对象审计，而sdlog_role的所有dml 和select 操作都不会记录对象审计
create table sdlog_role (c1 int);
create table sdlog_role_sd_sao_etl (c1 int);
set sdaudit_role='sd_sao_etl';
grant SELECT on sdlog_role_sd_sao_etl to sd_sao_etl;
select * from sdlog_role_sd_sao_etl;
insert into sdlog_role_sd_sao_etl values(1);
select * from sdlog_role;
insert into sdlog_role values(1);
SELECT
*
FROM (
SELECT
database_name,
json ->> 'object' AS obj_name,
json ->> 'type' AS obj_type,
json -> 'commands' AS actions,
regexp_replace(query, E'[\\n\\r\\t ]+', ' ', 'g') AS query,
processed_rows,
scanned_rows,
parameters
FROM (
SELECT
database_name,
jsonb_array_elements_text(objects::jsonb)::json AS json,
parameters,
processed_rows,
scanned_rows,
query
FROM
sdlog.sdlog where command_tag !='MPPEXEC UTILITY')) b
WHERE
((database_name = 'sdlog_test_db'
and obj_name like '%sdlog_role%' )or obj_name like '%sdlog_role_sd_sao_etl%')
and obj_type in ('table','database')
ORDER BY
database_name,
obj_name,
query;
database_name | obj_name | obj_type | actions | query | processed_rows | scanned_rows | parameters
---------------+------------------------------+----------+------------------+----------------------------------------------+----------------+--------------+------------
sdlog_test_db | public.sdlog_role | table | ["CREATE TABLE"] | create table sdlog_role (c1 int); | 0 | 0 |
sdlog_test_db | public.sdlog_role_sd_sao_etl | table | ["CREATE TABLE"] | create table sdlog_role_sd_sao_etl (c1 int); | 0 | 0 |
sdlog_test_db | public.sdlog_role_sd_sao_etl | table | ["SELECT"] | select * from sdlog_role_sd_sao_etl; | 0 | 0 |
(3 rows)
解析CSV日志工具(sdlog)¶
提供了sdlog工具把日志转换为json数组的格式，管道数据流可以接jp工具，过滤一些json的key字段。实现更灵活的分析 除了解析CSV文件，还提供了打印CSV日志格式说明(-f参数)，同时提供打印创建CSV表的DDL语句(-p),便于入库分析，可以参见如下sdlog –help 阅读使用说明，如下。
usage: sdlog [-h] [-hh] [-hf [HELP_FIELD]] [-hc] [-cc [CHECK_CONFIG]]
[--version] [--debug] [-d] [-o OUTPUT] [-c] [-cn] [-E] [-J]
[-n NUMBER] [--length LENGTH] [-i ID] [-l LIKE] [-q] [-e]
[-t TIME] [-k KEY] [-v VALUE] [-lk LIKE_KEY] [-lv LIKE_VALUE]
[-f FILTER] [-fr FILTER_RAW]
[file [file ...]]
positional arguments:
file file to be processed
optional arguments:
-h, --help show this help message and exit
-hh, --help-help show more help message and exit
-hf [HELP_FIELD], --help-field [HELP_FIELD]
show all field names, then exit; show HELP_FIELD
details(`all` means all fields)
-hc, --help-create print create table sql for sdlog, then exit
-cc [CHECK_CONFIG], --check-config [CHECK_CONFIG]
check sdlog_cols conf from SDHOME, then exit
--version output version, then exit
--debug output debug logs
output arguments:
-d, --detail detail level of output records, repeatable
-o OUTPUT, --output OUTPUT
output records with fields OUTPUT, eg.
"sql_log_id,state,duration,query"
-c, --compact output in compact mode
-cn, --chinese output chinese field names
-E, --escape enable interpretation of "\n \t"
-J, --json-expand expand json in fileds
-n NUMBER, --number NUMBER
limit the number of output rows
--length LENGTH limit the length of each field
filter arguments:
-i ID, --id ID filter records with "sql_log_id = ID"
-l LIKE, --like LIKE filter records with "any field contains LIKE",
repeatable
-q, --query filter records with "log_type = T_QUERY"
-e, --error filter records with "state = error"
-t TIME, --time TIME filter records with "duration >= TIME"
-k KEY, --key KEY filter records with "KEY = VALUE", repeatable
-v VALUE, --value VALUE
filter records with "KEY = VALUE", repeatable
-lk LIKE_KEY, --like-key LIKE_KEY
filter records with "LIKE_KEY like LIKE_VALUE",
repeatable
-lv LIKE_VALUE, --like-value LIKE_VALUE
filter records with "LIKE_KEY like LIKE_VALUE",
repeatable
-f FILTER, --filter FILTER
filter records, like python format, eg.
"[log_type]=='T_QUERY'"
-fr FILTER_RAW, --filter-raw FILTER_RAW
filter records, python format, eg.
"r['log_type']=='T_QUERY'"
sdlog log.csv -n1 -d |jq
sdlog log.csv -i xxxx-xxxx-xx -cn --len 40 |jq
sdlog log.csv -i xxxx-xxxx-xx -o query -E
sdlog log.csv -l xxxx-xxxx -d
sdlog log.csv -q -t 60000 -dd
sdlog log.csv -q -e -d
sdlog log.csv -k log_type -v T_QUERY -k user_name -v seabox -k database_name -v seaboxsql -d
sdlog log.csv -f "[log_type]=='T_QUERY' and [user_name] == 'seabox' and [database_name] == 'seaboxsql'" -d
- 以下是一个使用例子，转换一个csv文件后，选择了一个json对象数据展示如下:
sdlog ./seaboxsql-2024-06-13_000000.csv
{
"log_time": "2024-06-13 11:15:38.281 CST",
"log_type": "T_QUERY",
"tag_global": "GLOBAL:",
"num_conns": 1,
"num_sess_active": 0,
"num_sess_queued": 0,
"global_mem": 8388608,
"sess_mem": 8388608,
"tag_session": "SESSION:",
"user_id": 10,
"user_name": "cl",
"database_name": "seaboxsql",
"process_id": 60702,
"connection_from": "[local]",
"session_id": "666a6455.ed1e",
"session_line_num": 4,
"sc_db_id": 100000001,
"sc_session_id": "1000000000003211",
"sc_command_count": 2,
"sc_slice": -1,
"command_tag": "CREATE TABLE",
"session_start_time": "2024-06-13 11:15:33.181 CST",
"virtual_transaction_id": "6/0",
"transaction_id": "0",
"sc_dist_tranx_id": "dx60016",
"local_tranx_id": "x0",
"sub_tranx_id": "sx0",
"tag_resource": "RESOURCE:",
"stat_cpu": 0,
"stat_mem_peak": 8388608,
"stat_read": 65536,
"stat_write": 1433600,
"stat_tmp_write": 0,
"stat_ic_recv": 0,
"stat_ic_send": 0,
"stat_ic_retry": 0,
"stat_sblk_hit": 291,
"stat_sblk_r": 0,
"stat_sblk_dt": 19,
"stat_sblk_w": 0,
"stat_tblk_r": 0,
"stat_tblk_w": 0,
"stat_blk_r_time": 0.0,
"stat_blk_w_time": 0.0,
"tag_statement": "STATEMENT:",
"duration": 84.42,
"query_start_time": "2024-06-13 11:15:38.197 CST",
"state": "ok",
"error_severity": "LOG",
"sql_state_code": "00000",
"message": "duration: 84.384 ms",
"detail": "",
"hint": "",
"context": "",
"processed_rows": 0,
"scanned_rows": 0,
"query_id": "3664417766048368402",
"query": "create table t2(c1 int);",
"query_pos": null,
"query_plan": "",
"costs": 0.0,
"internal_query": "",
"internal_query_pos": null,
"objects": "[{\"name\":\"public.t2\",\"type\":\"table\",\"actions\":[\"CREATE\"]}]",
"parameters": "",
"location": "",
"application_name": "ssql",
"action": "none",
"backend_type": "client backend",
"sql_log_id": "2bdacea967202-38d7ea4c68c8b-2",
"tag_extension": "EXTENSION:",
"msg_extra0": "",
"msg_extra1": "",
"msg_extra2": "",
"msg_extra3": "",
"msg_extra4": "",
"msg_extra5": "",
"msg_extra6": "",
"msg_extra7": "",
"global_detail": "",
"session_detail": "",
"stat_detail": "",
"query_detail": ""
}
日志归档管理工具(sdlog_archive_admin)¶
- sdlog_archive_admin插件: 启动/停止sdlog_archive服务，查看sdlog_archive的进程状态，查看日志文件处理进度等。
- 此插件是实例级别插件，多corrdinator情况下，只能管理当前登录coordinator的sdlog_archive服务.
- 使用方法参见如下例子：
seaboxsql=# create extension plpython3u;
CREATE EXTENSION
seaboxsql=# create extension sdlog_archive_admin ;
CREATE EXTENSION
seaboxsql=# select sdlog.sdlog_archive_start();
sdlog_archive_start
---------------------
t
(1 row)
seaboxsql=# select * from sdlog.sdlog_archive_status();
-[ RECORD 1 ]--------+--------------------------------------------------------
sc_db_id | 1
pid | 58351
status | running
conninfo | host=192.168.0.7:16666 user=sd_sao_etl dbname=seaboxsql
current_archive_file | seaboxsql-2024-04-17_135825.csv
current_server_file | seaboxsql-2024-04-17_140013.csv
seaboxsql=# select * from sdlog.sdlog_archive_stop();
-[ RECORD 1 ]------+--
sdlog_archive_stop | t
seaboxsql=# select * from sdlog.sdlog_archive_status();
-[ RECORD 1 ]--------+--------------------------------------------------------
sc_db_id | 1
pid | 58351
status | down
conninfo | host=192.168.0.7:16666 user=sd_sao_etl dbname=seaboxsql
current_archive_file | seaboxsql-2024-04-17_135825.csv
current_server_file | seaboxsql-2024-04-17_140013.csv
日志举例¶
按照类别选取一些日志行, 以及日志归档后表中的查询结果.通过对照更好的理解日志记录内容
服务启动日志(T_START 或者T_SHUTDOWN)¶
2024-06-12 17:38:41.220 CST,T_START,GLOBAL:,0,0,0,0,,SESSION:,"0",,,62782,,66696ca1.f53e,14,1,0,0,-1,,2024-06-12 17:38:41.112 CST,,0,dx0,x0,sx0,RESOURCE:,,,,,,,,,,,,,,,,,STATEMENT:,,,ok,LOG,00000,"database system is ready to accept connections",,,,,,,,,,0.00,,,,,,"",none,,2bdacea967202-0-0,EXTENSION:,,,,,,,,,,,
2024-06-13 15:02:49.568 CST,T_SHUTDOWN,GLOBAL:,,0,0,,,SESSION:,"0",,,62782,,66696ca1.f53e,62,100000001,0,0,-1,,2024-06-12 17:38:41.112 CST,,0,dx0,x0,sx0,RESOURCE:,,,,,,,,,,,,,,,,,STATEMENT:,,,ok,LOG,00000,"SeaboxSQL exit(1)",,,,,,,,,,0.00,,,,,,"",none,,2bdacea967202-0-0,EXTENSION:,,,,,,,,,,,,
audit=# select * from sdlog.sdlog where log_type like '%T_START%' limit 1;
-[ RECORD 1 ]----------+-----------------------------------------------
log_time | 2024-06-12 17:38:41.22+08
log_type | T_START
tag_global | GLOBAL:
num_conns | 0
num_sess_active | 0
num_sess_queued | 0
global_mem | 0
sess_mem |
tag_session | SESSION:
user_id | 0
user_name |
database_name |
process_id | 62782
connection_from |
session_id | 66696ca1.f53e
session_line_num | 14
sc_db_id | 1
sc_session_id | 0
sc_command_count | 0
sc_slice | -1
command_tag |
session_start_time | 2024-06-12 17:38:41.112+08
virtual_transaction_id |
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx0
tag_resource | RESOURCE:
stat_cpu |
stat_mem_peak |
stat_read |
stat_write |
stat_tmp_write |
stat_ic_recv |
stat_ic_send |
stat_ic_retry |
stat_sblk_hit |
stat_sblk_r |
stat_sblk_dt |
stat_sblk_w |
stat_tblk_r |
stat_tblk_w |
stat_blk_r_time |
stat_blk_w_time |
tag_statement | STATEMENT:
duration |
query_start_time |
state | ok
error_severity | LOG
sql_state_code | 00000
message | database system is ready to accept connections
detail |
hint |
context |
processed_rows |
scanned_rows |
query_id |
query |
query_pos |
query_plan |
costs | 0
internal_query |
internal_query_pos |
objects |
parameters |
location |
application_name |
action | none
backend_type |
sql_log_id | 2bdacea967202-0-0
tag_extension | EXTENSION:
msg_extra0 |
msg_extra1 |
msg_extra2 |
msg_extra3 |
msg_extra4 |
msg_extra5 |
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail |
query_detail |
audit=# select * from sdlog.sdlog where log_type like '%T_SHUTDOWN%' limit 1;
-[ RECORD 1 ]----------+---------------------------
log_time | 2024-06-13 15:02:49.568+08
log_type | T_SHUTDOWN
tag_global | GLOBAL:
num_conns |
num_sess_active | 0
num_sess_queued | 0
global_mem |
sess_mem |
tag_session | SESSION:
user_id | 0
user_name |
database_name |
process_id | 62782
connection_from |
session_id | 66696ca1.f53e
session_line_num | 62
sc_db_id | 100000001
sc_session_id | 0
sc_command_count | 0
sc_slice | -1
command_tag |
session_start_time | 2024-06-12 17:38:41.112+08
virtual_transaction_id |
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx0
tag_resource | RESOURCE:
stat_cpu |
stat_mem_peak |
stat_read |
stat_write |
stat_tmp_write |
stat_ic_recv |
stat_ic_send |
stat_ic_retry |
stat_sblk_hit |
stat_sblk_r |
stat_sblk_dt |
stat_sblk_w |
stat_tblk_r |
stat_tblk_w |
stat_blk_r_time |
stat_blk_w_time |
tag_statement | STATEMENT:
duration |
query_start_time |
state | ok
error_severity | LOG
sql_state_code | 00000
message | SeaboxSQL exit(1)
detail |
hint |
context |
processed_rows |
scanned_rows |
query_id |
query |
query_pos |
query_plan |
costs | 0
internal_query |
internal_query_pos |
objects |
parameters |
location |
application_name |
action | none
backend_type |
sql_log_id | 2bdacea967202-0-0
tag_extension | EXTENSION:
msg_extra0 |
msg_extra1 |
msg_extra2 |
msg_extra3 |
msg_extra4 |
msg_extra5 |
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail |
query_detail |
T_LOGIN和T_LOGOUT(登录信息）必须配置log_connections=on 和log_disconnections=on¶
2024-06-12 17:38:46.957 CST,T_LOGIN,GLOBAL:,1,0,0,0,0,SESSION:,"10","cl","seaboxsql",64020,"10.10.0.7:37900",66696ca6.fa14,2,1,2000000000000003,0,-1,"authentication",2024-06-12 17:38:46.955 CST,4/1,0,dx0,x0,sx1,RESOURCE:,,,,,,,,,,,,,,,,,STATEMENT:,,,ok,LOG,00000,"connection authorized: user=cl database=seaboxsql",,,,0,0,,,,,0.00,,,,,,"",none,"autovacuum launcher",2bdacea967208-71afd498d0003-0,EXTENSION:,"cl","seaboxsql","(null)","10.10.0.7","37900",,,,,,,
2024-06-12 19:11:55.078 CST,T_LOGOUT,GLOBAL:,1,0,0,0,,SESSION:,"10","cl","seaboxsql",28083,"10.10.0.7:58908",6669827b.6db3,6,1,2000000000000003,0,-1,"authentication",2024-06-12 19:11:55.063 CST,,0,dx0,x0,sx0,RESOURCE:,,,,,,,,,,,,,,,,,STATEMENT:,,,ok,LOG,00000,"disconnection: session time: 0:00:00.015 user=cl database=seaboxsql host=10.10.0.7 port=58908",,,,0,0,,,,,0.00,,,,,,"",none,"client backend(mpp follower)",2bdacea967208-71afd498d0003-0,EXTENSION:,"cl","seaboxsql","(null)","10.10.0.7","58908","15.304",,,,,
audit=# select * from sdlog.sdlog where log_type like '%T_LOGIN%' limit 1;
-[ RECORD 1 ]----------+--------------------------------------------------
log_time | 2024-06-12 17:38:46.957+08
log_type | T_LOGIN
tag_global | GLOBAL:
num_conns | 1
num_sess_active | 0
num_sess_queued | 0
global_mem | 0
sess_mem | 0
tag_session | SESSION:
user_id | 10
user_name | cl
database_name | seaboxsql
process_id | 64020
connection_from | 10.10.0.7:37900
session_id | 66696ca6.fa14
session_line_num | 2
sc_db_id | 1
sc_session_id | 2000000000000003
sc_command_count | 0
sc_slice | -1
command_tag | authentication
session_start_time | 2024-06-12 17:38:46.955+08
virtual_transaction_id | 4/1
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx1
tag_resource | RESOURCE:
stat_cpu |
stat_mem_peak |
stat_read |
stat_write |
stat_tmp_write |
stat_ic_recv |
stat_ic_send |
stat_ic_retry |
stat_sblk_hit |
stat_sblk_r |
stat_sblk_dt |
stat_sblk_w |
stat_tblk_r |
stat_tblk_w |
stat_blk_r_time |
stat_blk_w_time |
tag_statement | STATEMENT:
duration |
query_start_time |
state | ok
error_severity | LOG
sql_state_code | 00000
message | connection authorized: user=cl database=seaboxsql
detail |
hint |
context |
processed_rows | 0
scanned_rows | 0
query_id |
query |
query_pos |
query_plan |
costs | 0
internal_query |
internal_query_pos |
objects |
parameters |
location |
application_name |
action | none
backend_type | autovacuum launcher
sql_log_id | 2bdacea967208-71afd498d0003-0
tag_extension | EXTENSION:
msg_extra0 | cl
msg_extra1 | seaboxsql
msg_extra2 | (null)
msg_extra3 | 10.10.0.7
msg_extra4 | 37900
msg_extra5 |
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail |
query_detail |
audit=# select * from sdlog.sdlog where log_type like '%T_LOGOUT%' limit 1;
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------
log_time | 2024-06-12 19:11:55.078+08
log_type | T_LOGOUT
tag_global | GLOBAL:
num_conns | 1
num_sess_active | 0
num_sess_queued | 0
global_mem | 0
sess_mem |
tag_session | SESSION:
user_id | 10
user_name | cl
database_name | seaboxsql
process_id | 28083
connection_from | 10.10.0.7:58908
session_id | 6669827b.6db3
session_line_num | 6
sc_db_id | 1
sc_session_id | 2000000000000003
sc_command_count | 0
sc_slice | -1
command_tag | authentication
session_start_time | 2024-06-12 19:11:55.063+08
virtual_transaction_id |
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx0
tag_resource | RESOURCE:
stat_cpu |
stat_mem_peak |
stat_read |
stat_write |
stat_tmp_write |
stat_ic_recv |
stat_ic_send |
stat_ic_retry |
stat_sblk_hit |
stat_sblk_r |
stat_sblk_dt |
stat_sblk_w |
stat_tblk_r |
stat_tblk_w |
stat_blk_r_time |
stat_blk_w_time |
tag_statement | STATEMENT:
duration |
query_start_time |
state | ok
error_severity | LOG
sql_state_code | 00000
message | disconnection: session time: 0:00:00.015 user=cl database=seaboxsql host=10.10.0.7 port=58908
detail |
hint |
context |
processed_rows | 0
scanned_rows | 0
query_id |
query |
query_pos |
query_plan |
costs | 0
internal_query |
internal_query_pos |
objects |
parameters |
location |
application_name |
action | none
backend_type | client backend(mpp follower)
sql_log_id | 2bdacea967208-71afd498d0003-0
tag_extension | EXTENSION:
msg_extra0 | cl
msg_extra1 | seaboxsql
msg_extra2 | (null)
msg_extra3 | 10.10.0.7
msg_extra4 | 58908
msg_extra5 | 15.304
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail |
query_detail |
SQL结束语句全局信息(T_QUERY）必须配置log_duration参数¶
2024-06-13 16:08:34.286 CST,T_QUERY,GLOBAL:,1,0,0,8388608,8388608,SESSION:,"10","cl","seaboxsql",27368,"[local]",666aa8ba.6ae8,46,100000001,1000000000000016,65,0,"SELECT",2024-06-13 16:07:22.967 CST,6/0,0,dx0,x0,sx0,RESOURCE:,418,16777216,131072,0,0,2764312,2723464,0,84,1,0,0,0,0,0.000,0.000,STATEMENT:,127.182,2024-06-13 16:08:34.159 CST,ok,LOG,00000,"duration: 127.153 ms",,,,0,1,1407888400672571593,"select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from
(
select
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
from
customer
where
substring(c_phone from 1 for 2) in
('22', '23', '12', '19', '26', '28', '16')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone from 1 for 2) in
('22', '23', '12', '19', '26', '28', '16')
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode
LIMIT 1;",,,240.81,,,"[{""name"":""public.orders"",""type"":""table"",""actions"":[""SELECT""]},{""name"":""public.customer"",""type"":""table"",""actions"":[""SELECT""]}]",,,"ssql",none,"client backend",2bdbfbe38953b-38d7ea4c68010-41,EXTENSION:,,,,,,,,,,,,
audit=# select * from sdlog.sdlog where query like '%substring%' and query_id = '1407888400672571593';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------
log_time | 2024-06-13 16:08:34.286+08
log_type | T_QUERY
tag_global | GLOBAL:
num_conns | 1
num_sess_active | 0
num_sess_queued | 0
global_mem | 8388608
sess_mem | 8388608
tag_session | SESSION:
user_id | 10
user_name | cl
database_name | seaboxsql
process_id | 27368
connection_from | [local]
session_id | 666aa8ba.6ae8
session_line_num | 46
sc_db_id | 100000001
sc_session_id | 1000000000000016
sc_command_count | 65
sc_slice | 0
command_tag | SELECT
session_start_time | 2024-06-13 16:07:22.967+08
virtual_transaction_id | 6/0
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx0
tag_resource | RESOURCE:
stat_cpu | 418
stat_mem_peak | 16777216
stat_read | 131072
stat_write | 0
stat_tmp_write | 0
stat_ic_recv | 2764312
stat_ic_send | 2723464
stat_ic_retry | 0
stat_sblk_hit | 84
stat_sblk_r | 1
stat_sblk_dt | 0
stat_sblk_w | 0
stat_tblk_r | 0
stat_tblk_w | 0
stat_blk_r_time | 0
stat_blk_w_time | 0
tag_statement | STATEMENT:
duration | 127.182
query_start_time | 2024-06-13 16:08:34.159+08
state | ok
error_severity | LOG
sql_state_code | 00000
message | duration: 127.153 ms
detail |
hint |
context |
processed_rows | 0
scanned_rows | 1
query_id | 1407888400672571593
query | select +
| cntrycode, +
| count(*) as numcust, +
| sum(c_acctbal) as totacctbal +
| from +
| ( +
| select +
| substring(c_phone from 1 for 2) as cntrycode, +
| c_acctbal +
| from +
| customer +
| where +
| substring(c_phone from 1 for 2) in +
| ('22', '23', '12', '19', '26', '28', '16') +
| and c_acctbal > ( +
| select +
| avg(c_acctbal) +
| from +
| customer +
| where +
| c_acctbal > 0.00 +
| and substring(c_phone from 1 for 2) in +
| ('22', '23', '12', '19', '26', '28', '16') +
| ) +
| and not exists ( +
| select +
| * +
| from +
| orders +
| where +
| o_custkey = c_custkey +
| ) +
| ) as custsale +
| group by +
| cntrycode +
| order by +
| cntrycode +
| LIMIT 1;
query_pos |
query_plan |
costs | 240.81
internal_query |
internal_query_pos |
objects | [{"name":"public.orders","type":"table","actions":["SELECT"]},{"name":"public.customer","type":"table","actions":["SELECT"]}]
parameters |
location |
application_name | ssql
action | none
backend_type | client backend
sql_log_id | 2bdbfbe38953b-38d7ea4c68010-41
tag_extension | EXTENSION:
msg_extra0 |
msg_extra1 |
msg_extra2 |
msg_extra3 |
msg_extra4 |
msg_extra5 |
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail |
query_detail |
错误信息¶
2024-06-14 09:27:53.387 CST,T_QUERY,GLOBAL:,2,1,0,16777216,8388608,SESSION:,"10","cl","seaboxsql",43271,"[local]",666b9c34.a907,7,100000001,1000000000003129,4,-1,"CREATE TABLE",2024-06-14 09:26:12.946 CST,7/6227,0,dx0,x0,sx1,RESOURCE:,0,8388608,0,0,0,0,0,0,0,0,0,0,0,0,0.000,0.000,STATEMENT:,4.582,2024-06-14 09:27:53.382 CST,error,ERROR,42P07,"relation ""t2"" already exists",,,,0,0,3664417766048368402,"create table t2(c1 int);",,,0.00,,,,,,"ssql",none,"client backend",2bdbfbe38953b-38d7ea4c68c39-4,EXTENSION:,,,,,,,,,,,"{""procs"":20974,""totalram"":270205358080,""freeram"":99954712576,""sharedram"":0,""bufferram"":668188672,""totalswap"":79187406848,""freeswap"":41995218944}",
audit=# select * from sdlog.sdlog_error where error_severity = 'ERROR' and message like '%exists%' limit 1;
-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------
log_time | 2024-06-14 09:27:53.387+08
log_type | T_QUERY
tag_global | GLOBAL:
num_conns | 2
num_sess_active | 1
num_sess_queued | 0
global_mem | 16777216
sess_mem | 8388608
tag_session | SESSION:
user_id | 10
user_name | cl
database_name | seaboxsql
process_id | 43271
connection_from | [local]
session_id | 666b9c34.a907
session_line_num | 7
sc_db_id | 100000001
sc_session_id | 1000000000003129
sc_command_count | 4
sc_slice | -1
command_tag | CREATE TABLE
session_start_time | 2024-06-14 09:26:12.946+08
virtual_transaction_id | 7/6227
transaction_id | 0
sc_dist_tranx_id | dx0
local_tranx_id | x0
sub_tranx_id | sx1
tag_resource | RESOURCE:
stat_cpu | 0
stat_mem_peak | 8388608
stat_read | 0
stat_write | 0
stat_tmp_write | 0
stat_ic_recv | 0
stat_ic_send | 0
stat_ic_retry | 0
stat_sblk_hit | 0
stat_sblk_r | 0
stat_sblk_dt | 0
stat_sblk_w | 0
stat_tblk_r | 0
stat_tblk_w | 0
stat_blk_r_time | 0
stat_blk_w_time | 0
tag_statement | STATEMENT:
duration | 4.582
query_start_time | 2024-06-14 09:27:53.382+08
state | error
error_severity | ERROR
sql_state_code | 42P07
message | relation "t2" already exists
detail |
hint |
context |
processed_rows | 0
scanned_rows | 0
query_id | 3664417766048368402
query | create table t2(c1 int);
query_pos |
query_plan |
costs | 0
internal_query |
internal_query_pos |
objects |
parameters |
location |
application_name | ssql
action | none
backend_type | client backend
sql_log_id | 2bdbfbe38953b-38d7ea4c68c39-4
tag_extension | EXTENSION:
msg_extra0 |
msg_extra1 |
msg_extra2 |
msg_extra3 |
msg_extra4 |
msg_extra5 |
msg_extra6 |
msg_extra7 |
global_detail |
session_detail |
stat_detail | {"procs":20974,"totalram":270205358080,"freeram":99954712576,"sharedram":0,"bufferram":668188672,"totalswap":79187406848,"freeswap":41995218944}
query_detail |