From gnanam@zoniac.com Wed Mar  31 06:41:47 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 887A2634857
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 03:41:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 79277-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 06:41:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id F3CFC63486C
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 03:41:36 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2V6fVj6009671
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 01:41:33 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-admin@postgresql.org>
Subject: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 12:11:26 +0530
Organization: Zoniac Software Private Limited
Message-ID: <007501cad09d$326fefc0$974fcf40$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0076_01CAD0CB.4C282BC0"
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQnS95SkfuX6vjQrGmDciSF5EK2w==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5
	tests=BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/265
X-Sequence-Number: 34064

This is a multipart message in MIME format.

------=_NextPart_000_0076_01CAD0CB.4C282BC0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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'));


------=_NextPart_000_0076_01CAD0CB.4C282BC0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:9.0pt;
	font-family:"Arial","sans-serif";
	color:navy;}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Arial","sans-serif";
	color:navy;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>Hi,<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Even though I&#8217;ve enabled statistics collector =
in our
server, it is not collecting statistics, and because of this autovacuum =
is also
not running as expected.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>PostgreSQL version 8.2<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Parameters enabled related to this =
are:<o:p></o:p></p>

<p class=3DMsoNormal># - Query/Index Statistics Collector =
-<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>#stats_command_string =3D on<o:p></o:p></p>

<p class=3DMsoNormal>#update_process_title =3D on<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>#stats_start_collector =3D on<o:p></o:p></p>

<p =
class=3DMsoNormal>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></p>

<p class=3DMsoNormal>#stats_block_level =3D off<o:p></o:p></p>

<p class=3DMsoNormal>#stats_row_level =3D off<o:p></o:p></p>

<p class=3DMsoNormal>stats_block_level =3D on<o:p></o:p></p>

<p class=3DMsoNormal>stats_row_level =3D on<o:p></o:p></p>

<p class=3DMsoNormal>#stats_reset_on_server_start =3D off =
<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal># - Statistics Monitoring -<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>#log_parser_stats =3D off<o:p></o:p></p>

<p class=3DMsoNormal>#log_planner_stats =3D off<o:p></o:p></p>

<p class=3DMsoNormal>#log_executor_stats =3D off<o:p></o:p></p>

<p class=3DMsoNormal>#log_statement_stats =3D off<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I&#8217;m always getting zero value for all the =
tables.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>select pg_stat_get_tuples_inserted((select oid from =
pg_class
where relname =3D 'mytable'));<o:p></o:p></p>

<p class=3DMsoNormal>select pg_stat_get_tuples_updated((select oid from =
pg_class
where relname =3D 'mytable'));<o:p></o:p></p>

<p class=3DMsoNormal>select pg_stat_get_tuples_deleted((select oid from =
pg_class
where relname =3D 'mytable'));<o:p></o:p></p>

</div>

</body>

</html>

------=_NextPart_000_0076_01CAD0CB.4C282BC0--




From gnanam@zoniac.com Wed Mar  31 15:18:18 2010
Received: from wm.hub.org (unknown [200.46.204.128])
	by mail.postgresql.org (Postfix) with ESMTP id ED9A163404E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 12:18:18 -0300 (ADT)
Received: from maia (wm.hub.org [200.46.204.128])
	by wm.hub.org (Postfix) with ESMTP id CE6FC2EE226
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 15:18:18 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id A11A7633200
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 04:27:46 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2V7Re4h021804;
	Wed, 31 Mar 2010 02:27:42 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-admin@postgresql.org>
References: <007a01cad09d$33907af0$9ab170d0$@com>
In-Reply-To: <007a01cad09d$33907af0$9ab170d0$@com>
Subject: Re: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 12:57:35 +0530
Organization: Zoniac Software Private Limited
Message-ID: <008b01cad0a3$a50782e0$ef1688a0$@com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_008C_01CAD0D1.BEBFBEE0"
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQnS95SkfuX6vjQrGmDciSF5EK2wABd3FA
X-MS-TNEF-Correlator: 00000000FFE3983AD75C93419DA73DB02195F4D584344A00
Content-Language: en-us
X-Archive-Number: 201003/290
X-Sequence-Number: 34089

This is a multipart message in MIME format.

------=_NextPart_000_008C_01CAD0D1.BEBFBEE0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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'));


------=_NextPart_000_008C_01CAD0D1.BEBFBEE0
Content-Type: application/ms-tnef;
	name="winmail.dat"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
	filename="winmail.dat"

eJ8+IicHAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQOQBgAoFwAANQAAAAsAAgABAAAAAwAmAAAAAAALACkAAAAAAAsA
KwAAAAAAAwAuAAAAAAAeAHAAAQAAADYAAABTdGF0aXN0aWNzIENvbGxlY3RvciBub3QgY29sbGVj
dGluZyBzZXJ2ZXIgYWN0aXZpdGllcwAAAAIBcQABAAAAGwAAAAHK0J0veUpH7l+r40Kxpg3IkheR
CtsAAXdxQAALAAEOAAAAAAIBCg4BAAAAGAAAAAAAAAD/45g611yTQZ2nPbAhlfTVwoAAAAMAFA4B
AAAAHgAoDgEAAAAiAAAAMDAwMDAwMDMBZ25hbmFtQHpvbmlhYy5jb20BWk9OSUFDAAAAHgApDgEA
AAAiAAAAMDAwMDAwMDMBZ25hbmFtQHpvbmlhYy5jb20BWk9OSUFDAAAAAwDeP59OAAADAPE/CQQA
AAMAAlkAABYAAwAJWQIAAAALAAGACCAGAAAAAADAAAAAAAAARgAAAAADhQAAAAAAAAMAA4AIIAYA
AAAAAMAAAAAAAABGAAAAABCFAAAAAAAACwANgAggBgAAAAAAwAAAAAAAAEYAAAAAgoUAAAAAAAAL
ADqACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMAPYAIIAYAAAAAAMAAAAAAAABGAAAAABiF
AAAAAAAACwBSgAggBgAAAAAAwAAAAAAAAEYAAAAABoUAAAAAAAADAFOACCAGAAAAAADAAAAAAAAA
RgAAAAABhQAAAAAAAAMAoIADIAYAAAAAAMAAAAAAAABGAAAAAAGBAAAAAAAAAwCigAMgBgAAAAAA
wAAAAAAAAEYAAAAAE4EAAAEAAAALAKSAAyAGAAAAAADAAAAAAAAARgAAAAAcgQAAAAAAAAMApYAD
IAYAAAAAAMAAAAAAAABGAAAAACOBAAD///9/AwC8gAMgBgAAAAAAwAAAAAAAAEYAAAAAEIEAAAAA
AAADAL6AAyAGAAAAAADAAAAAAAAARgAAAAAqgQAAAAAAAAsAv4ADIAYAAAAAAMAAAAAAAABGAAAA
ACaBAAAAAAAABQDBgAMgBgAAAAAAwAAAAAAAAEYAAAAAAoEAAAAAAAAAAAAAHgDDgAMgBgAAAAAA
wAAAAAAAAEYAAAAAJ4EAAAEAAAABAAAAAAAAAAsAxYADIAYAAAAAAMAAAAAAAABGAAAAAAOBAAAA
AAAAAwDGgAMgBgAAAAAAwAAAAAAAAEYAAAAAEYEAAAAAAAALADaBAyAGAAAAAADAAAAAAAAARgAA
AAAkgQAAAAAAAAsAN4EDIAYAAAAAAMAAAAAAAABGAAAAACyBAAAAAAAAAwA4gQMgBgAAAAAAwAAA
AAAAAEYAAAAAKYEAAAAAAAADAD2BAyAGAAAAAADAAAAAAAAARgAAAAASgQAAAQAAAB4AQIEDIAYA
AAAAAMAAAAAAAABGAAAAACGBAAABAAAAAQAAAAAAAAALAB8OAQAAAAIB+A8BAAAAEAAAAP/jmDrX
XJNBnac9sCGV9NUCAfoPAQAAABAAAAD/45g611yTQZ2nPbAhlfTVAwD+DwUAAAACAQkQAQAAAJMR
AACPEQAA3E4AAExaRnUvjB9+AwAKAHJjcGcxMjWCMgNDaHRtbDEDMfhiaWQEAAMwAQMB9wqAJwKk
A+MCAGNoCsBzZfh0MCAHEwKAEIMAUARWvwhVB7ISVQ5RAwERVzIGAPsGwxJVMwRGEVkTaxJjCO9t
Cfc7GU8OMDUSUgxgY2cAUAsJAWQzNhHgC6U0ciAQgipcDrIBkA4QOWQgPA6yIHgO0ACAOkh2PSII
cG46BPBoYmUAwHMtbQ3gA2Bzam8BgC0FoG0f0A7QIl0fdW8f/yEJITBmDeBleyQlIeZ3In8jjyVw
BbBkjSHmeCWfJq86ZXgkcA0h120okA6wdHA6L6ovKPUuINcuIXEvJDRiLwHQMDQvDiAtgG0LIccr
p3cvwC53My4BBbBnL1RSL1JFhEMtDrI0MCI+EmOfHmcVcAqjMW8yf2czHgDxHyBlYWQxXQ7xM882
TzUepDYO8DwHgAGQIG4yYQeAPUcJ8ASQYXSvBbEFoAIwCfB0KJBNIOaUIFcn0SAOICAoJFA3HrAE
kAmAIAeAD1B1bS4pMU43rzNTNDkRc3Q4eWxlMV4/oQqjPCEELS0KoyAvKiBGGQIhIEQBEAuAaXRp
UwIgBCAqL0H0QAISLbpmANBlCqQBkQMwe0QlISDQbHk6QwdAaWIvBRAaYETHCrBuIRBlLcQxOhVw
MTUgSBAVcNtIUh4AM0iCGmB9CqNEH/FFL1RhaANxRq9HtA7wfjZIo0gQHgAeAEj5QkJTRz/yQs9C
EnAuTSEgTuMFsADAbCwgRlBRGg9QPnZRGEpKAMAwQAuAOjB3C4BMKVRELQbgAkAhgS55LhAwMQUw
TClJswCQel0kgDlWcFbPRbYiBxMiuCwicwBxV9AGciJMKSUYwzo5kHZ5SSZhOmlGUG5rUbBzR1FR
Ekj8eXAEkF0iU4shIFfQP/I8LXAFEAWwQyBGEDk571s/CjJMKTqweCFQBYM6MPFDQTp1bgSBXSFh
4VymfnYEAEMgCYBdb11AQoBsvRjgdwmAXp9fr1tOcAhw3wtQYe9i/2QJZWNFAMADEPlPYzE3Zv9o
Az/wXiBqAP8EkCEgOZACcFifWa9av1vPm20PbhA4bk9vX2wtGVD/C1BccXCfca9yv2FPV0hmwPxp
Zw6wXDBRY1bvP/J99yNJRFESQ2hwT8Fhda8esHYPdxgqwHAY8S0CIHN4f1fUMTBYRElFCrBne0+g
BmBjQzIcI0qGV+M4/C41C4BNcVZwVM9UgYhzfzvwif5JJlKyhn9FQYYiOveMFkkmQdA+Pm8/ch8B
LCAvP/8+Lz85QcFbBpAgZwc6sDzAISAgOV0+PPcfgY7EHxBvKOARgF4gAQG7gUIEIHYqsTrhPOF0
IeCfZWAPQADAKIGFMDI2IeDeL47EkECUckGwWwnwD1D8Zl2OoZIfky+UP5VDC2B+eQhgBUCWKpSl
lQKXEnD1lhxkOjBhl2GXx5AxnIuvm7CYb5DPkd9nP6IvNS9XM3+mjx5YNRHgPAbgZAZ5UcAAcGc9
RU4t9lUF8F0iPQoylhCqw2oUv6XwHnMAIQMwqhKFMDNI0H+sRhHgHisdcahvrt8eozm9TbA8UrE6
cAtgBBA9jBa/rCkAAK2Pr++0jx4rNjTxn58QsgRRJ7Lvs/9nODTxp2ViZVCQcj0ne/gnuQ+FuhNJ
um4mIzgybiCzAoCsSCc5DlC5121c8L5sm2ERsH2gqjBc8CB9kZ8LIKSctnWsR61pdGjB4bIuvl9u
YmVgv7thAUD3ude+MJrwSVKgm3DFj79i/ja/vABQudeVQGawu7E6MOxzX8wxACBfGMGQkIww3QWw
O8j/v2/AeyALgACQ8wEAR0BzcVGhQyDCn8Ov/8t2nQAEIM2fyg/LGyQx1G+/zq/Ae8V8HfA08ZUg
cKX+/w5AoQPbzsCAkDJHUawprDf/HXGsVQqi34gKgaxXCrHhmP+0LQHAkDHbzrZv5S+3P7hP/70/
um+7f7yP6g/a79ePxp//x6rb/90P3h/fL+A/4U/iX//jb+R/5t/8P+ev6L/tr+rfv+vv7P+9v9f/
2Q8BRHZPoP/BYxlQzJM8oqnweuEIgPlh/9Gv0r/A9ALQqfBDMDmwlgDuYR0QE7FtkGvB42aQxRH/
T6ARcaowCRDaf++f84/0n//1r/a/98/43wFf+v/9Xxmf//4v/z8ELxe/Am8Dfx5/D1//Ee/wz/Hf
EL8RzxLfE+8U//8WDxcfGC8ZPxufMP8cbx1/3yJvH58gryG/NddBlgBeIW4giACoMMjAY1GwNm9h
NUpDaHhAZp+AC5B0cNA6Ly93PYAug1DMkJZnCQHRIC44YGcvOvH0cy86sS+KEA2RShBDMFkIgC9y
bCANEi1rkG5dT+BnZ+FrwcyQaT7QLl8Lkp4QOPpP4JzgZKeCZgNC8NCRdHtIWVBFwFJMSU5LID0P
Ph/nPy9AP0FJfX1CoULwd9D5gWFjZtaggVCtaUSPRZ9/Rq9Hv0FXKq8ruVCKKS8v+mGl8CwKPwtP
y3bMT81R59CAlgCN8CBiqfCVlQ8//yRfKE8pXypvK38sjy2fUj//L78yH2M/Mu8z/zjvYV83L/84
P2gfWP9bjyWPJp9aX1tv/1x/XY9en1+vYL9hz2LfZT//ep+xK2ufeE97/3+vsP9qdSepwMJwCeA6
bo3wZTt9g4QtzVBLYGcwqsAJgCNAQjVDNERGijJwFHQ7jKBkQ3FnOjP9heIgigKHFWuPfs+BL4mv
v2XPZt+ID2j/qaGN+2Ktbytpf2qEZrKwdEhQaXo8ZTqXgIXjk9NYgG1pwGx5OiJUYcvwDdD4Iiwi
1BDQoEhQCeCi0GwiOzxjauR3h6DIwHdPmyCfYIfPkaRGcpXgOv9yz3PfkW+aiZBqnC+Sb5N//ZSP
Ojxjla+Ws5dfmG/IFGxHbmpADeB1DdA8kFsnDdCVYM1QOmenQm1AKnqysGlNsC5OoG1dbYksMdWg
kEFyrCuHoGX/d99UPp9vkG+sB7JgTXCaD5ud38fnVwlwhBBzZNQgjdFATXewDsAgMzHRQCwyMKHQ
hcAyocAxIPxQTamfqq+rv6zPrd+u7z24CFRycLDvsf/H9nBnzdERLYZAlVBuQEvsta8/tr+3z7jf
ue+6/6/5dWJealdxvM+937L3U0EHIP5DV0eD8IbwVzUOAgnUDWD3TcLRYELQc2z/bg9x/5pf/5tv
dS92P3dPw995b4sf2O/9wSswz3FMwH2rnr/aT94f/9sf3C/dP9+f42+Lr4y/jc//4t/PH26fb6/o
79B+1D/VT//WX9dv2H/ln/Rf5m/nf+iN/EhpU9zqT+4/70/wX/Fv//J/84/17wH/9r/3z+iP+h//
+y/rv+zP+3/8j/2f/q8Lf38AzwMvEe8D/wUPBh8QAkVLTfBqUHSjgHVntHBJwQjfIzgyMTcKK1NQ
6w+oTfAgsKBha0CzkFZj/8t1VzlqUBggD3HEP1UfViT/zYM7IM4wV8LMjstHOyBqQP2FMGJXcFiQ
lpBX8FWgGADXV9FYkFeQdk2wdaeQV8L/FjAV0B3fHu8P1cyCTiGo8PfNQRWAG5B4OnBNwLOQWN//
CG8MXw1vDn8PjxCfEv8Sv/8xnxTfFe8Grym/CM8J3wrv/ysfLC8tPy5PL18wbzLPQY8fM580rzW/
tXBL9FNRTPsbYUnQaVgBTSE23zfvO9//PO89/z8PQB9Cf0I/UM9EX/9FbzYvSO84TzlfOm9KT0tf
/0xvTX9Oj0+fUf9gv1LPU9/tVO9QXfCooGWlcEfwG5fvwIBkACkhF/BvI9XAgMiM/1bPWr9bz1zf
Xe9e/2APYm8PcL9jP2RPVQ0jIC0gxFF1zYB5L0mlMKWA+8svzDMtaL9pz2rfa+9s//9uD28fcX9x
P4DPc190b1VP/3jvV29Yf1mPek97X3xvfX9/fo9/n4H/kL+Cz4PfhO8juRwic1+pQZSwpTBfR0Dr
pGDNQT0dkG6F/4cPiv//jA+NH44vjz+Rn5FfoC+TfzOUj3V6dXCz4CkgX3C4cm9js8CWkM4AdMwQ
/5efmK+Zv5rPm9+c753/nw//oW+u76I/o0+E/6cPqB+IL/+JP6hvqX+qj6ufuG+tv7Af/77fsO+x
/7MPlfocIbqAlqH/zAamv7U/uS+6P7tPvF+9b/++f8Dfzm/Br8K/s3+1/7cP/9M/1E/VX9Zv13/Y
j9mf2q//27/cz93f3u/f/+EP4h/jL//kP+VP5l/nb+h/6Y/qn+uv/+y/7c/u3+/v8P/yD/Mf9C//
9T/2T/df+G/5f/qP+5/8r//9v/7P/98A7wH/Aw8EHwUv/wY/B08IXwlvCn8LjwyfDa//Dr8PzxDf
Ee8S/xQPFR8WL/8XPxhPGV8abxt/HI8dnx6v/x+/IM8h3yLvI/8lDyYfJy//KD8pTypfK28sfy2P
Lp8vr/8wvzHPMt8z7zT/Ng83Hzgv/zk/Ok87XzxvPX8+jz+fQK//Qb9Cz0PfRO9F/0cPSB9JL/9K
P0tPTF9Nb05/T49Qn1Gv/1K/U89U31XvVv9YD1kfWi//Wz9cT11fXm9ff2CPYZ9ir/9jv2TPZd9m
72f/aQ9qH2sv/2w/bU9uX29vcH9xj3Kfc6//dL91z3bfd+94/3oPex98L/99P35Pf1+Ab4F/go+D
n4Sv/4W/hs+H34jvif+LD4wfjS//jj+PT5BfkW+Sf5OPlJ+Vr/+Wv5fPmN+Z75r/nA+dH54v/58/
oE+hX6Jvo3+kj6Wfpq//p7+oz6nfqu+r/60Prh+vL/+wP7FPsl+zb8bFtPDP9LWH/8tQtm/G78f/
yQ/KH8svzD9/zU/Pr8E/0H/Rj9Kfv1EjAnPCwHRzX2JsbwBja19sZXZlbMAgPSBvZma5j7qf/7uv
vL+9z77fv+/CT8IP0L+HxC/FP8ZPX3Jvd8e//8jPyd/K78v/zQ/OH88v0Y9/3s/SX9Nv1H/c5McP
1uJu/9dP2F/Zb9p/24/cn92v4A//38/uX+Hv4v/kD9Zb5g/nH//oL+k/6k/rX+xv7s/uj/z/D/Cv
8b/VL9YxZXNldKpf9KBfAsBy1rByAyD//gD4ANbkuX/1z/bf9+/4///6D/sf/X/9Pwyf/18AbwF6
/wRvBX+0z7XfEc0GvwfPCN//Fa8K/w1fHB8OLw8/EE/Gd1AgLSBT5MFp5LBpkGNzIE30oGl0IHD4
aW5nIjAR3xLvFt8X7/8Y/xoPGx8dfx0/LB8fXyBv/xEvJD8TTxRfFW8lnyavJ7//KM8p3yrvLU88
Dy4fLy8wP/Ijx3BnXz3BAzEDkuTg/9bvMf817zb/OA85HzovOz9/PZ9K7z5vP39Aj0GUTjBu/m5C
L0M/RE9FX0ZvR39Ij39Jn0v/WV9Mz03fTu9BG2XweGVjdSNRUT9ST1Nf/1RvVX9Wj1efWK9bD2h/
W98PXO9d/0E55LJlbWVu/wLgYE9hX2JvY39kj2WfZq//Z79qH3efau9r/20Pdb9wz/8zHzQvfR9y
L3M/dE91X3Zvv3jPeI+In3qve78BiUl+7yAjODIxN4A7OTKJhbhtIIxAd2F5IvAuZwLQIpAjkXoD
QG8gwnaMQHVlIGaMEJDRedbQdGiSYImQx2ACsC7/fX9+j4J/g4+En4Wvhr+JH/+I35v/iv+MD3zP
lB+N33///4EPlX+Wj5efmK+Zv5rPnS+/q++d/58PoB8CwNaQYwPginBulF+RUV90dVDAHQKwXyOA
AzFu4GQoKPGxdW9pZJJw1lCQwLHhb68jtv+4D6mVd5MQAqAgMQKgbG5hbwBvoSdtwnmTQycpKTuh
L6I//6Yvpz+oT6lfqm+sz6yPwc+/rq+vv7DPsd+y4bKwZG7R/7OPtJ+1r7a/t8+437nvuv//vA+9
H74vvz/AT8Kv1o/Df+/Ej8Wfxq+yPGTcccjfye//yv/MD80fzi/PP9BP0V/Sbz/Tf9SP1Z/X/+vf
2M0xMOHlUS9kaXbbCegY6lwn7d/xr+S2NTjm0WJvvGR55b7zT/Xf5MY35tErpBLlvTP3ZX36sAAD
AA00/T+jBgMADzT9P6MGAgEUNAEAAAAQAAAATklUQfm/uAEAqgA32W4AAAIBfwABAAAAMQAAADAw
MDAwMDAwRkZFMzk4M0FENzVDOTM0MTlEQTczREIwMjE5NUY0RDU4NDM0NEEwMAAAAAADAAYQc7Cb
WwMABxCBBAAAAwAQEAAAAAADABEQAAAAAB4ACBABAAAAZQAAAElNQUxTT1NFRUlOR0FXRUlSRFRI
SU5HSUZJRE9TSE9XU1RBVFNTVEFSVENPTExFQ1RPUjtJTlNJREVQU1FMLElUU0FZU09GRklWRUFM
U09SRVNUQVJURURNWVNFUlZFUk1BTlkAAAAA4v4=

------=_NextPart_000_008C_01CAD0D1.BEBFBEE0--





From guillaume@lelarge.info Wed Mar  31 07:32:05 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8FA786328F0
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 04:32:05 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 87413-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 07:31:55 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74])
	by mail.postgresql.org (Postfix) with ESMTP id 1661F6325EC
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 04:31:55 -0300 (ADT)
Received: from [192.168.0.7] (uni14-1-82-233-223-216.fbx.proxad.net
	[82.233.223.216])
	by sd-17937.dedibox.fr (Postfix) with ESMTPA id 645873B8F5;
	Wed, 31 Mar 2010 09:31:53 +0200 (CEST)
Message-ID: <4BB2FA68.6060708@lelarge.info>
Date: Wed, 31 Mar 2010 09:31:52 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities
References: <007501cad09d$326fefc0$974fcf40$@com>
In-Reply-To: <007501cad09d$326fefc0$974fcf40$@com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/268
X-Sequence-Number: 34067

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



From gnanam@zoniac.com Wed Mar  31 08:59:03 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 1E6AA6333EA
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 05:59:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 83529-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 08:58:47 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 6B24F633197
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 05:58:52 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2V8wjie014688;
	Wed, 31 Mar 2010 03:58:47 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Guillaume Lelarge'" <guillaume@lelarge.info>
