| From: | Tino Schwarze <postgresql(at)tisc(dot)de> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org |
| Subject: | Re: Vacuum wait time problem |
| Date: | 2009-02-13 22:05:48 |
| Message-ID: | 20090213220548.GG12562@easy2.in-chemnitz.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
Hi Roger,
On Fri, Feb 13, 2009 at 01:56:32PM -0800, Roger Ging wrote:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Please don't post HTML mails to mailing lists. Thanks.
> I can only answer a couple of the questions at the moment. I had to
> kill the vacuum full and do a regular vacuum, so I can't get the iostat
> and vmstat outputs right now. This message is the reason I was trying
> to run vacuum full:<br>
> <br>
> INFO: "license": found 257 removable, 20265895 nonremovable row
> versions in 1088061 pages<br>
> DETAIL: 0 dead row versions cannot be removed yet.<br>
> There were 18434951 unused item pointers.<br>
> 687274 pages contain useful free space.<br>
> 0 pages are entirely empty.<br>
> CPU 38.15s/37.02u sec elapsed 621.19 sec.<br>
> WARNING: relation "licensing.license" contains more than
> "max_fsm_pages" pages with useful free space<br>
> HINT: Consider using VACUUM FULL on this relation or increasing the
> configuration parameter "max_fsm_pages".
Just raise max_fsm_pages to a reasonable value (I've heard a rule of
thumb somewhere: 65536 per GB of data), then do a regular vacuum. The
space reclaimed by the vacuum might not become available to the OS, but
it will be reused. The above warning tells you that the relation will
probably start growing rather fast because unused space within a table
cannot be tracked.
> A clean restore of the database to another server create a size on disk
> of about 244GB. This server was at over 400GB yesterday, and now,
> after aggressive vacuuming by hand, is down to 350GB. It had gotten so
> bad that the backup was not finished when I got in yesterday, almost 8
> hours after it started.<br>
In my experience, dump/restore is faster than VACUUM FULL. I've had a
VACUUM FULL running for about 3 days some time ago. Dump/restore took
less than 12 hours (about 200 GB database on disk after restore).
Actually, VACUUM FULL is a last-resort after something has been screwed.
HTH,
Tino.
--
"What we nourish flourishes." - "Was wir nähren erblüht."
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Marlowe | 2009-02-13 22:13:28 | Re: Vacuum wait time problem |
| Previous Message | Roger Ging | 2009-02-13 21:56:32 | Re: Vacuum wait time problem |