Thread: How to find the hits on the databases and tables in Postgres
Hi,
How to monitor the Hits on database and how many hits on each user tables Through query.
Is there any other tools for that so it can full fill my requirement for it .
Any one can please help me out
How to monitor the Hits on database and how many hits on each user tables Through query.
Is there any other tools for that so it can full fill my requirement for it .
Thanks,
On 05/04/2018 04:44 AM, nikhil raj wrote: > Hi, > Any one can please help me out > > How to monitor the Hits on database and how many hits on each user > tables Through query. > Is there any other tools for that so it can full fill my requirement for > it . https://www.postgresql.org/docs/10/static/monitoring-stats.html https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW pg_stat_user_tables Same as pg_stat_all_tables, except that only user tables are shown. > > Thanks, > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian Klaver,
Thanks for the fast response .
But here is the issue is here we have 200+ databases and 5 servers so cannot manually runs this command all ways is there any 3rd party tool for that which would give me the hits on DB and tables in it so that it would be so help full for me.
But here is the issue is here we have 200+ databases and 5 servers so cannot manually runs this command all ways is there any 3rd party tool for that which would give me the hits on DB and tables in it so that it would be so help full for me.
now currently prepared a scripts for that which goes and does for DB butr after that i have much manually work with it so is there any third party tool for that
Thanks,
On Fri, May 4, 2018 at 6:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 05/04/2018 04:44 AM, nikhil raj wrote:Hi,
Any one can please help me out
How to monitor the Hits on database and how many hits on each user tables Through query.
Is there any other tools for that so it can full fill my requirement for it .
https://www.postgresql.org/docs/10/static/monitoring-stats. html
https://www.postgresql.org/docs/10/static/monitoring-stats. html#PG-STAT-DATABASE-VIEW
https://www.postgresql.org/docs/10/static/monitoring-stats. html#PG-STAT-DATABASE-VIEW
pg_stat_user_tables Same as pg_stat_all_tables, except that only user tables are shown.
Thanks,
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/04/2018 09:45 AM, nikhil raj wrote: > Hi Adrian Klaver, > > Thanks for the fast response . > > But here is the issue is here we have 200+ databases and 5 servers so > cannot manually runs this command all ways is there any 3rd party tool > for that which would give me the hits on DB and tables in it so that it > would be so help full for me. > > now currently prepared a scripts for that which goes and does for DB > butr after that i have much manually work with it so is there any third > party tool for that The best I can do is point you at: https://www.postgresql.org/download/products/5-reporting-tools/ Others on the list may be able to provide more information. > > Thanks, > -- Adrian Klaver adrian.klaver@aklaver.com
nikhil raj schrieb am 04.05.2018 um 18:45: > But here is the issue is here we have 200+ databases and 5 servers so > cannot manually runs this command all ways is there any 3rd party > tool for that which would give me the hits on DB and tables in it so > that it would be so help full for me. > > now currently prepared a scripts for that which goes and does for DB > butr after that i have much manually work with it so is there any > third party tool for that Have a look at * http://dalibo.github.io/powa/ * http://opm.io/
On Fri, 4 May 2018 17:14:39 +0530 nikhil raj <nikhilraj474@gmail.com> wrote: > Hi, > Any one can please help me out > > How to monitor the Hits on database and how many hits on each user tables > Through query. > Is there any other tools for that so it can full fill my requirement for it pgBadger has always been my goto tool for that: https://github.com/dalibo/pgbadger -- Bill Moran <wmoran@potentialtech.com>
2018-05-05 13:03 GMT+02:00 PT <wmoran@potentialtech.com>:
On Fri, 4 May 2018 17:14:39 +0530
nikhil raj <nikhilraj474@gmail.com> wrote:
> Hi,
> Any one can please help me out
>
> How to monitor the Hits on database and how many hits on each user tables
> Through query.
> Is there any other tools for that so it can full fill my requirement for it
pgBadger has always been my goto tool for that: https://github.com/dalibo/pgbadger
There are some statistic per tables: .. select * from pg_stat_user_tables, indexes: select * from pg_stat_user_indexes, and databases: select * from pg_stat_database;
Regards
Pavel
--
Bill Moran <wmoran@potentialtech.com>
>How to monitor the Hits on database and how many hits on each user tables
> Through query.
Do these help? You can run them through a cron job.> Through query.
SELECT pg_stat_database.datname,
pg_stat_database.blks_read,
pg_stat_database.blks_hit,
round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+1)::double precision * 100::double precision)::numeric, 2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double precision)::numeric, 2) DESC;
pg_stat_database.blks_read,
pg_stat_database.blks_hit,
round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+1)::double precision * 100::double precision)::numeric, 2) AS cachehitratio
FROM pg_stat_database
WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
ORDER BY round((pg_stat_database.blks_hit::double precision
/ (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double precision)::numeric, 2) DESC;
-- For all tables in a specific database (you need to iterate through all db's)
SELECT n.nspname,
s.relname,
c.reltuples::bigint,
c.relfrozenxid,
age(c.relfrozenxid) AS age_frozenxid,
n_live_tup,
n_tup_ins,
n_tup_upd,
n_tup_del,
date_trunc('second', last_vacuum) as last_vacuum,
date_trunc('second', last_autovacuum) as last_autovacuum,
date_trunc('second', last_analyze) as last_analyze,
date_trunc('second', last_autoanalyze) as last_autoanalyze ,
round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples) AS av_threshold
,CASE WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END AS pct_dead,
CASE WHEN n_dead_tup > round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples)
THEN 'VACUUM'
ELSE 'ok'
END AS "av_needed"
FROM pg_stat_all_tables s
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE s.relname NOT LIKE 'pg_%'
AND s.relname NOT LIKE 'sql_%'
ORDER by 1, 2;
s.relname,
c.reltuples::bigint,
c.relfrozenxid,
age(c.relfrozenxid) AS age_frozenxid,
n_live_tup,
n_tup_ins,
n_tup_upd,
n_tup_del,
date_trunc('second', last_vacuum) as last_vacuum,
date_trunc('second', last_autovacuum) as last_autovacuum,
date_trunc('second', last_analyze) as last_analyze,
date_trunc('second', last_autoanalyze) as last_autoanalyze ,
round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples) AS av_threshold
,CASE WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END AS pct_dead,
CASE WHEN n_dead_tup > round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples)
THEN 'VACUUM'
ELSE 'ok'
END AS "av_needed"
FROM pg_stat_all_tables s
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE s.relname NOT LIKE 'pg_%'
AND s.relname NOT LIKE 'sql_%'
ORDER by 1, 2;
On Sat, May 5, 2018 at 8:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2018-05-05 13:03 GMT+02:00 PT <wmoran@potentialtech.com>:On Fri, 4 May 2018 17:14:39 +0530
nikhil raj <nikhilraj474@gmail.com> wrote:
> Hi,
> Any one can please help me out
>
> How to monitor the Hits on database and how many hits on each user tables
> Through query.
> Is there any other tools for that so it can full fill my requirement for it
pgBadger has always been my goto tool for that: https://github.com/dalibo/pgbadger There are some statistic per tables: .. select * from pg_stat_user_tables, indexes: select * from pg_stat_user_indexes, and databases: select * from pg_stat_database;RegardsPavel--
Bill Moran <wmoran@potentialtech.com>
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!