Cc: <pgsql-admin@postgresql.org>
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
In-Reply-To: <4BB2FA68.6060708@lelarge.info>
Subject: Re: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 14:28:41 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00b601cad0b0$5e713760$1b53a620$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQpDz+3Yd3FIPGRHi0uuCnkY1d3QADAnyg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.092 tagged_above=-10 required=5 tests=AWL=0.093,
	BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201003/275
X-Sequence-Number: 34074

I=92m also seeing a weird thing.  If I do =91show =
stats_start_collector;=92 inside
psql, it says =91off=92.

I=92ve 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]=20
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 =E9crit :
> Hi,
>=20
> =20
>=20
> 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.
>=20
> =20
>=20
> PostgreSQL version 8.2
>=20
> =20
>=20
> Parameters enabled related to this are:
>=20
> # - Query/Index Statistics Collector -
>=20
> =20
>=20
> #stats_command_string =3D on
>=20
> #update_process_title =3D on
>=20
> =20
>=20
> #stats_start_collector =3D on
>=20
> =20
>=20
>=20
> #stats_block_level =3D off
>=20
> #stats_row_level =3D off
>=20
> stats_block_level =3D on
>=20
> stats_row_level =3D on
>=20
> #stats_reset_on_server_start =3D off=20
>=20
> =20
>=20
> # - Statistics Monitoring -
>=20
> =20
>=20
> #log_parser_stats =3D off
>=20
> #log_planner_stats =3D off
>=20
> #log_executor_stats =3D off
>=20
> #log_statement_stats =3D off
>=20
> =20
>=20
> I'm always getting zero value for all the tables.
>=20
> =20
>=20
> select pg_stat_get_tuples_inserted((select oid from pg_class where =
relname
=3D
> 'mytable'));
>=20
> select pg_stat_get_tuples_updated((select oid from pg_class where =
relname
=3D
> 'mytable'));
>=20
> select pg_stat_get_tuples_deleted((select oid from pg_class where =
relname
=3D
> 'mytable'));
>=20
>=20

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 =3D on" and restart once again PostgreSQL.


--=20
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com




From guillaume@lelarge.info Wed Mar  31 09:05:02 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8F967633197
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 06:05:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 85099-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 09:04:47 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74])
	by mail.postgresql.org (Postfix) with ESMTP id E838A632504
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 06:04:51 -0300 (ADT)
Received: from [192.168.0.7] (uni14-1-82-233-223-216.fbx.proxad.net
	[82.233.223.216])
	by sd-17937.dedibox.fr (Postfix) with ESMTPA id B7FFF3B8F5;
	Wed, 31 Mar 2010 11:04:50 +0200 (CEST)
Message-ID: <4BB31032.6060809@lelarge.info>
Date: Wed, 31 Mar 2010 11:04:50 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
In-Reply-To: <00b601cad0b0$5e713760$1b53a620$@com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.232 tagged_above=-10 required=5 tests=AWL=-0.122,
	BAYES_05=-1.11
X-Spam-Level: 
X-Archive-Number: 201003/276
X-Sequence-Number: 34075

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



From gnanam@zoniac.com Wed Mar  31 09:15:20 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 53292633197
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 06:15:20 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 86504-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 09:15:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 993AC633E02
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 06:15:09 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2V9F2ta029825;
	Wed, 31 Mar 2010 04:15:04 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Guillaume Lelarge'" <guillaume@lelarge.info>
Cc: <pgsql-admin@postgresql.org>
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
In-Reply-To: <4BB31032.6060809@lelarge.info>
Subject: Re: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 14:44:58 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00c001cad0b2$a4a89c30$edf9d490$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQsTkljJpahPMARmG0NFGppAWbiQAAOyZA
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.03 tagged_above=-10 required=5 tests=AWL=-0.031,
	BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 201003/277
X-Sequence-Number: 34076

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]=20
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 =E9crit :
> I=92m also seeing a weird thing.  If I do =91show =
stats_start_collector;=92
inside
> psql, it says =91off=92.
>=20
> I=92ve also restarted my server many times after making all the =
changes.
>=20
> 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.
>=20

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 =3D on

and not

#stats_start_collector =3D on

or

#stats_start_collector =3D off

Once you've done this, restart PostgreSQL and it should work.


