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
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:
1.
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
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 week. The
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 seems
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
moving of
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 table)
I
--- 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 the
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
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,
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 inserts
--- 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
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
--- 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 to
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 it:
-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 your
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
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 situation where
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
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
should be
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
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
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
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 with merely by
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 smaller
--- 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:
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
--- 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
writing
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 the
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 PROTECTED] wrote:
What constitutes
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
-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 overrun your
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 I don't
34 matches
Mail list logo
