1

I am having trouble understanding the Postgresql's Statistics Collector.

Here is the official page of describing the pg_stat_database view.

I performed query on the pg_stat_database view,

 postgres=# select tup_inserted, tup_fetched from pg_stat_database;
 tup_inserted | tup_fetched 
--------------+-------------
            0 |           0
            0 |           0
            0 |      425511
            0 |      407918
            0 |           0
         1011 |      413373
          324 |      360500
         1167 |      345411
         1139 |      346655
          787 |      342410
         1049 |      328089
            0 |      204959
         1646 |      231987
          892 |      205624
           41 |       66538
            0 |       41609
          838 |     2591785
(17 rows)

But these are ridiculous numbers. Even If I combine all my databases' rows, still they won't be equal to this.

What data does this actually tells us ? And moreover I have not used these databases for weeks, means it is showing me old data.

I want to monitor the number of queries being performed, memory used, clients connected etc.

Is there any kind of tool to monitor my postgresql database ?

1

You actually have two different questions here. With respect to the numbers shown in pg_stat_database, most of them are cumulative since the last time they were reset, e.g. by pg_stat_reset() or friends. So, for example, if you frequently query a one-row table you will eventually accumulate high numbers of tup_fetched and blk_read_time.

  • so, its clear that it is not live data. Can you suggest any tool to live monitor postgresdb ? – luv.preet Apr 13 '17 at 18:50
  • @luv.preet: that is "live" data. It shows what has happened in your database. – a_horse_with_no_name Apr 13 '17 at 19:28
  • 1
    It is live as it's continuously updated. Some metrics are cumulative by definition; how else would you interpret "tuples read"? As for tools, what search criteria did you try when googling? – mustaccio Apr 13 '17 at 19:35
  • I did google it @mustaccio . I found OPM, pgwatch and others are not good OR paid. Benefit of asking to a community is that the community members tend to have experience in these things and can tell in a much better way than google, they can give pros and cons of a tools. And considering my question, you can analyze that I must have googled it before asking to community. – luv.preet Apr 13 '17 at 20:46
  • @a_horse_with_no_name but I had not done any work on my databases since 4,5 weeks. I did not insert anything in the postgre for past 3,4 weeks, and only now I have just fetched rows from these views. How these numbers are possible ? Do this collector count the rows fetched from these collector views also ? I think they are showing me data from day 1 when I installed postgre. – luv.preet Apr 13 '17 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.