--=20
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com




From guillaume@lelarge.info Wed Mar  31 09:26:35 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3B3CA633197
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 06:26:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 07150-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 09:26:24 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74])
	by mail.postgresql.org (Postfix) with ESMTP id C108E63358B
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 06:26:24 -0300 (ADT)
Received: from [192.168.0.7] (uni14-1-82-233-223-216.fbx.proxad.net
	[82.233.223.216])
	by sd-17937.dedibox.fr (Postfix) with ESMTPA id 53ED63B8F5;
	Wed, 31 Mar 2010 11:26:23 +0200 (CEST)
Message-ID: <4BB3153F.6010901@lelarge.info>
Date: Wed, 31 Mar 2010 11:26:23 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
	<00c001cad0b2$a4a89c30$edf9d490$@com>
In-Reply-To: <00c001cad0b2$a4a89c30$edf9d490$@com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/278
X-Sequence-Number: 34077

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



From gnanam@zoniac.com Wed Mar  31 10:51:34 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id C618D632F75
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 07:51:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 01298-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 10:51:18 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 2B417632504
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 07:51:23 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2VApHx6006627;
	Wed, 31 Mar 2010 05:51:19 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Guillaume Lelarge'" <guillaume@lelarge.info>
Cc: <pgsql-admin@postgresql.org>
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
	<00c001cad0b2$a4a89c30$edf9d490$@com>
	<4BB3153F.6010901@lelarge.info>
In-Reply-To: <4BB3153F.6010901@lelarge.info>
Subject: Re: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 16:21:12 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00da01cad0c0$169f3030$43dd9090$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQtDs8eU+ZDZuJQDGlVTNn7mze0gACwXtw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.115 tagged_above=-10 required=5 tests=AWL=0.070,
	BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201003/281
X-Sequence-Number: 34080

After restarting PostgreSQL, I can see the following parameters are set =
to
ON:
mydb=3D# show stats_start_collector;
 stats_start_collector
-----------------------
 on
(1 row)

mydb=3D# show stats_start_collector;
 stats_start_collector
-----------------------
 on
(1 row)

mydb=3D# show stats_block_level;
 stats_block_level
-------------------
 on
(1 row)

mydb=3D# 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 =3D
'mytable'));

Is there any other parameter I'm missing to make this work.

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]=20
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 =E9crit :
> OK.  I'll try that way.
>=20
> 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.


--=20
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com




From guillaume@lelarge.info Wed Mar  31 12:27:02 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D6A62633958
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 09:27:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 14833-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 12:26:44 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74])
	by mail.postgresql.org (Postfix) with ESMTP id 26B87633E5D
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 09:26:50 -0300 (ADT)
Received: from [192.168.0.7] (uni14-1-82-233-223-216.fbx.proxad.net
	[82.233.223.216])
	by sd-17937.dedibox.fr (Postfix) with ESMTPA id 2E9E93B906;
	Wed, 31 Mar 2010 14:26:48 +0200 (CEST)
Message-ID: <4BB33F87.3070901@lelarge.info>
Date: Wed, 31 Mar 2010 14:26:47 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
	<00c001cad0b2$a4a89c30$edf9d490$@com>
	<4BB3153F.6010901@lelarge.info>
	<00da01cad0c0$169f3030$43dd9090$@com>
In-Reply-To: <00da01cad0c0$169f3030$43dd9090$@com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.972 tagged_above=-10 required=5 tests=AWL=0.627,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/284
X-Sequence-Number: 34083

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



From gnanam@zoniac.com Wed Mar  31 13:23:16 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 37857633EBB
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 10:23:16 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 45971-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 13:23:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 6F36B6339CC
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 10:23:05 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o2VDMw9e008612;
	Wed, 31 Mar 2010 08:23:00 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Guillaume Lelarge'" <guillaume@lelarge.info>
Cc: <pgsql-admin@postgresql.org>
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
	<00c001cad0b2$a4a89c30$edf9d490$@com>
	<4BB3153F.6010901@lelarge.info>
	<00da01cad0c0$169f3030$43dd9090$@com>
	<4BB33F87.3070901@lelarge.info>
In-Reply-To: <4BB33F87.3070901@lelarge.info>
Subject: Re: Statistics Collector not collecting server activities
Date: Wed, 31 Mar 2010 18:52:53 +0530
Organization: Zoniac Software Private Limited
Message-ID: <00fd01cad0d5$46eed870$d4cc8950$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 12.0
thread-index: AcrQzW9wQnXqPRC5RDalbtHjmdM4XgABvgXQ
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5
	tests=BAYES_40=-0.185
X-Spam-Level: 
X-Archive-Number: 201003/287
X-Sequence-Number: 34086

show stats_row_level is also showing ON.
mydb=3D# 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]=20
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 =E9crit :
> After restarting PostgreSQL, I can see the following parameters are =
set to
> ON:
> mydb=3D# show stats_start_collector;
>  stats_start_collector
> -----------------------
>  on
> (1 row)
>=20
> mydb=3D# show stats_start_collector;
>  stats_start_collector
> -----------------------
>  on
> (1 row)
>=20
> mydb=3D# show stats_block_level;
>  stats_block_level
> -------------------
>  on
> (1 row)
>=20
> mydb=3D# show stats_reset_on_server_start;
>  stats_reset_on_server_start
> -----------------------------
>  off
> (1 row)
>=20
> 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
=3D
> 'mytable'));
>=20
> Is there any other parameter I'm missing to make this work.
>=20

Nope. But you didn't show stats_row_level. If it's on too, try "select *
from pg_stat_user_tables".


--=20
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com




From tgl@sss.pgh.pa.us Wed Mar  31 14:29:15 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 6A6B5634B0A
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 11:29:15 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 56395-01-4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 14:29:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id D05E3633AF8
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 11:28:34 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o2VESVkY020626;
	Wed, 31 Mar 2010 10:28:31 -0400 (EDT)
