Обсуждение: 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