Re: [PERFORM] Avoiding vacuum full on an UPDATE-heavy table
Bill, > As a quick fix, since we're upgrading to 7.4.2 in a few weeks anyhow > (which includes pg_autovacuum), I've simply set up an hourly vacuum on > this table. It only takes ~4 seconds to execute when kept up on an > hourly basis. Is there any penalty to vacuuming too frequently, other > than the time wasted in an unnecessary vacuum operation? Nope, no penalty other than the I/O and CPU load while vacuuming. If you have a lot of transactions involving serial writes to many tables, sometimes you can get into a deadlock situation, which is annoying, but I wouldn't assume this to be a problem until it crops up. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Avoiding vacuum full on an UPDATE-heavy table
Bill Montgomery <[EMAIL PROTECTED]> writes: > I have a particularly troublesome table in my 7.3.4 database. It > typically has less than 50k rows, and a usage pattern of about 1k > INSERTs, 50-100k UPDATEs, and no DELETEs per day. It is vacuumed and > analyzed three times per week. You probably want to vacuum (non-FULL) once a day, if not more often. Also take a look at your FSM settings --- it seems like a good bet that they're not large enough to remember all the free space in your database. With adequate FSM the table should stabilize at a physical size corresponding to number-of-live-rows + number-of-updates-between-VACUUMs, which would be three times the minimum possible size if you vacuum once a day (50K + 100K) or five times if you stick to every-other-day (50K + 200K). Your VACUUM FULL output shows that the table had bloated to hundreds of times the minimum size: > INFO: Rel : Pages: 188903 --> 393; Tuple(s) moved: 17985. and AFAIK the only way that will happen is if you fail to vacuum at all or don't have enough FSM. The indexes are looking darn large as well. In 7.3 about the only thing you can do about this is REINDEX the table every so often. 7.4 should behave better though. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Avoiding vacuum full on an UPDATE-heavy table
Matthew T. O'Connor wrote: Is there any way to avoid doing a periodic VACUUM FULL on this table, given the fairly radical usage pattern? Or is the (ugly) answer to redesign our application to avoid this usage pattern? pg_autovacuum would probably help as it monitors activity and vacuumus tables accordingly. It is not included with 7.3.x but if you download it and compile yourself it will work against a 7.3.x server. As a quick fix, since we're upgrading to 7.4.2 in a few weeks anyhow (which includes pg_autovacuum), I've simply set up an hourly vacuum on this table. It only takes ~4 seconds to execute when kept up on an hourly basis. Is there any penalty to vacuuming too frequently, other than the time wasted in an unnecessary vacuum operation? My hourly VACUUM VERBOSE output now looks like this: INFO: --Relation public.-- INFO: Index __idx: Pages 30452; Tuples 34990: Deleted 1226. CPU 0.67s/0.18u sec elapsed 0.87 sec. INFO: Index __idx: Pages 19054; Tuples 34991: Deleted 1226. CPU 0.51s/0.13u sec elapsed 1.35 sec. INFO: Removed 1226 tuples in 137 pages. CPU 0.01s/0.00u sec elapsed 1.30 sec. INFO: Pages 13709: Changed 31, Empty 0; Tup 34990: Vac 1226, Keep 0, UnUsed 567233. Total CPU 1.58s/0.31u sec elapsed 3.91 sec. INFO: Analyzing public. VACUUM With regards to Vivek's post about index bloat, I tried REINDEXing before I did a VACUUM FULL a month ago when performance had gotten dismal. It didn't help :-( Best Regards, Bill Montgomery ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Avoiding vacuum full on an UPDATE-heavy table
> "BM" == Bill Montgomery <[EMAIL PROTECTED]> writes: BM> Is there any way to avoid doing a periodic VACUUM FULL on this table, BM> given the fairly radical usage pattern? Or is the (ugly) answer to BM> redesign our application to avoid this usage pattern? I'll bet upgrading to 7.4.2 clears up your problems. I'm not sure if it was in 7.3 or 7.4 where the index bloat problem was solved. Try to see if just reindexing will help your performance. Also, run a plain vacuum at least nightly so that your table size stays reasonable. It won't take much time on a table with only 50k rows in it. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Avoiding vacuum full on an UPDATE-heavy table
> Is there any way to avoid doing a periodic VACUUM FULL on this table, > given the fairly radical usage pattern? Or is the (ugly) answer to > redesign our application to avoid this usage pattern? Yes, you should be able to doing avoid periodic VACUUM FULL. The problem is that your table needs to be vacuumed MUCH more often. What should happen is that assuming you have enough FSM space allocated and assuming you vacuum the "right" amount, your table will reach a steady state size. As you could see your from you vacumm verbose output your table was almost entriely dead space. pg_autovacuum would probably help as it monitors activity and vacuumus tables accordingly. It is not included with 7.3.x but if you download it and compile yourself it will work against a 7.3.x server. Good luck, Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Avoiding vacuum full on an UPDATE-heavy table
All, I have a particularly troublesome table in my 7.3.4 database. It typically has less than 50k rows, and a usage pattern of about 1k INSERTs, 50-100k UPDATEs, and no DELETEs per day. It is vacuumed and analyzed three times per week. However, the performance of queries performed on this table slowly degrades over a period of weeks, until even a "select count(*)" takes several seconds. The only way I've found to restore performance is to VACUUM FULL the table, which is highly undesireable in our application due to the locks it imposes. Here is the output of a psql session demonstrating the problem/solution. Note the \timing output after each of the SELECTs: => vacuum analyze ; NOTICE: VACUUM will be committed automatically VACUUM Time: 715900.74 ms => select count(*) from ; count --- 17978 (1 row) Time: 171789.08 ms => vacuum full verbose ; NOTICE: VACUUM will be committed automatically INFO: --Relation public.-- INFO: Pages 188903: Changed 60, reaped 188896, Empty 0, New 0; Tup 17987: Vac 1469, Keep/VTL 0/0, UnUsed 9120184, MinLen 92, MaxLen 468; Re-using: Free/Avail. Space 1504083956/1504083872; EndEmpty/Avail. Pages 0/188901. CPU 6.23s/1.07u sec elapsed 55.02 sec. INFO: Index __idx: Pages 29296; Tuples 17987: Deleted 1469. CPU 1.08s/0.20u sec elapsed 61.68 sec. INFO: Index __idx: Pages 18412; Tuples 17987: Deleted 1469. CPU 0.67s/0.05u sec elapsed 17.90 sec. INFO: Rel : Pages: 188903 --> 393; Tuple(s) moved: 17985. CPU 15.97s/19.11u sec elapsed 384.49 sec. INFO: Index __idx: Pages 29326; Tuples 17987: Deleted 17985. CPU 1.14s/0.65u sec elapsed 32.34 sec. INFO: Index __idx: Pages 18412; Tuples 17987: Deleted 17985. CPU 0.43s/0.32u sec elapsed 13.37 sec. VACUUM Time: 566313.54 ms => select count(*) from ; count --- 17987 (1 row) Time: 22.82 ms Is there any way to avoid doing a periodic VACUUM FULL on this table, given the fairly radical usage pattern? Or is the (ugly) answer to redesign our application to avoid this usage pattern? Also, how do I read the output of VACUUM FULL? http://www.postgresql.org/docs/7.3/interactive/sql-vacuum.html does not explain how to interpret the output, nor has google helped. I have a feeling that the full vacuum is compressing hundreds of thousands of pages of sparse data into tens of thousands of pages of dense data, thus reducing the number of block reads by an order of magnitude, but I'm not quite sure how to read the output. FWIW, this is last night's relevant output from the scheduled VACUUM ANALYZE. 24 days have passed since the VACUUM FULL above: INFO: --Relation public.xxx-- INFO: Index xxx_yyy_idx: Pages 30427; Tuples 34545: Deleted 77066. CPU 1.88s/0.51u sec elapsed 95.39 sec. INFO: Index xxx_zzz_idx: Pages 19049; Tuples 34571: Deleted 77066. CPU 0.83s/0.40u sec elapsed 27.92 sec. INFO: Removed 77066 tuples in 3474 pages. CPU 0.38s/0.32u sec elapsed 1.33 sec. INFO: Pages 13295: Changed 276, Empty 0; Tup 34540: Vac 77066, Keep 0, UnUsed 474020. Total CPU 3.34s/1.29u sec elapsed 125.00 sec. INFO: Analyzing public.xxx Best Regards, Bill Montgomery ---(end of broadcast)--- TIP 8: explain analyze is your friend
