Database & Application Miscellaneous: zero values in statistic views

• corneliaboenigk / 116 / Thurs, 14 Jan 2010 12:14:00 GMT / Comments (4)
• Hi all
  
  I would like to see what is going on in a database using the statistic
  views.
  
  I am superuser,
  stats_start_collector = on
  stats_row_level = on
  stats_block_level = on
  
  select * from pg_stat_user_tables;
  select * from pg_stat_user_indexes;
  select * from pg_statio_user_tables
  select * from pg_statio_user_indexes;
  
  are showing zeroes in all columns where UPDATEs, INSERTs, block-reads,
  hits should be shown.
  
  select * from pg_stat_database where datname = 'mydb';
  shows between 20 and 60 numbackends and from 11 to 50 commits.
  
  What could be the reason for this behaviour? What am I missing?
  
  Thanks in advance
  Conni
  
  (end of broadcast)
  TIP 2: Don't 'kill -9' the postmaster
• Keywords:

  zero, values, statistic, views, database, application

• http://database.itags.org/database-application/158201/«« Last Thread - Next Thread »»

• 1. Cornelia Boenigk <poppcorn (AT) cornelia-boenigk (DOT) dewrites:
     I would like to see what is going on in a database using the statistic
     views.
     
     I am superuser,
     stats_start_collector = on
     stats_row_level = on
     stats_block_level = on
     
     That all looks K, but it sounds like the stats collector is not running
     anyway. Have you checked with "ps" to see if the postmaster has a
     couple of child processes calling themselves stats buffer and stats
     collector? (You may need to say "ps aux" or "ps -ef" depending on
     platform to see these labels rather than generic "postmaster" labels.)
     
     If it's not running, look in the postmaster log right after restarting
     the postmaster; there may be a message there about why not.
     
     regards, tom lane
     
     (end of broadcast)
     TIP 4: Have you searched our list archives?
     
     

     tomlane | Mon, 05 May 2008 19:50:00 GMT |

  2. Tom,
     
     thanks for the quick response.
     
     stats_row_level and stats_block_level are disabled in postgresql.conf.
     I used the SET command to activate them.
     
     If it's not running, look in the postmaster log right after
     restarting the postmaster; there may be a message there about why
     not.
     
     I cannot restart the postmaster, it is a production server.
     
     Regards
     Conni
     
     (end of broadcast)
     TIP 4: Have you searched our list archives?
     
     

     corneliaboenigk | Mon, 05 May 2008 19:51:00 GMT |

  3. Cornelia Boenigk <poppcorn (AT) cornelia-boenigk (DOT) dewrites:
     stats_row_level and stats_block_level are disabled in postgresql.conf.
     I used the SET command to activate them.
     
     That's not going to accomplish a lot it'll only cause the session
     you did the SET in to report to the stats collector. You need to change
     them in postgresql.conf and SIGHUP the postmaster so that they'll take
     effect in all sessions.
     
     If it's not running, look in the postmaster log right after
     restarting the postmaster; there may be a message there about why
     not.
     
     I cannot restart the postmaster, it is a production server.
     
     [ shrug ] If the stats collector isn't running, you aren't going to
     get it to run without a postmaster restart. But you didn't say what
     the ps output showed.
     
     regards, tom lane
     
     (end of broadcast)
     TIP 5: don't forget to increase your free space map settings

     tomlane | Mon, 05 May 2008 19:52:00 GMT |

  4. Hi Tom
     
     But you didn't say what the ps output showed.
     
     ps aux | grep stats
     postgres 24979 0.0 0.0 24416 3780 ? S Nov07 0:07
     postgres: stats buffer process
     postgres 24980 0.0 0.0 24684 4024 ? S Nov07 0:10
     postgres: stats collector process
     
     Regards
     Conni
     
     (end of broadcast)
     TIP 1: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
     message can get through to the mailing list cleanly

     corneliaboenigk | Mon, 05 May 2008 19:53:00 GMT |