pg_stats not getting updated....

i just noted one more thing...
pg_stat_get_db_blocks_fetched/hit is getting updated
but pg_stat_get_blocks_fetched/hit are not getting
updated.....

why is this happening..

Regards
Himanshu

--- Himanshu Baweja <himanshubaweja@yahoo.com> wrote:

wht i wanted to do is... identify the tables which
are getting used simultaneously... so that i can
move them to different tablespaces....

for that i tried to do sampling of
"pg_statio_user_tables" for top 20 tables...(in
terms of usage)... so that i know how much io is
being done... for different tables and when....

now the problem is... pg_statio_user_tables is not
getting updated... at least wht i am able to make
out of documentation is they should be updated
regularly at each commit... but i am doing lots of
commits in my test application....

also docs state that withing each transaction block
postgres tries to give the same stats.... forget abt
transaction blocks.. i even tried.. disconnecting
and then reconnecting my sampling application every
two mins... but no use... each time i am getting
same stats...(only 4 updates in 30mins).....

one more thing that i noted is each time i run
analyze.... pg_statio_user_tables is updated....

plz note that all pg_stat* tables are not getting
updated not just pg_statio*....
i posted in general mailing list but no satisfying
reply so i thought maybe u all can tell whts
happening......

thx
Himanshu

---------------------------------
Discover Yahoo!
Find restaurants, movies, travel & more fun for the
weekend. Check it out!

__________________________________
Discover Yahoo!
Get on-the-go sports scores, stock quotes, news and more. Check it out!
http://discover.yahoo.com/mobile.html

Himanshu Baweja <himanshubaweja@yahoo.com> writes:

i just noted one more thing...
pg_stat_get_db_blocks_fetched/hit is getting updated
but pg_stat_get_blocks_fetched/hit are not getting
updated.....

That's pretty difficult to credit after looking at the pgstat.c code:
every incoming blocks_fetched count is added to both per-table and
per-database stats. I wonder if you are looking at the wrong per-table
entries?

regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

That's pretty difficult to credit after looking at
the pgstat.c code:
every incoming blocks_fetched count is added to both
per-table and
per-database stats. I wonder if you are looking at
the wrong per-table
entries?

i am 100% sure....
"SELECT pg_stat_get_db_blocks_fetched(764755937),
pg_stat_get_db_blocks_hit(764755937);"
gives be constantly increasing stats and

"SELECT relname,heap_blks_read from
pg_statio_user_tables order by heap_blks_read DESC
LIMIT 15;"

is still showing me all zero 4 mins into the test
until i first vacuum analyze is done....

just think abt this.... if we get these stats how
easily we can decide the division of tables in
tablespaces.... just write a simple program which will
collect the data every t mins... analyze it and move
them to diff tablespaces...

is there any other way of finding table usage???

thx a lot tom
Himanshu

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Himanshu Baweja <himanshubaweja@yahoo.com> writes:

"SELECT pg_stat_get_db_blocks_fetched(764755937),
pg_stat_get_db_blocks_hit(764755937);"
gives be constantly increasing stats and

"SELECT relname,heap_blks_read from
pg_statio_user_tables order by heap_blks_read DESC
LIMIT 15;"

is still showing me all zero 4 mins into the test
until i first vacuum analyze is done....

Um, looking at the view definition, heap_blks_read is the *difference*
between blocks_fetched and blocks_hit ... is it possible your test is
testing a 100%-cached situation, such that those two numbers increase
in lockstep?

regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Himanshu Baweja <himanshubaweja@yahoo.com> writes:

"SELECT pg_stat_get_db_blocks_fetched(764755937),
pg_stat_get_db_blocks_hit(764755937);"
gives be constantly increasing stats and

"SELECT relname,heap_blks_read from
pg_statio_user_tables order by heap_blks_read DESC
LIMIT 15;"

is still showing me all zero 4 mins into the test
until i first vacuum analyze is done....

Um, looking at the view definition, heap_blks_read
is the *difference*
between blocks_fetched and blocks_hit ... is it
possible your test is
testing a 100%-cached situation, such that those two
numbers increase
in lockstep?

regards, tom lane

both blocks fetched and block reads are zero... had
already checked for that.... => block hit is also
zero...

any ideas now...

thx
Himanshu

__________________________________
Discover Yahoo!
Have fun online with music videos, cool games, IM and more. Check it out!
http://discover.yahoo.com/online.html

hey tom and others
look at these....

how is the sum of all tables != database....

////////////////////////
qe18=# SELECT
pg_stat_get_db_blocks_fetched(771773788),pg_stat_get_db_blocks_hit(771773788);
pg_stat_get_db_blocks_fetched |
pg_stat_get_db_blocks_hit
-------------------------------+---------------------------
63787 |
61398
(1 row)

qe18=# SELECT sum(heap_blks_hit),sum(heap_blks_read)
from pg_statio_all_tables;
sum | sum
-------+-----
36200 | 942
(1 row)
////////////////////////////////////////

as far as the code goes both are same....

/*
* Process all table entries in the message.
*/
for (i = 0; i < msg->m_nentries; i++)
{
tabentry = (PgStat_StatTabEntry *)
hash_search(dbentry->tables,
(void *) &(tabmsg[i].t_id),
HASH_ENTER, &found);

if (!found)
{
/*
* If it's a new table entry, initialize counters
to the
* values we just got.
*/
tabentry->numscans = tabmsg[i].t_numscans;
tabentry->tuples_returned =
tabmsg[i].t_tuples_returned;
tabentry->tuples_fetched =
tabmsg[i].t_tuples_fetched;
tabentry->tuples_inserted =
tabmsg[i].t_tuples_inserted;
tabentry->tuples_updated =
tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted =
tabmsg[i].t_tuples_deleted;
tabentry->blocks_fetched =
tabmsg[i].t_blocks_fetched;
tabentry->blocks_hit = tabmsg[i].t_blocks_hit;

tabentry->destroy = 0;
}
else
{
/*
* Otherwise add the values to the existing entry.
*/
tabentry->numscans += tabmsg[i].t_numscans;
tabentry->tuples_returned +=
tabmsg[i].t_tuples_returned;
tabentry->tuples_fetched +=
tabmsg[i].t_tuples_fetched;
tabentry->tuples_inserted +=
tabmsg[i].t_tuples_inserted;
tabentry->tuples_updated +=
tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted +=
tabmsg[i].t_tuples_deleted;
tabentry->blocks_fetched +=
tabmsg[i].t_blocks_fetched;
tabentry->blocks_hit += tabmsg[i].t_blocks_hit;
}

/*
* And add the block IO to the database entry.
*/
dbentry->n_blocks_fetched +=
tabmsg[i].t_blocks_fetched;
dbentry->n_blocks_hit += tabmsg[i].t_blocks_hit;
}
///////////////////////////

any ideas why is this happening...

thx
Himanshu

__________________________________
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html