OOps, forgot to attach the file ..... it is attached.
-----Original Message-----
From: Lane Van Ingen [mailto:lvaningen@xxxxxxxxx]
Sent: Monday, May 01, 2006 3:24 PM
To: Ketema Harris; pgsql-admin@xxxxxxxxxxxxxx
Subject: RE: [ADMIN] IO query
Ketema, see if the attached file helps you. Something I put together to
easily monitor PostgreSQL databases, version 8.0 and up, put together a year
ago. It was not set up to go after more than one database, but you could
easily modify to make it do that.
(1) Install all of the SQL in attached file perf_function.txt; note that
database name is hard-wired in a variable named ws_database; also, it
assumes that your namespace (schema) is 'public'.
(2) Save existing config file.
(3) Alter your config parameters as documented in opening paragraph of
function analyze_performance() at the end of perf_function.txt .
(4) SIGHUP the engine to reload new config parms: pg_ctl reload -s -w
(5) Do a 'SHOW ALL' to see your config parameters are set as you want them
(6) Allow time for PostgreSQL to accumulate statistics for you.
(7) Run 'select * from analyze_performance('report','','','','') to sample
your run statistics at intervals into two tables (perf_stats_database, for
database level statistics) and (perf_stats_objects, for tables / index)
statistics.
(8) select from the contents of the two tables mentioned in (7) to see the
results
Email if questions.
-----Original Message-----
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx]On Behalf Of Ketema Harris
Sent: Thursday, April 27, 2006 1:54 PM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: [ADMIN] IO query
Does anyone have a function or query that will loop through all the dbs in a
cluster, gather all the oids for every table, then call
pg_stat_get_db_blocks_fetched(oid) and pg_stat_get_db_blocks_hit(oid),
subtracting the latter from the former to get an estimate of kernel read()
calls? I would like to write on if there is not one already out there, but
I don¹t know how to find the oid of a table with a query. Where is that
stored?
Thanks
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
/*
Filename: functions_dbperformance.txt
Overview: PLSQL Performance Functions written for PostgreSQL v. 8.0.3
Date: 04/07/2005
Requires: This script is designed to use the Postgres Database, v.8
Changes:
04/07/2005 Lane Van Ingen Established initial file with 9 functions
*/
CREATE TABLE perf_stats_database
( database_name varchar NOT NULL,
updated_time timestamp(3) NOT NULL,
shared_buffers int8 NOT NULL DEFAULT 0,
work_memory int8 NOT NULL DEFAULT 0,
maint_work_mem int8 NOT NULL DEFAULT 0,
insertz int8 NOT NULL DEFAULT 0,
updatez int8 NOT NULL DEFAULT 0,
deletez int8 NOT NULL DEFAULT 0,
idx_scan int8 NOT NULL DEFAULT 0,
seq_scan int8 NOT NULL DEFAULT 0,
commitz int8 NOT NULL DEFAULT 0,
rollbackz int8 NOT NULL DEFAULT 0,
dsk_reads int8 NOT NULL DEFAULT 0,
mem_reads int8 NOT NULL DEFAULT 0,
pct_memory_hits numeric(8,4) NOT NULL DEFAULT 0,
database_sz varchar NOT NULL,
CONSTRAINT perf_stats_database_pk PRIMARY KEY (database_name, updated_time)
) WITHOUT OIDS;
CREATE TABLE perf_stats_objects
( database_name varchar NOT NULL,
updated_time timestamp(3) NOT NULL,
obj_name varchar NOT NULL,
obj_name1 varchar NOT NULL,
obj_type varchar(5) NOT NULL,
dsk_reads int8 NOT NULL DEFAULT 0,
mem_reads int8 NOT NULL DEFAULT 0,
pct_memory_hits numeric(8,4) NOT NULL DEFAULT 0,
idx_scan int8 NOT NULL DEFAULT 0,
seq_scan int8 NOT NULL DEFAULT 0,
pct_index_hits numeric(8,4) NOT NULL DEFAULT 0,
insertz int8 NOT NULL DEFAULT 0,
updatez int8 NOT NULL DEFAULT 0,
deletez int8 NOT NULL DEFAULT 0,
net_kernal_reads int8 NOT NULL DEFAULT 0,
table_len int8 NOT NULL DEFAULT 0,
tuple_len int8 NOT NULL DEFAULT 0,
dead_tuple_len int8 NOT NULL DEFAULT 0,
free_space int8 NOT NULL DEFAULT 0,
tuple_pct numeric(8,4) NOT NULL DEFAULT 0,
dead_tuple_pct numeric(8,4) NOT NULL DEFAULT 0,
free_pct numeric(8,4) NOT NULL DEFAULT 0,
system_pct numeric(8,4) NOT NULL DEFAULT 0,
pagez int8 NOT NULL DEFAULT 0,
bytez int8 NOT NULL DEFAULT 0,
rowz int8 NOT NULL DEFAULT 0,
columnz int2 NOT NULL DEFAULT 0,
CONSTRAINT perf_stats_objects_pk PRIMARY KEY (database_name, updated_time, obj_name, obj_name1)
) WITHOUT OIDS;
----------------------------------- database_size() -----------------------------------
CREATE OR REPLACE FUNCTION database_size(name)
RETURNS int8 AS
'$libdir/dbsize', 'database_size'
LANGUAGE 'c' VOLATILE STRICT;
---------------------------------- insert_perf_db() -----------------------------------
CREATE OR REPLACE FUNCTION insert_perf_db("varchar", "timestamp", int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, int8, "numeric", "varchar")
RETURNS text AS
$BODY$
DECLARE
database_name ALIAS for $1;
updated_time ALIAS for $2;
shared_buffers ALIAS for $3;
work_memory ALIAS for $4;
maint_work_mem ALIAS for $5;
insertz ALIAS for $6;
updatez ALIAS for $7;
deletez ALIAS for $8;
idx_scan ALIAS for $9;
seq_scan ALIAS for $10;
commitz ALIAS for $11;
rollbackz ALIAS for $12;
dsk_reads ALIAS for $13;
mem_reads ALIAS for $14;
pct_memory_hits ALIAS for $15;
database_sz ALIAS for $16;
insert_stmt text;
BEGIN
insert_stmt := 'insert into perf_stats_database values (\''
|| database_name || '\', \''
|| to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS') || '\', '
|| ltrim(to_char(shared_buffers,'999999999999999999')) || ', '
|| ltrim(to_char(work_memory,'999999999999999999')) || ', '
|| ltrim(to_char(maint_work_mem,'999999999999999999')) || ', '
|| ltrim(to_char(insertz,'999999999999999999')) || ', '
|| ltrim(to_char(updatez,'999999999999999999')) || ', '
|| ltrim(to_char(deletez,'999999999999999999')) || ', '
|| ltrim(to_char(idx_scan,'999999999999999999')) || ', '
|| ltrim(to_char(seq_scan,'999999999999999999')) || ', '
|| ltrim(to_char(commitz,'999999999999999999')) || ', '
|| ltrim(to_char(rollbackz,'999999999999999999')) || ', '
|| ltrim(to_char(dsk_reads,'999999999999999999')) || ', '
|| ltrim(to_char(mem_reads,'999999999999999999')) || ', '
|| ltrim(to_char(pct_memory_hits,'9999D9999')) || ', \''
|| database_sz || '\')';
RETURN insert_stmt;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
---------------------------------- insert_perf_obj() ----------------------------------
CREATE OR REPLACE FUNCTION insert_perf_obj("varchar", "timestamp", "varchar", "varchar", "varchar", int8, int8, "numeric", int8, int8, "numeric", int8, int8, int8, int8, int8, int8, int8, int8, "numeric", "numeric", "numeric", "numeric", int8, int8, int8, int2)
RETURNS text AS
$BODY$
DECLARE
database_name ALIAS for $1;
updated_time ALIAS for $2;
obj_name ALIAS for $3;
obj_name1 ALIAS for $4;
obj_type ALIAS for $5;
dsk_reads ALIAS for $6;
mem_reads ALIAS for $7;
pct_memory_hits ALIAS for $8;
idx_scan ALIAS for $9;
seq_scan ALIAS for $10;
pct_index_hits ALIAS for $11;
insertz ALIAS for $12;
updatez ALIAS for $13;
deletez ALIAS for $14;
net_kernal_reads ALIAS for $15;
table_len ALIAS for $16;
tuple_len ALIAS for $17;
dead_tuple_len ALIAS for $18;
free_space ALIAS for $19;
tuple_pct ALIAS for $20;
dead_tuple_pct ALIAS for $21;
free_pct ALIAS for $22;
system_pct ALIAS for $23;
pagez ALIAS for $24;
bytez ALIAS for $25;
rowz ALIAS for $26;
columnz ALIAS for $27;
insert_stmt text;
BEGIN
insert_stmt := 'insert into perf_stats_objects values(\''
|| database_name || '\', \''
|| to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS') || '\', \''
|| obj_name || '\', \'' || obj_name1 || '\', \'' || obj_type || '\', '
|| ltrim(to_char(dsk_reads,'999999999999999999')) || ', '
|| ltrim(to_char(mem_reads,'999999999999999999')) || ', '
|| ltrim(to_char(pct_memory_hits,'9999D9999')) || ', '
|| ltrim(to_char(idx_scan,'999999999999999999')) || ', '
|| ltrim(to_char(seq_scan,'999999999999999999')) || ', '
|| ltrim(to_char(pct_index_hits,'9999D9999')) || ', '
|| ltrim(to_char(insertz,'999999999999999999')) || ', '
|| ltrim(to_char(updatez,'999999999999999999')) || ', '
|| ltrim(to_char(deletez,'999999999999999999')) || ', '
|| ltrim(to_char(net_kernal_reads,'999999999999999999')) || ', '
|| ltrim(to_char(table_len,'999999999999999999')) || ', '
|| ltrim(to_char(tuple_len,'999999999999999999')) || ', '
|| ltrim(to_char(dead_tuple_len,'999999999999999999')) || ', '
|| ltrim(to_char(free_space,'999999999999999999')) || ', '
|| ltrim(to_char(tuple_pct,'9999D9999')) || ', '
|| ltrim(to_char(dead_tuple_pct,'9999D9999')) || ', '
|| ltrim(to_char(free_pct,'9999D9999')) || ', '
|| ltrim(to_char(system_pct,'9999D9999')) || ', '
|| ltrim(to_char(pagez,'999999999999999999')) || ', '
|| ltrim(to_char(bytez,'999999999999999999')) || ', '
|| ltrim(to_char(rowz,'999999999999999999')) || ', '
|| ltrim(to_char(columnz,'999999999999999999')) || ')';
RETURN insert_stmt;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--------------------------------- pg_database_size() ----------------------------------
CREATE OR REPLACE FUNCTION pg_database_size(oid)
RETURNS int8 AS
'$libdir/dbsize', 'pg_database_size'
LANGUAGE 'c' VOLATILE STRICT;
---------------------------------- pg_size_pretty() ------------------------------------
CREATE OR REPLACE FUNCTION pg_size_pretty(int8)
RETURNS text AS
'$libdir/dbsize', 'pg_size_pretty'
LANGUAGE 'c' VOLATILE STRICT;
------------------------------------ pgstattuple() -------------------------------------
CREATE OR REPLACE FUNCTION pgstattuple(oid)
RETURNS pgstattuple_type AS
'$libdir/pgstattuple', 'pgstattuplebyid'
LANGUAGE 'c' VOLATILE STRICT;
CREATE OR REPLACE FUNCTION pgstattuple(text)
RETURNS pgstattuple_type AS
'$libdir/pgstattuple', 'pgstattuple'
LANGUAGE 'c' VOLATILE STRICT;
---------------------------------- update_perf_obj() ----------------------------------
CREATE OR REPLACE FUNCTION update_perf_obj(int8, int8, int8, int8, "varchar", "timestamp", "varchar", "varchar")
RETURNS text AS
$BODY$
DECLARE
pagez ALIAS for $1;
bytesz ALIAS for $2;
rowz ALIAS for $3;
columnz ALIAS for $4;
database_name ALIAS for $5;
updated_time ALIAS for $6;
relname ALIAS for $7;
relindex ALIAS for $8;
update_stmt text;
BEGIN
update_stmt := 'update perf_stats_objects set '
|| ' pagez = ' || ltrim(to_char(pagez,'999999999999999999')) || ', '
|| ' bytez = ' || ltrim(to_char(bytesz,'999999999999999999')) || ', '
|| ' rowz = ' || ltrim(to_char(rowz,'999999999999999999')) || ', '
|| ' columnz = ' || ltrim(to_char(columnz,'999999999999999999'))
|| ' where database_name = \'' || database_name || '\' and updated_time = \''
|| to_char(updated_time,'YYYY-MM-DD HH24:MI:SS.MS')
|| '\' and obj_name = \'' || relname
|| '\' and obj_name1 = \'' || relindex || '\'';
RETURN update_stmt;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-------------------------------- analyze_performance() --------------------------------
CREATE OR REPLACE FUNCTION analyze_performance("varchar", "varchar", "varchar", "varchar", "varchar")
RETURNS "varchar" AS
$BODY$
-- sample command line: select * from analyze_performance('report','','','','')
-- Configuration file settings of PostgreSQL database needed by this function
-- (uses on the following config values from pg_settings):
-- 'log_destination' = stderr
-- 'redirect_stderr' = true
-- 'log_directory' = <your choice>
-- 'log_filename' = <not null>
-- 'log_min_error_statement' = debug1
-- 'log_min_duration_statement' = <your choice, 60 recommended>
-- 'debug_print_plan' = <your choice, true or false>
-- 'debug_pretty_print' = <your choice, true or false>
-- 'log_statement' = mod
-- 'stats_start_collector' = true (on)
-- 'stats_reset_on_server_start' = true (on)
-- 'stats_command_string' = true (on)
-- 'stats_row_level' = true (on)
-- 'stats_block_level' = true (on)
DECLARE
command_line ALIAS for $1; -- 'report'
-- NOTE: (1) the following command line arguments are settable via 'start'
-- (2) all of following arguments are used by 'report'
-- (3) all run-time arguments are reset to system defaults on 'stop'
command_parm1 ALIAS FOR $2; -- defines ms for logging of long-run queries
command_parm2 ALIAS FOR $3; -- how to print query: 'plan' or 'pretty'
command_parm3 ALIAS for $4; -- reset stats on restart: 'Y' or 'N'
command_parm4 ALIAS for $5; -- rpt results to monitor: 'Y' or 'N'(dflt)
-- record areas
config_settings RECORD;
database_hit_stats RECORD; -- varchar, int4, int4, int4, int4, numeric
pg_class_hold RECORD; -- varchar, varchar, int4, int4, int4, int2
statio_user_indexes RECORD; -- varchar, bigint, bigint, numeric
statio_user_tables RECORD; -- varchar, varchar, bigint, bigint, numeric
stat_tuple RECORD; -- int4, int4, int4, flt4, int4, int4, flt4, int4,
-- int4
-- table_len = phys length of tbl (bytes)
-- tuple_count = number live tuples (rows)
-- tuple_len = totl of row length (bytes)
-- tuple_percent = live rows % of [table_len]
-- dead_tuple_count = number of dead rows
-- dead_tuple_len = totl dead row length (bytes)
-- dead_tuple_percent = dead rows % of [table_len]
-- free_space = free space in bytes
-- free_percent = free space % of [table_len]
stat_user_tables RECORD; -- varchar, bigint, bigint, numeric, bigint, bigint,
-- bigint
table_view RECORD; -- integer, varchar
-- variables
-- for configuration parms set at boot-up, with defaults
ws_log_directory varchar;
ws_log_filename varchar;
-- for configuration parms can be set at run time, with defaults
ws_log_min_duration_statement varchar := '250'; -- defines slow query ms or -1
-- indicator variables
no char(1) := 'N';
yes char(1) := 'Y';
-- variables
main_command varchar;
print_parse varchar := 'false';
print_plan varchar := 'false';
print_pretty varchar := 'false';
print_rewritten varchar := 'false';
sql_insert1 text; -- for inserting tbl info into perf_stats_objects
sql_insert2 text; -- for inserting idx info into perf_stats_objects
sql_insert3 text; -- for inserting db info into perf_stats_database
sql_update1 text; -- for updating tbl info into perf_stats_objects
ws_blocks_fetched bigint := 0;
ws_blocks_fetched_accum bigint := 0;
ws_blocks_hit bigint := 0;
ws_blocks_hit_accum bigint := 0;
ws_database varchar := 'our database';
ws_database_oid integer;
ws_database_sz text;
ws_deletes_accum bigint := 0;
ws_fatal_error char(1) := no;
ws_first_command char(1);
ws_first_line char(1);
ws_idxname_work varchar;
ws_index char(5) := 'index';
ws_index_count bigint := 0;
ws_index_scans_accum bigint := 0;
ws_inserts_accum bigint := 0;
ws_maint_work_mem integer := 0;
ws_net_kernal_reads bigint := 0;
ws_previous_time timestamp(3);
ws_print char(1);
ws_record_count integer := 0;
ws_schema_oid integer;
ws_sequential_scans_accum bigint := 0;
ws_shared_buffers integer := 0;
ws_sys_space float := 0;
ws_type char(5) := 'table';
ws_updated_time timestamp(3);
ws_updates_accum bigint := 0;
ws_work_mem integer := 0;
BEGIN
if command_line = 'report' then
NULL;
else
RAISE NOTICE 'Invalid Command = % % % %', command_line, command_parm1,
command_parm2, command_parm3;
RETURN 'Invalid_Command';
end if;
select localtimestamp into ws_updated_time;
ws_previous_time := ws_updated_time - interval '6 months';
select count(*) into ws_record_count
from perf_stats_database
where updated_time < ws_previous_time;
if ws_record_count > 0 then -- purge rcds older than 6 months
delete from perf_stats_database where updated_time < ws_previous_time;
end if;
select count(*) into ws_record_count
from perf_stats_objects
where updated_time < ws_previous_time;
if ws_record_count > 0 then -- purge rcds older than 6 months
delete from perf_stats_objects where updated_time < ws_previous_time;
end if;
-- present performance stats on individual tables & indexes
if command_line = 'report' then
analyze;
select oid into ws_database_oid from pg_database where datname = ws_database;
select oid into ws_schema_oid from pg_namespace where nspname = 'public';
if ((command_parm4 = yes) OR (command_parm4 = no)) then
ws_print := command_parm4;
else
ws_print := no;
end if;
if ws_print = yes then
RAISE NOTICE '- - - - - - - Summary Table and Index Statistics - - - - - - -';
end if;
for table_view in
select oid, relname::varchar from pg_class
where relnamespace = ws_schema_oid and relkind = 'r'
order by 2
loop
-- get statistics on table reads and hits
if ws_print = yes then
RAISE NOTICE ' ';
end if;
select relname::varchar AS tblname,
case
when (heap_blks_read + toast_blks_read) < 1 then 0
else coalesce((heap_blks_read + toast_blks_read),0)
end AS dsk_reads,
case
when (heap_blks_hit + toast_blks_hit) < 1 then 0
else coalesce((heap_blks_hit + toast_blks_hit),0)
end AS mem_reads,
case
when (heap_blks_read +heap_blks_hit + toast_blks_read + toast_blks_hit) < 1
then 0::numeric
else coalesce((round((((heap_blks_hit + toast_blks_hit) /
(heap_blks_read + heap_blks_hit + toast_blks_read +
toast_blks_hit)::float) * 100)::numeric,2)),0.00::numeric,2)
end AS pct_memory_hits
into statio_user_tables
from pg_statio_user_tables
where relname = table_view.relname;
-- get statistics on table index, types DML activity, and size
select relname::varchar AS tblname,
coalesce(seq_scan,0)::int8 AS seq_scan,
coalesce(idx_scan,0)::int8 AS idx_scan,
case
when (idx_scan + seq_scan) = 0 then 0.00::numeric
else coalesce((round(((idx_scan / (seq_scan + idx_scan)::float) * 100)::numeric,2)),
0.00::numeric)
end AS pct_index_hits,
n_tup_ins::int8 AS insertz,
n_tup_upd::int8 AS updatez,
n_tup_del::int8 AS deletez
into stat_user_tables
from pg_stat_user_tables
where relname = table_view.relname;
ws_inserts_accum := ws_inserts_accum + stat_user_tables.insertz;
ws_updates_accum := ws_updates_accum + stat_user_tables.updatez;
ws_deletes_accum := ws_deletes_accum + stat_user_tables.deletez;
-- get statistics on kernal vs memory read efficiency
select * into ws_blocks_fetched
from pg_stat_get_db_blocks_fetched(table_view.oid);
ws_blocks_fetched_accum = ws_blocks_fetched_accum + ws_blocks_fetched;
select * into ws_blocks_hit
from pg_stat_get_db_blocks_hit(table_view.oid);
ws_blocks_fetched_accum = ws_blocks_fetched_accum + ws_blocks_fetched;
ws_blocks_hit_accum = ws_blocks_hit_accum + ws_blocks_hit;
ws_net_kernal_reads = 0;
if ws_blocks_fetched > ws_blocks_hit then
ws_net_kernal_reads = (ws_blocks_fetched - ws_blocks_hit);
end if;
ws_index_scans_accum :=
(ws_index_scans_accum + stat_user_tables.idx_scan);
ws_sequential_scans_accum :=
(ws_sequential_scans_accum + stat_user_tables.seq_scan);
-- report results
if ws_print = yes then
RAISE NOTICE 'NAME: %',table_view.relname;
RAISE NOTICE 'TBL:Inserts Updates Deletes Seq Scans Idx Scans Idx Use%';
RAISE NOTICE 'TBL:% % % % % %',
stat_user_tables.insertz, stat_user_tables.updatez, stat_user_tables.deletez,
stat_user_tables.seq_scan, stat_user_tables.idx_scan, stat_user_tables.pct_index_hits;
RAISE NOTICE ' KrnlReads DskReads MemReads %MemHits';
RAISE NOTICE ' % % % %',
ws_net_kernal_reads, statio_user_tables.dsk_reads,
statio_user_tables.mem_reads, statio_user_tables.pct_memory_hits;
end if;
-- get statistics on adequacy of vacuuming
select * into stat_tuple from pgstattuple(table_view.oid);
ws_sys_space := (100.00 - (stat_tuple.tuple_percent +
stat_tuple.dead_tuple_percent + stat_tuple.free_percent));
if ws_print = yes then
RAISE NOTICE ' Vacuuming Adequacy Percentages';
RAISE NOTICE 'TblLnBytes LiveTupls DeadTupls FreeSpBytes Live Dead Free Sys';
RAISE NOTICE '% % % % % % % %',
stat_tuple.table_len, stat_tuple.tuple_len, stat_tuple.dead_tuple_len,
stat_tuple.free_space, stat_tuple.tuple_percent,
stat_tuple.dead_tuple_percent, stat_tuple.free_percent, ws_sys_space;
end if;
sql_insert1 := insert_perf_obj(ws_database::varchar,
ws_updated_time::timestamp,
table_view.relname::varchar,
table_view.relname::varchar,
'table'::varchar,
statio_user_tables.dsk_reads::int8,
statio_user_tables.mem_reads::int8,
statio_user_tables.pct_memory_hits::numeric,
stat_user_tables.idx_scan::int8,
stat_user_tables.seq_scan::int8,
stat_user_tables.pct_index_hits::numeric,
stat_user_tables.insertz::int8,
stat_user_tables.updatez::int8,
stat_user_tables.deletez::int8,
ws_net_kernal_reads::int8,
stat_tuple.table_len::int8,
stat_tuple.tuple_len::int8,
stat_tuple.dead_tuple_len::int8,
stat_tuple.free_space::int8,
stat_tuple.tuple_percent::numeric,
stat_tuple.dead_tuple_percent::numeric,
stat_tuple.free_percent::numeric,
ws_sys_space::numeric,
0::int8,0::int8,0::int8,0::int2);
execute sql_insert1;
-- get statistics on index reads and hits
ws_index_count = 0;
select count(*)::int8 into ws_index_count
from pg_statio_user_indexes
where pg_statio_user_indexes.relname = table_view.relname;
if ws_index_count > 0 then
select * into statio_user_indexes from pg_statio_user_indexes limit 1;
ws_first_line := yes;
for statio_user_indexes in
select relname::varchar AS relname,
indexrelname::varchar AS idxname,
idx_blks_read::int8 AS dsk_reads,
idx_blks_hit::int8 AS mem_reads,
case
when (idx_blks_read + idx_blks_hit) = 0 then 0.00::numeric
else coalesce((round(((idx_blks_hit / (idx_blks_read + idx_blks_hit)::float)
* 100)::numeric,2)), 0.00::numeric,2)
end AS pct_memory_hits
from pg_statio_user_indexes
where relname = table_view.relname
loop
ws_idxname_work := statio_user_indexes.idxname;
if ws_print = yes then
if ws_first_line = yes then
ws_first_line = no;
RAISE NOTICE 'IDX:Idx Name DskReads MemReads %MemHits';
end if;
RAISE NOTICE 'IDX: % % % %',
ws_idxname_work, statio_user_indexes.dsk_reads,
statio_user_indexes.mem_reads, statio_user_indexes.pct_memory_hits;
end if;
select * into sql_insert2 from insert_perf_obj(ws_database::varchar,
ws_updated_time::timestamp,
statio_user_indexes.relname::varchar,
statio_user_indexes.idxname::varchar,
'index'::varchar,
statio_user_indexes.dsk_reads::int8,
statio_user_indexes.mem_reads::int8,
statio_user_indexes.pct_memory_hits::numeric,
0::int8,0::int8,0::numeric,0::int8,0::int8,0::int8,0::int8,
0::int8,0::int8,0::int8,0::int8,0::numeric,0::numeric,0::numeric,
0::numeric,0::int8,0::int8,0::int8,0::int2);
execute sql_insert2;
end loop;
end if;
end loop;
-- present disk resource usage stats on all table and index objects
ws_first_line := yes;
for pg_class_hold in
select relname::varchar AS relname,
relname::varchar AS relindex,
'table'::varchar AS relobj,
relpages::int8 AS pagez,
reltuples::int8 AS rowz,
(relpages * 8192)::int8 AS bytez,
relnatts::int2 AS columnz
from pg_class
where relkind = 'r'
and relname not like 'sql_%'
and relname not like 'pg_%'
union
select pgc1.relname::varchar,
pgc2.relname::varchar AS relindex,
'index'::varchar AS relobj,
pgc2.relpages::int8 AS pagez,
pgc2.reltuples::int8 AS rowz,
(pgc2.relpages * 8192)::int8 AS bytez,
pgc2.relnatts::int2 AS columnz
from pg_index pgi1, pg_class pgc1, pg_class pgc2
where pgi1.indexrelid = pgc2.oid
and pgi1.indrelid = pgc1.oid
and pgc2.relname not like 'sql_%'
and pgc2.relname not like 'pg_%'
order by 1,2,3 desc
loop
if ws_print = yes then
if ws_first_line = yes then
ws_first_line := no;
RAISE NOTICE ' ';
RAISE NOTICE '- - - - - - - Table / Index Size Statistics - - - - - - -';
RAISE NOTICE 'Name Type Cols Pages Rows Bytes';
end if;
RAISE NOTICE '% % % % % %',
pg_class_hold.relindex, pg_class_hold.relobj, pg_class_hold.columnz,
pg_class_hold.pagez, pg_class_hold.rowz,pg_class_hold.bytez;
end if;
sql_update1 := update_perf_obj(pg_class_hold.pagez::int8,
pg_class_hold.bytez::int8,
pg_class_hold.rowz::int8,
pg_class_hold.columnz::int8,
ws_database::varchar,
ws_updated_time::timestamp,
pg_class_hold.relname::varchar,
pg_class_hold.relindex::varchar);
execute sql_update1;
end loop;
-- present performance stats on database as a whole
select into database_hit_stats datname::varchar AS database,
xact_commit AS commitz, xact_rollback AS rollbackz, blks_read AS dsk_reads,
blks_hit AS mem_reads,
case
when (blks_read + blks_hit) = 0 then 0
else round(((blks_hit / (blks_read + blks_hit)::float) * 100)::numeric,2)
end AS pct_memory_hits
from pg_stat_database where datname = ws_database;
select * into ws_database_sz from pg_size_pretty(pg_database_size(ws_database_oid));
if ws_print = yes then
RAISE NOTICE ' ';
RAISE NOTICE ' DATABASE = % (%)', ws_database, ws_database_sz;
RAISE NOTICE '- - - - - - - Overall Database Summary Statistics - - - - - - -';
RAISE NOTICE ' Commits RllBcks Inserts Updates Deletes';
RAISE NOTICE ' % % % % %',
database_hit_stats.commitz, database_hit_stats.rollbackz, ws_inserts_accum,
ws_updates_accum, ws_deletes_accum;
RAISE NOTICE ' KnlReads SeqScans DskReads MemReads % MemHits';
RAISE NOTICE ' % % % % %',
ws_net_kernal_reads, ws_sequential_scans_accum, database_hit_stats.dsk_reads,
database_hit_stats.mem_reads, database_hit_stats.pct_memory_hits;
end if;
sql_insert3 := insert_perf_db(ws_database::varchar,
ws_updated_time::timestamp,
ws_shared_buffers::int8,
ws_work_mem::int8,
ws_maint_work_mem::int8,
ws_inserts_accum::int8,
ws_updates_accum::int8,
ws_deletes_accum::int8,
ws_index_scans_accum::int8,
ws_sequential_scans_accum::int8,
database_hit_stats.commitz::int8,
database_hit_stats.rollbackz::int8,
database_hit_stats.dsk_reads::int8,
database_hit_stats.mem_reads::int8,
database_hit_stats.pct_memory_hits::numeric,
ws_database_sz::varchar);
execute sql_insert3;
if ws_print = yes then
RAISE NOTICE ' ';
end if;
select setting into ws_log_directory
from pg_settings
where name = 'log_directory';
select setting into ws_log_directory
from pg_settings
where name = 'log_filename';
select setting into ws_log_min_duration_statement
from pg_settings
where name = 'log_min_duration_statement';
if FOUND then
if ws_log_min_duration_statement <> '-1' then
RAISE NOTICE 'Queries taking longer than % ms recorded in %',
ws_log_min_duration_statement, ws_log_directory;
end if;
end if;
end if;
RETURN 'OK';
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
