To test your theory about COMMIT; BEGIN; you could check the PostgreSQL log,
it is likely to contain a line:
WARNING: there is no transaction in progress
"Jeff Boes" <jboes(at)qtm(dot)net> wrote in message
> Our largest (by total byte count) table has a nearly-equal flow of data in
> out on a daily basis (that is, we add some 40k rows during the day, and
> every night expire stuff that is timestamped beyond our cutoff, which
> about 40k deletions).
> After the deletions, the table gets vacuumed (not FULL):
> INFO: --Relation public.articles_content--
> INFO: Index ix_articles_content_pk: Pages 398; Tuples 180175: Deleted 0.
> CPU 0.00s/0.08u sec elapsed 0.08 sec.
> INFO: Removed 122 tuples in 77 pages.
> CPU 0.00s/0.01u sec elapsed 0.02 sec.
> INFO: Pages 3886: Changed 0, Empty 0; Tup 180175: Vac 122, Keep 26437,
> Total CPU 0.02s/0.17u sec elapsed 0.19 sec.
> INFO: --Relation pg_toast.pg_toast_634643688--
> INFO: Index pg_toast_634643688_index: Pages 27156; Tuples 1256923:
> CPU 2.32s/0.80u sec elapsed 27.93 sec.
> INFO: Removed 732 tuples in 250 pages.
> CPU 0.01s/0.04u sec elapsed 0.05 sec.
> INFO: Pages 1667633: Changed 71514, Empty 0; Tup 1256923: Vac 732, Keep
> UnUsed 5499031.
> Total CPU 148.07s/12.52u sec elapsed 809.71 sec.
> INFO: Analyzing public.articles_content
> The "Keep" and "UnUsed" numbers seem high to me, compared to other tables.
> anyone interpret these and tell me anything about what we could do with
> table to make it "look" better? (I'm not convinced that the extra space
> anything but sequential scans, and we don't do that to this table.)
> If it helps: I have a theory that database connections from our webserver
> is running PHP and mod_perl) are in "idle in transaction" states (in other
> words, they issue "Commit; Begin;" and then sleep), which causes the
> retain bulk (nice image, eh?).
In response to
pgsql-admin by date
|Next:||From: Adam Ruth||Date: 2003-11-26 21:33:56|
|Subject: Re: pg_lo_import alternative ...|
|Previous:||From: Oli Sennhauser||Date: 2003-11-26 17:40:18|
|Subject: Re: Size on Disk|