Re: How to find the hits on the databases and tables in Postgres

>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.*

*-- For all databases*

*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;*

*-- 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;*

On Sat, May 5, 2018 at 8:13 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2018-05-05 13:03 GMT+02:00 PT <wmoran(at)potentialtech(dot)com>:
>
>> On Fri, 4 May 2018 17:14:39 +0530
>> nikhil raj <nikhilraj474(at)gmail(dot)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(at)potentialtech(dot)com>
>>
>>
>

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!