Re: 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(at)yahoo(dot)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(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Himanshu Baweja <himanshubaweja(at)yahoo(dot)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