From: Lane Van Ingen <lvaningen@esncc.com>
To: Ketema Harris <ketema@c4direct.com>
To: pgsql-admin@postgresql.org
Subject: Re: IO query
Date: Mon, 1 May 2006 15:24:09 -0400
Message-ID: <EKEMKEFLOMKDDLIALABICEEOCKAA.lvaningen@esncc.com> (raw)
In-Reply-To: <C0767D8D.2D78%ketema@c4direct.com>
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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Ketema Harris
Sent: Thursday, April 27, 2006 1:54 PM
To: pgsql-admin@postgresql.org
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: lvaningen@esncc.com, ketema@c4direct.com
Subject: Re: IO query
In-Reply-To: <EKEMKEFLOMKDDLIALABICEEOCKAA.lvaningen@esncc.com>
References: <C0767D8D.2D78%ketema@c4direct.com> <EKEMKEFLOMKDDLIALABICEEOCKAA.lvaningen@esncc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox