Jim Nasby wrote:
> Is there no way to change the index code to allow for moving index
> tuples from one page to another? If we could do that then presumably
> we could free up substantially more pages.
This paper
@inproceedings{DBLP:conf/sigmod/ZouS96,
author= {C. Zou and B. Salzberg}
Is there no way to change the index code to allow for moving index
tuples from one page to another? If we could do that then presumably
we could free up substantially more pages.
On Jan 30, 2007, at 10:18 PM, Bruce Momjian wrote:
Added to TODO:
* Add REINDEX CONCURRENTLY, like CRE
Tom Lane wrote:
> Bill Moran <[EMAIL PROTECTED]> writes:
> > The entire database was around 28M prior to the upgrades, etc. Immediately
> > after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> > 165M. Following a database-wide reindex, it dropped to 30M.
>
> As Alvaro said
I found this thread quite depressing because I had forgotten the VACUUM
FULL only reclaims totally empty pages. I have applied the following
documentation patch to recommend periodic REINDEX, and backpatched to
8.2.X docs. I also added some TODO items so hopefully at least we will
keep track of
Added to TODO:
* Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY
This is difficult because you must upgrade to an exclusive table lock
to replace the existing index file. CREATE INDEX CONCURRENTLY does
not
have this complication. This wou
We have a large number (50+) of pre-8.2 clusters. How can I
best/most easily identify those indices most bloated and in need
of reindex/rebuilding?
Ed
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://ar
On Fri, 2007-01-19 at 20:03, Jeremy Haile wrote:
> Is it feasible to add a "reindex concurrently" that doesn't lock the
> table for the rebuild, then locks the table when doing a second pass to
> pickup rows that were changed after the first pass? Or something like
> that
IIRC, the objection
Is it feasible to add a "reindex concurrently" that doesn't lock the
table for the rebuild, then locks the table when doing a second pass to
pickup rows that were changed after the first pass? Or something like
that
On Fri, 19 Jan 2007 12:45:03 -0500, "Tom Lane" <[EMAIL PROTECTED]> said:
> "E
"Ed L." <[EMAIL PROTECTED]> writes:
> Online index creation definitely helps us toward 24x7. But
> wouldn't we still have to drop the old index, thus blocking
> production queries?
Yes, but only for a very short period.
regards, tom lane
---(end
On Friday January 19 2007 2:11 am, Csaba Nagy wrote:
>
> > I afraid I don't see how any of the answers I saw discussed
> > fit a 24x7 operation. Reindex, drop index, vacuum full, ...
> > they all block production queries of one sort or another for
> > significant periods of time (minutes) on large
[snip]
> I afraid I don't see how any of the answers I saw discussed fit a
> 24x7 operation. Reindex, drop index, vacuum full, ... they all
> block production queries of one sort or another for significant
> periods of time (minutes) on large (multi/tens of GB) tables,
> and thus are infeasibl
On Thursday January 18 2007 6:07 am, Bill Moran wrote:
> Right. It doesn't _look_ that way from the graph, but that's
> because I only graph total DB size. I expect if I graphed
> data and index size separately, it would be evident.
pg_total_relation_size() might give you what you want there.
>
In response to Tom Lane <[EMAIL PROTECTED]>:
> Bill Moran <[EMAIL PROTECTED]> writes:
> > It wasn't the fact that it bloated that surprised me. It was the
> > _magnitude_ of bloat that I wasn't expecting, as well as the fact that
> > it was _all_ _index_ bloat.
>
> Um, no, you had plenty of tabl
Bill Moran <[EMAIL PROTECTED]> writes:
> It wasn't the fact that it bloated that surprised me. It was the
> _magnitude_ of bloat that I wasn't expecting, as well as the fact that
> it was _all_ _index_ bloat.
Um, no, you had plenty of table *and* index bloat before. The problem
here is that VACU
On Jan 17, 2007, at 11:56 AM, Tom Lane wrote:
So the above doesn't sound too unlikely. Perhaps we should recommend
vac full + reindex as standard cleanup procedure. Longer term, maybe
teach vac full to do an automatic reindex if it's moved more than X
% of
a vac full + reindex is a waste o
Tom Lane wrote:
> Bill Moran <[EMAIL PROTECTED]> writes:
>> The entire database was around 28M prior to the upgrades, etc. Immediately
>> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
>> 165M. Following a database-wide reindex, it dropped to 30M.
>
> As Alvaro said, v
In response to Ben <[EMAIL PROTECTED]>:
> Hey Bill. How do you monitor your shared buffer usage? My understanding
> was that there wasn't a good way to see what was used vs. allocated.
echo "select count(*) from pg_buffercache where reldatabase is not null;" |
$PSQL_BIN -P tuples_only -U pgsql
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
>
> > The entire database was around 28M prior to the upgrades, etc. Immediately
> > after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> > 165M. Following a database-wide reindex, it dropped to 30M.
>
Bill Moran wrote:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
Oh, so it was clearly the upgrade procedure that caused the bl
Bill Moran <[EMAIL PROTECTED]> writes:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
As Alvaro said, vacuum full doesn't shrink
[snip]
> Come to think of it, an auto-reindex option might be nice in core someday.
> TODO item?
Marry it with autovacuum + online index build, and it will be cool ;-)
BTW, having a privileged background thread doing the reindex could be a
solution to most of the objections regarding online reind
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Today I decided to run reindex during a slow period, and was shocked to
> find the database size drop from 165M to 30M. Keep in mind that the
> 165M is after vacuum full. So, apparently, there was 135M of index bloat?
> That seems a little excess
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
> >
> > We just did a bunch of maintenance on one of our production databases that
> > involved a lot of alter tables and moving records about and the like.
> >
> > Afterwards, I did a vacuum full and analyze to get the datab
Bill Moran wrote:
>
> We just did a bunch of maintenance on one of our production databases that
> involved a lot of alter tables and moving records about and the like.
>
> Afterwards, I did a vacuum full and analyze to get the database back on
> track -- autovac maintains it under normal operati
We just did a bunch of maintenance on one of our production databases that
involved a lot of alter tables and moving records about and the like.
Afterwards, I did a vacuum full and analyze to get the database back on
track -- autovac maintains it under normal operations.
Today I decided to run r
25 matches
Mail list logo
