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

插件的简介和安装

插件简介

插件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=# \dx
                                                                    List of installed extensions
        Name         | Version |    Schema    |                                                     Description                                                     
---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------
 dbms_ddl            | 1.0     | sys          | DBMS_DDL system package
 dbms_output         | 1.0     | sys          | DBMS_OUTPUT system package
 dbms_utility        | 1.0     | sys          | dbms_utility extension package
 kdb_cast            | 1.0     | sys          | kdb_cast extension
 kdb_license         | 1.0     | pg_catalog   | kdb_license extension
 kdb_oracle_datatype | 1.7     | sys          | kdb_oracle_datatype extension
 kdb_tinyint         | 1.0     | pg_catalog   | Create a new data type tinyint and its functions operators and indexes
 kingbase_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 package
 plpgsql             | 1.0     | pg_catalog   | PL/pgSQL procedural language
 plsql               | 1.0     | pg_catalog   | PL/SQL procedural language
 src_restrict        | 1.0     | src_restrict | src restrict plugin
 sys_anon            | 1.0     | anon         | provides data masking functionality
 sys_freespacemap    | 1.2     | sys          | examine the free space map (FSM)
 sys_kwr             | 1.6     | public       | KingbaseES auto workload repository and report builder
 sys_stat_statements | 1.10    | public       | track parsing, planning and execution statistics of all SQL statements executed
 sysaudit            | 1.0     | sysaudit     | provides auditing functionality
 sysmac              | 1.0     | sysmac       | Mac for Kingbase
 xlog_record_read    | 1.0     | pg_catalog   | xlog_record_read functions
(19 rows)

kingbase=# \dx sys_kwr
                            List of installed extensions
  Name   | 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 perf
 sequence perf.kwr_snapshots_snap_id_seq
 table perf.ksh_history
 table perf.ksh_statements
 table perf.kwr_index_list
 table perf.kwr_last_bgwriter
 table perf.kwr_last_database
 table perf.kwr_last_host_cpu
 table perf.kwr_last_host_io
 table perf.kwr_last_host_net
 table perf.kwr_last_index
 table perf.kwr_last_inst_event
 table perf.kwr_last_inst_io
 table perf.kwr_last_inst_lock
 table perf.kwr_last_snapshot
 table perf.kwr_last_sql_io
 table perf.kwr_last_sql_prof
 table perf.kwr_last_sql_time
 table perf.kwr_last_sql_wait
 table perf.kwr_last_table
 table perf.kwr_last_ufunc
 table perf.kwr_snap_bgwriter
 table perf.kwr_snap_database
 table perf.kwr_snap_host_cpu
 table perf.kwr_snap_host_detail_cpu
 table perf.kwr_snap_host_io
 table perf.kwr_snap_host_mem
 table perf.kwr_snap_host_net
 table perf.kwr_snap_idx_suggest
 table perf.kwr_snap_index
 table perf.kwr_snap_index_total
 table perf.kwr_snap_inst_event
 table perf.kwr_snap_inst_io
 table perf.kwr_snap_inst_lock
 table perf.kwr_snap_os_info
 table perf.kwr_snap_process_mem
 table perf.kwr_snap_server_info
 table perf.kwr_snap_settings
 table perf.kwr_snap_shmem
 table perf.kwr_snapshots
 table perf.kwr_snap_sql_all
 table perf.kwr_snap_sql_io
 table perf.kwr_snap_sql_prof
 table perf.kwr_snap_sql_stmt
 table perf.kwr_snap_sql_stmt_total
 table perf.kwr_snap_sql_time
 table perf.kwr_snap_sql_wait
 table perf.kwr_snap_table
 table perf.kwr_snap_table_total
 table perf.kwr_snap_userfunc
 table perf.kwr_snap_userfunc_total
 table perf.kwr_stmt_list
 table perf.kwr_table_list
 table perf.kwr_ufunc_list
 type perf.ksh_data_type
 type perf.ksh_mem_op
 type perf.ksh_sampling
 view dba_hist_active_sess_history
 view perf.kwr_snap_index_view
 view perf.kwr_snap_settings_view
 view perf.kwr_snap_sql_stmt_view
 view perf.kwr_snap_table_view
 view perf.kwr_snap_userfunc_view
 view perf.session_history
 view perf.sys_sql_profile
 view "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 ，默认为off

track_instance: 开启统计实例级IO，关键活动，锁，默认为off

track_wait_timing: 开启累积式等待事件记录等待时间的功能，默认为on

track_counts: 统计数据库活动，默认为on

track_io_timing: 开启IO计时统计功能，默认为off

track_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、状态等，默认：on

sys_stat_statements.max: 设置sys_stat_statement跟踪的最大语句数，默认：5000

sys_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、状态等，默认：on

track_counts: 统计数据库活动，默认为on

track_sql: 开启统计 sql 时间，等待事件，IO ，默认为off

track_wait_timing: 开启累积式等待事件记录等待时间的功能

track_io_timing: 开启IO计时统计功能，默认为off

track_functions: 开启函数统计功能，推荐用 'all'，默认为'none'

*/

---

插件使用

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

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

自动快照

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.6
       2 | 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.6
       2 | 2023-06-26 07:43:07-07 |          1 | 1.6
       3 | 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 functions
 Schema |    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_file
                                                   List of functions
 Schema |        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_cn
                                           List of functions
 Schema |        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_html
                                          List of functions
 Schema |      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_cn
                                           List of functions
 Schema |        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_text
                                          List of functions
 Schema |      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起始快照ID

end_id结束快照ID

format报告的格式，只支持'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 functions
 Schema |    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_file
                                                                                                                                     List of functions
 Schema |        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 functions
 Schema |          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_snapshots
                                                                                   List of functions
 Schema |              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分钟，最大不超过60

slot_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_report
                                 List of functions
 Schema |    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_file
                                             List of functions
 Schema |        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起始快照ID

end_id结束快照ID

file_path在服务器上保存报告文件的全路径，必须有写文件的权限

*/