Re: Curious about dead rows.

Merlin Moncure wrote:
> On Nov 13, 2007 9:26 PM, Jean-David Beyer <jeandavid8@verizon.net> wrote:
>> Merlin Moncure wrote:
>>> what does pg_stat_all_tables say (assuming row level stats are on)?
>> It says stuff like this:
>>
>>  relname  | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins |
>> n_tup_upd | n_tup_del
>> ----------+----------+--------------+----------+---------------+-----------+-
>>  ibd      |       75 |      9503850 |       11 |       2350555 |   2416845 |
>>         0 |         0
>>  vl_cf    |      139 |     38722575 |       22 |       5392609 |   5692814 |
>>         0 |         0
>>  vl_li    |      139 |     39992838 |       22 |       5569855 |   5885516 |
>>         0 |         0
>>
>> I removed the relid and schemaname and squeezed the other columns so it
>> would not be quite so wide. Is this what you might like to know?
>
> it tells me that you aren't crazy, and that rollbacks are the likely
> the cause, although you appear to be watching the logs pretty
> carefully.  you can check pg_stat_database to confirm if your
> rollbacks are in line with your expectations.  or, you might by seeing
> some corner case conditions...are any fields in the table foreign
> keyed to another table (cascading update/delete)?  do you have any
> functions with handled exceptions or savepoints? (I'm guessing no to
> the latter).
>
How do I reset the counters in pg_stat_database and pg_stat_all_tables?
I tried just restarting postgres, but it seems to be saved in the database,
not just in the RAM of the server.

Right now I am getting:

stock=> SELECT * FROM pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read |
blks_hit
-------+-----------+-------------+-------------+---------------+-----------+----------

 16402 | stock     |           1 |   261428429 |       3079861 |         0 |
       0
(4 rows)

I just watched these as the loading program runs, and I can account for all
the new rollbacks, that come after the dead rows are found.

I suppose that blks_read and blks_hit are zero because there are 8 GBytes
RAM on this machine and I give 2GBytes to shared_buffers = 253000 so that
all sits in RAM.

I know there have been rollbacks but I do a REINDEX, CLUSTER, and VACUUM
ANALYZE before starting the inserts in question. Do I need to do a VACUUM
FULL ANALYZE instead?

When there were errors in the input data, the program just rolls back the
transaction and gives up on that input file. (The program processes hundreds
of input files and I get an additional input file each week. I then correct
the error in the input file and start over. I do not do updates because the
input file needs to be corrected anyhow. and the easiest way to check it is
to load it into the database and let the loader programs check it.)

Keeping things in perspective, the autovacuum gets these eventually, and I
do not think it is really hurting performance all that much. But I would
like to understand what is going on.

--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 06:25:01 up 21 days, 23:43, 0 users, load average: 4.02, 4.01, 4.00