目录结构

注：提前言明本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、KingbaseES V8产品文档首页，点击前往
2、Kingbase 文档库，点击前往
3、北京人大金仓信息技术股份有限公司官网首页，点击前往

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享抛砖引玉一起学习
3、本文不提供任何资源不存在任何交易与任何组织和机构无关
4、大家可以根据需要自行复制粘贴以及作为其他个人用途，但是不允许转载不允许商用（写作不易，还请见谅 💖）

Kingbase数据库性能调优工具sys_kwr插件的简介和使用

文章快速说明索引
插件的简介和安装
- 插件简介
- 插件安装
- 插件配置
- - KWR报告
  - KSH报告
  - KDDM报告
- 插件使用
- - 自动快照
  - 手动快照
  - 生成报告

文章快速说明索引

学习目标：

目的：本专栏将会为大家介绍Kingbase的功能使用、特性分析以及相关最新内核开发等技术的分享。

学习内容：（详见目录）

1、Kingbase数据库性能调优工具sys_kwr插件的简介和使用

学习时间：

2023年06月26日 19:53:38

学习产出：

1、Kingbase数据库性能调优工具sys_kwr插件的简介和使用
2、CSDN 技术博客 1篇

插件的简介和安装

插件简介

插件sys_kwr是KingbaseES的一个扩展插件。主要功能是通过周期性自动记录性能统计相关的快照，分析出KingbaseES的操作系统运行环境、数据库时间组成、等待事件和TOP SQL等性能指标，为数据库性能调优提供指导。

sys_kwr 是 Kingbase Auto Workload Repertories 的简称，可以对 KingbaseES 的负载信息自动以快照的方式进行记录和保存，并提供负载相关的性能报告。

在1.6版本中合入了 KSH 功能，KSH 不在做为单独的插件使用，创建 KWR 插件后便可以使用 KSH 功能。

插件名为 sys_kwr
插件版本 V1.6

插件安装

插件sys_kwr加载方式：KingbaseES数据库默认将其添加到 kingbase.conf 文件的 shared_preload_libraries 中，重启数据库时自动加载。

配置如下：

shared_preload_libraries = 'sys_kwr'

[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.kingbase=# create extension sys_kwr ;
CREATE EXTENSION
kingbase=# \dxList of installed extensionsName         | Version |    Schema    |                                                     Description                                                     
---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------dbms_ddl            | 1.0     | sys          | DBMS_DDL system packagedbms_output         | 1.0     | sys          | DBMS_OUTPUT system packagedbms_utility        | 1.0     | sys          | dbms_utility extension packagekdb_cast            | 1.0     | sys          | kdb_cast extensionkdb_license         | 1.0     | pg_catalog   | kdb_license extensionkdb_oracle_datatype | 1.7     | sys          | kdb_oracle_datatype extensionkdb_tinyint         | 1.0     | pg_catalog   | Create a new data type tinyint and its functions operators and indexeskingbase_version    | 1.0     | pg_catalog   | This is a utility that provides function related to version number,  it is used to get the Kingbase version number.owa_util            | 1.0     | sys          | owa_util system packageplpgsql             | 1.0     | pg_catalog   | PL/pgSQL procedural languageplsql               | 1.0     | pg_catalog   | PL/SQL procedural languagesrc_restrict        | 1.0     | src_restrict | src restrict pluginsys_anon            | 1.0     | anon         | provides data masking functionalitysys_freespacemap    | 1.2     | sys          | examine the free space map (FSM)sys_kwr             | 1.6     | public       | KingbaseES auto workload repository and report buildersys_stat_statements | 1.10    | public       | track parsing, planning and execution statistics of all SQL statements executedsysaudit            | 1.0     | sysaudit     | provides auditing functionalitysysmac              | 1.0     | sysmac       | Mac for Kingbasexlog_record_read    | 1.0     | pg_catalog   | xlog_record_read functions
(19 rows)kingbase=# \dx sys_kwrList of installed extensionsName   | Version | Schema |                      Description                       
---------+---------+--------+--------------------------------------------------------sys_kwr | 1.6     | public | KingbaseES auto workload repository and report builder
(1 row)kingbase=#

下面来看一下该插件所创建的数据库对象，如下：

kingbase=# \dx+ sys_kwr                    Objects in extension "sys_kwr"Object description                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------function perf.bg_wait_event_htbl(jsonb,integer,integer,integer,text)function perf.bgwriter_stats_htbl(jsonb,integer,integer,integer,text)function perf.bgwriter_stats(integer,integer)function perf.bgwriter_stats_reset_htbl(jsonb,integer,integer,text)function perf.bgwriter_stats_reset(integer,integer)function perf.buffcache_pages()function perf.check_attr_exists(text,text,text)function perf.check_queryid_md5(bpchar,bigint,text,text,text)function perf.check_settings()function perf.check_snapid(integer,integer)function perf.check_stmt_all_setting(integer,integer)function perf.check_stmt_cnt(integer,integer,text)function perf.collect_bgwriter_stats(integer)function perf.collect_cpu_stats(integer)function perf.collect_database_stats(integer)function perf.collect_host_detail_cpu_stats(integer)function perf.collect_inst_event_stats(integer)function perf.collect_instio_stats(integer)function perf.collect_instlock_stats(integer)function perf.collect_io_stats(integer)function perf.collect_memory_stats(integer)function perf.collect_network_stats(integer)function perf.collect_obj_stats(integer)function perf.collect_process_mem_stats(integer)function perf.collect_queries(bpchar)function perf.collect_shmem_stats(integer)function perf.collect_sqlio_stats(integer)function perf.collect_sql_prof_stats(integer)function perf.collect_sqltime_stats(integer)function perf.collect_statements_all_stats(integer,oid,oid,bigint)function perf.collect_statements_stats(integer,integer)function perf.collect_wait_stats(integer)function perf.create_snapshot()function perf.database_stats_htbl(jsonb,integer,integer,integer,text)function perf.database_stats(integer,integer)function perf.database_stats_reset_htbl(jsonb,integer,integer,text)function perf.database_stats_reset(integer,integer)function perf.datafileio_stats_total(integer,integer)function perf.db_objs_desc_htbl(integer,integer,text)function perf.dbtime_bydb_htbl(jsonb,integer,integer,integer,text)function perf.dbtime_bymsg_htbl(jsonb,integer,integer,integer,text)function perf.diff_array_length(text[],integer)function perf.diff_coalesce(text,text)function perf.diff_isnumeric(text)function perf.drop_snapshots(integer,integer)function perf.eff_percent_htbl(jsonb,integer,integer,integer,text)function perf.fg_wait_class_htbl(jsonb,integer,integer,integer,text)function perf.fg_wait_event_htbl(jsonb,integer,integer,integer,text)function perf.fg_wait_stats(integer,integer)function perf.get_bg_wait_event_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_bgwriter_stats_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_database_stats_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_dbtime_bydb_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_dbtime_bymsg_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_dbtime(integer,integer)function perf.get_dbtime_item(integer,integer,text,boolean)function perf.get_eff_percent_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_elapsed_time(integer,integer)function perf.get_fg_wait_class_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_fg_wait_event_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_host_config_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_host_io_stats_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_instance_io_stats_diff(jsonb,integer,integer,integer,integer,integer,text)function perf.get_instance_lock_stats_diff(jsonb,integer,integer,integer,integer,integer,text)function perf.get_instance_sql_count_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_instance_top_event_stats_diff(jsonb,integer,integer,integer,integer,integer,text)function perf.get_inst_io_datafile_profile_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_inst_io_profile_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_load_profile_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_memory_statistics_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_settings_and_changes_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_shared_memory_stats_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_sql_list_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_time_model_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_dbcpu_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_dbtime_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_dml_tables_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_exev_calls_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_gets_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_gets_indexes_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_gets_tables_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_io_time_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_parse_time_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_plan_time_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_reads_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_reads_indexes_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_read_tables_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_seqscan_tables_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_shared_dirtied_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_shared_written_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_temp_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_time_funcs_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_wait_class_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_top_wait_event_diff(jsonb,integer,integer,integer,integer,integer)function perf.get_wait_time(integer,integer,text)function perf.host_cpu_stats_htbl(jsonb,integer,integer,integer,text)function perf.host_detail_cpu_stats_htbl(jsonb,integer,integer,integer,text)function perf.host_io_stats_htbl(jsonb,integer,integer,integer,text)function perf.host_memory_stats_htbl(jsonb,integer,integer,integer,text)function perf.host_network_stats_htbl(jsonb,integer,integer,integer,text)function perf.instance_event_stats_htbl(jsonb,integer,integer,integer,text)function perf.instance_io_stats_htbl(jsonb,integer,integer,integer,text,text)function perf.instance_lock_stats_htbl(jsonb,integer,integer,integer,text,text)function perf.instance_sql_count(jsonb,integer,integer,integer,text)function perf.instance_top_event_stats_htbl(jsonb,integer,integer,integer,text,text)function perf.inst_io_datafile_profile_htbl(jsonb,integer,integer,integer,text)function perf.inst_io_profile_htbl(jsonb,integer,integer,integer,text)function perf.is_windows()function perf.jsonb_replace(jsonb,jsonb)function perf.kddm_checkpoint_advisor(integer,integer,integer,integer,numeric)function perf.kddm_ckpts_stats(integer,integer)function perf.kddm_complete_sql_list(integer,integer)function perf.kddm_cpu_load_advisor(integer,integer)function perf.kddm_dbcpu_stats(integer,integer,integer,integer,numeric)function perf.kddm_dbtime_report(integer,integer,integer,integer,numeric)function perf.kddm_fgwait_stats(integer,integer,integer,integer,numeric)function perf.kddm_file_extend_advisor(integer,integer,integer,integer,numeric)function perf.kddm_generate_report(integer,integer)function perf.kddm_guc_advisor(bigint,text,bigint,bigint)function perf.kddm_index_advisor(integer,integer)function perf.kddm_inst_io_hit(integer,integer,text)function perf.kddm_inst_io_pct(integer,integer,text)function perf.kddm_io_time_report(integer,integer,integer,integer,numeric)function perf.kddm_io_time_stats(integer,integer)function perf.kddm_io_wait_pct(integer,integer)function perf.kddm_line(text,text,boolean)function perf.kddm_net_error_advisor(integer,integer)function perf.kddm_net_trans_advisor(integer,integer,integer)function perf.kddm_page_prune_advisor(integer,integer)function perf.kddm_print_client_title(integer,integer,text)function perf.kddm_print_cpu_contents(integer,integer,text)function perf.kddm_print_cpu_title(integer,integer,text)function perf.kddm_print_io_contents(integer,integer,text)function perf.kddm_print_io_title(integer,integer,text)function perf.kddm_print_lwlock_contents(integer,integer,text)function perf.kddm_print_lwlock_title(integer,integer,text)function perf.kddm_print_net_contents(integer,integer,text)function perf.kddm_print_report(text,text,text)function perf.kddm_print_wait_contents(integer,integer,text)function perf.kddm_print_wait_titile_by_rank(integer,integer,integer,integer,integer,integer,text)function perf.kddm_print_wait_title(integer,integer,text)function perf.kddm_print_wait_topsql(integer,integer,text,integer,integer,numeric)function perf.kddm_report(integer,integer)function perf.kddm_report_to_file(integer,integer,text)function perf.kddm_share_buf_advisor(integer,integer,integer,integer,numeric)function perf.kddm_simple_query_advisor(integer,integer)function perf.kddm_split_long_sql(text,integer,integer)function perf.kddm_sql_report(integer,integer,bigint)function perf.kddm_temp_buf_advisor(integer,integer,integer,integer,numeric)function perf.kddm_time_order(integer,integer)function perf.kddm_top_sql_report(integer,integer)function perf.kddm_top_wait_report(integer,integer)function perf.kddm_trans_rollback_advisor(integer,integer)function perf.kddm_wait_content_by_rank(integer,integer,integer,integer,integer,integer,text)function perf.kddm_wait_dbpct(integer,integer,text)function perf.kddm_wait_elapsed_pct(integer,integer,text)function perf.kddm_wait_event_desc(text)function perf.kddm_wait_rank(integer,integer,text,boolean)function perf.kddm_wait_topsql(integer,integer,text,integer,integer,numeric)function perf.kddm_wal_insert_advisor(integer,integer)function perf.kddm_wal_io_advisor(integer,integer,integer,integer,numeric)function perf.kddm_wal_sync_advisor(integer,integer,integer,integer,numeric)function perf.kddm_wal_write_advisor(integer,integer)function perf.kddm_work_mem_advisor(integer,integer,integer,integer,numeric)function perf.ksh_check_env()function perf.ksh_check_slotwidth(timestamp with time zone,timestamp with time zone,perf.ksh_data_type,integer)function perf.ksh_data(perf.ksh_data_type,timestamp with time zone,timestamp with time zone,timestamp with time zone,timestamp with time zone)function perf.ksh_db(timestamp with time zone,timestamp with time zone,boolean)function perf.ksh_drop_temptbl(text)function perf.ksh_generate_report(timestamp with time zone,integer,integer,text)function perf.ksh_generate_timeranges(timestamp with time zone,timestamp with time zone,integer)function perf.ksh_get_avg_active_session(numeric,numeric,perf.ksh_data_type)function perf.ksh_get_centered_msg(text,text)function perf.ksh_get_closest_minute(timestamp with time zone)function perf.ksh_get_header(text,integer[],text[])function perf.ksh_get_percentage(numeric,numeric)function perf.ksh_get_ringbuf_end()function perf.ksh_get_ringbuf_start()function perf.ksh_get_slotwidth(timestamp with time zone,timestamp with time zone,perf.ksh_data_type)function perf.ksh_get_timespec(timestamp with time zone,timestamp with time zone)function perf.ksh_get_widths(integer[],text[])function perf.ksh_is_relation_exist(text,text)function perf.ksh_jsonb_replace(jsonb,jsonb)function perf.ksh_jsonb_replace_ksh(jsonb,jsonb)function perf.ksh_mem_raw(timestamp with time zone,timestamp with time zone,integer)function perf.ksh_mem(timestamp with time zone,timestamp with time zone,perf.ksh_mem_op)function perf.ksh_nodata_wrapper(text)function perf.ksh_nodata_wrapper_text(text)function perf.ksh_openum_to_int(perf.ksh_mem_op)function perf.ksh_outline_query(text,integer)function perf.ksh_report_activity_over_time(jsonb,timestamp with time zone,timestamp with time zone,integer,perf.ksh_data_type,text)function perf.ksh_report_by_snapshots(integer,integer,integer,text)function perf.ksh_report_complete_list_of_sql_text(jsonb,text)function perf.ksh_report_header(jsonb,timestamp with time zone,timestamp with time zone,timestamp with time zone,timestamp with time zone,integer,integer,perf.ksh_data_type,text)function perf.ksh_report(timestamp with time zone,integer,integer,text)function perf.ksh_report_to_file_by_snapshots(integer,integer,text,text,integer)function perf.ksh_report_to_file(timestamp with time zone,integer,integer,text,text)function perf.ksh_report_top_background_event(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_blocking_sessions(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_client_id(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_databases(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_db_objects(integer,perf.ksh_data_type)function perf.ksh_report_top_locks(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_lwlocks(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_phase_of_execution(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_plsql_procs(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_session(jsonb,text)function perf.ksh_report_top_session_running_pqs(jsonb,integer,perf.ksh_data_type,text)function perf.ksh_report_top_sql_command_type(jsonb,integer,perf.ksh_data_type,text)function perf.ksh_report_top_sql_using_literals(jsonb,integer,integer,perf.ksh_data_type,text)function perf.ksh_report_top_sql_with_top_event(jsonb,text)function perf.ksh_report_top_user_event(jsonb,integer,perf.ksh_data_type,text)function perf.ksh_split_longsql(text)function perf.ksh_timer(integer)function perf.ksh_write_report(text,text)function perf.kwr_byte2mb(double precision)function perf.kwr_check_env()function perf.kwr_cpu_info()function perf.kwr_cpu_info_ex()function perf.kwr_debug()function perf.kwr_diff_report_html_cn(integer,integer,integer,integer,integer)function perf.kwr_diff_report_html(integer,integer,integer,integer,integer)function perf.kwr_diff_report(integer,integer,integer,integer)function perf.kwr_diff_report_to_file(integer,integer,integer,integer,text)function perf.kwr_diff_snapshots_info_htbl(jsonb,integer,integer,integer,integer,text)function perf.kwr_generate_diff_report(integer,integer,integer,integer)function perf.kwr_generate_report(integer,integer,text)function perf.kwr_long_string(text,integer)function perf.kwr_memory_info()function perf.kwr_os_info()function perf.kwr_percent(double precision,double precision)function perf.kwr_query_hash(text,text,text,integer)function perf.kwr_report_html_cn(integer,integer,integer)function perf.kwr_report_html(integer,integer,integer)function perf.kwr_report(integer,integer,text)function perf.kwr_report_ksh(jsonb,integer,integer,text)function perf.kwr_report_text_cn(integer,integer,integer)function perf.kwr_report_text(integer,integer,integer)function perf.kwr_report_to_file(integer,integer,text,text)function perf.kwr_round(double precision,integer)function perf.kwr_round_null(double precision,integer)function perf.kwr_rpad(text,integer)function perf.kwr_rpad_time(timestamp without time zone,integer)function perf.kwr_time(timestamp without time zone,text)function perf.load_profile_htbl(jsonb,integer,integer,integer,text)function perf.memory_statistics_htbl(jsonb,integer,integer,integer,text)function perf.nodata_wrapper(text)function perf.nodata_wrapper_text(text)function perf.process_memory_stats_htbl(jsonb,integer,integer,integer,text)function perf.reset_snapshots()function perf.save_diff_report_with_file_ext_name(text,text,text,integer,integer,integer,integer)function perf.save_report_with_ext_name(text,text,integer,integer)function perf.save_report_with_file_ext_name(text,text,text,integer,integer)function perf.save_report_with_full_path(text,text)function perf.server_info_htbl(jsonb,integer,integer,text)function perf.settings_and_changes_htbl(jsonb,integer,integer,text)function perf.settings_and_changes(integer,integer)function perf.shared_memory_stats_htbl(jsonb,integer,integer,integer,text)function perf.snapshot_cleanup(integer)function perf.snapshot_dbobj_delta(integer,integer)function perf.snapshots_info_htbl(jsonb,integer,integer,text)function perf.snapshot_timer()function perf.sqlio_stats_total(integer,integer)function perf.sql_level(bigint,integer,integer,integer,boolean,integer,integer)function perf.sql_list_htbl(jsonb,text)function perf.sql_prof_collect(integer,text)function perf.sql_prof_stats_1_3(integer,integer)function perf.sqltime_stats(integer,integer,boolean)function perf.statements_stats(integer,integer)function perf.sys_cpu_info()function perf.sys_cpu_memory_by_process()function perf.sys_cpu_stat_by_process(bigint)function perf.sys_cpu_stats_info()function perf.sys_cpu_usage_info()function perf.sys_disk_info()function perf.sys_io_analysis_info()function perf.sys_io_stat_byprocess(bigint)function perf.sys_io_stat_info()function perf.sys_load_avg_info()function perf.sys_memory_info()function perf.sys_mempage_stat_byprocess(bigint)function perf.sys_network_info()function perf.sys_os_info()function perf.sys_process_info()function perf.system_info_htbl(jsonb,integer,integer,text)function perf.text_minus(text,text)function perf.text_percent(text,text)function perf.text_plus(text,text,text)function perf.text_round(text,integer)function perf.time_model_htbl(jsonb,integer,integer,integer,text)function perf.top_calls_funcs_htbl(jsonb,integer,integer,integer,text)function perf.top_dbcpu_htbl(jsonb,integer,integer,integer,text)function perf.top_dbtime_htbl(jsonb,integer,integer,integer,text)function perf.top_dml_tables_htbl(jsonb,integer,integer,integer,text)function perf.top_execute_time_htbl(jsonb,integer,integer,integer,text)function perf.top_exev_calls_htbl(jsonb,integer,integer,integer,text)function perf.top_functions(integer,integer)function perf.top_gets_htbl(jsonb,integer,integer,integer,text)function perf.top_gets_indexes_htbl(jsonb,integer,integer,integer,text)function perf.top_gets_tables_htbl(jsonb,integer,integer,integer,text)function perf.top_heap_hit_tables_htbl(jsonb,integer,integer,integer,text)function perf.top_hit_indexes_htbl(jsonb,integer,integer,integer,text)function perf.top_indexes(integer,integer)function perf.top_io_indexes_hit(integer,integer)function perf.top_io_indexes(integer,integer)function perf.top_io_tables_hit(integer,integer)function perf.top_io_tables(integer,integer)function perf.top_io_time_htbl(jsonb,integer,integer,integer,text)function perf.top_local_htbl(jsonb,integer,integer,integer,text)function perf.top_parse_time_htbl(jsonb,integer,integer,integer,text)function perf.top_plan_time_htbl(jsonb,integer,integer,integer,text)function perf.top_reads_htbl(jsonb,integer,integer,integer,text)function perf.top_reads_indexes_htbl(jsonb,integer,integer,integer,text)function perf.top_read_tables_htbl(jsonb,integer,integer,integer,text)function perf.top_return_rows_htbl(jsonb,integer,integer,integer,text)function perf.top_seqscan_tables_htbl(jsonb,integer,integer,integer,text)function perf.top_shared_dirtied_htbl(jsonb,integer,integer,integer,text)function perf.top_shared_written_htbl(jsonb,integer,integer,integer,text)function perf.top_statements(integer,integer)function perf.top_tables(integer,integer)function perf.top_temp_htbl(jsonb,integer,integer,integer,text)function perf.top_time_funcs_htbl(jsonb,integer,integer,integer,text)function perf.top_wait_class_htbl(jsonb,integer,integer,integer,text)function perf.top_wait_event_htbl(jsonb,integer,integer,integer,text)function perf.unused_indexes_htbl(jsonb,integer,integer,integer,text)function perf.update_dbobjs(integer)function perf.update_settings(integer)function perf.wait_stats(integer,integer,boolean)operator perf.||(anyarray,anyarray)operator perf.||(anyarray,anyelement)operator perf.||(anyelement,anyarray)operator perf.||(anynonarray,text)operator perf.||(bit varying,bit varying)operator perf.||(bytea,bytea)operator perf.||(jsonb,jsonb)operator perf.||(text,anynonarray)operator perf.||(text,text)operator perf.||(tsquery,tsquery)operator perf.||(tsvector,tsvector)schema perfsequence perf.kwr_snapshots_snap_id_seqtable perf.ksh_historytable perf.ksh_statementstable perf.kwr_index_listtable perf.kwr_last_bgwritertable perf.kwr_last_databasetable perf.kwr_last_host_cputable perf.kwr_last_host_iotable perf.kwr_last_host_nettable perf.kwr_last_indextable perf.kwr_last_inst_eventtable perf.kwr_last_inst_iotable perf.kwr_last_inst_locktable perf.kwr_last_snapshottable perf.kwr_last_sql_iotable perf.kwr_last_sql_proftable perf.kwr_last_sql_timetable perf.kwr_last_sql_waittable perf.kwr_last_tabletable perf.kwr_last_ufunctable perf.kwr_snap_bgwritertable perf.kwr_snap_databasetable perf.kwr_snap_host_cputable perf.kwr_snap_host_detail_cputable perf.kwr_snap_host_iotable perf.kwr_snap_host_memtable perf.kwr_snap_host_nettable perf.kwr_snap_idx_suggesttable perf.kwr_snap_indextable perf.kwr_snap_index_totaltable perf.kwr_snap_inst_eventtable perf.kwr_snap_inst_iotable perf.kwr_snap_inst_locktable perf.kwr_snap_os_infotable perf.kwr_snap_process_memtable perf.kwr_snap_server_infotable perf.kwr_snap_settingstable perf.kwr_snap_shmemtable perf.kwr_snapshotstable perf.kwr_snap_sql_alltable perf.kwr_snap_sql_iotable perf.kwr_snap_sql_proftable perf.kwr_snap_sql_stmttable perf.kwr_snap_sql_stmt_totaltable perf.kwr_snap_sql_timetable perf.kwr_snap_sql_waittable perf.kwr_snap_tabletable perf.kwr_snap_table_totaltable perf.kwr_snap_userfunctable perf.kwr_snap_userfunc_totaltable perf.kwr_stmt_listtable perf.kwr_table_listtable perf.kwr_ufunc_listtype perf.ksh_data_typetype perf.ksh_mem_optype perf.ksh_samplingview dba_hist_active_sess_historyview perf.kwr_snap_index_viewview perf.kwr_snap_settings_viewview perf.kwr_snap_sql_stmt_viewview perf.kwr_snap_table_viewview perf.kwr_snap_userfunc_viewview perf.session_historyview perf.sys_sql_profileview "v$active_session_history"
(405 rows)kingbase=#

注：以上是sys_kwr插件所创建的数据库对象，涵盖了KWR KSH KDDM等内容。今天我们学习的重点在于插件的安装和使用，具体的描述和分析后面另起博客进行学习！

插件配置

KWR报告

KWR报告的使用和展示内容依赖于相关GUC参数的控制，如下：

-- 要想使用 KWR 的全部报告功能，建议在kingbase.conf里添加以下参数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_kwr.enable = on

以上各参数的解释，如下：

/*track_sql: 开启统计 sql 时间，等待事件，IO ，默认为offtrack_instance: 开启统计实例级IO，关键活动，锁，默认为offtrack_wait_timing: 开启累积式等待事件记录等待时间的功能，默认为ontrack_counts: 统计数据库活动，默认为ontrack_io_timing: 开启IO计时统计功能，默认为offtrack_functions: 开启函数统计功能，推荐用 'all'，默认为'none'sys_stat_statements.track: 跟踪统计SQL语句的访问，推荐用 'top'，默认为'none'sys_kwr.enable: 自动对收集到的数据生成快照并保存到kwr库里，默认为off*/

还有一些可以直接使用默认值的参数，如下：

/*sys_kwr.topn: kwr报告显示topn条件记录，默认20条sys_kwr.history_days: kwr快照数据保留天数，默认8天sys_kwr.interval: kwr自动采集快照的间隔，默认60分钟sys_kwr.language: kwr报告使用语言，默认为中文（chinese 或 chn），可选为英文(english 或eng)sys_stat_statements.track_parse: pg_stat_statements记录parse次数和时间，默认开启sys_stat_statements.track_plan: pg_stat_statements记录plan次数和时间，默认开启*/

KSH报告

KSH 以每秒采样的方式进行会话和数据收集，并将采集数据放入内存的 Ringbuf 队列中，采集的数据主要包括：会话、应用、等待事件、命令类型、QueryId等。其主要使用场景是：当前或历史某个时点，发生了什么样的异常，系统在执行/运行什么任务。

KSH 功能相关的参数在kingbase.conf中配置，如下：

shared_preload_libraries ='sys_stat_statements, sys_kwr'
track_activities = on
sys_stat_statements.max = 10000
sys_stat_statements.track =all
sys_kwr.collect_ksh = on           # default = off
sys_kwr.ringbuf_size = 200000      # default = 100000
sys_kwr.history_days = 3           # default = 8；最小 2，最大 31
sys_kwr.language = 'english'       # default = ‘chinese’

以上各参数的解释，如下：

/*track_activities: 跟踪活动会话的等待事件、执行SQL、状态等，默认：onsys_stat_statements.max: 设置sys_stat_statement跟踪的最大语句数，默认：5000sys_stat_statements.track: 跟踪统计SQL语句的访问，推荐用 'top'，默认为'none'sys_kwr.collect_ksh: 启用或禁用 KSH 数据收集sys_kwr.ringbuf_size: 设置ksh ringbuf大小sys_kwr.history_days: 设置最大的 KSH 历史数据存储sys_kwr.language: KSH 报告展示中文/英语信息*/

注：采集报告需要在开启：track_activities，并需要设定 sys_kwr.collect_ksh=on 的情况下才可以查看KSH。不过开启该参数会有一定的性能损耗。

KDDM报告

KDDM 功能相关的参数在kingbase.conf中配置，如下：

kingbase=# create extension sys_qualstats ;
CREATE EXTENSION
kingbase=#

关于pg_qualstats插件的源码解析，后面我会另起一篇博客详解。其使用上可以看一下这位老哥的博客：

pg_qualstats，点击前往

shared_preload_libraries ='sys_stat_statements, sys_kwr, sys_qualstats'
sys_qualstats.enabled = true
sys_qualstats.sample_rate = 1
sys_stat_statements.track = 'top'
track_activities = on
track_counts = on
track_sql = on
track_wait_timing = on
track_io_timing = on
track_functions = 'all'

以上各参数的解释，如下：

/*sys_qualstats.enabled: sys_qualstats是否启用sys_qualstats.sample_rate: 确保所有的query都会被抓取到sys_stat_statements.track: 跟踪统计SQL语句的访问，推荐用 'top'，默认为'none'track_activities: 跟踪活动会话的等待事件、执行SQL、状态等，默认：ontrack_counts: 统计数据库活动，默认为ontrack_sql: 开启统计 sql 时间，等待事件，IO ，默认为offtrack_wait_timing: 开启累积式等待事件记录等待时间的功能track_io_timing: 开启IO计时统计功能，默认为offtrack_functions: 开启函数统计功能，推荐用 'all'，默认为'none'*/

插件使用

sys_kwr通过周期性自动记录性能统计相关的快照，分析出KingbaseES的操作系统运行环境、数据库时间组成、等待事件和TOP SQL等性能指标，为数据库性能调优提供指导。相关统计如下：

SQL语句执行过程中产生的等待事件, IO 和执行时间
Top SQL
数据库对象统计信息
数据库实例统计信息

自动快照

KingbaseES启动服务后，sys_kwr collector 后台进程后会每个小时采集一次负载信息，并保存到kwr库(test) 。如下：

在这里插入图片描述

查询快照列表，如下：

-- 注：我这里 把sys_kwr.interval改成10分钟[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.kingbase=# SELECT * FROM perf.kwr_snapshots;snap_id | snap_time | sess_count | snap_version 
---------+-----------+------------+--------------
(0 rows)kingbase=# show sys_kwr.interval;   sys_kwr.interval 
------------------10
(1 row)kingbase=# select now();now              
-------------------------------2023-06-26 07:32:38.576289-07
(1 row)kingbase=# SELECT * FROM perf.kwr_snapshots;snap_id |       snap_time        | sess_count | snap_version 
---------+------------------------+------------+--------------1 | 2023-06-26 07:33:04-07 |          1 | 1.6
(1 row)kingbase=# select now();                    now              
-------------------------------2023-06-26 07:44:24.858101-07
(1 row)kingbase=# SELECT * FROM perf.kwr_snapshots;snap_id |       snap_time        | sess_count | snap_version 
---------+------------------------+------------+--------------1 | 2023-06-26 07:33:04-07 |          1 | 1.62 | 2023-06-26 07:43:07-07 |          1 | 1.6
(2 rows)kingbase=#

手动快照

手工快照通过SQL语句来执行，如下：

kingbase=# SELECT * FROM perf.create_snapshot();create_snapshot 
-----------------3
(1 row)kingbase=# SELECT * FROM perf.kwr_snapshots;    snap_id |       snap_time        | sess_count | snap_version 
---------+------------------------+------------+--------------1 | 2023-06-26 07:33:04-07 |          1 | 1.62 | 2023-06-26 07:43:07-07 |          1 | 1.63 | 2023-06-26 07:46:41-07 |          1 | 1.6
(3 rows)kingbase=#

生成报告

KWR报告的生成：有了负载信息的快照数据后，就可以调用以下2个函数来生成kwr报告，如下：

SELECT * FROM perf.kwr_report(start_id integer, end_id integer, format text = 'text');
SELECT * FROM perf.kwr_report_to_file(start_id integer, end_id integer, format  text, file_path text);-- 内部接口
select * from perf.kwr_report_html_cn(integer,integer,integer);
select * from perf.kwr_report_html(integer,integer,integer);
select * from perf.kwr_report_text_cn(integer,integer,integer);
select * from perf.kwr_report_text(integer,integer,integer);kingbase=# \df perf.kwr_report List of functionsSchema |    Name    | Result data type |                        Argument data types                         | Type 
--------+------------+------------------+--------------------------------------------------------------------+------perf   | kwr_report | text             | start_id integer, end_id integer, format text DEFAULT 'text'::text | func
(1 row)kingbase=# \df perf.kwr_report_to_fileList of functionsSchema |        Name        | Result data type |                      Argument data types                      | Type 
--------+--------------------+------------------+---------------------------------------------------------------+------perf   | kwr_report_to_file | boolean          | start_id integer, end_id integer, format text, file_path text | func
(1 row)kingbase=# \df perf.kwr_report_html_cnList of functionsSchema |        Name        | Result data type |              Argument data types               | Type 
--------+--------------------+------------------+------------------------------------------------+------perf   | kwr_report_html_cn | text             | start_id integer, end_id integer, topn integer | func
(1 row)kingbase=# \df perf.kwr_report_htmlList of functionsSchema |      Name       | Result data type |              Argument data types               | Type 
--------+-----------------+------------------+------------------------------------------------+------perf   | kwr_report_html | text             | start_id integer, end_id integer, topn integer | func
(1 row)kingbase=# \df perf.kwr_report_text_cnList of functionsSchema |        Name        | Result data type |              Argument data types               | Type 
--------+--------------------+------------------+------------------------------------------------+------perf   | kwr_report_text_cn | text             | start_id integer, end_id integer, topn integer | func
(1 row)kingbase=# \df perf.kwr_report_textList of functionsSchema |      Name       | Result data type |              Argument data types               | Type 
--------+-----------------+------------------+------------------------------------------------+------perf   | kwr_report_text | text             | start_id integer, end_id integer, topn integer | func
(1 row)kingbase=#-- perf.kwr_report()生成报告并加报告内容自动保存到KingbaseES的data/sys_log目录下-- perf.kwr_report_to_file()则生成报告并将内容保存指定的file_path路径下-- 生成html报告, 如下：
-- SELECT * FROM perf.kwr_report(1,2,'html');

参数的解释说明，如下：

/*start_id起始快照IDend_id结束快照IDformat报告的格式，只支持'text'和'html'格式，建议使用'html'格式file_path在服务器上保存报告文件的全路径，必须有写文件的权限*/

KSH报告的生成：

SELECT perf.ksh_report({start_ts}, {duration}, {slot_width});
SELECT * FROM perf.ksh_report_to_file({start_ts},{duration},{slot_width},{file_path},{format});
SELECT * FROM perf.ksh_report_by_snapshots({start_snapid}, {end_snapid}, {slot_width}, {format});
SELECT * FROM perf.ksh_report_to_file_by_snapshots({start_snapid}, {end_snapid}, {file_path}, {format}, {slot_width});kingbase=# \df perf.ksh_report        List of functionsSchema |    Name    | Result data type |                                                                                        Argument data types                                                                                         | Type 
--------+------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------perf   | ksh_report | text             | start_ts timestamp with time zone DEFAULT (CURRENT_TIMESTAMP - '0:15:00.000000'::pg_catalog.interval), duration integer DEFAULT 15, slot_width integer DEFAULT 0, format text DEFAULT 'text'::text | func
(1 row)kingbase=# \df perf.ksh_report_to_fileList of functionsSchema |        Name        | Result data type |                                                                                                        Argument data types                                                                                                         | Type 
--------+--------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------perf   | ksh_report_to_file | boolean          | start_ts timestamp with time zone DEFAULT (CURRENT_TIMESTAMP - '+00 00:15:00'::interval minute(2)), duration integer DEFAULT 15, slot_width integer DEFAULT 0, file_path text DEFAULT NULL::text, format text DEFAULT 'text'::text | func
(1 row)kingbase=# \df perf.ksh_report_by_snapshots                                                                                                                                                                                                                                                                                                                                                                                                                                                                                List of functionsSchema |          Name           | Result data type |                                       Argument data types                                        | Type 
--------+-------------------------+------------------+--------------------------------------------------------------------------------------------------+------perf   | ksh_report_by_snapshots | text             | start_id integer, end_id integer, slot_width integer DEFAULT 0, format text DEFAULT 'text'::text | func
(1 row)kingbase=# \df perf.ksh_report_to_file_by_snapshotsList of functionsSchema |              Name               | Result data type |                                               Argument data types                                                | Type 
--------+---------------------------------+------------------+------------------------------------------------------------------------------------------------------------------+------perf   | ksh_report_to_file_by_snapshots | boolean          | start_id integer, end_id integer, file_path text, format text DEFAULT 'text'::text, slot_width integer DEFAULT 0 | func
(1 row)kingbase=#

参数的解释说明，如下：

/*start_ts: 报告开始时间，默认：当前时间-15分钟duration: 报告时长，默认到15分钟，最大不超过60slot_width: 报告最小区间，输入0时系统自动计算合适的start_snapid: 起始快照号end_snapid: 结束快照号file_path: 报告生成地址，示例：’/home/username/ksh_report.html’format: 报告生成格式，可选择 ’html’和’text’两种格式*/

KDDM报告的生成：

SELECT * FROM perf.kddm_report(start_id integer, end_id integer);
SELECT * FROM perf.kddm_report_to_file(start_id integer, end_id integer, file_path text);kingbase=# \df perf.kddm_reportList of functionsSchema |    Name     | Result data type |       Argument data types        | Type 
--------+-------------+------------------+----------------------------------+------perf   | kddm_report | text             | start_id integer, end_id integer | func
(1 row)kingbase=# \df perf.kddm_report_to_fileList of functionsSchema |        Name         | Result data type |               Argument data types                | Type 
--------+---------------------+------------------+--------------------------------------------------+------perf   | kddm_report_to_file | boolean          | start_id integer, end_id integer, file_path text | func
(1 row)kingbase=#

参数的解释说明，如下：

/*start_id起始快照IDend_id结束快照IDfile_path在服务器上保存报告文件的全路径，必须有写文件的权限*/

KingbaseES的学习心得和知识总结（三）|Kingbase数据库性能调优工具sys_kwr插件的简介和使用

目录结构

Kingbase数据库性能调优工具sys_kwr插件的简介和使用

文章快速说明索引

插件的简介和安装

插件简介

插件安装

插件配置

KWR报告

KSH报告

KDDM报告

插件使用

自动快照

手动快照

生成报告

相关文章

R回归诊断（car包）

R语言数学建模（１）：Regression analysis

R语言完成t-SNE分析和画图

R语言easier包预测免疫治疗响应

R语言之数据清洗与准备

R语言复习总结

R语言随机森林

TCP三挥四握