Here is the latest I received from our Field Engineering regard to the
Index Corruption and the growth of our tables. I would appreciate any
help to figure out why we get the index corruption and why our tables
grow so fast?

[Ali Ebrahimi]  alie@atlas.com 

>      
>      PG_VERSION v6.3, FreeBSD 3.0-971031-SNAP
>      
>        The 'before and after' file lists below show a fifty percent 
>      increase in the size of user account during the course of one
> day.  
>      Since the database is around 300,000 records we might assume that
> the 
>      increase reflects about 150,000 added records.  We are averaging
> about 
>      25,000 completed transactions to acct_history each day.  This
> data 
>      suggests about six updates to user_acct for each update to 
>      acct_history which is about what we expect.
>      
>        What we don't expect is for our user_acct to grow (so much!) in
> size 
>      with simple updates.
>      
>        We also don't expect 'btree: BTP_CHAIN flag was expected'
> errors to 
>      be popping up.  We have seen btree errors in both acct_history
> and 
>      user_acct.  acct_history_acct_no_idx is non-unique, 
>      user_acct_card_no_idx is unique, the other user_acct indexes are 
>      non-unique.  All indexes are btree.
>      
>        We did not see these errors until the tables grew over 80 Meg.
>      
>        In acct_history we are doing inserts only.  In user_acct we are
> 
>      doing updates only.
>      
>        I estimate, at peak load, we are processing an average of five 
>      transactions per second to user_acct.  Spikes would probably go
> as 
>      high as 15-20 trans per second.  On acct_history it would be more
> like 
>      one transaction per second.  user_acct occurences outnumber 
>      acct_history 5:1.
>      
>        Also notice the indices grew *after* reindexing and vacuuming.
> We 
>      did add 5000 cards today but aren't the indices suppposed to
> update on 
>      insert?
>      
>        We'd like to solve two problems here:
>      
>         1. BTP_CHAIN errors cause system crash during peak traffic.
>         2. Table sizes grow too much in short period of time.
>      
>        Best Regards,
>      
>            -Dave
>      
>        David Schanen : Atlas Telecom : mtv@scene.com
>      
>        ----------  Before Reindex and Vacuum ---------
>      
>        pgsql  176611328 May  6 02:13 acct_history
>        pgsql   55787520 May  6 02:13 acct_history_acct_no_idx
>        pgsql  120594432 May  6 02:17 user_acct
>        pgsql   22855680 May  6 02:17 user_acct_acct_no_idx
>        pgsql   31547392 May  6 02:17 user_acct_card_acct_sim_idx
>        pgsql   15908864 May  6 02:17 user_acct_card_no_idx
>        pgsql    9986048 May  6 02:17 user_acct_serial_no_idx
>        pgsql   12328960 May  6 02:17 user_acct_sim_idx
>        pgsql       8192 May  6 02:13 user_acct_state
>        pgsql      16384 May  2 03:00 user_acct_state_state_idx
>      
>        ----------  After Reindex and Vacuum ---------
>      
>        pgsql  176611328 May  6 02:13 acct_history
>        pgsql   55787520 May  6 02:13 acct_history_acct_no_idx
>        pgsql   81649664 May  6 02:41 user_acct
>        pgsql   29327360 May  6 02:41 user_acct_acct_no_idx
>        pgsql   45195264 May  6 02:41 user_acct_card_acct_sim_idx
>        pgsql   18595840 May  6 02:41 user_acct_card_no_idx
>        pgsql   15212544 May  6 02:41 user_acct_serial_no_idx
>        pgsql   12566528 May  6 02:41 user_acct_sim_idx
>        pgsql       8192 May  6 02:13 user_acct_state
>        pgsql      16384 May  2 03:00 user_acct_state_state_idx

Tue, 5 May 1998 14:16:19 -0700
AliE@atlas.com
FW: BTP_CHAIN flag was expected
On Tue, 5 May 1998 AliE@atlas.com wrote:

> >         1. BTP_CHAIN errors cause system crash during peak traffic.

	Try upgrading to v6.3.2 ... I won't guarantee it, but the
BTP_CHAIN problem has disappeared on our system(s) since we've upgraded...

> >         2. Table sizes grow too much in short period of time.

	What sort of transactions are being performed?  If updates, then
the only way of shrinking the files back down again is to perform a vacuum
periodically... 


Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 5 May 1998 18:29:58 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] FW: BTP_CHAIN flag was expected