pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgood old VACUUM FULL
5+ messages / 5 participants
[nested] [flat]
* good old VACUUM FULL
@ 2011-03-23 00:52 felix <crucialfelix@gmail.com>
2011-03-23 04:24 ` Re: good old VACUUM FULL Chris <dmagick@gmail.com>
2011-03-23 06:16 ` Re: good old VACUUM FULL Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: felix @ 2011-03-23 00:52 UTC (permalink / raw)
To: pgsql-performance
I posted many weeks ago about a severe problem with a table that was
obviously bloated and was stunningly slow. Up to 70 seconds just to get a
row count on 300k rows.
I removed the text column, so it really was just a few columns of fixed
data.
Still very bloated. Table size was 450M
The advice I was given was to do CLUSTER, but this did not reduce the table
size in the least.
Nor performance.
Also to resize my free space map (which still does need to be done).
Since that involves tweaking the kernel settings, taking the site down and
rebooting postgres and exposing the system to all kinds of risks and
unknowns and expensive experimentations I was unable to do it and have had
to hobble along with a slow table in my backend holding up jobs.
Much swearing that nobody should ever do VACUUM FULL. Manual advises
against it. Only crazy people do that.
Finally I decide to stop taking advice.
ns=> explain analyze select count(*) from fastadder_fastadderstatus;
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=62602.08..62602.09 rows=1 width=0) (actual
time=25320.000..25320.000 rows=1 loops=1)
-> Seq Scan on fastadder_fastadderstatus (cost=0.00..61815.86
rows=314486 width=0) (actual time=180.000..25140.000 rows=314493 loops=1)
Total runtime: *25320.000* ms
ns=> vacuum full fastadder_fastadderstatus;
took about 20 minutes
ns=> explain analyze select count(*) from fastadder_fastadderstatus;
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7478.03..7478.04 rows=1 width=0) (actual
time=940.000..940.000 rows=1 loops=1)
-> Seq Scan on fastadder_fastadderstatus (cost=0.00..6691.82
rows=314482 width=0) (actual time=0.000..530.000 rows=314493 loops=1)
Total runtime: *940.000 ms*
moral of the story: if your table is really bloated, just do VACUUM FULL
CLUSTER will not reduce table bloat in and identical fashion
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: good old VACUUM FULL
2011-03-23 00:52 good old VACUUM FULL felix <crucialfelix@gmail.com>
@ 2011-03-23 04:24 ` Chris <dmagick@gmail.com>
2011-04-01 03:41 ` Re: good old VACUUM FULL Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 1 reply; 5+ messages in thread
From: Chris @ 2011-03-23 04:24 UTC (permalink / raw)
To: pgsql-performance
On 23/03/11 11:52, felix wrote:
> I posted many weeks ago about a severe problem with a table that was
> obviously bloated and was stunningly slow. Up to 70 seconds just to get
> a row count on 300k rows.
>
> I removed the text column, so it really was just a few columns of fixed
> data.
> Still very bloated. Table size was 450M
>
> The advice I was given was to do CLUSTER, but this did not reduce the
> table size in the least.
> Nor performance.
>
> Also to resize my free space map (which still does need to be done).
> Since that involves tweaking the kernel settings, taking the site down
> and rebooting postgres and exposing the system to all kinds of risks and
> unknowns and expensive experimentations I was unable to do it and have
> had to hobble along with a slow table in my backend holding up jobs.
>
> Much swearing that nobody should ever do VACUUM FULL. Manual advises
> against it. Only crazy people do that.
<snip>
> moral of the story: if your table is really bloated, just do VACUUM FULL
You'll need to reindex that table now - vacuum full can bloat your
indexes which will affect your other queries.
reindex table fastadder_fastadderstatus;
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: good old VACUUM FULL
2011-03-23 00:52 good old VACUUM FULL felix <crucialfelix@gmail.com>
2011-03-23 04:24 ` Re: good old VACUUM FULL Chris <dmagick@gmail.com>
@ 2011-04-01 03:41 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Craig Ringer @ 2011-04-01 03:41 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
On 23/03/2011 12:24 PM, Chris wrote:
> You'll need to reindex that table now - vacuum full can bloat your
> indexes which will affect your other queries.
It doesn't seem to matter much for a one-off. Index bloat problems have
mainly been encountered where people are running VACUUM FULL as part of
routine maintenance - for example, from a nightly cron job.
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: good old VACUUM FULL
2011-03-23 00:52 good old VACUUM FULL felix <crucialfelix@gmail.com>
@ 2011-03-23 06:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-03-23 13:13 ` Re: good old VACUUM FULL Shaun Thomas <sthomas@peak6.com>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2011-03-23 06:16 UTC (permalink / raw)
To: felix <crucialfelix@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 22, 2011 at 6:52 PM, felix <crucialfelix@gmail.com> wrote:
> I posted many weeks ago about a severe problem with a table that was
> obviously bloated and was stunningly slow. Up to 70 seconds just to get a
> row count on 300k rows.
> I removed the text column, so it really was just a few columns of fixed
> data.
> Still very bloated. Table size was 450M
> The advice I was given was to do CLUSTER, but this did not reduce the table
> size in the least.
Then either cluster failed (did you get an error message) or the table
was not bloated. Given that it looks like it was greatly reduced in
size by the vacuum full, I'd guess cluster failed for some reason.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: good old VACUUM FULL
2011-03-23 00:52 good old VACUUM FULL felix <crucialfelix@gmail.com>
2011-03-23 06:16 ` Re: good old VACUUM FULL Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-03-23 13:13 ` Shaun Thomas <sthomas@peak6.com>
0 siblings, 0 replies; 5+ messages in thread
From: Shaun Thomas @ 2011-03-23 13:13 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: felix <crucialfelix@gmail.com>; pgsql-performance
On 03/23/2011 01:16 AM, Scott Marlowe wrote:
> Then either cluster failed (did you get an error message) or the table
> was not bloated. Given that it looks like it was greatly reduced in
> size by the vacuum full, I'd guess cluster failed for some reason.
Or it just bloated again. Remember, he still hasn't changed his
max_fsm_pages setting, and that table apparently experiences *very* high
turnover.
A 25x bloat factor isn't unheard of for such a table. We have one that
needs to have autovacuum or be manually vacuumed frequently because it
experiences several thousand update/deletes per minute. The daily
turnover of that particular table is around 110x. If our fsm settings
were too low, or we didn't vacuum regularly, I could easily see that
table quickly becoming unmanageable. I fear for his django_session table
for similar reasons.
Felix, I know you don't want to "experiment" with kernel parameters, but
you *need* to increase your max_fsm_pages setting.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-04-01 03:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-23 00:52 good old VACUUM FULL felix <crucialfelix@gmail.com>
2011-03-23 04:24 ` Chris <dmagick@gmail.com>
2011-04-01 03:41 ` Craig Ringer <craig@postnewspapers.com.au>
2011-03-23 06:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-03-23 13:13 ` Shaun Thomas <sthomas@peak6.com>
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