Re: Help tuning autovacuum - seeing lots of relationbloat

Opps - that was confusing. The idle in transaction was from one box and the autovacuum was from another.

So, one question was answered, auto vacuum is running and selecting the tables but apparently not at the
same time as my app probably due to this "idle in transaction". I will track it down and see what the difference is.

thanks

On Wednesday 21 June 2006 13:21, jody brownell wrote:
> That is interesting.
>
> There is one thread keeping a transaction open it appears from ps
>
> postgres: app app xxx(42644) idle in transaction
>
> however, I created a test table "t" not configured in pg_autovacuum. I inserted a whack of rows and saw this.
>
> Jun 21 12:38:45 vanquish postgres[1525]: [8-1] LOG: autovacuum: processing database "qradar"
> Jun 21 12:38:45 vanquish postgres[1525]: [9-1] DEBUG: autovac: will VACUUM ANALYZE t
> Jun 21 12:38:45 vanquish postgres[1525]: [10-1] DEBUG: vacuuming "public.t"
> Jun 21 12:38:48 vanquish postgres[1525]: [11-1] DEBUG: "t": removed 8104311 row versions in 51620 pages
> Jun 21 12:38:48 vanquish postgres[1525]: [11-2] DETAIL: CPU 0.93s/0.70u sec elapsed 1.70 sec.
> Jun 21 12:38:48 vanquish postgres[1525]: [12-1] DEBUG: "t": found 8104311 removable, 0 nonremovable row versions in 51620 pages
> Jun 21 12:38:48 vanquish postgres[1525]: [12-2] DETAIL: 0 dead row versions cannot be removed yet.
>
> followed a later (after I did a similar insert op on target) by this
>
> Jun 21 13:00:46 vanquish postgres[3311]: [12-1] LOG: autovacuum: processing database "qradar"
> Jun 21 13:00:46 vanquish postgres[3311]: [13-1] DEBUG: autovac: will VACUUM target
> Jun 21 13:00:46 vanquish postgres[3311]: [14-1] DEBUG: vacuuming "public.target"
> Jun 21 13:01:51 vanquish postgres[3311]: [15-1] DEBUG: index "target_pkey" now contains 1296817 row versions in 25116 pages
> Jun 21 13:01:51 vanquish postgres[3311]: [15-2] DETAIL: 5645230 index row versions were removed.
> Jun 21 13:01:51 vanquish postgres[3311]: [15-3] ^I116 index pages have been deleted, 60 are currently reusable.
> Jun 21 13:01:51 vanquish postgres[3311]: [15-4] ^ICPU 1.29s/7.44u sec elapsed 48.65 sec.
> Jun 21 13:02:19 vanquish postgres[3311]: [16-1] DEBUG: index "target_network_key" now contains 1296817 row versions in 19849 pages
> Jun 21 13:02:19 vanquish postgres[3311]: [16-2] DETAIL: 5645230 index row versions were removed.
> Jun 21 13:02:19 vanquish postgres[3311]: [16-3] ^I32 index pages have been deleted, 0 are currently reusable.
> Jun 21 13:02:19 vanquish postgres[3311]: [16-4] ^ICPU 0.89s/6.61u sec elapsed 27.77 sec.
> Jun 21 13:02:47 vanquish postgres[3311]: [17-1] DEBUG: index "target_network_details_id_idx" now contains 1296817 row versions in 23935 pages
> Jun 21 13:02:47 vanquish postgres[3311]: [17-2] DETAIL: 5645230 index row versions were removed.
> Jun 21 13:02:47 vanquish postgres[3311]: [17-3] ^I17814 index pages have been deleted, 0 are currently reusable.
> Jun 21 13:02:47 vanquish postgres[3311]: [17-4] ^ICPU 0.93s/7.52u sec elapsed 27.36 sec.
> Jun 21 13:03:23 vanquish postgres[3311]: [18-1] DEBUG: index "target_tulu_idx" now contains 1296817 row versions in 24341 pages
> Jun 21 13:03:23 vanquish postgres[3311]: [18-2] DETAIL: 5645230 index row versions were removed.
> Jun 21 13:03:23 vanquish postgres[3311]: [18-3] ^I18495 index pages have been deleted, 0 are currently reusable.
> Jun 21 13:03:23 vanquish postgres[3311]: [18-4] ^ICPU 1.37s/5.38u sec elapsed 36.95 sec.
> Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: "target": removed 5645231 row versions in 106508 pages
> Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec.
> Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: "target": found 5645231 removable, 1296817 nonremovable row versions in 114701 pages
> Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet.
>
> this was with the "Idle in transaction" though.....
>
> Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence.
> Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline.
>
> I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often.
>
> make sense?
>
>
> On Wednesday 21 June 2006 12:42, Csaba Nagy wrote:
> > On Wed, 2006-06-21 at 17:27, jody brownell wrote:
> > > Our application is broken down quite well. We have two main writing processes
> > > writing to two separate sets of tables. No crossing over, nothign to prohibit the
> > > vacuuming in the nature which you describe.
> >
> > It really doesn't matter what table are you touching, as it doesn't
> > matter if you read or write either, what matters is how long ago was the
> > last "begin" without "commit" or "rollback". VACUUM will not touch
> > tuples which were deleted after the oldest not yet finished transaction
> > started, regardless if that transaction touched the vacuumed table or
> > not in any way...
> >
> > > My longest transaction on the tables in question are typically quite short until
> > > of course they begin to bloat.
> >
> > Well, your application might be completely well behaved and still your
> > DBA (or your favorite DB access tool for that matter) can leave open
> > transactions in an interactive session. It never hurts to check if you
> > actually have "idle in transaction" sessions. It happened a few times to
> > us, some of those were bad coding on ad-hoc tools written by us, others
> > were badly behaved DB access tools opening a transaction immediately
> > after connect and after each successful command, effectively leaving an
> > open transaction when leaving it open while having lunch...
> >
> > So it might very well be that some interactive or ad hoc tools you're
> > using to manage the DB are your problem.
> >
> > Cheers,
> > Csaba.
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>