人大金仓学习之四-kmonitor
背景
kmonitor 其实时一个很好的工具和思路
开元的软件封装一下, 减轻技术复杂度,提高部署效率
并且能够更加快速的定位解决问题. 能够极大的提升客户体验.
并且界面比较好看, 比kwr 看起来高大上多了.
不仅可以解决部分实际问题, 也可以提高产品的产品力
下载地址
需要注意 这个组件官方没有下载地址,
需要联系原厂, 获取对应的安装截止.
截止里面其实包含一套 grafana的安装和默认配置
包含一套java写的 kingbase_exporter
以及默认安装的一套prometheus
外加一个node_exporter
需要注意, 这个安装介质时区分架构和平台的.
部署文档
2.1 创建kmonitor用户
使⽤system⽤户登陆监控⽬标数据库，执⾏scripts/KmonitorUserInit.sql中语句，创建kmonitor⽤户并进⾏授权
-- 创建监控用户
CREATE USER kmonitor with password 'kmonitor';
-- 添加用户标注
COMMENT ON ROLE kmonitor IS 'kingbase KMonitor user';
-- 授权监控用户
GRANT pg_monitor TO kmonitor; -- V008R006
GRANT SYS_SIGNAL_BACKEND TO kmonitor; -- V008R003
-- 授权监控用户使用kwr扩展
GRANT USAGE ON SCHEMA perf TO kmonitor;
如未开启kwr插件或不支持kwr插件版本执行以下语句创建perf schema:
CREATE SCHEMA IF NOT EXISTS perf;
Oracle模式下创建tps和qps的SQL
注：oracle模式执行
-- V008R006(oracle mode)
create or replace function perf.tps()
returns int as $$
declare
v1 int8;
v2 int8;
v3 int8;
begin
select sum(xact_commit + xact_rollback) from sys_stat_database into v1;
perform sys_sleep(1);
perform sys_stat_clear_snapshot();
select sum(xact_commit + xact_rollback) from sys_stat_database into v2;
select v2-v1 into v3;
return v3;
end;
$$ language plpgsql;
create or replace function perf.qps()
returns int as $$
declare
v1 int8;
v2 int8;
v3 int8;
begin
select sum(calls) from sys_stat_msgaccum
where message = 'Simple Query' or message = 'Execute' into v1;
perform sys_sleep(1);
perform sys_stat_clear_snapshot();
select sum(calls) from sys_stat_msgaccum
where message = 'Simple Query' or message = 'Execute' into v2;
select v2-v1 into v3;
return v3;
end;
$$ language plpgsql;
修改配置文件
shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr, sys_squeeze, src_restrict'
sys_stat_statements.track='all'
# 这里面再添加几个kwr需要使用的参数, 可以一并使用
track_activities = on
track_sql = on
track_instance = on
track_wait_timing = on
track_counts = on
track_io_timing = on
track_functions = 'all'
sys_stat_statements.track = 'top'
sys_stat_statements.track_parse = on
sys_stat_statements.track_plan = on
sys_kwr.enable = on
sys_kwr.history_days = 14
sys_kwr.interval = 30
可以用来生成 kwr报告:
select * from perf.create_snapshot();
再次执行
select * from perf.create_snapshot();
select * from perf.kwr_report_to_file(2,3,'html','/nvme01/kingbase/kwr/zhaobsh_002_003.html');
这个SQL可以查看表大小信息
select schemaname,relname,sys_size_pretty(sys_total_relation_size(relid)) from sys_stat_user_tables order by sys_relation_size(relid) desc limit 30;
---
## kmonitor的相关设置
其实只需要修改一个文件就可以了, 但是可能需要执行两个文件.
需要注意 kmonitor不一定需要运行到kingbase上面
可以在其他机器上面运行. 但是链接人大金仓就可以.
kmonitor server ("kmonitor" "prometheus" "consul" "kingbase_exporter" "node_exporter" "alertmanager")
KMONITOR_SERVER=("kmonitor" "prometheus" "consul" "kingbase_exporter" "node_exporter")
kmonitor config
KMONITOR_PORT=3000
prometheus config
PROMETHEUS_PORT=9090
STORAGE_TSDB_PATH="data"
STORAGE_TSDB_RETENTION_TIME="15d"
STORAGE_TSDB_RETENTION_SIZE="512MB"
kingbase_exporter config
KINGBASE_EXPORTER_PORT=1234
node_exporter config
NODE_EXPORTER_PORT=9100
consul config
CONSUL_SERVER_PORT=8300
CONSUL_SERF_LAN_PORT=8301
CONSUL_SERF_WAN_PORT=8202
CONSUL_HTTP_PORT=8500
CONSUL_DNS_PORT=8600
CONSUL_DATA="consul_data"
kingbase config
KINGBASE_ADDRESS="192.168.255.xxx"
KINGBASE_PORT=54321
KINGBASE_DB="test"
KINGBASE_USER="kmonitor"
KINGBASE_PASSWD="xxxx"
alertmanager config
ALERTMANAGER_PORT=9093
ALERTMANAGER_CLUSTER_PORT=9094
local install enable(1) disable(0)
KINGBASE_EXPORTER_LOCAL_INSTALL=1
NODE_EXPORTER_LOCAL_INSTALL=1
ALERTMANAGER_LOCAL_INSTALL=0
excluded application
EXCLUDED_PROCESS=("")
register config
CLUSTER="Kingbase集群"
USER="kingbase"
REGISTER_KINGBASE_LIST=("192.168.255.yyy_1234")
REGISTER_NODE_LIST=("192.168.255.xxx_9100")
deregister config
DEREGISTER_LIST=()