On 17 October 2011 02:01, Scott Marlowe scott.marl...@gmail.com wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz mabew...@gmail.com 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
On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz mabew...@gmail.com wrote:
On 17 October 2011 02:01, Scott Marlowe scott.marl...@gmail.com wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz mabew...@gmail.com wrote:
Hi,
just a couple of questions:
will there be an index bloat if I have:
-
On 17 October 2011 15:42, Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz mabew...@gmail.com wrote:
On 17 October 2011 02:01, Scott Marlowe scott.marl...@gmail.com wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz mabew...@gmail.com wrote:
Hi,
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 text column
I add random strings.
regards
Szymon
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz mabew...@gmail.com 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
Scott Marlowe wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz mabew...@gmail.com 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
PostgreSQL 8.4.8 on i386-apple-darwin10.7.0, compiled by GCC
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664),
64-bit
I'm not sure exactly what's going on, but I've got a table defined like this:
CREATE TABLE foo (
...
tags varchar(1000)[],
...
);
CREATE INDEX
I got a query to calculate index bloat as follows:
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html
---
CREATE OR REPLACE VIEW bloat AS
SELECT
schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE
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
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,
I use Postgres 8.1 on linux
I have several tables to which I need insert about 200-500 records per
minute.
Records contains timestamp (actual time), and this timestamp is part of
primary key and index.
I need to keep data for 1 month. I daily delete data older than 1 month
and than run vacuum
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
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
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
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
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, vacuum
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 was
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?
[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 infeasible
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
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 of
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:
Ed L.
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 table *and*
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.
At
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
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 operations.
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 database back on
-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 excessive
[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
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
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
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.
Oh, so
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
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, vacuum full
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
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 VACUUM
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
-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 to have
@postgresql.org
Subject: Re: FW: [GENERAL] index bloat
David Esposito [EMAIL PROTECTED] writes:
BTW, the tail of the VACUUM VERBOSE output ought to have
something about
overall usage of the FSM --- what does that look like?
INFO: free space map: 528 relations, 172357 pages stored
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;
ROLLBACK;
-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 this
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 you do it
-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:
... 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 pages?
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 ranges
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 have 5%
-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 table on
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
David Esposito [EMAIL PROTECTED] writes:
BTW, the tail of the VACUUM VERBOSE output ought to have
something about
overall usage of the FSM --- what does that look like?
INFO: free space map: 528 relations, 172357 pages stored; 170096 total
pages needed
DETAIL: Allocated FSM size: 1
Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, July 08, 2005 9:52 AM
To: David Esposito
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] index bloat
David Esposito [EMAIL PROTECTED] writes:
Hmm, how are you getting 1/6? The ballpark seems to be
about 50
-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
...
I do a
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 successive
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
Hello all,
Executive summary: I have btree index bloat ... I have read all of the
threads I could find on the problem and wanted to confirm that there are no
tuning parameters that could at least reduce the severity of the problem
Detail:
PostgreSQL 8.0.1 on RHEL3
Overall Database Size: 9GB
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 of the
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 PROTECTED]
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 course
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 if
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 issue if
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 row, to enable the query that clears out old data
to
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 row, to
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 set
63 matches
Mail list logo
