Thread: zero values in statistic views

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

Cornelia Boenigk <poppcorn@cornelia-boenigk.de> writes:
> 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 OK, 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

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

Cornelia Boenigk <poppcorn@cornelia-boenigk.de> writes:
> 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

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