Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>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!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux