Vacuum stats interpreted?
От | Jeff Boes |
---|---|
Тема | Vacuum stats interpreted? |
Дата | |
Msg-id | 6b79e0aa2a1c3668937e1ce087c4a819@news.teranews.com обсуждение исходный текст |
Ответы |
Re: Vacuum stats interpreted?
|
Список | pgsql-admin |
Our largest (by total byte count) table has a nearly-equal flow of data in and out on a daily basis (that is, we add some 40k rows during the day, and then every night expire stuff that is timestamped beyond our cutoff, which results in 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, UnUsed 135721. 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: Deleted 732. 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 172914, 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. Can anyone interpret these and tell me anything about what we could do with this table to make it "look" better? (I'm not convinced that the extra space affects 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 (which 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 tables to retain bulk (nice image, eh?).
В списке pgsql-admin по дате отправления: