From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Scott Carey <scott@richrelevance.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Robert Schnabel <schnabelr@missouri.edu>
Cc: david@lang.hm <david@lang.hm>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: How to best use 32 15k.7 300GB drives?
Date: Fri, 04 Feb 2011 11:19:13 +0200
Message-ID: <4D4BC491.7080908@gmail.com> (raw)
In-Reply-To: <AANLkTimhu_2QgX=UX8QtQn_W0c-JqxNmF4+kPZOPgP0v@mail.gmail.com>
References: <20110128172810.GE30352@tamriel.snowman.net>
<C9684183.1E40B%scott@richrelevance.com>
<AANLkTikWy+U5qK88EhefVo1c-jRTe4gs-01X528XtFKn@mail.gmail.com>
<AANLkTimhu_2QgX=UX8QtQn_W0c-JqxNmF4+kPZOPgP0v@mail.gmail.com>
03.02.11 20:42, Robert Haas написав(ла):
> 2011/1/30 Віталій Тимчишин<tivv00@gmail.com>:
>> I was thinking if a table file could be deleted if it has no single live
>> row. And if this could be done by vacuum. In this case vacuum on table that
>> was fully updated recently could be almost as good as cluster - any scan
>> would skip such non-existing files really fast. Also almost no disk space
>> would be wasted.
> VACUUM actually already does something along these lines. If there
> are 1 or any larger number of entirely-free pages at the end of a
> table, VACUUM will truncate them away. In the degenerate case where
> ALL pages are entirely-free, this results in zeroing out the file.
>
> The problem with this is that it rarely does much. Consider a table
> with 1,000,000 pages, 50% of which contain live rows. On average, how
> many pages will this algorithm truncate away? Answer: if the pages
> containing live rows are randomly distributed, approximately one.
Yes, but take into account operations on a (by different reasons)
clustered tables, like removing archived data (yes I know, this is best
done with partitioning, but one must still go to a point when he will
decide to use partitioning :) ).
> Your idea of having a set of heaps rather than a single heap is an
> interesting one, but it's pretty much catering to the very specific
> case of a full-table update. I think the code changes needed would be
> far too invasive to seriously contemplate doing it just for that one
> case - although it is an important case that I would like to see us
> improve.
Why do you expect such a invasive code changes? I know little about
postgresql code layering, but what I propose (with changing delete to
truncate) is:
1) Leave tuple addressing as it is now
2) Allow truncated files, treating non-existing part as if it contained
not used tuples
3) Make vacuum truncate file if it has not used tuples at the end.
The only (relatively) tricky thing I can see is synchronizing truncation
with parallel ongoing scan.
Best regards, Vitalii Tymchyshyn
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, robertmhaas@gmail.com, scott@richrelevance.com, sfrost@snowman.net, scott.marlowe@gmail.com, schnabelr@missouri.edu, david@lang.hm
Subject: Re: How to best use 32 15k.7 300GB drives?
In-Reply-To: <4D4BC491.7080908@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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