KingbaseE数据库运维管理案例---sys_log日志管理
KingbaseES有三种日志：
sys_wal（WAL 日志，即重做日志） 内容一般不具有可读性强制开启
sys_log（数据库运行日志） 内容可读 默认关闭的，需要设置参数启动
sys_clog（事务提交日志，记录的是事务的元数据） 内容一般不具有可读性 强制开启
KingbaseES运行日志可以实现日志输出记录，默认是没有启动记录。这个日志一般是记录服务器与DB的状态，比如各种Error信息，定位慢查询SQL，数据库的启动关闭信息，发生checkpoint过于频繁等的告警信息，诸如此类。
KingbaseES常用日志参数
log_line_prefix参数说明：
%a = application name 应用名称
%u = user name 用户名称
%d = database name 数据库名称
%r = remote host and port 远程主机与端口
%h = remote host 远程主机
%p = process ID 进程号
%t = timestamp without milliseconds 时间戳格式
%m = timestamp with millisecond 时间戳格式
%n = timestamp with milliseconds (as a Unix epoch) 时间戳格式
%i = command tag 命令标签
%e = SQL state SQL语句状态
日志设置方式
启动pg_log配置与日志参数
ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_directory = 'sys_log';
ALTER SYSTEM SET log_filename = 'kingbase-%Y-%m-%d_%H%M%S.log';
ALTER SYSTEM SET log_rotation_age = '7d';
ALTER SYSTEM SET log_rotation_size = '100MB';
ALTER SYSTEM SET log_min_messages = warning;
记录执行慢的SQL语句
ALTER SYSTEM SET log_min_duration_statement = 3000;
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_duration = off;
ALTER SYSTEM SET log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db = %d,remote = %r app = %a';
监控数据库中长时间的锁
ALTER SYSTEM SET log_lock_waits = on;
记录DDL操作
ALTER SYSTEM SET log_statement = 'ddl';
参数查询
select name,setting,short_desc from sys_settings
where name like 'log_%';
重启服务&Reload参数
// 重启服务
sys_ctl restart -m fast -D /kbdata/c8r6/data
// Reload参数
select sys_reload_conf();
CSV日志导入到数据库分析
# 创建数据表
CREATE TABLE sys_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
PRIMARY KEY (session_id, session_line_num)
);
prod=# \d sys_log
Table "public.sys_log"
Column | Type | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------
log_time | timestamp(3) with time zone | | |
user_name | text | | |
database_name | text | | |
process_id | integer | | |
connection_from | text | | |
session_id | text | | not null |
session_line_num | bigint | | not null |
command_tag | text | | |
session_start_time | timestamp with time zone | | |
virtual_transaction_id | text | | |
transaction_id | bigint | | |
error_severity | text | | |
sql_state_code | text | | |
message | text | | |
detail | text | | |
hint | text | | |
internal_query | text | | |
internal_query_pos | integer | | |
context | text | | |
query | text | | |
query_pos | integer | | |
location | text | | |
application_name | text | | |
Indexes:
"sys_log_pkey" PRIMARY KEY, btree (session_id, session_line_num)
:# CSV日志加载
\copy sys_log from ‘<CSV日志路径>’ with csv;
prod=# \copy sys_log from '/home/kingbase/cluster/R6C8/HAC8/kingbase/data/sys_log/kingbase-2024-07-11_103134.csv' with csv;
COPY 7
# 数据检索测试
// csv日志区间范围
prod=# select min(log_time),max(log_time) from sys_log;
min(log_time) | max(log_time)
-------------------------------+----------------------------
2024-07-12 00:31:34.513000+08 | 2024-07-12 00:34:49.281+08
(1 row)
// 模糊检索字段信息
prod=# select * from sys_log;
log_time | user_name | database_name | process_id | connection_from | session_
id | session_line_num | command_tag | session_start_time | virtual_transaction_id | tra
nsaction_id | error_severity | sql_state_code | message
| detail | hint
| internal_query | internal_query_pos | context | query | query_pos | l
ocation | application_name
----------------------------+-----------+---------------+------------+---------------------+----------
-----+------------------+----------------------+------------------------+------------------------+----
------------+----------------+----------------+-------------------------------------------------------
--------------------------------------+--------+------------------------------------------------------
--+----------------+--------------------+---------+------------------------------------+-----------+--
--------+------------------
2024-07-12 00:31:34.513+08 | | | 13484 | | 668f4406.
34ac | 1 | | 2024-07-12 00:31:34+08 | |
0 | LOG | 00000 | ending log output to stderr
| | Future log output will go to log destination "csvlog"
. | | | | | |
|
2024-07-12 00:31:34.517+08 | | | 13486 | | 668f4406.
34ae | 1 | | 2024-07-12 00:31:34+08 | |
0 | LOG | 00000 | database system was shut down at 2024-07-11 10:31:33 C
ST | |
| | | | | |
|
2024-07-12 00:31:34.523+08 | | | 13484 | | 668f4406.
34ac | 2 | | 2024-07-12 00:31:34+08 | |
0 | LOG | 00000 | database system is ready to accept connections
| |
| | | | | |
|
2024-07-12 00:31:40.960+08 | esrep | | 13641 | 192.168.1.202:58744 | 668f440c.
3549 | 1 | idle | 2024-07-12 00:31:40+08 | 4/0 |
0 | LOG | 00000 | received replication command: IDENTIFY_SYSTEM
| |
| | | | | |
| node2
2024-07-12 00:31:40.960+08 | esrep | | 13641 | 192.168.1.202:58744 | 668f440c.
3549 | 2 | idle | 2024-07-12 00:31:40+08 | 4/0 |
0 | LOG | 00000 | received replication command: START_REPLICATION SLOT "
repmgr_slot_2" 2/A2000000 TIMELINE 49 | |
| | | | | |
| node2
2024-07-12 00:31:41.039+08 | esrep | | 13641 | 192.168.1.202:58744 | 668f440c.
3549 | 3 | streaming 2/A30000A0 | 2024-07-12 00:31:40+08 | 4/0 |
0 | LOG | 00000 | standby "node2" is now a synchronous standby with prio
rity 1 | |
| | | | | |
| node2
2024-07-12 00:34:49.281+08 | system | prod | 14785 | [local] | 668f4433.
39c1 | 1 | COPY | 2024-07-12 00:32:19+08 | 8/74 |
0 | ERROR | 42P01 | relation "sys_lof" does not exist
| |
| | | | COPY sys_lof FROM STDIN with csv; | |
| ksql
(7 rows)
prod=# select log_time,database_name,user_name,application_name,message from sys_log
prod-# where message like '%duration%';
log_time | database_name | user_name | application_name | message
----------+---------------+-----------+------------------+---------
(0 rows)
prod=# select log_time,database_name,user_name,application_name,message from sys_log
where message like '%database%';
log_time | database_name | user_name | application_name | m
essage
----------------------------+---------------+-----------+------------------+--------------------------
--------------------------------
2024-07-12 00:31:34.517+08 | | | | database system was shut
down at 2024-07-11 10:31:33 CST
2024-07-12 00:31:34.523+08 | | | | database system is ready
to accept connections
(2 rows)
SQL统计信息
sys_stat_statements统计了SQL的很多信息，方便我们分析SQL的性能。
# 参数配置
alter system set shared_preload_libraries = sys_stat_statements;
# 重启服务&Reload参数
sys_ctl restart -m fast -D /kbdata/11.2/data
# 创建扩展表
CREATE EXTENSION sys_stat_statements;
# 查询TOP10
SELECT query, calls, total_parse_time+total_plan_time+total_exec_time total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM sys_stat_statements
ORDER BY average DESC LIMIT 10;
prod=# SELECT query, calls, total_parse_time+total_plan_time+total_exec_time total_time,
prod-# (total_time/calls) as average ,rows,
prod-# 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0)
prod-# AS hit_percent
prod-# FROM sys_stat_statements
prod-# ORDER BY average DESC LIMIT 10;
query | calls | total_time | average | rows | hit_percent
-------+-------+------------+---------+------+-------------
(0 rows)
※ 统计结果一直都在，重启也不会清零。通过如下命令可以手工清零。
select sys_stat_statements_reset() ;
prod=# select sys_stat_statements_reset() ;
sys_stat_statements_reset()
-----------------------------
(1 row)
日志保留周期
通常我们会对日志进行定期保留以保证不会撑爆磁盘容量，此时需要考虑日志保留周期。
核心参数：
每小时一个文件，保留一天：
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'kingbase-%H.log'
log_truncate_on_rotation = on
log_rotation_age = 1hour
log_rotation_size = 100MB
每天一个文件，保留一个月：
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'kingbase-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1day
log_rotation_size = 100MB
每个月一个文件，保留一年：
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'kingbase-%m.log'
log_truncate_on_rotation = on
log_rotation_age = 1month
log_rotation_size = 100MB
每天一个文件，保留一年：
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'kingbase-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1day
log_rotation_size = 100MB
分类:
KingbaseES
标签:
kingbaseES
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
2023-07-11 KingbaseES V8R6集群运维案例之---备节点恢复为单实例库