pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex growth problem
5+ messages / 3 participants
[nested] [flat]
* index growth problem
@ 2006-10-18 22:20 Graham Davis <gdavis@refractions.net>
0 siblings, 1 reply; 5+ messages in thread
From: Graham Davis @ 2006-10-18 22:20 UTC (permalink / raw)
To: pgsql-performance
I have a question about index growth.
The way I understand it, dead tuples in indexes were not reclaimed by
VACUUM commands in the past. However, I've read in a few forum posts
that this was changed somewhere between 7.4 and 8.0.
I'm having an issue where my GIST indexes are growing quite large, and
running a VACUUM doesn't appear to remove the dead tuples. For example,
if I check out the size an index before running any VACUUM :
select pg_relation_size('asset_positions_position_idx');
pg_relation_size
------------------
11624448
(1 row)
The size is about 11Mb. If I run a VACUUM command in verbose, I see
this about the index:
INFO: index "asset_positions_position_idx" now contains 4373 row
versions in 68 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.16 sec.
When I run the same command to find the size after the VACUUM, it hasn't
changed. However, if I drop and then recreate this index, the size
becomes much smaller (almost half the size):
drop index asset_positions_position_idx;
DROP INDEX
CREATE INDEX asset_positions_position_idx ON asset_positions USING GIST
(position GIST_GEOMETRY_OPS);
CREATE INDEX
select pg_relation_size('asset_positions_position_idx');
pg_relation_size
------------------
6225920
(1 row)
Is there something I am missing here, or is the reclaiming of dead
tuples for these indexes just not working when I run a VACUUM? Is it
suppose to work?
--
Graham Davis
Refractions Research Inc.
gdavis@refractions.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index growth problem
@ 2006-10-18 22:33 Jim C. Nasby <jim@nasby.net>
parent: Graham Davis <gdavis@refractions.net>
0 siblings, 2 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-10-18 22:33 UTC (permalink / raw)
To: Graham Davis <gdavis@refractions.net>; +Cc: pgsql-performance
On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
> I have a question about index growth.
>
> The way I understand it, dead tuples in indexes were not reclaimed by
> VACUUM commands in the past. However, I've read in a few forum posts
> that this was changed somewhere between 7.4 and 8.0.
There was a change to indexes that made vacuum more effective; I don't
remember the details off-hand.
> I'm having an issue where my GIST indexes are growing quite large, and
> running a VACUUM doesn't appear to remove the dead tuples. For example,
> if I check out the size an index before running any VACUUM :
>
> select pg_relation_size('asset_positions_position_idx');
> pg_relation_size
> ------------------
> 11624448
> (1 row)
>
> The size is about 11Mb. If I run a VACUUM command in verbose, I see
> this about the index:
>
> INFO: index "asset_positions_position_idx" now contains 4373 row
> versions in 68 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.16 sec.
>
> When I run the same command to find the size after the VACUUM, it hasn't
> changed. However, if I drop and then recreate this index, the size
> becomes much smaller (almost half the size):
>
> drop index asset_positions_position_idx;
> DROP INDEX
>
> CREATE INDEX asset_positions_position_idx ON asset_positions USING GIST
> (position GIST_GEOMETRY_OPS);
> CREATE INDEX
>
> select pg_relation_size('asset_positions_position_idx');
> pg_relation_size
> ------------------
> 6225920
> (1 row)
>
> Is there something I am missing here, or is the reclaiming of dead
> tuples for these indexes just not working when I run a VACUUM? Is it
> suppose to work?
That's not really a useful test to see if VACUUM is working. VACUUM can
only trim space off the end of a relation (index or table), where by
'end' I mean the end of the last file for that relation on the
filesystem. This means it's pretty rare for VACUUM to actually shrink
files on-disk for tables. This can be even more difficult for indexes (I
think it's virtually impossible to shrink a B-tree index file).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index growth problem
@ 2006-10-18 22:39 Graham Davis <gdavis@refractions.net>
parent: Jim C. Nasby <jim@nasby.net>
1 sibling, 1 reply; 5+ messages in thread
From: Graham Davis @ 2006-10-18 22:39 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
So I guess any changes that were made to make VACUUM and FSM include
indexes
does not remove the necessity to reindex (as long as we don't want index
sizes to bloat and grow larger than they need be).
Is that correct?
Graham.
Jim C. Nasby wrote:
>On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>
>
>>I have a question about index growth.
>>
>>The way I understand it, dead tuples in indexes were not reclaimed by
>>VACUUM commands in the past. However, I've read in a few forum posts
>>that this was changed somewhere between 7.4 and 8.0.
>>
>>
>
>There was a change to indexes that made vacuum more effective; I don't
>remember the details off-hand.
>
>
>
>>I'm having an issue where my GIST indexes are growing quite large, and
>>running a VACUUM doesn't appear to remove the dead tuples. For example,
>>if I check out the size an index before running any VACUUM :
>>
>>select pg_relation_size('asset_positions_position_idx');
>>pg_relation_size
>>------------------
>> 11624448
>>(1 row)
>>
>>The size is about 11Mb. If I run a VACUUM command in verbose, I see
>>this about the index:
>>
>>INFO: index "asset_positions_position_idx" now contains 4373 row
>>versions in 68 pages
>>DETAIL: 0 index pages have been deleted, 0 are currently reusable.
>>CPU 0.00s/0.00u sec elapsed 0.16 sec.
>>
>>When I run the same command to find the size after the VACUUM, it hasn't
>>changed. However, if I drop and then recreate this index, the size
>>becomes much smaller (almost half the size):
>>
>>drop index asset_positions_position_idx;
>>DROP INDEX
>>
>>CREATE INDEX asset_positions_position_idx ON asset_positions USING GIST
>>(position GIST_GEOMETRY_OPS);
>>CREATE INDEX
>>
>>select pg_relation_size('asset_positions_position_idx');
>>pg_relation_size
>>------------------
>> 6225920
>>(1 row)
>>
>>Is there something I am missing here, or is the reclaiming of dead
>>tuples for these indexes just not working when I run a VACUUM? Is it
>>suppose to work?
>>
>>
>
>That's not really a useful test to see if VACUUM is working. VACUUM can
>only trim space off the end of a relation (index or table), where by
>'end' I mean the end of the last file for that relation on the
>filesystem. This means it's pretty rare for VACUUM to actually shrink
>files on-disk for tables. This can be even more difficult for indexes (I
>think it's virtually impossible to shrink a B-tree index file).
>
>
--
Graham Davis
Refractions Research Inc.
gdavis@refractions.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index growth problem
@ 2006-10-18 22:51 Jim C. Nasby <jim@nasby.net>
parent: Graham Davis <gdavis@refractions.net>
0 siblings, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-10-18 22:51 UTC (permalink / raw)
To: Graham Davis <gdavis@refractions.net>; +Cc: pgsql-performance
On Wed, Oct 18, 2006 at 03:39:56PM -0700, Graham Davis wrote:
> So I guess any changes that were made to make VACUUM and FSM include
> indexes
> does not remove the necessity to reindex (as long as we don't want index
> sizes to bloat and grow larger than they need be).
> Is that correct?
Not in recent releases, no. Remember that any index on a field that gets
update activity will naturally have some amount of empty space due to
page splits, but this is normal (and actually desireable). So you can't
just compare index size before and after a REINDEX and assume
something's wrong if REINDEX shrinks the index; that gain is artificial.
So long as you are vacuuming frequently enough and keep the free space
map large enough, there shouldn't be any need to REINDEX.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: index growth problem
@ 2006-10-18 23:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jim@nasby.net>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-10-18 23:00 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Graham Davis <gdavis@refractions.net>; pgsql-performance
"Jim C. Nasby" <jim@nasby.net> writes:
> On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote:
>> When I run the same command to find the size after the VACUUM, it hasn't
>> changed.
> That's not really a useful test to see if VACUUM is working. VACUUM can
> only trim space off the end of a relation (index or table), where by
> 'end' I mean the end of the last file for that relation on the
> filesystem. This means it's pretty rare for VACUUM to actually shrink
> files on-disk for tables. This can be even more difficult for indexes (I
> think it's virtually impossible to shrink a B-tree index file).
Right; IIRC, a plain VACUUM doesn't even try to shorten the physical
index file, because of locking considerations. The important question
is whether space gets recycled properly for re-use within the index.
If the index continues to grow over time, then you might have a problem
with insufficient FSM space (or not vacuuming often enough).
It might be worth pointing out that VACUUM isn't intended to try to
reduce the disk file to the shortest possible length --- the assumption
is that you are doing vacuuming on a regular basis and so the file
length should converge to a "steady state", wherein the internal free
space runs out about the time you do another VACUUM and reclaim some
more space for re-use. There's not really any point in being more
aggressive than that; we'd just create additional disk I/O when the
filesystem releases and later reassigns space to the file.
Of course, this argument fails in the scenario where you make a large
and permanent reduction in the amount of data in a table. There are
various hacks you can use to clean up in that case --- use TRUNCATE not
DELETE if you can, or consider using CLUSTER (not VACUUM FULL). Some
variants of ALTER TABLE will get rid of internal free space, too.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-10-18 23:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-18 22:20 index growth problem Graham Davis <gdavis@refractions.net>
2006-10-18 22:33 ` Jim C. Nasby <jim@nasby.net>
2006-10-18 22:39 ` Graham Davis <gdavis@refractions.net>
2006-10-18 22:51 ` Jim C. Nasby <jim@nasby.net>
2006-10-18 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox