On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty wrote:
> is there some way to view the level of "bloat that needs full" in each
> table, so i could write a script that alerts me to the need of a
> "vacuum full" without waiting for random queries to "get slow"?
>
> looking at the results of the "bloa
> See ALTER TABLE and CREATE TABLE (and the Index variants).
>
> ALTER TABLE foo SET (fillfactor=90);
I'll try that.
> This will leave on average, 10% of every 8k block empty and allow updates to
> columns to more likely live within the same block.
Good for the items table.
Probably bad for th
On 6/4/09 6:16 AM, "Robert Haas" wrote:
> On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty wrote:
>> Seems like "VACUUM FULL" could figure out to do that too depending on
>> the bloat-to-table-size ratio ...
>>
>> - copy all rows to new table
>> - lock for a millisecond while renaming table
On 6/4/09 4:31 AM, "Erik Aronesty" wrote:
>> read the entry on pg_stat_all_tables
>
> yeah, it's running ... vacuum'ed last night
>
> it's odd, to me, that the performance would degrade so extremely
> (noticeably) over the course of one year on a table which has few
> insertions, no deletions,
On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty wrote:
> Seems like "VACUUM FULL" could figure out to do that too depending on
> the bloat-to-table-size ratio ...
>
> - copy all rows to new table
> - lock for a millisecond while renaming tables
> - drop old table.
You'd have to lock the table
> read the entry on pg_stat_all_tables
yeah, it's running ... vacuum'ed last night
it's odd, to me, that the performance would degrade so extremely
(noticeably) over the course of one year on a table which has few
insertions, no deletions,and daily updates of an integer non null
column (stock lev
Erik Aronesty wrote:
I think, perhaps, autovac wasn't running on that machine.
Is there any way to check to see if it's running?
since it looks like stats are on too
http://www.network-theory.co.uk/docs/postgresql/vol3/ViewingCollectedStatistics.html
read the entry on pg_stat_all_tables
Erik Aronesty writes:
> I think, perhaps, autovac wasn't running on that machine.
> Is there any way to check to see if it's running?
> I have enabled all the options , and I know it's running on my other
> servers because I see
> LOG: autovacuum entries (a profusion of them)
> I suspect,
I think, perhaps, autovac wasn't running on that machine.
Is there any way to check to see if it's running?
I have enabled all the options , and I know it's running on my other
servers because I see
LOG: autovacuum entries (a profusion of them)
I suspect, perhaps, that it's just not showin
Erik Aronesty writes:
> but why wasn't autovac enough to reclaim at least *most* of the space?
Autovac isn't meant to reclaim major amounts of bloat; it's more in the
line of trying to prevent it from happening in the first place. To
reclaim bloat it would have to execute VACUUM FULL, or some ot
it was all vacuum full...thanks
the other 2 servers truncate and reload that table from time to time
... IE: they are always vacuumed
as the "master" ... that server never does it... hence the bloat
but why wasn't autovac enough to reclaim at least *most* of the space?
that table *does* get up
Craig Ringer writes:
> Tom Lane wrote:
>> I'm betting on varying degrees of table bloat. Have you tried vacuum
>> full, cluster, etc?
> Or, if you have been using VACUUM FULL, try REINDEXing the tables,
> because it could easily be index bloat. Clustering the table will take
> care of index bloa
Tom Lane wrote:
> Erik Aronesty writes:
>> I have 3 servers, all with identical databases, and each performing
>> very differently for the same queries.
>
> I'm betting on varying degrees of table bloat. Have you tried vacuum
> full, cluster, etc?
Or, if you have been using VACUUM FULL, try REI
Erik Aronesty writes:
> I have 3 servers, all with identical databases, and each performing
> very differently for the same queries.
I'm betting on varying degrees of table bloat. Have you tried vacuum
full, cluster, etc?
regards, tom lane
--
Sent via pgsql-performance
14 matches
Mail list logo
