Re: Postgres performance slowly gets worse over a month

I look at it like this:

Your database takes up space X after a full vacuum and is ready for the
next 6 months of inserts. Then , over the next 6 months it grows by
space Y, it now occupies X+Y Space.

You then remove a bunch of old tuples. Space is still X+Y. You now
have 2 basic options:

1. Run a vacuum full -- this locks the entier table, and de-fragments
all unused space, so space is now back to X. Table will grow incremently
by Y over the next 6 months again.
2. Run a lazy vacuum-- no lock, no de-fragment, space is still X+Y.
Assuming max_fsm_pages was large enough to hold all the changed pages,
over the next 6 months, the space remains fixed at about X+Y. You are
now re-using the unused table space.

Either solution will work. If you really want to cut disk space, choose
1. If you want to keep the space at about it optimal size and avoid any
downtime, choose 2.

--Michael

Michael G. Martin wrote:

> Yea, you're correct. I think you'll be able to avoid the vacuum full
> and re-use the tuples by making sure max_fsm_pages is large enough to
> handle the number of pages changed by the 6-month massacre. After your
> vacuum, note the unused tuples and page size of the table. Then, as
> you incremently add new stuff over the next 6 months, you should see
> the unused tuples decrease while the page size remains failry fixed.
> The only other thing you may want to do more frequently is analyze if
> the new tuples might change some statistics during the 6-month interval.
>
> --Michael
>
> Joshua Daniel Franklin wrote:
>
>>Perhaps I wasn't clear. There really aren't any (daily) "no-longer needed
>>tuples", just added ones. I am under the impression that vacuum is just for
>>freeing up tuples to be re-used, so the only time it needs to be run is after
>>the 6-monthly tuple massacre, at which time I would also need to set
>>max_fsm_pages to a huge number.
>>
>>--- "Michael G. Martin" <michael(at)vpmonline(dot)com> wrote:
>>
>>
>>>I believe the more frequently you vacuum, the faster it will go, so that
>>>may be the driving factor in deciding. Personally, each day, I'd add
>>>the new tuples then remove the no-longer needed tuples, make sure
>>>max_fsm_pages is large enough to handle all the pages removed in the
>>>largest table, then run a vacuum analyze on the table or entire
>>>database. Run it each night and it will be nice and fast and you
>>>shouldn't ever need to worry about locking the entire table with a
>>>vacuum full or spend time to re-create the table and indicies.
>>>
>>>That's what I do which I think is the most automated,maintainance-free
>>>solution. I currently run a lazy vacuum analyze each night after making
>>>my large changes. My tables don't change enough during the day to
>>>require mid-day vacuums.
>>>
>>>--Michael
>>>
>>>Joshua Daniel Franklin wrote:
>>>
>>>
>>>>>In addition, max_fsm_pages has an impact on how many pages will be
>>>>>available to be marked as re-usable. If you have a huge table and
>>>>>changes are impacting more than the default 10,000 pages this is set to,
>>>>>you will want to bump this number up. My problem was I saw my UnUsed
>>>>>tuples always growing and not being re-used until I bumped this value
>>>>>up. As I watched the vacuum verbose output each run, I notices more
>>>>>than 10k pages were in fact changing between vacuums.
>>>>>
>>>>>
>>>>>
>>>>This has made me think about something we've been doing. We've got one
>>>>db that is used basically read-only; every day ~15000 records are added,
>>>>but very rarely are any deleted. What we've been doing is just letting it
>>>>sit until it gets close to too big for the filesystem, then lopping off
>>>>the earliest 6 months worth of records. The question is, is it best
>>>>to do this then set the max_fsm_pages to a huge number and vacuum full?
>>>>Or should I change it so scripts remove the oldest day and vacuum before
>>>>adding the next days?
>>>>
>>>>Or just rebuild the db every time. :)
>>>>
>>>>
>>>>
>>
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Yahoo! Health - Feel better, live better
>>http://health.yahoo.com
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>message can get through to the mailing list cleanly
>>
>>
>