On 25/09/2007, Vivek Khera <[EMAIL PROTECTED]> wrote:
> Recommending I run vacuum intermixed with the data purge is a non-
> starter; the vacuum on these tables takes a couple of hours. I'd
> never finish purging my data with that kind of delay.
...
> I will investigate the fill-factor. That se
"Vivek Khera" <[EMAIL PROTECTED]> writes:
> On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote:
>
>> I feel your pain. But I seem to have (mostly) solved my problem in three
>> ways:
>
> My particular usage pattern (add data continuously, purge *some* of the data
> once per week or every other w
On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote:
I feel your pain. But I seem to have (mostly) solved my problem in
three ways:
My particular usage pattern (add data continuously, purge *some* of
the data once per week or every other week. The purge is what seems
to kill it. Last tim
On 24/09/2007, Vivek Khera <[EMAIL PROTECTED]> wrote:
>
> my FSM is way bigger than I ever use (vacuum never reports shortage)
> and I still get bloat that needs to be purged out with a reindex on
> occasion.
Vivek,
I feel your pain. But I seem to have (mostly) solved my problem in three ways:
On Sep 18, 2007, at 1:14 AM, Joshua D. Drake wrote:
Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
reindex/cluster indexes?
If you overrun your max_fsm_pages, no:
else yes;
my algorithm is: if (true) then yes;
my FSM is way bigger than I ever use (vacuum never report
On Sep 19, 2007, at 11:00 AM, Richard Broersma Jr wrote:
--- Erik Jones <[EMAIL PROTECTED]> wrote:
Also, note that once we have HOT...
I am not sure what the acronym "HOT" stands for. Does it have
something to do with MVCC?
Heap Only Tuple. Here's a link to the (latest?) readme for i
--- Erik Jones <[EMAIL PROTECTED]> wrote:
> Also, note that once we have HOT...
I am not sure what the acronym "HOT" stands for. Does it have something to do
with MVCC?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget
On Sep 19, 2007, at 10:30 AM, Richard Broersma Jr wrote:
Not quite. Once a page has reached it's fill factor percentage full,
no more inserts will happen on that page, only updates. Also, I
think you have large/small backwards wrt fill factor. If you have a
fill factor of, say, 40% then onc
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> 2. Is this fill factor enough to have on the table, or should I also
> do a fill factor for specific indexes? Or both the table and the
> index? (I have four btree indexes on the table)
I don't think that fill factor can be applied to the table. The
> Not quite. Once a page has reached it's fill factor percentage full,
> no more inserts will happen on that page, only updates. Also, I
> think you have large/small backwards wrt fill factor. If you have a
> fill factor of, say, 40% then once a page has reached 40% full no
> more inser
On Sep 19, 2007, at 9:29 AM, Richard Broersma Jr wrote:
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
Then I am confused again about how the fill factor stuff works. Let's
say I have a table with four BTREE indexes. Should all of them have a
fill factor of about 60 (lower than the default 90, t
Phoenix Kiula wrote:
> Then I am confused again about how the fill factor stuff works. Let's
> say I have a table with four BTREE indexes. Should all of them have a
> fill factor of about 60 (lower than the default 90, that is) to be
> effective? Or will it help if I lower the fill factor on only a
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> Then I am confused again about how the fill factor stuff works. Let's
> say I have a table with four BTREE indexes. Should all of them have a
> fill factor of about 60 (lower than the default 90, that is) to be
> effective? Or will it help if I lower
On 19/09/2007, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> --- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
>
> > 2. Is this fill factor enough to have on the table, or should I also
> > do a fill factor for specific indexes? Or both the table and the
> > index? (I have four btree indexes on the t
On 19/09/2007, Gregory Williamson <[EMAIL PROTECTED]> wrote:
...
> Can't speak directly to PostgreSQL but in Informix the fill factor is
> useful for tweaking indexes. A very high fill factor is useful for tables
> that are static -- any inserts or changes to the index trigger a *lot* of
> movin
Richard Broersma Jr wrote:
--- Gregory Williamson <[EMAIL PROTECTED]> wrote:
A very low fill factor means that pages are
"sparse" and so inserts and updates are less likely to trigger massive b-tree rebalancings.
I take it that "massive b-tree rebalancings" could cause a problem with
--- Gregory Williamson <[EMAIL PROTECTED]> wrote:
> A very low fill factor means that pages are
> "sparse" and so inserts and updates are less likely to trigger massive b-tree
> rebalancings.
I take it that "massive b-tree rebalancings" could cause a problem with the
performance of disk
writi
ers made me say this.)
-Original Message-
From: [EMAIL PROTECTED] on behalf of Richard Broersma Jr
Sent: Tue 9/18/2007 10:29 AM
To: Phoenix Kiula; Bill Moran
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
--- Phoenix Kiula <[EMAIL PRO
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> What constitutes a "small fill factor"? Would 70 be good? I guess my
> current must have been the default, which the manual says is 100.
On the following link:
http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html#SQL-CREATEINDEX-STORA
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> Thanks for a very informative post! One question:
>
> > I'm not sure how to find the current value, but a smaller fill factor
> > on busy tables should lead to less fragmentation, thus more efficient
> > indexes over time. Keep in mind that a
Thanks for a very informative post! One question:
> I'm not sure how to find the current value, but a smaller fill factor
> on busy tables should lead to less fragmentation, thus more efficient
> indexes over time. Keep in mind that a smaller fill factor will also
> lead to larger indexes initia
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> > If you find that reindexing improves performance, then you should
> > investigate further. Depending on the exact nature of the problem,
> > there are many possible solutions, three that come to mind:
> > * Add RAM/SHM
>
> Can I add SHM wit
Hi,
> Now, I can merrily increase the shared_buffers, but the manual warns
> me against increasing the value too much because it is "per
> transaction" value.
Shared_buffers is not per-transaction. Where did you find this information?
- Sander
---(end of broadcast)
On 18/09/2007, Sander Steffann <[EMAIL PROTECTED]> wrote:
> Hi,
>
> > Can I add SHM with merely by managing the entry in sysctl.conf? My
> > current values:
> >
> > kernel.shmmax = 536870912
> > kernel.shmall = 536870912
> >
> > My "shared_buffers" in postgresql.conf is "2". From the website
>
Hi,
> Can I add SHM with merely by managing the entry in sysctl.conf? My
> current values:
>
> kernel.shmmax = 536870912
> kernel.shmall = 536870912
>
> My "shared_buffers" in postgresql.conf is "2". From the website
> http://www.desknow.com/kb/idx/12/061/article/ I notice that shmmax
> shou
> Unfortunately, folks like Phoenix are looking for yes/no answers, and
> with many of these questions, the _correct_ answer is "it depends on
> your workload"
I wanted merely to simplify the advice that gets dispensed on this
list, often conflicting to novice ears like mine. So I appreciate your
On 9/18/07, Ow Mun Heng <[EMAIL PROTECTED]> wrote:
> On Tue, 2007-09-18 at 06:01 -0400, Bill Moran wrote:
> > * (with newer version) reduce the fill factor and REINDEX
>
> What is fill factor?
See "Index Storage Parameters":
http://www.postgresql.org/docs/8.2/static/sql-createindex.html
-
On Tue, 2007-09-18 at 06:01 -0400, Bill Moran wrote:
> * (with newer version) reduce the fill factor and REINDEX
What is fill factor?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
"Filip Rembiałkowski" <[EMAIL PROTECTED]> wrote:
>
> 2007/9/18, Joshua D. Drake <[EMAIL PROTECTED]>:
>
> > If you overrun your max_fsm_pages then vacuum analyze IS NOT ENOUGH. If
> > you do not overrun your max_fsm_pages, yes vacuum analyze can deal with
> > the issue.
>
> Are you sure? I have a
2007/9/18, Joshua D. Drake <[EMAIL PROTECTED]>:
> If you overrun your max_fsm_pages then vacuum analyze IS NOT ENOUGH. If
> you do not overrun your max_fsm_pages, yes vacuum analyze can deal with
> the issue.
Are you sure? I have a situation where above is no true. postgres
version 8.1.8. while v
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Ow Mun Heng wrote:
> On Mon, 2007-09-17 at 22:14 -0700, Joshua D. Drake wrote:
>> Phoenix Kiula wrote:
>>> So a YES/NO question:
>>>
>>> Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
>>> reindex/cluster indexes?
>> If you overrun
On Mon, 2007-09-17 at 22:14 -0700, Joshua D. Drake wrote:
> Phoenix Kiula wrote:
> > So a YES/NO question:
> >
> > Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
> > reindex/cluster indexes?
>
> If you overrun your max_fsm_pages, no:
> else yes;
Maybe my english suck, but
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Phoenix Kiula wrote:
> The manual is vague. Several threads about this, in language that is
> ambiguous to me.
>
> So a YES/NO question:
>
> Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
> reindex/cluster indexes?
If you overru
The manual is vague. Several threads about this, in language that is
ambiguous to me.
So a YES/NO question:
Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I
reindex/cluster indexes?
Thanks.
---(end of broadcast)---
TIP 3: Have y
34 matches
Mail list logo