To: gnanam@zoniac.com
cc: "'Guillaume Lelarge'" <guillaume@lelarge.info>, pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities 
In-reply-to: <00fd01cad0d5$46eed870$d4cc8950$@com> 
References: <007501cad09d$326fefc0$974fcf40$@com>
	<4BB2FA68.6060708@lelarge.info>
	<00b601cad0b0$5e713760$1b53a620$@com>
	<4BB31032.6060809@lelarge.info>
	<00c001cad0b2$a4a89c30$edf9d490$@com>
	<4BB3153F.6010901@lelarge.info>
	<00da01cad0c0$169f3030$43dd9090$@com>
	<4BB33F87.3070901@lelarge.info>
	<00fd01cad0d5$46eed870$d4cc8950$@com>
Comments: In-reply-to "Gnanakumar" <gnanam@zoniac.com>
	message dated "Wed, 31 Mar 2010 18:52:53 +0530"
Date: Wed, 31 Mar 2010 10:28:31 -0400
Message-ID: <20625.1270045711@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/289
X-Sequence-Number: 34088

"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



From tgl@sss.pgh.pa.us Wed Mar  31 21:42:58 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id EC8486328F0
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 18:42:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 02047-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 31 Mar 2010 21:42:43 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 3E7C1632613
	for <pgsql-admin@postgresql.org>; Wed, 31 Mar 2010 18:42:47 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o2VLgkIZ028455;
	Wed, 31 Mar 2010 17:42:46 -0400 (EDT)
To: gnanam@zoniac.com
cc: pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities 
In-reply-to: <008b01cad0a3$a50782e0$ef1688a0$@com> 
References: <007a01cad09d$33907af0$9ab170d0$@com>
	<008b01cad0a3$a50782e0$ef1688a0$@com>
Comments: In-reply-to "Gnanakumar" <gnanam@zoniac.com>
	message dated "Wed, 31 Mar 2010 12:57:35 +0530"
Date: Wed, 31 Mar 2010 17:42:46 -0400
Message-ID: <28454.1270071766@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.464 tagged_above=-10 required=5 tests=AWL=0.135,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/294
X-Sequence-Number: 34093

"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



From gnanam@zoniac.com Fri Apr  9 13:33:52 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 8CED06329DA
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  9 Apr 2010 10:33:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 01994-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  9 Apr 2010 13:33:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id B49C46334F2
	for <pgsql-admin@postgresql.org>; Fri,  9 Apr 2010 10:33:38 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o39DXTpI008541;
	Fri, 9 Apr 2010 08:33:32 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-admin@postgresql.org>
References: <007a01cad09d$33907af0$9ab170d0$@com>
	<008b01cad0a3$a50782e0$ef1688a0$@com>
	<28454.1270071766@sss.pgh.pa.us>
In-Reply-To: <28454.1270071766@sss.pgh.pa.us>
Subject: Re: Statistics Collector not collecting server activities 
Date: Fri, 9 Apr 2010 19:03:28 +0530
Organization: Zoniac Software Private Limited
Message-ID: <004801cad7e9$414f7a80$c3ee6f80$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcrRGxrz6a+JnK3tTqS3fe+1fLIIoAGzZJcw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.182 tagged_above=-10 required=5 tests=AWL=-0.474,
	MAY_BE_FORGED=0.292
X-Spam-Level: 
X-Archive-Number: 201004/110
X-Sequence-Number: 34204

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




From Kevin.Grittner@wicourts.gov Fri Apr  9 14:06:23 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 2F8EB63293F
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  9 Apr 2010 11:06:23 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 32960-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  9 Apr 2010 14:06:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 8D7596324E2
	for <pgsql-admin@postgresql.org>; Fri,  9 Apr 2010 11:06:12 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 09 Apr 2010 09:06:11 -0500
Message-Id: <4BBEEDF90200002500030638@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 09 Apr 2010 09:06:01 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>,<gnanam@zoniac.com>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: Statistics Collector not collecting server
	 activities
References: <007a01cad09d$33907af0$9ab170d0$@com>
	<008b01cad0a3$a50782e0$ef1688a0$@com> <28454.1270071766@sss.pgh.pa.us>
	<004801cad7e9$414f7a80$c3ee6f80$@com>
In-Reply-To: <004801cad7e9$414f7a80$c3ee6f80$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74
X-Spam-Level: 
X-Archive-Number: 201004/111
X-Sequence-Number: 34205

"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



From gnanam@zoniac.com Sat Apr  10 06:25:14 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E817963253F
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 10 Apr 2010 03:25:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 17394-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 10 Apr 2010 06:25:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs47.luxsci.com (rs47.luxsci.com [65.61.166.88])
	by mail.postgresql.org (Postfix) with ESMTP id 529DD6324E2
	for <pgsql-admin@postgresql.org>; Sat, 10 Apr 2010 03:25:04 -0300 (ADT)
