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

[Melvin Davidson <melvin6925@xxxxxxxxx>]

>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.stehule@xxxxxxxxx> wrote:

2018-05-05 13:03 GMT+02:00 PT <wmoran@xxxxxxxxxxxxxxxxx>:

On Fri, 4 May 2018 17:14:39 +0530
nikhil raj <nikhilraj474@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>

--

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