On 17 October 2011 15:42, Merlin Moncure wrote:
> On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz wrote:
> >
> >
> > On 17 October 2011 02:01, Scott Marlowe wrote:
> >>
> >> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
> >> > Hi,
> >> > just a couple of questions:
> >> > will there be an ind
On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz wrote:
>
>
> On 17 October 2011 02:01, Scott Marlowe wrote:
>>
>> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
>> > Hi,
>> > just a couple of questions:
>> > will there be an index bloat if I have:
>> > - a serial column and only add rows to the
On 17 October 2011 02:01, Scott Marlowe wrote:
> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
> > Hi,
> > just a couple of questions:
> > will there be an index bloat if I have:
> > - a serial column and only add rows to the table?
> > - a text column and I only add rows to the table?
> >
Scott Marlowe wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
Hi,
just a couple of questions:
will there be an index bloat if I have:
- a serial column and only add rows to the table?
- a text column and I only add rows to the table?
For the serial column the numbers are only inc
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
> Hi,
> just a couple of questions:
> will there be an index bloat if I have:
> - a serial column and only add rows to the table?
> - a text column and I only add rows to the table?
> For the serial column the numbers are only incremented, for the
OK, as far as I saw you never mentioned what PG version you are running,
but if it's 8.2.x then I think I know what's going on. The thing that
was bothering me was the discrepancy in size of the two indexes. Now
the entries in pg_shdepend_reference_index are all going to be
references to roles, a
Tom Lane wrote:
Joseph S <[EMAIL PROTECTED]> writes:
... and when I notice that the tuplesperpage for the indexes is low (or
that the indexes are bigger then the tables themselves) I know it is
time for a VACUUM FULL and REINDEX on that table.
If you are taking the latter as a blind must-be-w
Monika Cernikova <[EMAIL PROTECTED]> writes:
> Can you help me how to stop index growing or reindex database if I CAN'T
> STOP writing records?
I think you have max_fsm_pages set too small.
> max_fsm_pages = 40
That corresponds to about 3Gb, or a tenth the size of your DB. Not en
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
This week is looking busy for me but hopefully I'll be able to play around
with various vacuuming frequencies for this table ...
Thanks for all of your help; I'll report on my progress
-Dave
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 13, 2005
On Wed, Jul 13, 2005 at 05:39:33PM -0400, Tom Lane wrote:
> (Memo to hackers: this is a fairly interesting case for autovacuum
> I think. The overall update rate on the table is not high enough to
> trigger frequent vacuums, unless autovacuum is somehow made aware that
> particular index key rang
"David Esposito" <[EMAIL PROTECTED]> writes:
> ... and the way new keys are
> inserted into the index is to always add them to a new page (where the 'new'
> page is either a truly new page, or a page that is completely empty), rather
> than using up some of the fragmented space within existing page
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 13, 2005 3:45 PM
>
> Hmm, this is preferentially touching stuff near the right end of the
> index, ie, it's going to bloat the pages associated with higher keys.
> As I understand your usage of these
"David Esposito" <[EMAIL PROTECTED]> writes:
> Hmm, if I keep running the following query while the test program is going
> (giving it a few iterations to rest between executions), the steady-state
> usage of the indexes seems to go up ... it doesn't happen every time you run
> the query, but if yo
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, July 13, 2005 2:10 PM
> To: David Esposito
>
> Plain VACUUM doesn't try very hard to shorten the table physically, so
> that's not surprising either. But the internal free space should get
> picked up at t
"David Esposito" <[EMAIL PROTECTED]> writes:
> You're right that the index behavior is well-behaved with the cycle of
> INSERT / DELETE / VACUUM ... But while it was running, I started a second
> session to the database after the 60th iteration and did
> BEGIN;
> SELECT COUNT(*) FROM bigboy;
> RO
First, thank you for spending so much time on this issue
Second, I think I might have found a good lead ... I replicated the test you
described below (minus the updating of 10% of the records) ... I've attached
the PHP script (I'm more proficient at writing PHP than a shell script; you
should be a
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 12:26 PM
>
> "David Esposito" <[EMAIL PROTECTED]> writes:
> > As promised, here are two runs of VACUUM VERBOSE on the
> problem table ...
>
> BTW, the tail of the VACUUM VERBOSE output ought t
psed 1200.48 sec.
INFO: analyzing "patronmail.campaign_email"
INFO: "campaign_email": scanned 3000 of 93960 pages, containing 178601 live
rows and 4 dead rows; 3000 rows in sample, 5593783 estimated total rows
> -Original Message-
> From: Tom Lane [mailto:[EMAI
"David Esposito" <[EMAIL PROTECTED]> writes:
> As promised, here are two runs of VACUUM VERBOSE on the problem table ...
BTW, the tail of the VACUUM VERBOSE output ought to have something about
overall usage of the FSM --- what does that look like?
regards, tom lane
-
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 12, 2005 10:14 AM
>
> "David Esposito" <[EMAIL PROTECTED]> writes:
> > As promised, here are two runs of VACUUM VERBOSE on the
> problem table ...
> > There was a lot of activity on the campaign_email
"David Esposito" <[EMAIL PROTECTED]> writes:
> As promised, here are two runs of VACUUM VERBOSE on the problem table ...
> There was a lot of activity on the campaign_email table on Friday
> (Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)
Well, what these numbers show is that you ha
Sure thing ... I turned on VERBOSE so I'll let it run for the next few days
(the weekend is kind of a bad time since activity on the database is low)
but by monday or tuesday I should have a few nightly runs to post to the
list ..
Thanks,
Dave
> -Original Message-
> From: Tom Lane [mailt
"David Esposito" <[EMAIL PROTECTED]> writes:
> Hmm, how are you getting 1/6? The ballpark seems to be about 50% or more for
> those first 4 ...
Ooops, I got confused about which column was which.
Could we see the results of "vacuum verbose" on this table? Even
better, verbose output from two suc
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 11:53 PM
>
> "David Esposito" <[EMAIL PROTECTED]> writes:
> > Size of "problem" table: 6 million rows
> > Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> > million/day
"David Esposito" <[EMAIL PROTECTED]> writes:
> Size of "problem" table: 6 million rows
> Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> million/day
> ...
> I do a nightly VACUUM (not VACUUM FULL)
Given those parameters, you should expect a "slack" proportion of about
1/6th
Quoth [EMAIL PROTECTED] (Alvaro Herrera):
> On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote:
>
>> b) Only a dump-restore major version upgrade (which we'll do next time we
>> can take the system out for long enough) will avoid the issue.
>
> "Long enough" could be a minutes or seconds
On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote:
b) Only a dump-restore major version upgrade (which we'll do next
time we
can take the system out for long enough) will avoid the issue.
On 6 Dec 2004, at 16:18, Alvaro Herrera wrote:
"Long enough" could be a minutes or seconds issue i
On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote:
> b) Only a dump-restore major version upgrade (which we'll do next time we
> can take the system out for long enough) will avoid the issue.
"Long enough" could be a minutes or seconds issue if you use Slony-I,
I've heard ... (Of cou
From: "Christopher Browne" <[EMAIL PROTECTED]>
> The "empty pages not reclaimed" problem is something that did indeed
> get fixed in the post-7.2 days. I _think_ it was 7.4, but it might
> have been 7.3.
> In short, 7.4.x is indeed a good resolution to your issue.
From: "Tom Lane" <[EMAIL PROTE
Clinging to sanity, [EMAIL PROTECTED] ("Julian Scarfe") mumbled into her beard:
> I've got a box running 7.2.1 (yes, I know :-() in which an index for
> a rapidly turning over (and regularly vacuumed) table is growing
> steadily in size. The index in question is on a timestamp field
> that is just
"Julian Scarfe" <[EMAIL PROTECTED]> writes:
> I've got a box running 7.2.1 (yes, I know :-() in which an index for a
> rapidly turning over (and regularly vacuumed) table is growing steadily in
> size. The index in question is on a timestamp field that is just set to
> now() on the entry of the ro
55 matches
Mail list logo