Received: from gnanam (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs47.luxsci.com (8.13.1/8.13.7) with ESMTP id o3A6OvBO025651;
	Sat, 10 Apr 2010 01:24:59 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	"'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-admin@postgresql.org>
References: <007a01cad09d$33907af0$9ab170d0$@com>
	<008b01cad0a3$a50782e0$ef1688a0$@com>
	<28454.1270071766@sss.pgh.pa.us>
	<004801cad7e9$414f7a80$c3ee6f80$@com>
	<4BBEEDF90200002500030638@gw.wicourts.gov>
In-Reply-To: <4BBEEDF90200002500030638@gw.wicourts.gov>
Subject: Re: Statistics Collector not collecting server	 activities
Date: Sat, 10 Apr 2010 11:54:56 +0530
Organization: Zoniac Software Private Limited
Message-ID: <000a01cad876$8c4bd140$a4e373c0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcrX7dL1HBCanq09TfC5IJ8PQ4hFXgAh40fg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.292 tagged_above=-10 required=5
	tests=MAY_BE_FORGED=0.292
X-Spam-Level: 
X-Archive-Number: 201004/125
X-Sequence-Number: 34219

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




From Kevin.Grittner@wicourts.gov Sun Apr  11 17:56:47 2010
Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 3951E632734
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sun, 11 Apr 2010 14:56:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 26871-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sun, 11 Apr 2010 17:56:34 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 84DE06324A1
	for <pgsql-admin@postgresql.org>; Sun, 11 Apr 2010 14:56:36 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Sun, 11 Apr 2010 12:56:34 -0500
Message-Id: <4BC1C70102000025000306DD@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Sun, 11 Apr 2010 12:56:33 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <gnanam@zoniac.com>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: Statistics Collector not collecting server	
	 activities
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.132 tagged_above=-10 required=5 tests=AWL=-1.122,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201004/126
X-Sequence-Number: 34220

"Gnanakumar"  wrote:
 
> Does stats collector process need any other special
> resource/privilege/operations/port to start?
 
If I understand correctly, it uses UDP.  I don't think PostgreSQL
uses UDP outside of that.
 
-Kevin





From gnanam@zoniac.com Mon Aug  2 10:36:12 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 985A663240A
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  2 Aug 2010 07:36:12 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 58215-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  2 Aug 2010 10:36:03 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rs73.luxsci.com (rs73.luxsci.com [64.49.254.135])
	by mail.postgresql.org (Postfix) with ESMTP id D72296323CD
	for <pgsql-admin@postgresql.org>; Mon,  2 Aug 2010 07:36:04 -0300 (ADT)
Received: from GNANAM (ABTS-TN-Static-186.58.165.122.airtelbroadband.in
	[122.165.58.186] (may be forged)) (authenticated bits=0)
	by rs73.luxsci.com (8.13.8/8.13.8) with ESMTP id o72AZxBA003183;
	Mon, 2 Aug 2010 05:36:01 -0500
Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>
Cc: <pgsql-admin@postgresql.org>
References: <4BC1C70102000025000306DD@gw.wicourts.gov>
In-Reply-To: <4BC1C70102000025000306DD@gw.wicourts.gov>
Subject: Re: Statistics Collector not collecting server		 activities
Date: Mon, 2 Aug 2010 16:05:56 +0530
Organization: Zoniac Software Private Limited
Message-ID: <000601cb322e$804dae10$80e90a30$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcrZoFNbQHoHl4bfSN6GlkHNILWxMBYjHOmg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5
	tests=BAYES_40=-0.001
X-Spam-Level: 
X-Archive-Number: 201008/7
X-Sequence-Number: 35256

>> Does stats collector process need any other special
>> resource/privilege/operations/port to start?
 
> If I understand correctly, it uses UDP.  I don't think PostgreSQL
> uses UDP outside of that.
 
Still I'm not able to resolve/fix this statistics collector (not
starting/working) in one of our customer production server and because of
this autovacuum daemon is also not able to perform its job effectively,
thereby it is indirectly impacting on the disk space growth.

There are no anti-virus program running, no SE-Linux, etc.  I've pasted
below my "/etc/sysconfig/iptables" to reveal my firewall rules.

Does PostgreSQL establish working UDP sockets on some specific defined port
or it chooses the port randomly?

Looking at experts guidance to resolve my issue.

=================================
[root@dbserver ~]# more /etc/sysconfig/iptables
# Generated by iptables-save v1.3.5 on Mon Sep 14 20:04:30 2009
*nat
:PREROUTING ACCEPT [10934:1556118]
:POSTROUTING ACCEPT [0:0]
:OUTPUT ACCEPT [111392:6686084]
-A PREROUTING -p tcp -m tcp --dport 80 -j REDIRECT --to-ports 5050
-A POSTROUTING -j MASQUERADE
-A OUTPUT -d 192.168.0.200 -p tcp -m tcp --dport 80 -j DNAT --to-destination
192.168.0.200:5050
-A OUTPUT -d 127.0.0.1 -p tcp -m tcp --dport 80 -j DNAT --to-destination
127.0.0.1:5050
COMMIT
# Completed on Mon Sep 14 20:04:30 2009
# Generated by iptables-save v1.3.5 on Mon Sep 14 20:04:30 2009
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -j ACCEPT
-A FORWARD -j ACCEPT
-A OUTPUT -j ACCEPT
COMMIT
# Completed on Mon Sep 14 20:04:30 2009
=================================





From tgl@sss.pgh.pa.us Mon Aug  2 14:04:11 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id CCE1B632BC2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  2 Aug 2010 11:04:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 64645-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  2 Aug 2010 14:04:02 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 4E1ED6329B3
	for <pgsql-admin@postgresql.org>; Mon,  2 Aug 2010 11:04:04 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o72E42ur015481;
	Mon, 2 Aug 2010 10:04:02 -0400 (EDT)
To: gnanam@zoniac.com
cc: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	pgsql-admin@postgresql.org
Subject: Re: Statistics Collector not collecting server activities 
In-reply-to: <000601cb322e$804dae10$80e90a30$@com> 
References: <4BC1C70102000025000306DD@gw.wicourts.gov>
	<000601cb322e$804dae10$80e90a30$@com>
Comments: In-reply-to "Gnanakumar" <gnanam@zoniac.com>
	message dated "Mon, 02 Aug 2010 16:05:56 +0530"
Date: Mon, 02 Aug 2010 10:04:01 -0400
Message-ID: <15480.1280757841@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201008/10
X-Sequence-Number: 35259

"Gnanakumar" <gnanam@zoniac.com> writes:
> Does PostgreSQL establish working UDP sockets on some specific defined port
> or it chooses the port randomly?

The stats collector socket is opened on whatever port bind() chooses to
assign.

			regards, tom lane