pg_stats not getting updated....

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_stats not getting updated....

Himanshu Baweja
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!
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Himanshu Baweja
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 <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Tom Lane-2
Himanshu Baweja <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Himanshu Baweja
--- Tom Lane <[hidden email]> 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 

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Tom Lane-2
Himanshu Baweja <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Himanshu Baweja
--- Tom Lane <[hidden email]> wrote:

> Himanshu Baweja <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: pg_stats not getting updated....

Himanshu Baweja
In reply to this post by Tom Lane-2
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]