Обсуждение: Stat estiamtes off - why?

Поиск
Список
Период
Сортировка

Stat estiamtes off - why?

От
"Nicholson, Brad (Toronto, ON, CA)"
Дата:

Hi,


I’m seeing something odd in my DB stats.  This is PG 9.2.4 on CentOS.  I know the stats collector can be off at times, but I am curious as to why it would be off in such a case.  I know that under heavy load the stats collector can be off sometimes, but this system was barely doing anything (CPU ~ 90% idle).  PG didn’t shutdown at all either.

 

I’m curious how it ended up with 191 live tuples, but only did 17 inserts to the table.  In actuality, the table is empty at the time I ran this query, manually analyzing it updated the live_tuples back down to 0.  I

 

select n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup from pg_stat_user_tables where relname = 'foo';

-[ RECORD 1 ]-----+------------------------------

n_tup_ins         | 17

n_tup_upd         | 305

n_tup_del         | 17

n_tup_hot_upd     | 297

n_live_tup        | 191

n_dead_tup        | 11

 

Thanks,

Brad.

Re: Stat estiamtes off - why?

От
Tom Lane
Дата:
"Nicholson, Brad (Toronto, ON, CA)" <bnicholson@hp.com> writes:
> I'm seeing something odd in my DB stats.  This is PG 9.2.4 on CentOS.  I know the stats collector can be off at
times,but I am curious as to why it would be off in such a case.  I know that under heavy load the stats collector can
beoff sometimes, but this system was barely doing anything (CPU ~ 90% idle).  PG didn't shutdown at all either. 

Most likely there was some DML happening concurrently with a VACUUM or
ANALYZE at some point in the past.  VACUUM/ANALYZE update the live and
dead tuple counts based on what they saw, but a transaction that added
such tuples (that were counted by the maintenance op) could commit after
that happens, and then you end up with double-counted tuples.

            regards, tom lane