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