Thread: Statistics Collector not collecting server activities
Hi,
Even though I’ve enabled statistics collector in our server, it is not collecting statistics, and because of this autovacuum is also not running as expected.
PostgreSQL version 8.2
Parameters enabled related to this are:
# - Query/Index Statistics Collector -
#stats_command_string = on
#update_process_title = on
#stats_start_collector = on
#stats_block_level = off
#stats_row_level = off
stats_block_level = on
stats_row_level = on
#stats_reset_on_server_start = off
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
I’m always getting zero value for all the tables.
select pg_stat_get_tuples_inserted((select oid from pg_class where relname = 'mytable'));
select pg_stat_get_tuples_updated((select oid from pg_class where relname = 'mytable'));
select pg_stat_get_tuples_deleted((select oid from pg_class where relname = 'mytable'));
Le 31/03/2010 08:41, Gnanakumar a écrit : > Hi, > > > > Even though I've enabled statistics collector in our server, it is not > collecting statistics, and because of this autovacuum is also not running as > expected. > > > > PostgreSQL version 8.2 > > > > Parameters enabled related to this are: > > # - Query/Index Statistics Collector - > > > > #stats_command_string = on > > #update_process_title = on > > > > #stats_start_collector = on > > > > > #stats_block_level = off > > #stats_row_level = off > > stats_block_level = on > > stats_row_level = on > > #stats_reset_on_server_start = off > > > > # - Statistics Monitoring - > > > > #log_parser_stats = off > > #log_planner_stats = off > > #log_executor_stats = off > > #log_statement_stats = off > > > > I'm always getting zero value for all the tables. > > > > select pg_stat_get_tuples_inserted((select oid from pg_class where relname = > 'mytable')); > > select pg_stat_get_tuples_updated((select oid from pg_class where relname = > 'mytable')); > > select pg_stat_get_tuples_deleted((select oid from pg_class where relname = > 'mytable')); > > If the stats collector was disabled before, did you restart PostgreSQL? if you did restart, you should drop the # in front of "stats_start_collector = on" and restart once again PostgreSQL. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Im also seeing a weird thing. If I do show stats_start_collector; inside psql, it says off. Ive also restarted my server many times after making all the changes. As per 8.2 doc, http://www.postgresql.org/docs/8.2/interactive/runtime-config-statistics.htm l, stats_start_collector is on by default. -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] Sent: Wednesday, March 31, 2010 1:02 PM To: gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Statistics Collector not collecting server activities Le 31/03/2010 08:41, Gnanakumar a écrit : > Hi, > > > > Even though I've enabled statistics collector in our server, it is not > collecting statistics, and because of this autovacuum is also not running as > expected. > > > > PostgreSQL version 8.2 > > > > Parameters enabled related to this are: > > # - Query/Index Statistics Collector - > > > > #stats_command_string = on > > #update_process_title = on > > > > #stats_start_collector = on > > > > > #stats_block_level = off > > #stats_row_level = off > > stats_block_level = on > > stats_row_level = on > > #stats_reset_on_server_start = off > > > > # - Statistics Monitoring - > > > > #log_parser_stats = off > > #log_planner_stats = off > > #log_executor_stats = off > > #log_statement_stats = off > > > > I'm always getting zero value for all the tables. > > > > select pg_stat_get_tuples_inserted((select oid from pg_class where relname = > 'mytable')); > > select pg_stat_get_tuples_updated((select oid from pg_class where relname = > 'mytable')); > > select pg_stat_get_tuples_deleted((select oid from pg_class where relname = > 'mytable')); > > If the stats collector was disabled before, did you restart PostgreSQL? if you did restart, you should drop the # in front of "stats_start_collector = on" and restart once again PostgreSQL. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Le 31/03/2010 10:58, Gnanakumar a écrit : > I’m also seeing a weird thing. If I do ‘show stats_start_collector;’ inside > psql, it says ‘off’. > > I’ve also restarted my server many times after making all the changes. > > As per 8.2 doc, > http://www.postgresql.org/docs/8.2/interactive/runtime-config-statistics.htm > l, stats_start_collector is on by default. > If it is off (as you found with the "show stats_start_collector" statement), you should getting ride of the # in front of the stats_start_collector parameter. Ie, you should have stats_start_collector = on and not #stats_start_collector = on or #stats_start_collector = off Once you've done this, restart PostgreSQL and it should work. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
OK. I'll try that way. But, as per 8.2 doc, by default it is ON. So if I comment with just '#' infront of the parameter, and restart PostgreSQL, this change will not be reflected, which means it should work with default value ON? -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] Sent: Wednesday, March 31, 2010 2:35 PM To: gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Statistics Collector not collecting server activities Le 31/03/2010 10:58, Gnanakumar a écrit : > Im also seeing a weird thing. If I do show stats_start_collector; inside > psql, it says off. > > Ive also restarted my server many times after making all the changes. > > As per 8.2 doc, > http://www.postgresql.org/docs/8.2/interactive/runtime-config-statistics.htm > l, stats_start_collector is on by default. > If it is off (as you found with the "show stats_start_collector" statement), you should getting ride of the # in front of the stats_start_collector parameter. Ie, you should have stats_start_collector = on and not #stats_start_collector = on or #stats_start_collector = off Once you've done this, restart PostgreSQL and it should work. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Le 31/03/2010 11:14, Gnanakumar a écrit : > OK. I'll try that way. > > But, as per 8.2 doc, by default it is ON. So if I comment with just '#' > infront of the parameter, and restart PostgreSQL, this change will not be > reflected, which means it should work with default value ON? Yes, you're right. Maybe you have stats_start_collector at different places in your postgresql.conf. The last value is used in this case. So you should better check this. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
After restarting PostgreSQL, I can see the following parameters are set to ON: mydb=# show stats_start_collector; stats_start_collector ----------------------- on (1 row) mydb=# show stats_start_collector; stats_start_collector ----------------------- on (1 row) mydb=# show stats_block_level; stats_block_level ------------------- on (1 row) mydb=# show stats_reset_on_server_start; stats_reset_on_server_start ----------------------------- off (1 row) But still, if I query after performing heavy DML operations, I'm always getting zero, even though there are INSERT/UPDATE/DELETE. select pg_stat_get_tuples_inserted((select oid from pg_class where relname = 'mytable')); Is there any other parameter I'm missing to make this work. -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] Sent: Wednesday, March 31, 2010 2:56 PM To: gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Statistics Collector not collecting server activities Le 31/03/2010 11:14, Gnanakumar a écrit : > OK. I'll try that way. > > But, as per 8.2 doc, by default it is ON. So if I comment with just '#' > infront of the parameter, and restart PostgreSQL, this change will not be > reflected, which means it should work with default value ON? Yes, you're right. Maybe you have stats_start_collector at different places in your postgresql.conf. The last value is used in this case. So you should better check this. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Le 31/03/2010 12:51, Gnanakumar a écrit : > After restarting PostgreSQL, I can see the following parameters are set to > ON: > mydb=# show stats_start_collector; > stats_start_collector > ----------------------- > on > (1 row) > > mydb=# show stats_start_collector; > stats_start_collector > ----------------------- > on > (1 row) > > mydb=# show stats_block_level; > stats_block_level > ------------------- > on > (1 row) > > mydb=# show stats_reset_on_server_start; > stats_reset_on_server_start > ----------------------------- > off > (1 row) > > But still, if I query after performing heavy DML operations, I'm always > getting zero, even though there are INSERT/UPDATE/DELETE. > select pg_stat_get_tuples_inserted((select oid from pg_class where relname = > 'mytable')); > > Is there any other parameter I'm missing to make this work. > Nope. But you didn't show stats_row_level. If it's on too, try "select * from pg_stat_user_tables". -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
show stats_row_level is also showing ON. mydb=# show stats_row_level; stats_row_level ----------------- on (1 row) I also ran "select * from pg_stat_user_tables", all columns are showing either 0 or NULL value for all the tables in my database. I don't find any values other than this for the entire tables in the database. -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] Sent: Wednesday, March 31, 2010 5:57 PM To: gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Statistics Collector not collecting server activities Le 31/03/2010 12:51, Gnanakumar a écrit : > After restarting PostgreSQL, I can see the following parameters are set to > ON: > mydb=# show stats_start_collector; > stats_start_collector > ----------------------- > on > (1 row) > > mydb=# show stats_start_collector; > stats_start_collector > ----------------------- > on > (1 row) > > mydb=# show stats_block_level; > stats_block_level > ------------------- > on > (1 row) > > mydb=# show stats_reset_on_server_start; > stats_reset_on_server_start > ----------------------------- > off > (1 row) > > But still, if I query after performing heavy DML operations, I'm always > getting zero, even though there are INSERT/UPDATE/DELETE. > select pg_stat_get_tuples_inserted((select oid from pg_class where relname = > 'mytable')); > > Is there any other parameter I'm missing to make this work. > Nope. But you didn't show stats_row_level. If it's on too, try "select * from pg_stat_user_tables". -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
"Gnanakumar" <gnanam@zoniac.com> writes:
>> But still, if I query after performing heavy DML operations, I'm always
>> getting zero, even though there are INSERT/UPDATE/DELETE.
Is the stats collector actually running according to "ps"? If not, look
in the postmaster log to see why it failed (problems creating the
communication socket are the most common cause).
regards, tom lane
I'm also seeing a weird thing. If I do 'show stats_start_collector;' inside psql, it says 'off'. I've also restarted my server many times after making all the changes. As per 8.2 doc, http://www.postgresql.org/docs/8.2/interactive/runtime-config-statistics.htm l, stats_start_collector is on by default. From: Gnanakumar [mailto:gnanam@zoniac.com] Sent: Wednesday, March 31, 2010 12:11 PM To: pgsql-admin@postgresql.org Subject: Statistics Collector not collecting server activities Hi, Even though I've enabled statistics collector in our server, it is not collecting statistics, and because of this autovacuum is also not running as expected. PostgreSQL version 8.2 Parameters enabled related to this are: # - Query/Index Statistics Collector - #stats_command_string = on #update_process_title = on #stats_start_collector = on #stats_block_level = off #stats_row_level = off stats_block_level = on stats_row_level = on #stats_reset_on_server_start = off # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off I'm always getting zero value for all the tables. select pg_stat_get_tuples_inserted((select oid from pg_class where relname = 'mytable')); select pg_stat_get_tuples_updated((select oid from pg_class where relname = 'mytable')); select pg_stat_get_tuples_deleted((select oid from pg_class where relname = 'mytable'));
Attachment
"Gnanakumar" <gnanam@zoniac.com> writes:
> I'm also seeing a weird thing. If I do 'show stats_start_collector;' inside
> psql, it says 'off'.
Yeah, this is the expected state if the collector fails to start for
some reason. Look into the postmaster log for a startup-time message
indicating why; but again, I'll bet on problems creating its UDP socket.
regards, tom lane
Hi Tom,
As you said, I notice the following in postmaster log:
2010-04-08 17:30:41 ISTLOG: could not receive test message on socket for
statistics collector: Connection refused
2010-04-08 17:30:41 ISTLOG: disabling statistics collector for lack of
working socket
What is going wrong? I couldn't understand from the above lines.
If postgresql service can start without any issues, why not stats collector
process the same way?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, April 01, 2010 3:13 AM
To: gnanam@zoniac.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Statistics Collector not collecting server activities
"Gnanakumar" <gnanam@zoniac.com> writes:
> I'm also seeing a weird thing. If I do 'show stats_start_collector;'
inside
> psql, it says 'off'.
Yeah, this is the expected state if the collector fails to start for
some reason. Look into the postmaster log for a startup-time message
indicating why; but again, I'll bet on problems creating its UDP socket.
regards, tom lane
"Gnanakumar" <gnanam@zoniac.com> wrote: > As you said, I notice the following in postmaster log: > 2010-04-08 17:30:41 ISTLOG: could not receive test message on > socket for statistics collector: Connection refused > 2010-04-08 17:30:41 ISTLOG: disabling statistics collector for > lack of working socket > > What is going wrong? I couldn't understand from the above lines. Is an OS firewall or anti-virus program running on this machine? Any other unusual security features, like SE-Linux? -Kevin
Hi Kevin, No, it is not SE-Linux and no anti-virus programs are running. I can again cross-check whether any firewall is enabled. But again if postgresql service can start without any issues, why not stats collector process the same way? Does stats collector process need any other special resource/privilege/operations/port to start? Can you let me know in this aspect, so that it would be helpful to troubleshoot better. -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Friday, April 09, 2010 7:36 PM To: 'Tom Lane'; gnanam@zoniac.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Statistics Collector not collecting server activities "Gnanakumar" <gnanam@zoniac.com> wrote: > As you said, I notice the following in postmaster log: > 2010-04-08 17:30:41 ISTLOG: could not receive test message on > socket for statistics collector: Connection refused > 2010-04-08 17:30:41 ISTLOG: disabling statistics collector for > lack of working socket > > What is going wrong? I couldn't understand from the above lines. Is an OS firewall or anti-virus program running on this machine? Any other unusual security features, like SE-Linux? -Kevin