Обсуждение: pg_stats not getting updated....
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!
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