Re: Help tuning autovacuum - seeing lots of relationbloat - Mailing list pgsql-performance

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
51620pages 
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
19849pages 
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
versionsin 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
24341pages 
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
versionsin 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
TXmore 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.
>
>
>