[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: [PERFORM] zero value in statistics collector's result
From: "Yinan Li" <yinanli () cse ! ust ! hk>
Date: 2007-09-23 17:00:24
Message-ID: 003801c7fe03$3c3f8020$b4be8060$ () ust ! hk
[Download RAW message or body]
UbJGR;7b MIME 8qJ=5D6`2?7VSJ<~!#
Hi,
When I use the statistics collector to see the number of IO, I always get
zero in almost all of columns. I really want to know the reason for that.
The result of statistics view:
# select * from pg_statio_user_tables;
relid | schemaname | relname | heap_blks_read | heap_blks_hit |
idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |
tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+------------
---+--------------+-----------------+----------------+----------------+-----
----------
16386 | public | tab | 0 | 0 |
0 | 0 | | | |
(1 row)
# select * from pg_statio_user_indexes;
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read |
idx_blks_hit
-------+------------+------------+---------+--------------+---------------+-
-------------
16386 | 24581 | public | tab | idx_tab_a2 | 0 |
0
(1 row)
I've set:
stats_start_collector = on
stats_row_level = on
stats_block_level = on
and I think that the statistics collector should be running, because:
$ ps aux|grep stats
postgres 3688 0.0 0.0 7272 648 ? Ss 00:48 0:00 postgres:
stats collector process
postgres 29790 0.0 0.0 4004 712 pts/2 S+ 04:44 0:00 grep stats
Any help would be really appreciated.
Regards,
Yinan
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:SimSun;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:SimSun;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:SimSun;
panose-1:2 1 6 0 3 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
font-size:10.5pt;
font-family:"Calibri","sans-serif";}
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML \9884\8BBE\683C\5F0F Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:SimSun;}
span.HTMLChar
{mso-style-name:"HTML \9884\8BBE\683C\5F0F Char";
mso-style-priority:99;
mso-style-link:"HTML \9884\8BBE\683C\5F0F";
font-family:SimSun;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=ZH-CN link=blue vlink=purple style='text-justify-trim:punctuation'>
<div class=Section1>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>Hi,<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>When I use the
statistics collector to see the number of IO, I always get zero in almost all
of columns. I really want to know the reason for that.<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>The result of
statistics view:<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'># select * from \
pg_statio_user_tables;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'> relid | schemaname | \
relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | \
toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit \
<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>-------+------------+-------- \
-+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'> 16386 | \
public | tab \
| 0 \
| 0 \
| 0 \
| 0 \
|
|
|
|
<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>(1 \
row)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'># select * from \
pg_statio_user_indexes;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'> relid | indexrelid | \
schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit \
<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>-------+------------+-------- \
----+---------+--------------+---------------+--------------<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'> 16386 \
| 24581 | public | \
tab | idx_tab_a2 \
| 0 \
| \
0<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>(1 \
row)<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>I’ve \
set:<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>stats_start_collector
= on<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>stats_row_level = \
on<o:p></o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>stats_block_level = \
on<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>and I think that the \
statistics collector should be running, because: <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>$ ps aux|grep \
stats<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>postgres 3688 \
0.0 0.0 7272 648 \
? Ss 00:48 0:00 \
postgres: stats collector \
process
<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>postgres 29790 \
0.0 0.0 4004 712 pts/2 S+ \
04:44 0:00 grep stats<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal align=left style='text-align:left'><span lang=EN-US
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>Any help would be
really appreciated.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>Regards,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US \
style='font-size:9.0pt;font-family:"Arial","sans-serif"'>Yinan<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic