--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: omm
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: liuge; Type: SCHEMA; Schema: -; Owner: liuge
--
CREATE SCHEMA liuge;
ALTER SCHEMA liuge OWNER TO liuge;
--
-- Name: mppdbpermission; Type: SCHEMA; Schema: -; Owner: omm
--
CREATE SCHEMA mppdbpermission;
ALTER SCHEMA mppdbpermission OWNER TO omm;
--
-- Name: myschema; Type: SCHEMA; Schema: -; Owner: omm
--
CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO omm;
--
-- Name: pmk; Type: SCHEMA; Schema: -; Owner: omm
--
CREATE SCHEMA pmk;
ALTER SCHEMA pmk OWNER TO omm;
--
-- Name: xiniu2; Type: SCHEMA; Schema: -; Owner: xiniu2
--
CREATE SCHEMA xiniu2;
ALTER SCHEMA xiniu2 OWNER TO xiniu2;
SET search_path = pmk;
--
-- Name: check_node_type(); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION check_node_type() RETURNS text
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_node_type CHAR(1);
BEGIN
SELECT n.node_type
INTO l_node_type
FROM pgxc_node n, pg_settings s
WHERE s.name = 'pgxc_node_name'
AND n.node_name = s.setting;
IF l_node_type = 'D'
THEN
RETURN 'ERROR:: PMK commands can not be executed from data node. Please execute it from coordinator.';
ELSE
RETURN NULL;
END IF;
END;
$$;
ALTER FUNCTION pmk.check_node_type() OWNER TO omm;
--
-- Name: check_pmk_enabled(); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION check_pmk_enabled() RETURNS text
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_pmk_enabled_i TEXT;
BEGIN
SELECT UPPER(config_value)
INTO l_pmk_enabled_i
FROM pmk.pmk_configuration
WHERE config_param_name = 'Enable PMK';
IF l_pmk_enabled_i = 'FALSE'
THEN
RETURN 'ERROR:: PMK should be enabled to use the PMK features.';
ELSE
RETURN NULL;
END IF;
END;
$$;
ALTER FUNCTION pmk.check_pmk_enabled() OWNER TO omm;
--
-- Name: check_start_end_dates(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION check_start_end_dates(INOUT io_start_pmk_time timestamp with time zone, INOUT io_end_pmk_time timestamp with time zone, OUT o_error_message text) RETURNS record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_last_collect_time timestamp with time zone;
BEGIN
SELECT last_snapshot_collect_time
INTO l_last_collect_time
FROM pmk.pmk_meta_data;
IF io_start_pmk_time > l_last_collect_time
THEN
o_error_message := 'ERROR:: The from-time provided is greater than the last statistics-collection time(' || l_last_collect_time || '). Invalid value(s) provided for the input time-range';
RETURN;
END IF;
IF io_end_pmk_time IS NULL
THEN
io_end_pmk_time := l_last_collect_time;
IF io_start_pmk_time IS NULL
THEN
io_start_pmk_time := io_end_pmk_time;
END IF;
ELSE
IF (io_start_pmk_time IS NULL) OR
(io_start_pmk_time > io_end_pmk_time)
THEN
o_error_message := 'ERROR:: Invalid value(s) provided for the input time-range';
RETURN;
END IF;
END IF;
END;
$$;
ALTER FUNCTION pmk.check_start_end_dates(INOUT io_start_pmk_time timestamp with time zone, INOUT io_end_pmk_time timestamp with time zone, OUT o_error_message text) OWNER TO omm;
--
-- Name: configure_parameter(character varying, text); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION configure_parameter(i_config_param_name character varying, i_config_value text) RETURNS boolean
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_collect_count_value INT;
l_config_value TEXT;
l_upper_config_param TEXT;
l_error_message TEXT;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
l_upper_config_param := UPPER(TRIM(BOTH ' ' FROM i_config_param_name));
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
AND l_upper_config_param <> 'ENABLE PMK'
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
IF i_config_param_name IS NULL
THEN
l_error_message := 'ERROR:: Null should not be provided for configuration parameter name.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
IF i_config_value IS NULL
THEN
l_error_message := 'ERROR:: Null should not be provided for configuration value.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
IF l_upper_config_param = 'COLLECTION COUNT'
THEN
l_collect_count_value := i_config_value::int;
IF l_collect_count_value < -1
THEN
l_error_message := 'ERROR:: Configuration value "' || i_config_value || '" should not be less than -1.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
ELSIF l_collect_count_value = 0
THEN
l_error_message := 'ERROR:: 0 should not be provided since atleast one collection should be retained.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
ELSE
l_config_value := l_collect_count_value;
END IF;
ELSIF l_upper_config_param = 'ENABLE PMK'
THEN
l_config_value := UPPER(TRIM(BOTH ' ' FROM i_config_value));
IF l_config_value NOT IN ('TRUE', 'FALSE')
THEN
l_error_message := 'ERROR:: Allowed values are TRUE or FALSE for the configuration parameter "Enable PMK".';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
END IF;
SET allow_concurrent_tuple_update = ON;
UPDATE pmk.pmk_configuration
SET config_value = l_config_value
WHERE UPPER(config_param_name) = l_upper_config_param;
IF NOT FOUND THEN
l_error_message := 'ERROR:: Invalid configuration parameter "' || i_config_param_name || '" provided for configuring PMK parameter ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$;
ALTER FUNCTION pmk.configure_parameter(i_config_param_name character varying, i_config_value text) OWNER TO omm;
--
-- Name: delete_expired_snapshots(); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION delete_expired_snapshots() RETURNS void
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_collection_count INT;
l_retention_snapshot_id INT;
BEGIN
-- Deleting node statistics based on "collection count" config param
SELECT config_value
INTO l_collection_count
FROM pmk.pmk_configuration
WHERE config_param_name = 'Collection Count';
IF l_collection_count > -1
THEN
IF l_collection_count = 0
THEN
l_collection_count := 1;
END IF;
SELECT MIN(snapshot_id)
INTO l_retention_snapshot_id
FROM ( SELECT snapshot_id
FROM pmk.pmk_snapshot
ORDER BY snapshot_id DESC
LIMIT l_collection_count );
DELETE FROM pmk.pmk_snapshot_datanode_stat
WHERE snapshot_id < l_retention_snapshot_id;
DELETE FROM pmk.pmk_snapshot_coordinator_stat
WHERE snapshot_id < l_retention_snapshot_id;
DELETE FROM pmk.pmk_snapshot
WHERE snapshot_id < l_retention_snapshot_id;
END IF;
END;
$$;
ALTER FUNCTION pmk.delete_expired_snapshots() OWNER TO omm;
--
-- Name: find_node_stat(boolean); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION find_node_stat(i_skip_supper_role boolean, OUT o_number_of_files_1 integer, OUT o_physical_reads_1 bigint, OUT o_physical_writes_1 bigint, OUT o_read_time_1 bigint, OUT o_write_time_1 bigint, OUT o_physical_memory_1 bigint, OUT o_shared_buffer_size_1 bigint, OUT o_session_memory_total_size_1 bigint, OUT o_session_memory_used_size_1 bigint, OUT o_blocks_read_1 bigint, OUT o_blocks_hit_1 bigint, OUT o_db_size_1 bigint, OUT o_work_memory_size_1 bigint, OUT o_sorts_in_memory_1 bigint, OUT o_sorts_in_disk_1 bigint, OUT o_active_sql_count_1 integer, OUT o_wait_sql_count_1 integer, OUT o_session_count_1 integer, OUT o_busy_time_1 numeric, OUT o_idle_time_1 numeric, OUT o_iowait_time_1 numeric, OUT o_db_cpu_time_1 numeric, OUT o_db_memory_usage_1 bigint, OUT o_node_startup_time_1 timestamp with time zone, OUT o_node_host_name_1 text, OUT o_xact_commit_1 bigint, OUT o_xact_rollback_1 bigint, OUT o_checkpoints_timed_1 bigint, OUT o_checkpoints_req_1 bigint, OUT o_checkpoint_write_time_1 double precision) RETURNS record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
BEGIN
SELECT o_number_of_files
, o_physical_reads
, o_physical_writes
, o_read_time
, o_write_time
, o_physical_memory
, o_shared_buffer_size
, o_session_memory_total_size
, o_session_memory_used_size
, o_blocks_read
, o_blocks_hit
, o_db_size
, o_work_memory_size
, o_sorts_in_memory
, o_sorts_in_disk
, o_active_sql_count
, o_wait_sql_count
, o_session_count
, o_busy_time
, o_idle_time
, o_iowait_time
, o_db_cpu_time
, o_db_memory_usage
, o_node_startup_time
, o_node_host_name
, o_xact_commit
, o_xact_rollback
, o_checkpoints_timed
, o_checkpoints_req
, o_checkpoint_write_time
INTO o_number_of_files_1
, o_physical_reads_1
, o_physical_writes_1
, o_read_time_1
, o_write_time_1
, o_physical_memory_1
, o_shared_buffer_size_1
, o_session_memory_total_size_1
, o_session_memory_used_size_1
, o_blocks_read_1
, o_blocks_hit_1
, o_db_size_1
, o_work_memory_size_1
, o_sorts_in_memory_1
, o_sorts_in_disk_1
, o_active_sql_count_1
, o_wait_sql_count_1
, o_session_count_1
, o_busy_time_1
, o_idle_time_1
, o_iowait_time_1
, o_db_cpu_time_1
, o_db_memory_usage_1
, o_node_startup_time_1
, o_node_host_name_1
, o_xact_commit_1
, o_xact_rollback_1
, o_checkpoints_timed_1
, o_checkpoints_req_1
, o_checkpoint_write_time_1
FROM pmk.find_perf_stat(i_skip_supper_role);
END;
$$;
ALTER FUNCTION pmk.find_node_stat(i_skip_supper_role boolean, OUT o_number_of_files_1 integer, OUT o_physical_reads_1 bigint, OUT o_physical_writes_1 bigint, OUT o_read_time_1 bigint, OUT o_write_time_1 bigint, OUT o_physical_memory_1 bigint, OUT o_shared_buffer_size_1 bigint, OUT o_session_memory_total_size_1 bigint, OUT o_session_memory_used_size_1 bigint, OUT o_blocks_read_1 bigint, OUT o_blocks_hit_1 bigint, OUT o_db_size_1 bigint, OUT o_work_memory_size_1 bigint, OUT o_sorts_in_memory_1 bigint, OUT o_sorts_in_disk_1 bigint, OUT o_active_sql_count_1 integer, OUT o_wait_sql_count_1 integer, OUT o_session_count_1 integer, OUT o_busy_time_1 numeric, OUT o_idle_time_1 numeric, OUT o_iowait_time_1 numeric, OUT o_db_cpu_time_1 numeric, OUT o_db_memory_usage_1 bigint, OUT o_node_startup_time_1 timestamp with time zone, OUT o_node_host_name_1 text, OUT o_xact_commit_1 bigint, OUT o_xact_rollback_1 bigint, OUT o_checkpoints_timed_1 bigint, OUT o_checkpoints_req_1 bigint, OUT o_checkpoint_write_time_1 double precision) OWNER TO omm;
--
-- Name: find_perf_stat(boolean); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION find_perf_stat(i_skip_supper_role boolean, OUT o_number_of_files integer, OUT o_physical_reads bigint, OUT o_physical_writes bigint, OUT o_read_time bigint, OUT o_write_time bigint, OUT o_physical_memory bigint, OUT o_shared_buffer_size bigint, OUT o_session_memory_total_size bigint, OUT o_session_memory_used_size bigint, OUT o_blocks_read bigint, OUT o_blocks_hit bigint, OUT o_db_size bigint, OUT o_work_memory_size bigint, OUT o_sorts_in_memory bigint, OUT o_sorts_in_disk bigint, OUT o_active_sql_count integer, OUT o_wait_sql_count integer, OUT o_session_count integer, OUT o_busy_time numeric, OUT o_idle_time numeric, OUT o_iowait_time numeric, OUT o_db_cpu_time numeric, OUT o_db_memory_usage bigint, OUT o_node_startup_time timestamp with time zone, OUT o_node_host_name text, OUT o_xact_commit bigint, OUT o_xact_rollback bigint, OUT o_checkpoints_timed bigint, OUT o_checkpoints_req bigint, OUT o_checkpoint_write_time double precision) RETURNS record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE
l_block_size int;
l_record_chk int;
BEGIN
o_node_startup_time := pg_postmaster_start_time();
o_node_host_name := get_hostname();
SELECT COUNT(*) AS number_of_files
, SUM(phyrds) AS physical_reads
, SUM(phywrts) AS physical_writes
, SUM(readtim) AS read_time
, SUM(writetim) AS write_time
INTO o_number_of_files
, o_physical_reads
, o_physical_writes
, o_read_time
, o_write_time
FROM pv_file_stat;
IF o_number_of_files = 0
THEN
o_physical_reads := 0;
o_physical_writes := 0;
o_read_time := 0;
o_write_time := 0;
END IF;
WITH os_stat AS
(
SELECT os.name AS statname
, os.value AS statvalue
FROM pv_os_run_info os
WHERE os.name IN ( 'PHYSICAL_MEMORY_BYTES', 'BUSY_TIME', 'IDLE_TIME', 'IOWAIT_TIME' )
)
SELECT (SELECT statvalue FROM os_stat WHERE statname = 'PHYSICAL_MEMORY_BYTES')
, (SELECT statvalue FROM os_stat WHERE statname = 'BUSY_TIME')
, (SELECT statvalue FROM os_stat WHERE statname = 'IDLE_TIME')
, (SELECT statvalue FROM os_stat WHERE statname = 'IOWAIT_TIME')
INTO o_physical_memory
, o_busy_time
, o_idle_time
, o_iowait_time
FROM DUAL;
-- pv_db_time is not available; temporarily PMK extension is used.
o_db_cpu_time := total_cpu();
o_db_memory_usage := total_memory()*1024;
WITH config_value AS
( SELECT name
, setting::bigint AS config_value
FROM pg_settings
WHERE name IN ( 'block_size', 'shared_buffers', 'work_mem' )
)
, config_value1 AS
( SELECT (SELECT config_value FROM config_value WHERE name = 'block_size') AS block_size
, (SELECT config_value FROM config_value WHERE name = 'shared_buffers') AS shared_buffers
, (SELECT config_value FROM config_value WHERE name = 'work_mem') AS work_mem
FROM DUAL
)
SELECT block_size
, (shared_buffers * block_size)::bigint
, (work_mem * 1024)::bigint
INTO l_block_size
, o_shared_buffer_size
, o_work_memory_size
FROM config_value1;
/* Commented since these statistics are not used for node and cluster reports
*/
o_session_memory_total_size := 0;
o_session_memory_used_size := 0;
SELECT SUM(blks_read)::bigint
, SUM(blks_hit)::bigint
, SUM(xact_commit)::bigint
, SUM(xact_rollback)::bigint
INTO o_blocks_read
, o_blocks_hit
, o_xact_commit
, o_xact_rollback
FROM pg_stat_database;
o_db_size := 0;
IF i_skip_supper_role = 'TRUE'
THEN
WITH session_state AS
( SELECT state, waiting , usename
FROM pg_stat_activity a, pg_roles r
WHERE r.rolsuper = 'f' AND a.usename = r.rolname
)
, active_session AS
( SELECT state, waiting , usename
FROM session_state s, pg_roles r
WHERE s.state IN ('active', 'fastpath function call', 'retrying')
AND r.rolsuper = 'f' AND s.usename = r.rolname
)
SELECT ( SELECT COUNT(*) FROM active_session )
, ( SELECT COUNT(*) FROM active_session WHERE waiting = TRUE )
, ( SELECT COUNT(*) FROM session_state )
INTO o_active_sql_count, o_wait_sql_count , o_session_count
FROM DUAL;
ELSE
WITH session_state AS
( SELECT state, waiting
FROM pg_stat_activity
)
, active_session AS
( SELECT state, waiting
FROM session_state
WHERE state IN ('active', 'fastpath function call', 'retrying')
)
SELECT ( SELECT COUNT(*) FROM active_session )
, ( SELECT COUNT(*) FROM active_session WHERE waiting = TRUE )
, ( SELECT COUNT(*) FROM session_state )
INTO o_active_sql_count, o_wait_sql_count, o_session_count
FROM DUAL;
END IF;
-- Currently, the below statistics are calculated from pv_session_stat (which is not accurate) since pv_db_stat is not available
WITH sort_state AS
( SELECT statname
, SUM(value)::bigint AS sorts_cnt
FROM pv_session_stat
WHERE statname IN ('n_sort_in_memory', 'n_sort_in_disk')
GROUP BY statname
)
SELECT (SELECT sorts_cnt FROM sort_state WHERE statname = 'n_sort_in_memory')
, (SELECT sorts_cnt FROM sort_state WHERE statname = 'n_sort_in_disk')
INTO o_sorts_in_memory
, o_sorts_in_disk
FROM DUAL;
SELECT SUM(checkpoints_timed)::bigint
, SUM(checkpoints_req)::bigint
, SUM(checkpoint_write_time)::bigint
INTO o_checkpoints_timed
, o_checkpoints_req
, o_checkpoint_write_time
FROM pg_stat_bgwriter;
END;
$$;
ALTER FUNCTION pmk.find_perf_stat(i_skip_supper_role boolean, OUT o_number_of_files integer, OUT o_physical_reads bigint, OUT o_physical_writes bigint, OUT o_read_time bigint, OUT o_write_time bigint, OUT o_physical_memory bigint, OUT o_shared_buffer_size bigint, OUT o_session_memory_total_size bigint, OUT o_session_memory_used_size bigint, OUT o_blocks_read bigint, OUT o_blocks_hit bigint, OUT o_db_size bigint, OUT o_work_memory_size bigint, OUT o_sorts_in_memory bigint, OUT o_sorts_in_disk bigint, OUT o_active_sql_count integer, OUT o_wait_sql_count integer, OUT o_session_count integer, OUT o_busy_time numeric, OUT o_idle_time numeric, OUT o_iowait_time numeric, OUT o_db_cpu_time numeric, OUT o_db_memory_usage bigint, OUT o_node_startup_time timestamp with time zone, OUT o_node_host_name text, OUT o_xact_commit bigint, OUT o_xact_rollback bigint, OUT o_checkpoints_timed bigint, OUT o_checkpoints_req bigint, OUT o_checkpoint_write_time double precision) OWNER TO omm;
--
-- Name: get_cluster_active_sql_count(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_active_sql_count(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_active_sql_count integer, OUT o_avg_active_sql_count numeric, OUT o_min_active_sql_count integer, OUT o_max_active_sql_count integer) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of active SQL count statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, tot_active_sql_count
, avg_active_sql_count
, min_active_sql_count
, max_active_sql_count
FROM ( SELECT s.pmk_curr_collect_start_time
, SUM(active_sql_count)::int AS tot_active_sql_count
, ROUND(AVG(active_sql_count), 2)::numeric(9, 2) AS avg_active_sql_count
, MIN(active_sql_count)::int AS min_active_sql_count
, MAX(active_sql_count)::int AS max_active_sql_count
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
GROUP BY s.pmk_curr_collect_start_time
)
ORDER BY stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_active_sql_count(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_active_sql_count integer, OUT o_avg_active_sql_count numeric, OUT o_min_active_sql_count integer, OUT o_max_active_sql_count integer) OWNER TO omm;
--
-- Name: get_cluster_disk_usage_stat(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_disk_usage_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, i_db_size text, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_datanode_db_size text, OUT o_max_datanode_db_size text, OUT o_tot_physical_writes bigint, OUT o_max_node_physical_writes bigint, OUT o_max_node_write_per_sec numeric, OUT o_avg_write_per_sec numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
DECLARE l_db_size bigint;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster disk usage statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
IF i_db_size = '0'
THEN
SELECT SUM(pg_database_size(oid))::bigint
INTO l_db_size
FROM pg_database;
ELSE
SELECT SUM(i_db_size)::bigint
INTO l_db_size;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
, disk_stat AS
(
SELECT s.pmk_curr_collect_start_time
, db_size
, physical_writes_delta
, write_time_delta
, ( physical_writes_delta * 1000000.0 / NULLIF(write_time_delta, 0) )::numeric(20,2) AS node_write_per_sec
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, pg_size_pretty(tot_datanode_db_size) AS tot_datanode_db_size
, pg_size_pretty(max_datanode_db_size) AS max_datanode_db_size
, tot_physical_writes
, max_node_physical_writes
, max_node_write_per_sec
, ( tot_physical_writes * 1000000.0 / NULLIF(tot_write_time, 0) )::numeric(20,2) AS avg_write_per_sec
FROM ( SELECT pmk_curr_collect_start_time
, l_db_size::bigint AS tot_datanode_db_size
, MAX(db_size)::bigint AS max_datanode_db_size
, SUM(physical_writes_delta)::bigint AS tot_physical_writes
, SUM(write_time_delta)::bigint AS tot_write_time
, MAX(physical_writes_delta)::bigint AS max_node_physical_writes
, MAX(node_write_per_sec) AS max_node_write_per_sec
FROM disk_stat
GROUP BY pmk_curr_collect_start_time
)
ORDER BY pmk_curr_collect_start_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_disk_usage_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, i_db_size text, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_datanode_db_size text, OUT o_max_datanode_db_size text, OUT o_tot_physical_writes bigint, OUT o_max_node_physical_writes bigint, OUT o_max_node_write_per_sec numeric, OUT o_avg_write_per_sec numeric) OWNER TO omm;
--
-- Name: get_cluster_host_cpu_stat(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_host_cpu_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_avg_cpu_total_time numeric, OUT o_avg_cpu_busy_time numeric, OUT o_avg_cpu_iowait_time numeric, OUT o_cpu_busy_perc numeric, OUT o_cpu_io_wait_perc numeric, OUT o_min_cpu_busy_perc numeric, OUT o_max_cpu_busy_perc numeric, OUT o_min_cpu_iowait_perc numeric, OUT o_max_cpu_iowait_perc numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster host CPU statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
, os_cpu_stat AS
( SELECT s.pmk_curr_collect_start_time
, node_host
, node_name
, (busy_time_delta * 10) AS cpu_busy_time
, (idle_time_delta * 10) AS cpu_idle_time
, (iowait_time_delta * 10) AS cpu_iowait_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT s.pmk_curr_collect_start_time
, node_host
, node_name
, (busy_time_delta * 10) AS cpu_busy_time
, (idle_time_delta * 10) AS cpu_idle_time
, (iowait_time_delta * 10) AS cpu_iowait_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
, os_cpu_stat1 AS
( SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, node_host
, cpu_busy_time
, cpu_idle_time
, cpu_iowait_time
, (cpu_busy_time+cpu_idle_time+cpu_iowait_time)::numeric AS cpu_total_time
FROM ( SELECT pmk_curr_collect_start_time
, node_host
, cpu_busy_time
, cpu_idle_time
, cpu_iowait_time
, rank() OVER (PARTITION BY pmk_curr_collect_start_time, node_host ORDER BY cpu_busy_time DESC, node_name) AS node_cpu_busy_order
FROM os_cpu_stat
)
WHERE node_cpu_busy_order = 1
)
SELECT hcs.stat_collect_time
, AVG(hcs.cpu_total_time)::numeric(21, 3) AS avg_cpu_total_time
, AVG(hcs.cpu_busy_time)::numeric(21, 3) AS avg_cpu_busy_time
, AVG(hcs.cpu_iowait_time)::numeric(21, 3) AS avg_cpu_iowait_time
, ( (SUM(cpu_busy_time) * 100.0) / NULLIF(SUM(cpu_total_time), 0) )::numeric(5, 2) AS cpu_busy_perc
, ( (SUM(cpu_iowait_time) * 100.0) / NULLIF(SUM(cpu_total_time), 0) )::numeric(5, 2) AS cpu_io_wait_perc
, MIN(hcs.cpu_busy_time_perc)::numeric(5, 2) AS min_cpu_busy_perc
, MAX(hcs.cpu_busy_time_perc)::numeric(5, 2) AS max_cpu_busy_perc
, MIN(hcs.cpu_iowait_time_perc)::numeric(5, 2) AS min_cpu_iowait_perc
, MAX(hcs.cpu_iowait_time_perc)::numeric(5, 2) AS max_cpu_iowait_perc
FROM ( SELECT node_host
, stat_collect_time
, cpu_total_time
, cpu_busy_time
, cpu_iowait_time
, ( (cpu_busy_time * 100.0) / NULLIF(cpu_total_time, 0) )::numeric(5, 2) AS cpu_busy_time_perc
, ( (cpu_iowait_time * 100.0) / NULLIF(cpu_total_time, 0) )::numeric(5, 2) AS cpu_iowait_time_perc
FROM os_cpu_stat1 ) hcs
GROUP BY hcs.stat_collect_time
ORDER BY hcs.stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_host_cpu_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_avg_cpu_total_time numeric, OUT o_avg_cpu_busy_time numeric, OUT o_avg_cpu_iowait_time numeric, OUT o_cpu_busy_perc numeric, OUT o_cpu_io_wait_perc numeric, OUT o_min_cpu_busy_perc numeric, OUT o_max_cpu_busy_perc numeric, OUT o_min_cpu_iowait_perc numeric, OUT o_max_cpu_iowait_perc numeric) OWNER TO omm;
--
-- Name: get_cluster_io_stat(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_io_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_number_of_files integer, OUT o_physical_reads bigint, OUT o_physical_writes bigint, OUT o_read_time bigint, OUT o_write_time bigint, OUT o_avg_read_per_sec numeric, OUT o_avg_read_time numeric, OUT o_avg_write_per_sec numeric, OUT o_avg_write_time numeric, OUT o_min_node_read_per_sec numeric, OUT o_max_node_read_per_sec numeric, OUT o_min_node_read_time numeric, OUT o_max_node_read_time numeric, OUT o_min_node_write_per_sec numeric, OUT o_max_node_write_per_sec numeric, OUT o_min_node_write_time numeric, OUT o_max_node_write_time numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster I/O statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, number_of_files
, physical_reads
, physical_writes
, read_time
, write_time
, ( physical_reads * 1000000.0 / NULLIF(read_time, 0) )::numeric(20,2) AS avg_read_per_sec
, ( read_time * 1.0 / NULLIF(physical_reads, 0) )::numeric(20,3) AS avg_read_time
, ( physical_writes * 1000000.0 / NULLIF(write_time, 0) )::numeric(20,2) AS avg_write_per_sec
, ( write_time * 1.0 / NULLIF(physical_writes, 0) )::numeric(20,3) AS avg_write_time
, min_node_read_per_sec
, max_node_read_per_sec
, min_node_read_time
, max_node_read_time
, min_node_write_per_sec
, max_node_write_per_sec
, min_node_write_time
, max_node_write_time
FROM ( SELECT pmk_curr_collect_start_time
, SUM(number_of_files)::int AS number_of_files
, SUM(physical_reads_delta)::bigint AS physical_reads
, SUM(physical_writes_delta)::bigint AS physical_writes
, SUM(read_time_delta)::bigint AS read_time
, SUM(write_time_delta)::bigint AS write_time
, MIN(node_read_per_sec) AS min_node_read_per_sec
, MAX(node_read_per_sec) AS max_node_read_per_sec
, MIN(node_read_time) AS min_node_read_time
, MAX(node_read_time) AS max_node_read_time
, MIN(node_write_per_sec) AS min_node_write_per_sec
, MAX(node_write_per_sec) AS max_node_write_per_sec
, MIN(node_write_time) AS min_node_write_time
, MAX(node_write_time) AS max_node_write_time
FROM ( SELECT s.pmk_curr_collect_start_time
, node_name
, number_of_files
, physical_reads_delta
, physical_writes_delta
, read_time_delta
, write_time_delta
, ( physical_reads_delta * 1000000.0 / NULLIF(read_time_delta, 0) )::numeric(20,2) AS node_read_per_sec
, ( read_time_delta * 1.0 / NULLIF(physical_reads_delta, 0) )::numeric(20,3) AS node_read_time
, ( physical_writes_delta * 1000000.0 / NULLIF(write_time_delta, 0) )::numeric(20,2) AS node_write_per_sec
, ( write_time_delta * 1.0 / NULLIF(physical_writes_delta, 0) )::numeric(20,3) AS node_write_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT s.pmk_curr_collect_start_time
, node_name
, number_of_files
, physical_reads_delta
, physical_writes_delta
, read_time_delta
, write_time_delta
, ( physical_reads_delta * 1000000.0 / NULLIF(read_time_delta, 0) )::numeric(20,2) AS node_read_per_sec
, ( read_time_delta * 1.0 / NULLIF(physical_reads_delta, 0) )::numeric(20,3) AS node_read_time
, ( physical_writes_delta * 1000000.0 / NULLIF(write_time_delta, 0) )::numeric(20,2) AS node_write_per_sec
, ( write_time_delta * 1.0 / NULLIF(physical_writes_delta, 0) )::numeric(20,3) AS node_write_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
GROUP BY pmk_curr_collect_start_time
)
ORDER BY stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_io_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_number_of_files integer, OUT o_physical_reads bigint, OUT o_physical_writes bigint, OUT o_read_time bigint, OUT o_write_time bigint, OUT o_avg_read_per_sec numeric, OUT o_avg_read_time numeric, OUT o_avg_write_per_sec numeric, OUT o_avg_write_time numeric, OUT o_min_node_read_per_sec numeric, OUT o_max_node_read_per_sec numeric, OUT o_min_node_read_time numeric, OUT o_max_node_read_time numeric, OUT o_min_node_write_per_sec numeric, OUT o_max_node_write_per_sec numeric, OUT o_min_node_write_time numeric, OUT o_max_node_write_time numeric) OWNER TO omm;
--
-- Name: get_cluster_memory_sort_stat(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_memory_sort_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_memory_sort_ratio numeric, OUT o_min_memory_sort_ratio numeric, OUT o_max_memory_sort_ratio numeric, OUT o_total_memory_sorts bigint, OUT o_total_disk_sorts bigint) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster memory sort statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, ( (total_memory_sorts * 100.0) / NULLIF(total_disk_sorts+total_memory_sorts, 0) )::numeric(5, 2) AS memory_sort_ratio
, min_memory_sort_ratio
, max_memory_sort_ratio
, total_memory_sorts
, total_disk_sorts
FROM ( SELECT pmk_curr_collect_start_time
, SUM(memory_sorts)::bigint AS total_memory_sorts
, SUM(disk_sorts)::bigint AS total_disk_sorts
, MIN(memory_sort_ratio)::numeric(5, 2) AS min_memory_sort_ratio
, MAX(memory_sort_ratio)::numeric(5, 2) AS max_memory_sort_ratio
FROM ( SELECT s.pmk_curr_collect_start_time
, node_name
, sorts_in_memory_delta AS memory_sorts
, sorts_in_disk_delta AS disk_sorts
, ( (sorts_in_memory_delta * 100.0) / NULLIF((sorts_in_disk_delta + sorts_in_memory_delta), 0) )::numeric(5, 2) AS memory_sort_ratio
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT s.pmk_curr_collect_start_time
, node_name
, sorts_in_memory_delta AS memory_sorts
, sorts_in_disk_delta AS disk_sorts
, ( (sorts_in_memory_delta * 100.0) / NULLIF((sorts_in_disk_delta + sorts_in_memory_delta), 0) )::numeric(5, 2) AS memory_sort_ratio
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
GROUP BY pmk_curr_collect_start_time
)
ORDER BY stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_memory_sort_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_memory_sort_ratio numeric, OUT o_min_memory_sort_ratio numeric, OUT o_max_memory_sort_ratio numeric, OUT o_total_memory_sorts bigint, OUT o_total_disk_sorts bigint) OWNER TO omm;
--
-- Name: get_cluster_mppdb_cpu_stat(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_mppdb_cpu_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_avg_mppdb_cpu_time numeric, OUT o_avg_host_cpu_busy_time numeric, OUT o_avg_host_cpu_total_time numeric, OUT o_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_mppdb_cpu_time_perc_wrt_total_time numeric, OUT o_min_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_max_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_min_mppdb_cpu_time_perc_wrt_total_time numeric, OUT o_max_mppdb_cpu_time_perc_wrt_total_time numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster MPPDB CPU statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
, cpu_stat AS
( SELECT s.snapshot_id
, s.pmk_curr_collect_start_time
, dns.node_name
, dns.node_host
, (dns.busy_time_delta * 10) AS host_cpu_busy_time
, (dns.idle_time_delta * 10) AS host_cpu_idle_time
, (dns.iowait_time_delta * 10) AS host_cpu_iowait_time
, (dns.db_cpu_time_delta * 10) AS mppdb_cpu_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT s.snapshot_id
, s.pmk_curr_collect_start_time
, dns.node_name
, dns.node_host
, (dns.busy_time_delta * 10) AS host_cpu_busy_time
, (dns.idle_time_delta * 10) AS host_cpu_idle_time
, (dns.iowait_time_delta * 10) AS host_cpu_iowait_time
, (dns.db_cpu_time_delta * 10) AS mppdb_cpu_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
, host_cpu_stat AS
( SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, node_host
, host_cpu_busy_time
, host_cpu_idle_time
, host_cpu_iowait_time
, (host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time)::numeric AS host_cpu_total_time
FROM ( SELECT pmk_curr_collect_start_time
, node_host
, host_cpu_busy_time
, host_cpu_idle_time
, host_cpu_iowait_time
, rank() OVER (PARTITION BY snapshot_id, node_host
ORDER BY host_cpu_busy_time DESC, node_name) AS node_cpu_busy_order
FROM cpu_stat
)
WHERE node_cpu_busy_order = 1
)
, host_cpu_stat_summary AS
( SELECT stat_collect_time
, AVG(host_cpu_busy_time)::numeric(21, 3) AS avg_host_cpu_busy_time
, AVG(host_cpu_total_time)::numeric(21, 3) AS avg_host_cpu_total_time
, SUM(host_cpu_busy_time)::numeric(21, 3) AS tot_host_cpu_busy_time
, SUM(host_cpu_total_time)::numeric(21, 3) AS tot_host_cpu_total_time
FROM host_cpu_stat
GROUP BY stat_collect_time
)
, mppdb_cpu_stat0 AS
( SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, node_name
, mppdb_cpu_time
, host_cpu_busy_time
, (host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time)::numeric AS host_cpu_total_time
FROM cpu_stat
)
, mppdb_cpu_stat AS
( SELECT stat_collect_time
, node_name
, mppdb_cpu_time
, ( (mppdb_cpu_time * 100.0) / NULLIF(host_cpu_busy_time, 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_busy_time
, ( (mppdb_cpu_time * 100.0) / NULLIF(host_cpu_total_time, 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_total_time
FROM mppdb_cpu_stat0
)
, mppdb_cpu_stat_summary AS
( SELECT stat_collect_time
, AVG(mppdb_cpu_time)::numeric(21, 3) AS avg_mppdb_cpu_time
, SUM(mppdb_cpu_time)::numeric(21, 3) AS tot_mppdb_cpu_time
, MIN(mppdb_cpu_time_perc_wrt_busy_time)::numeric(5, 2) AS min_mppdb_cpu_time_perc_wrt_busy_time
, MAX(mppdb_cpu_time_perc_wrt_busy_time)::numeric(5, 2) AS max_mppdb_cpu_time_perc_wrt_busy_time
, MIN(mppdb_cpu_time_perc_wrt_total_time)::numeric(5, 2) AS min_mppdb_cpu_time_perc_wrt_total_time
, MAX(mppdb_cpu_time_perc_wrt_total_time)::numeric(5, 2) AS max_mppdb_cpu_time_perc_wrt_total_time
FROM mppdb_cpu_stat
GROUP BY stat_collect_time
)
SELECT mcs.stat_collect_time
, mcs.avg_mppdb_cpu_time
, hcs.avg_host_cpu_busy_time
, hcs.avg_host_cpu_total_time
, CASE WHEN mcs.tot_mppdb_cpu_time < hcs.tot_host_cpu_busy_time
THEN ( (mcs.tot_mppdb_cpu_time * 100.0) / NULLIF(hcs.tot_host_cpu_busy_time, 0) )::numeric(5, 2)
ELSE 100.00
END AS mppdb_cpu_time_perc_wrt_busy_time
, CASE WHEN mcs.tot_mppdb_cpu_time < hcs.tot_host_cpu_total_time
THEN ( (mcs.tot_mppdb_cpu_time * 100.0) / NULLIF(hcs.tot_host_cpu_total_time, 0) )::numeric(5, 2)
ELSE 100.00
END AS mppdb_cpu_time_perc_wrt_total_time
, mcs.min_mppdb_cpu_time_perc_wrt_busy_time
, mcs.max_mppdb_cpu_time_perc_wrt_busy_time
, mcs.min_mppdb_cpu_time_perc_wrt_total_time
, mcs.max_mppdb_cpu_time_perc_wrt_total_time
FROM mppdb_cpu_stat_summary mcs
, host_cpu_stat_summary hcs
WHERE mcs.stat_collect_time = hcs.stat_collect_time
ORDER BY mcs.stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_mppdb_cpu_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_avg_mppdb_cpu_time numeric, OUT o_avg_host_cpu_busy_time numeric, OUT o_avg_host_cpu_total_time numeric, OUT o_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_mppdb_cpu_time_perc_wrt_total_time numeric, OUT o_min_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_max_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_min_mppdb_cpu_time_perc_wrt_total_time numeric, OUT o_max_mppdb_cpu_time_perc_wrt_total_time numeric) OWNER TO omm;
--
-- Name: get_cluster_session_count(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_session_count(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_session_count integer, OUT o_avg_session_count numeric, OUT o_min_session_count integer, OUT o_max_session_count integer) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of session count statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, tot_session_count
, avg_session_count
, min_session_count
, max_session_count
FROM ( SELECT s.pmk_curr_collect_start_time
, SUM(session_count)::int AS tot_session_count
, ROUND(AVG(session_count), 2)::numeric(9, 2) AS avg_session_count
, MIN(session_count)::int AS min_session_count
, MAX(session_count)::int AS max_session_count
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
GROUP BY s.pmk_curr_collect_start_time
)
ORDER BY stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_session_count(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_tot_session_count integer, OUT o_avg_session_count numeric, OUT o_min_session_count integer, OUT o_max_session_count integer) OWNER TO omm;
--
-- Name: get_cluster_shared_buffer_stat(timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_cluster_shared_buffer_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_shared_buffer_hit_ratio numeric, OUT o_min_shared_buffer_hit_ratio numeric, OUT o_max_shared_buffer_hit_ratio numeric, OUT o_total_blocks_read bigint, OUT o_total_blocks_hit bigint) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of cluster shared buffer statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT pmk_curr_collect_start_time::timestamp AS stat_collect_time
, ( (total_blocks_hit * 100.0) / NULLIF(total_blocks_read+total_blocks_hit, 0) )::numeric(5, 2) AS shared_buffer_hit_ratio
, min_shared_buffer_hit_ratio
, max_shared_buffer_hit_ratio
, total_blocks_read
, total_blocks_hit
FROM ( SELECT pmk_curr_collect_start_time
, SUM(blocks_read)::bigint AS total_blocks_read
, SUM(blocks_hit)::bigint AS total_blocks_hit
, MIN(shared_buffer_hit_ratio)::numeric(5, 2) AS min_shared_buffer_hit_ratio
, MAX(shared_buffer_hit_ratio)::numeric(5, 2) AS max_shared_buffer_hit_ratio
FROM ( SELECT s.pmk_curr_collect_start_time
, node_name
, blocks_read_delta AS blocks_read
, blocks_hit_delta AS blocks_hit
, ( (blocks_hit_delta * 100.0) / NULLIF((blocks_read_delta + blocks_hit_delta), 0) )::numeric(5, 2) AS shared_buffer_hit_ratio
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT s.pmk_curr_collect_start_time
, node_name
, blocks_read_delta AS blocks_read
, blocks_hit_delta AS blocks_hit
, ( (blocks_hit_delta * 100.0) / NULLIF((blocks_read_delta + blocks_hit_delta), 0) )::numeric(5, 2) AS shared_buffer_hit_ratio
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
GROUP BY pmk_curr_collect_start_time
)
ORDER BY stat_collect_time;
END;
$$;
ALTER FUNCTION pmk.get_cluster_shared_buffer_stat(i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_stat_collect_time timestamp without time zone, OUT o_shared_buffer_hit_ratio numeric, OUT o_min_shared_buffer_hit_ratio numeric, OUT o_max_shared_buffer_hit_ratio numeric, OUT o_total_blocks_read bigint, OUT o_total_blocks_hit bigint) OWNER TO omm;
--
-- Name: get_configuration_parameter(text); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_configuration_parameter(i_config_param_name text) RETURNS TABLE(config_param_name character varying, config_value text)
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_upper_config_param TEXT;
l_error_message TEXT;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_upper_config_param := UPPER(TRIM(BOTH ' ' FROM i_config_param_name));
IF l_upper_config_param = 'ALL'
THEN
RETURN QUERY
SELECT config_param_name
, config_value
FROM pmk.pmk_configuration
ORDER BY config_param_name;
ELSE
RETURN QUERY
SELECT config_param_name
, config_value
FROM pmk.pmk_configuration
WHERE UPPER(config_param_name) = l_upper_config_param;
END IF;
END;
$$;
ALTER FUNCTION pmk.get_configuration_parameter(i_config_param_name text) OWNER TO omm;
--
-- Name: get_meta_data(); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_meta_data(OUT l_pmk_curr_collect_start_time timestamp with time zone, OUT l_pmk_last_collect_start_time timestamp with time zone, OUT l_last_snapshot_id integer) RETURNS record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_error_message TEXT;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
SELECT last_snapshot_id, last_snapshot_collect_time
INTO l_last_snapshot_id, l_pmk_last_collect_start_time
FROM pmk.pmk_meta_data;
l_pmk_curr_collect_start_time := date_trunc('second', current_timestamp);
IF l_pmk_curr_collect_start_time < l_pmk_last_collect_start_time
THEN
l_error_message := 'ERROR:: There is a change in system time of Gauss MPPDB host. PMK does not support the scenarios related to system time change.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
ELSIF l_pmk_curr_collect_start_time = l_pmk_last_collect_start_time
THEN
l_error_message := 'ERROR:: Multiple statistics-collections can not be done within a second.';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
END;
$$;
ALTER FUNCTION pmk.get_meta_data(OUT l_pmk_curr_collect_start_time timestamp with time zone, OUT l_pmk_last_collect_start_time timestamp with time zone, OUT l_last_snapshot_id integer) OWNER TO omm;
--
-- Name: get_node_cpu_stat(text, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_node_cpu_stat(i_node_name text, i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_node_type character, OUT o_node_name text, OUT o_node_host text, OUT o_stat_collect_time timestamp without time zone, OUT o_mppdb_cpu_time bigint, OUT o_host_cpu_busy_time bigint, OUT o_host_cpu_total_time bigint, OUT o_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_mppdb_cpu_time_perc_wrt_total_time numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_node_type char(1);
l_node_name text;
l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_node_name := UPPER(i_node_name);
IF l_node_name <> 'ALL'
THEN
SELECT MAX(node_type)
INTO l_node_type
FROM pgxc_node
WHERE UPPER(node_name) = l_node_name
LIMIT 1;
IF l_node_type IS NULL
THEN
l_error_message := 'ERROR:: Invalid node name ("' || i_node_name || '") provided during generation of node (MPPDB instance) CPU statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of node (MPPDB instance) CPU statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
IF l_node_name = 'ALL'
THEN
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, mppdb_cpu_time
, host_cpu_busy_time
, (host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time)::bigint AS host_cpu_total_time
, ( (LEAST(mppdb_cpu_time,host_cpu_busy_time) * 100.0) / NULLIF(host_cpu_busy_time, 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_busy_time
, ( (LEAST(mppdb_cpu_time,host_total_cpu_time) * 100.0) / NULLIF((host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time), 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_total_time
FROM ( SELECT 'D'::char(1) AS node_type
, node_name
, node_host
, s.pmk_curr_collect_start_time
, (busy_time_delta * 10)::bigint AS host_cpu_busy_time
, (idle_time_delta * 10)::bigint AS host_cpu_idle_time
, (iowait_time_delta * 10)::bigint AS host_cpu_iowait_time
, ((busy_time_delta+idle_time_delta+iowait_time_delta)*10)::bigint AS host_total_cpu_time
, (db_cpu_time_delta * 10)::bigint AS mppdb_cpu_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT 'C'::char(1) AS node_type
, node_name
, node_host
, s.pmk_curr_collect_start_time
, (busy_time_delta * 10)::bigint AS host_cpu_busy_time
, (idle_time_delta * 10)::bigint AS host_cpu_idle_time
, (iowait_time_delta * 10)::bigint AS host_cpu_iowait_time
, ((busy_time_delta+idle_time_delta+iowait_time_delta)*10)::bigint AS host_total_cpu_time
, (db_cpu_time_delta * 10)::bigint AS mppdb_cpu_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
ORDER BY node_type, node_name, stat_collect_time;
ELSE
IF l_node_type = 'D'
THEN
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT 'D'::char(1) AS node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, mppdb_cpu_time
, host_cpu_busy_time
, (host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time)::bigint AS host_cpu_total_time
, ( (mppdb_cpu_time * 100.0) / NULLIF(host_cpu_busy_time, 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_busy_time
, ( (mppdb_cpu_time * 100.0) / NULLIF((host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time), 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_total_time
FROM ( SELECT node_name
, node_host
, s.pmk_curr_collect_start_time
, (busy_time_delta * 10)::bigint AS host_cpu_busy_time
, (idle_time_delta * 10)::bigint AS host_cpu_idle_time
, (iowait_time_delta * 10)::bigint AS host_cpu_iowait_time
, (db_cpu_time_delta * 10)::bigint AS mppdb_cpu_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
AND UPPER(node_name) = l_node_name
)
ORDER BY node_name, stat_collect_time;
ELSE
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT 'C'::char(1) AS node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, mppdb_cpu_time
, host_cpu_busy_time
, (host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time)::bigint AS host_cpu_total_time
, ( (mppdb_cpu_time * 100.0) / NULLIF(host_cpu_busy_time, 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_busy_time
, ( (mppdb_cpu_time * 100.0) / NULLIF((host_cpu_busy_time+host_cpu_idle_time+host_cpu_iowait_time), 0) )::numeric(5, 2) AS mppdb_cpu_time_perc_wrt_total_time
FROM ( SELECT node_name
, node_host
, s.pmk_curr_collect_start_time
, (busy_time_delta * 10)::bigint AS host_cpu_busy_time
, (idle_time_delta * 10)::bigint AS host_cpu_idle_time
, (iowait_time_delta * 10)::bigint AS host_cpu_iowait_time
, (db_cpu_time_delta * 10)::bigint AS mppdb_cpu_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
AND UPPER(node_name) = l_node_name
)
ORDER BY node_name, stat_collect_time;
END IF; -- end of l_node_type = 'D'
END IF; -- end of l_node_name = 'ALL'
END;
$$;
ALTER FUNCTION pmk.get_node_cpu_stat(i_node_name text, i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_node_type character, OUT o_node_name text, OUT o_node_host text, OUT o_stat_collect_time timestamp without time zone, OUT o_mppdb_cpu_time bigint, OUT o_host_cpu_busy_time bigint, OUT o_host_cpu_total_time bigint, OUT o_mppdb_cpu_time_perc_wrt_busy_time numeric, OUT o_mppdb_cpu_time_perc_wrt_total_time numeric) OWNER TO omm;
--
-- Name: get_node_io_stat(text, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: pmk; Owner: omm
--
CREATE FUNCTION get_node_io_stat(i_node_name text, i_start_pmk_time timestamp with time zone, i_end_pmk_time timestamp with time zone, OUT o_node_type character, OUT o_node_name text, OUT o_node_host text, OUT o_stat_collect_time timestamp without time zone, OUT o_number_of_files integer, OUT o_physical_reads bigint, OUT o_physical_writes bigint, OUT o_read_time bigint, OUT o_write_time bigint, OUT o_avg_read_per_sec numeric, OUT o_avg_read_time numeric, OUT o_avg_write_per_sec numeric, OUT o_avg_write_time numeric) RETURNS SETOF record
LANGUAGE plpgsql NOT SHIPPABLE
AS $$
DECLARE l_node_type char(1);
l_node_name text;
l_error_message text;
BEGIN
l_error_message := pmk.check_node_type();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_error_message := pmk.check_pmk_enabled();
IF l_error_message IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
l_node_name := UPPER(i_node_name);
IF l_node_name <> 'ALL'
THEN
SELECT MAX(node_type)
INTO l_node_type
FROM pgxc_node
WHERE UPPER(node_name) = l_node_name
LIMIT 1;
IF l_node_type IS NULL
THEN
l_error_message := 'ERROR:: Invalid node name ("' || i_node_name || '") provided during generation of node (MPPDB instance) I/O statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
END IF;
-- Verifying the input start and end times
pmk.check_start_end_dates(i_start_pmk_time, i_end_pmk_time, l_error_message);
IF l_error_message IS NOT NULL
THEN
l_error_message := l_error_message || ' during generation of node (MPPDB instance) I/O statistics ...';
DBMS_OUTPUT.PUT_LINE(l_error_message);
RETURN;
END IF;
IF l_node_name = 'ALL'
THEN
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, number_of_files
, physical_reads
, physical_writes
, read_time
, write_time
, ( physical_reads * 1000000.0 / NULLIF(read_time, 0) )::numeric(20,2) AS avg_read_per_sec
, ( read_time * 1.0 / NULLIF(physical_reads, 0) )::numeric(20,3) AS avg_read_time
, ( physical_writes * 1000000.0 / NULLIF(write_time, 0) )::numeric(20,2) AS avg_write_per_sec
, ( write_time * 1.0 / NULLIF(physical_writes, 0) )::numeric(20,3) AS avg_write_time
FROM ( SELECT 'D'::char(1) AS node_type
, node_name
, node_host
, s.pmk_curr_collect_start_time
, number_of_files
, physical_reads_delta AS physical_reads
, physical_writes_delta AS physical_writes
, read_time_delta AS read_time
, write_time_delta AS write_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
UNION ALL
SELECT 'C'::char(1) AS node_type
, node_name
, node_host
, s.pmk_curr_collect_start_time
, number_of_files
, physical_reads_delta AS physical_reads
, physical_writes_delta AS physical_writes
, read_time_delta AS read_time
, write_time_delta AS write_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
)
ORDER BY node_type, node_name, stat_collect_time;
ELSE
IF l_node_type = 'D'
THEN
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT 'D'::char(1) AS node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, number_of_files
, physical_reads
, physical_writes
, read_time
, write_time
, ( physical_reads * 1000000.0 / NULLIF(read_time, 0) )::numeric(20,2) AS avg_read_per_sec
, ( read_time * 1.0 / NULLIF(physical_reads, 0) )::numeric(20,3) AS avg_read_time
, ( physical_writes * 1000000.0 / NULLIF(write_time, 0) )::numeric(20,2) AS avg_write_per_sec
, ( write_time * 1.0 / NULLIF(physical_writes, 0) )::numeric(20,3) AS avg_write_time
FROM ( SELECT node_name
, node_host
, pmk_curr_collect_start_time
, number_of_files
, physical_reads_delta AS physical_reads
, physical_writes_delta AS physical_writes
, read_time_delta AS read_time
, write_time_delta AS write_time
FROM pmk.pmk_snapshot_datanode_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
AND UPPER(node_name) = l_node_name
)
ORDER BY node_name, stat_collect_time;
ELSE
RETURN QUERY
WITH snap AS
( SELECT snapshot_id
, current_snapshot_time AS pmk_curr_collect_start_time
FROM pmk.pmk_snapshot
WHERE current_snapshot_time BETWEEN i_start_pmk_time AND i_end_pmk_time
)
SELECT 'C'::char(1) AS node_type
, node_name
, node_host
, pmk_curr_collect_start_time::timestamp AS stat_collect_time
, number_of_files
, physical_reads
, physical_writes
, read_time
, write_time
, ( physical_reads * 1000000.0 / NULLIF(read_time, 0) )::numeric(20,2) AS avg_read_per_sec
, ( read_time * 1.0 / NULLIF(physical_reads, 0) )::numeric(20,3) AS avg_read_time
, ( physical_writes * 1000000.0 / NULLIF(write_time, 0) )::numeric(20,2) AS avg_write_per_sec
, ( write_time * 1.0 / NULLIF(physical_writes, 0) )::numeric(20,3) AS avg_write_time
FROM ( SELECT node_name
, node_host
, pmk_curr_collect_start_time
, number_of_files
, physical_reads_delta AS physical_reads
, physical_writes_delta AS physical_writes
, read_time_delta AS read_time
, write_time_delta AS write_time
FROM pmk.pmk_snapshot_coordinator_stat dns, snap s
WHERE dns.snapshot_id = s.snapshot_id
AND UPPER(node_name) = l_node_name
)
ORDER BY node_name, stat_collect_time;
END IF; -- end of l_node_type = 'D'
END IF; -- end of l_node_name = 'ALL'
END;
$$;
632
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
