Statistics Collector not collecting server activities

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

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">


charset=3Dus-ascii">

Re: Statistics Collector not collecting server activities

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-confi g-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+IicHAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcA
GAAAAElQTS5NaWNyb3NvZnQgTWFpbC5Ob3RlADEIAQOQBgAoFwAANQAAAAsA
AgABAAAAAwAmAAAAAAALACkAAAAAAAsAKwAAAAAAAwAuAAAAAAAeAHAAAQAA
ADYAAABTdGF0aXN0aWNzIENvbGxlY3RvciBub3QgY29sbGVjdGluZyBzZXJ2
ZXIgYWN0aXZpdGllcwAAAAIBcQABAAAAGwAAAAHK0J0veUpH7l+r40Kxpg3I
kheRCtsAAXdxQAALAAEOAAAAAAIBCg4BAAAAGAAAAAAAAAD/45g611yTQZ2n
PbAhlfTVwoAAAAMAFA4BAAAAHgAoDgEAAAAiAAAAMDAwMDAwMDMBZ25hbmFt
QHpvbmlhYy5jb20BWk9OSUFDAAAAHgApDgEAAAAiAAAAMDAwMDAwMDMBZ25h
bmFtQHpvbmlhYy5jb20BWk9OSUFDAAAAAwDeP59OAAADAPE/CQQAAAMAAlkA
ABYAAwAJWQIAAAALAAGACCAGAAAAAADAAAAAAAAARgAAAAADhQAAAAAAAAMA
A4AIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAAAAAACwANgAggBgAAAAAAwAAA
AAAAAEYAAAAAgoUAAAAAAAALADqACCAGAAAAAADAAAAAAAAARgAAAAAOhQAA
AAAAAAMAPYAIIAYAAAAAAMAAAAAAAABGAAAAABiFAAAAAAAACwBSgAggBgAA
AAAAwAAAAAAAAEYAAAAABoUAAAAAAAADAFOACCAGAAAAAADAAAAAAAAARgAA
AAABhQAAAAAAAAMAoIADIAYAAAAAAMAAAAAAAABGAAAAAAGBAAAAAAAAAwCi
gAMgBgAAAAAAwAAAAAAAAEYAAAAAE4EAAAEAAAALAKSAAyAGAAAAAADAAAAA
AAAARgAAAAAcgQAAAAAAAAMApYADIAYAAAAAAMAAAAAAAABGAAAAACOBAAD/
//9/AwC8gAMgBgAAAAAAwAAAAAAAAEYAAAAAEIEAAAAAAAADAL6AAyAGAAAA
AADAAAAAAAAARgAAAAAqgQAAAAAAAAsAv4ADIAYAAAAAAMAAAAAAAABGAAAA
ACaBAAAAAAAABQDBgAMgBgAAAAAAwAAAAAAAAEYAAAAAAoEAAAAAAAAAAAAA
HgDDgAMgBgAAAAAAwAAAAAAAAEYAAAAAJ4EAAAEAAAABAAAAAAAAAAsAxYAD
IAYAAAAAAMAAAAAAAABGAAAAAAOBAAAAAAAAAwDGgAMgBgAAAAAAwAAAAAAA
AEYAAAAAEYEAAAAAAAALADaBAyAGAAAAAADAAAAAAAAARgAAAAAkgQAAAAAA
AAsAN4EDIAYAAAAAAMAAAAAAAABGAAAAACyBAAAAAAAAAwA4gQMgBgAAAAAA
wAAAAAAAAEYAAAAAKYEAAAAAAAADAD2BAyAGAAAAAADAAAAAAAAARgAAAAAS
gQAAAQAAAB4AQIEDIAYAAAAAAMAAAAAAAABGAAAAACGBAAABAAAAAQAAAAAA
AAALAB8OAQAAAAIB+A8BAAAAEAAAAP/jmDrXXJNBnac9sCGV9NUCAfoPAQAA
ABAAAAD/45g611yTQZ2nPbAhlfTVAwD+DwUAAAACAQkQAQAAAJMRAACPEQAA
3E4AAExaRnUvjB9+AwAKAHJjcGcxMjWCMgNDaHRtbDEDMfhiaWQEAAMwAQMB
9wqAJwKkA+MCAGNoCsBzZfh0MCAHEwKAEIMAUARWvwhVB7ISVQ5RAwERVzIG
APsGwxJVMwRGEVkTaxJjCO9tCfc7GU8OMDUSUgxgY2cAUAsJAWQzNhHgC6U0
ciAQgipcDrIBkA4QOWQgPA6yIHgO0ACAOkh2PSIIcG46BPBoYmUAwHMtbQ3g
A2Bzam8BgC0FoG0f0A7QIl0fdW8f/yEJITBmDeBleyQlIeZ3In8jjyVwBbBk
jSHmeCWfJq86ZXgkcA0h120okA6wdHA6L6ovKPUuINcuIXEvJDRiLwHQMDQv
DiAtgG0LIccrp3cvwC53My4BBbBnL1RSL1JFhEMtDrI0MCI+EmOfHmcVcAqj
MW8yf2czHgDxHyBlYWQxXQ7xM882TzUepDYO8DwHgAGQIG4yYQeAPUcJ8ASQ
YXSvBbEFoAIwCfB0KJBNIOaUIFcn0SAOICAoJFA3HrAEkAmAIAeAD1B1bS4p
MU43rzNTNDkRc3Q4eWxlMV4/oQqjPCEELS0KoyAvKiBGGQIhIEQBEAuAaXRp
UwIgBCAqL0H0QAISLbpmANBlCqQBkQMwe0QlISDQbHk6QwdAaWIvBRAaYETH
CrBuIRBlLcQxOhVwMTUgSBAVcNtIUh4AM0iCGmB9CqNEH/FFL1RhaANxRq9H
tA7wfjZIo0gQHgAeAEj5QkJTRz/yQs9CEnAuTSEgTuMFsADAbCwgRlBRGg9Q
PnZRGEpKAMAwQAuAOjB3C4BMKVRELQbgAkAhgS55LhAwMQUwTClJswCQel0k
gDlWcFbPRbYiBxMiuCwicwBxV9AGciJMKSUYwzo5kHZ5SSZhOmlGUG5rUbBz
R1FREkj8eXAEkF0iU4shIFfQP/I8LXAFEAWwQyBGEDk571s/CjJMKTqweCFQ
BYM6MPFDQTp1bgSBXSFh4VymfnYEAEMgCYBdb11AQoBsvRjgdwmAXp9fr1tO
cAhw3wtQYe9i/2QJZWNFAMADEPlPYzE3Zv9oAz/wXiBqAP8EkCEgOZACcFif
Wa9av1vPm20PbhA4bk9vX2wtGVD/C1BccXCfca9yv2FPV0hmwPxpZw6wXDBR
Y1bvP/J99yNJRFESQ2hwT8Fhda8esHYPdxgqwHAY8S0CIHN4f1fUMTBYRElF
CrBne0+gBmBjQzIcI0qGV+M4/C41C4BNcVZwVM9UgYhzfzvwif5JJlKyhn9F
QYYiOveMFkkmQdA+Pm8/ch8BLCAvP/8+Lz85QcFbBpAgZwc6sDzAISAgOV0+
PPcfgY7EHxBvKOARgF4gAQG7gUIEIHYqsTrhPOF0IeCfZWAPQADAKIGFMDI2
IeDeL47EkECUckGwWwnwD1D8Zl2OoZIfky+UP5VDC2B+eQhgBUCWKpSllQKX
EnD1lhxkOjBhl2GXx5AxnIuvm7CYb5DPkd9nP6IvNS9XM3+mjx5YNRHgPAbg
ZAZ5UcAAcGc9RU4t9lUF8F0iPQoylhCqw2oUv6XwHnMAIQMwqhKFMDNI0H+s
RhHgHisdcahvrt8eozm9TbA8UrE6cAtgBBA9jBa/rCkAAK2Pr++0jx4rNjTx
n58QsgRRJ7Lvs/9nODTxp2ViZVCQcj0ne/gnuQ+FuhNJum4mIzgybiCzAoCs
SCc5DlC5121c8L5sm2ERsH2gqjBc8CB9kZ8LIKSctnWsR61pdGjB4bIuvl9u
YmVgv7thAUD3ude+MJrwSVKgm3DFj79i/ja/vABQudeVQGawu7E6MOxzX8wx
ACBfGMGQkIww3QWwO8j/v2/AeyALgACQ8wEAR0BzcVGhQyDCn8Ov/8t2nQAE
IM2fyg/LGyQx1G+/zq/Ae8V8HfA08ZUgcKX+/w5AoQPbzsCAkDJHUawprDf/
HXGsVQqi34gKgaxXCrHhmP+0LQHAkDHbzrZv5S+3P7hP/70/um+7f7yP6g/a
79ePxp//x6rb/90P3h/fL+A/4U/iX//jb+R/5t/8P+ev6L/tr+rfv+vv7P+9
v9f/2Q8BRHZPoP/BYxlQzJM8oqnweuEIgPlh/9Gv0r/A9ALQqfBDMDmwlgDu
YR0QE7FtkGvB42aQxRH/T6ARcaowCRDaf++f84/0n//1r/a/98/43wFf+v/9
Xxmf//4v/z8ELxe/Am8Dfx5/D1//Ee/wz/HfEL8RzxLfE+8U//8WDxcfGC8Z
PxufMP8cbx1/3yJvH58gryG/NddBlgBeIW4giACoMMjAY1GwNm9hNUpDaHhA
Zp+AC5B0cNA6Ly93PYAug1DMkJZnCQHRIC44YGcvOvH0cy86sS+KEA2RShBD
MFkIgC9ybCANEi1rkG5dT+BnZ+FrwcyQaT7QLl8Lkp4QOPpP4JzgZKeCZgNC
8NCRdHtIWVBFwFJMSU5LID0PPh/nPy9AP0FJfX1CoULwd9D5gWFjZtaggVCt
aUSPRZ9/Rq9Hv0FXKq8ruVCKKS8v+mGl8CwKPwtPy3bMT81R59CAlgCN8CBi
qfCVlQ8//yRfKE8pXypvK38sjy2fUj//L78yH2M/Mu8z/zjvYV83L/84P2gf
WP9bjyWPJp9aX1tv/1x/XY9en1+vYL9hz2LfZT//ep+xK2ufeE97/3+vsP9q
dSepwMJwCeA6bo3wZTt9g4QtzVBLYGcwqsAJgCNAQjVDNERGijJwFHQ7jKBk
Q3FnOjP9heIgigKHFWuPfs+BL4mvv2XPZt+ID2j/qaGN+2Ktbytpf2qEZrKw
dEhQaXo8ZTqXgIXjk9NYgG1pwGx5OiJUYcvwDdD4Iiwi1BDQoEhQCeCi0Gwi
OzxjauR3h6DIwHdPmyCfYIfPkaRGcpXgOv9yz3PfkW+aiZBqnC+Sb5N//ZSP
Ojxjla+Ws5dfmG/IFGxHbmpADeB1DdA8kFsnDdCVYM1QOmenQm1AKnqysGlN
sC5OoG1dbYksMdWgkEFyrCuHoGX/d99UPp9vkG+sB7JgTXCaD5ud38fnVwlw
hBBzZNQgjdFATXewDsAgMzHRQCwyMKHQhcAyocAxIPxQTamfqq+rv6zPrd+u
7z24CFRycLDvsf/H9nBnzdERLYZAlVBuQEvsta8/tr+3z7jfue+6/6/5dWJe
aldxvM+937L3U0EHIP5DV0eD8IbwVzUOAgnUDWD3TcLRYELQc2z/bg9x/5pf
/5tvdS92P3dPw995b4sf2O/9wSswz3FMwH2rnr/aT94f/9sf3C/dP9+f42+L
r4y/jc//4t/PH26fb6/o79B+1D/VT//WX9dv2H/ln/Rf5m/nf+iN/EhpU9zq
T+4/70/wX/Fv//J/84/17wH/9r/3z+iP+h//+y/rv+zP+3/8j/2f/q8Lf38A
zwMvEe8D/wUPBh8QAkVLTfBqUHSjgHVntHBJwQjfIzgyMTcKK1NQ6w+oTfAg
sKBha0CzkFZj/8t1VzlqUBggD3HEP1UfViT/zYM7IM4wV8LMjstHOyBqQP2F
MGJXcFiQlpBX8FWgGADXV9FYkFeQdk2wdaeQV8L/FjAV0B3fHu8P1cyCTiGo
8PfNQRWAG5B4OnBNwLOQWN//CG8MXw1vDn8PjxCfEv8Sv/8xnxTfFe8Grym/
CM8J3wrv/ysfLC8tPy5PL18wbzLPQY8fM580rzW/tXBL9FNRTPsbYUnQaVgB
TSE23zfvO9//PO89/z8PQB9Cf0I/UM9EX/9FbzYvSO84TzlfOm9KT0tf/0xv
TX9Oj0+fUf9gv1LPU9/tVO9QXfCooGWlcEfwG5fvwIBkACkhF/BvI9XAgMiM
/1bPWr9bz1zfXe9e/2APYm8PcL9jP2RPVQ0jIC0gxFF1zYB5L0mlMKWA+8sv
zDMtaL9pz2rfa+9s//9uD28fcX9xP4DPc190b1VP/3jvV29Yf1mPek97X3xv
fX9/fo9/n4H/kL+Cz4PfhO8juRwic1+pQZSwpTBfR0DrpGDNQT0dkG6F/4cP
iv//jA+NH44vjz+Rn5FfoC+TfzOUj3V6dXCz4CkgX3C4cm9js8CWkM4AdMwQ
/5efmK+Zv5rPm9+c753/nw//oW+u76I/o0+E/6cPqB+IL/+JP6hvqX+qj6uf
uG+tv7Af/77fsO+x/7MPlfocIbqAlqH/zAamv7U/uS+6P7tPvF+9b/++f8Df
zm/Br8K/s3+1/7cP/9M/1E/VX9Zv13/Yj9mf2q//27/cz93f3u/f/+EP4h/j
L//kP+VP5l/nb+h/6Y/qn+uv/+y/7c/u3+/v8P/yD/Mf9C//9T/2T/df+G/5
f/qP+5/8r//9v/7P/98A7wH/Aw8EHwUv/wY/B08IXwlvCn8LjwyfDa//Dr8P
zxDfEe8S/xQPFR8WL/8XPxhPGV8abxt/HI8dnx6v/x+/IM8h3yLvI/8lDyYf
Jy//KD8pTypfK28sfy2PLp8vr/8wvzHPMt8z7zT/Ng83Hzgv/zk/Ok87Xzxv
PX8+jz+fQK//Qb9Cz0PfRO9F/0cPSB9JL/9KP0tPTF9Nb05/T49Qn1Gv/1K/
U89U31XvVv9YD1kfWi//Wz9cT11fXm9ff2CPYZ9ir/9jv2TPZd9m72f/aQ9q
H2sv/2w/bU9uX29vcH9xj3Kfc6//dL91z3bfd+94/3oPex98L/99P35Pf1+A
b4F/go+Dn4Sv/4W/hs+H34jvif+LD4wfjS//jj+PT5BfkW+Sf5OPlJ+Vr/+W
v5fPmN+Z75r/nA+dH54v/58/oE+hX6Jvo3+kj6Wfpq//p7+oz6nfqu+r/60P
rh+vL/+wP7FPsl+zb8bFtPDP9LWH/8tQtm/G78f/yQ/KH8svzD9/zU/Pr8E/
0H/Rj9Kfv1EjAnPCwHRzX2JsbwBja19sZXZlbMAgPSBvZma5j7qf/7uvvL+9
z77fv+/CT8IP0L+HxC/FP8ZPX3Jvd8e//8jPyd/K78v/zQ/OH88v0Y9/3s/S
X9Nv1H/c5McP1uJu/9dP2F/Zb9p/24/cn92v4A//38/uX+Hv4v/kD9Zb5g/n
H//oL+k/6k/rX+xv7s/uj/z/D/Cv8b/VL9YxZXNldKpf9KBfAsBy1rByAyD/
/gD4ANbkuX/1z/bf9+/4///6D/sf/X/9Pwyf/18AbwF6/wRvBX+0z7XfEc0G
vwfPCN//Fa8K/w1fHB8OLw8/EE/Gd1AgLSBT5MFp5LBpkGNzIE30oGl0IHD4
aW5nIjAR3xLvFt8X7/8Y/xoPGx8dfx0/LB8fXyBv/xEvJD8TTxRfFW8lnyav
J7//KM8p3yrvLU88Dy4fLy8wP/Ijx3BnXz3BAzEDkuTg/9bvMf817zb/OA85
HzovOz9/PZ9K7z5vP39Aj0GUTjBu/m5CL0M/RE9FX0ZvR39Ij39Jn0v/WV9M
z03fTu9BG2XweGVjdSNRUT9ST1Nf/1RvVX9Wj1efWK9bD2h/W98PXO9d/0E5
5LJlbWVu/wLgYE9hX2JvY39kj2WfZq//Z79qH3efau9r/20Pdb9wz/8zHzQv
fR9yL3M/dE91X3Zvv3jPeI+In3qve78BiUl+7yAjODIxN4A7OTKJhbhtIIxA
d2F5IvAuZwLQIpAjkXoDQG8gwnaMQHVlIGaMEJDRedbQdGiSYImQx2ACsC7/
fX9+j4J/g4+En4Wvhr+JH/+I35v/iv+MD3zPlB+N33///4EPlX+Wj5efmK+Z
v5rPnS+/q++d/58PoB8CwNaQYwPginBulF+RUV90dVDAHQKwXyOAAzFu4GQo
KPGxdW9pZJJw1lCQwLHhb68jtv+4D6mVd5MQAqAgMQKgbG5hbwBvoSdtwnmT
QycpKTuhL6I//6Yvpz+oT6lfqm+sz6yPwc+/rq+vv7DPsd+y4bKwZG7R/7OP
tJ+1r7a/t8+437nvuv//vA+9H74vvz/AT8Kv1o/Df+/Ej8Wfxq+yPGTcccjf
ye//yv/MD80fzi/PP9BP0V/Sbz/Tf9SP1Z/X/+vf2M0xMOHlUS9kaXbbCegY
6lwn7d/xr+S2NTjm0WJvvGR55b7zT/Xf5MY35tErpBLlvTP3ZX36sAADAA00
/T+jBgMADzT9P6MGAgEUNAEAAAAQAAAATklUQfm/uAEAqgA32W4AAAIBfwAB
AAAAMQAAADAwMDAwMDAwRkZFMzk4M0FENzVDOTM0MTlEQTczREIwMjE5NUY0
RDU4NDM0NEEwMAAAAAADAAYQc7CbWwMABxCBBAAAAwAQEAAAAAADABEQAAAA
AB4ACBABAAAAZQAAAElNQUxTT1NFRUlOR0FXRUlSRFRISU5HSUZJRE9TSE9X
U1RBVFNTVEFSVENPTExFQ1RPUjtJTlNJREVQU1FMLElUU0FZU09GRklWRUFM
U09SRVNUQVJURURNWVNFUlZFUk1BTlkAAAAA4v4=

------=_NextPart_000_008C_01CAD0D1.BEBFBEE0
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

Re: 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 runni=
ng 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 reln=
ame =3D
> 'mytable'));
>=20
> select pg_stat_get_tuples_updated((select oid from pg_class where relna=
me =3D
> 'mytable'));
>=20
> select pg_stat_get_tuples_deleted((select oid from pg_class where relna=
me =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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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-confi g-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
> 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
> PostgreSQL version 8.2
>=20
>
>=20
> Parameters enabled related to this are:
>=20
> # - Query/Index Statistics Collector -
>=20
>
>=20
> #stats_command_string =3D on
>=20
> #update_process_title =3D on
>=20
>
>=20
> #stats_start_collector =3D on
>=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
> # - Statistics Monitoring -
>=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
> I'm always getting zero value for all the tables.
>=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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: 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-confi g-statistic=
s.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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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-confi g-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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: 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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: 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 reln=
ame =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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

"Gnanakumar" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

"Gnanakumar" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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" 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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server

"Gnanakumar" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

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" 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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server

"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



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

>> 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
=================================



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Statistics Collector not collecting server activities

"Gnanakumar" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin