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
