Re: Catching up Production from Warm Standby aftermaintenance - Please help
От
Scott Mead
Тема
Re: Catching up Production from Warm Standby aftermaintenance
- Please help
Дата
Msg-id
d3ab2ec80907071050y731b9a6fna5e0ff483528db4b@mail.gmail.com
Ответ на
Re: Catching up Production from Warm Standby
aftermaintenance - Please help (Jennifer Spencer)
Список
Дерево обсуждения
Catching up Production from Warm Standby after maintenance - Please
help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Mead <scott.lists@enterprisedb.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby
after maintenance - Please help "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Catching up Production from Warm Standby after
maintenance - Please help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby aftermaintenance - Please help "Scott Whitney" <swhitney@journyx.com>
Re: Catching up Production from Warm Standby
aftermaintenance - Please help Alvaro Herrera <alvherre@commandprompt.com>
Re: Catching up Production from Warm Standbyaftermaintenance - Please help "Scott Whitney" <swhitney@journyx.com>
Re: Catching up Production from Warm Standbyaftermaintenance - Please help Michael Monnerie <michael.monnerie@is.it-management.at>
Re: Catching up Production from Warm Standbyaftermaintenance
- Please help Lewis Kapell <lkapell@setonhome.org>
Re: Catching up Production from Warm Standbyaftermaintenance
- Please help Scott Mead <scott.lists@enterprisedb.com>
Re: Catching up Production from Warm Standby aftermaintenance
- Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby aftermaintenance - Please help "Scott Whitney" <swhitney@journyx.com>
Re: Catching up Production from Warm Standby
aftermaintenance - Please help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby aftermaintenance
- Please help Scott Mead <scott.lists@enterprisedb.com>
Re: Catching up Production from Warm Standby
aftermaintenance - Please help "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help Jennifer Spencer <jenniferm411@hotmail.com>
Re: Catching up Production from Warm Standby
after maintenance - Please help "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Catching up Production from Warm Standby after
maintenance - Please help Scott Marlowe <scott.marlowe@gmail.com>
Re: Catching up Production from Warm Standby after
maintenance - Please help "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
On Tue, Jul 7, 2009 at 1:40 PM, Jennifer Spencer <jenniferm411@hotmail.com> wrote:
You are sure that the XID wraparound is gone? That's good news. No other reasons for vacuum full on the entire database.
I think we're talking apples and gorillas on the use of the word 'full'.
There is a command:
VACUUM FULL;
When you do that, you lock a table and much hatred reigns upon you from user-land.
When you:
vacuum all databases in a cluster (notice, no 'FULL' here), (could be through autovacuum)
You are doing a vacuum that is capable of operating alongside transactions.
XID wrap-around is still an issue, but if you have autovacuum on and you pay attention to your logs, you'll be okay.
We could do it a table at a time if we absolutely have to do it, and that would minimize down time on the rest of the system.
No need, see above.
Also, a quick note about your growth pattern (~ 1 TB / year)
If you're going to be growing that much, index builds on a TB of data really stink. You may want to look into constraint_exclusion partitioning (maybe by quarter or something easy to chunk up ) would be worth-while. When you do need to do index rebuilds, you can:
A) Do them concurrently
Rebuilding an index becomes
CREATE INDEX CONCURRENTLY my_index_1 ....
DROP INDEX my_index_0;
ANALYZE table;
That way, you aren't blocking during that rebuild.
B) Your indexes will be a subset of your data, in my example, you'd only be building one quarter's worth of indexes.
C) It's possible that after a quarter is over, you won't even need to reindex because maybe you won't need data from a previous quarter.
Good - glad to be wrong about that! We do mostly inserts, no updates and very few deletes. We drop entire tables but don't delete often. We have very long rows, though. Do you think the above is a situation likely to create extreme bloat?
I wonder, what do you use to decide when to reindex? Under this situation, it's very possible that you don't need to do it all that often. Are you just flying 'willy-nilly' about reindexing things, or is there some indicator you use?
--Scott
-Jennifer
> > I _think_ autovacuum, somewhere around early 8.x resolves the transaction
> > wrap-around issues, but someone else should verify that.
>
> Ayup.
>> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
Lauren found her dream laptop. Find the PC that’s right for you.
В списке pgsql-admin по дате отправления
От: Lewis Kapell
Дата:
От: Scott Mead
Дата:
