Re: Vacuum wait time problem - Mailing list pgsql-admin

Scott,<br /><br /> I can only answer a couple of the questions at the moment.  I had to kill the vacuum full and do a
regularvacuum, so I can't get the iostat and vmstat outputs right now.  This message is the reason I was trying to run
vacuumfull:<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
pagescontain 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: 
Considerusing VACUUM FULL on this relation or increasing the configuration parameter "max_fsm_pages".<br /><br /> A
cleanrestore 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
finishedwhen I got in yesterday, almost 8 hours after it started.<br /><br /> The machine has been under heavy load
24/7for a couple of months, so I have not been able to upgrade versions.  I am taking it offline this weekend and will
installthe latest.  I'll try to re-create the scenario I had going on yesterday over the weekend and get some io
statistics.<br/><br /> Roger<br /><br /> Scott Marlowe wrote: <blockquote
cite="mid:dcc563d10902131321h548104a0vdf3d16f0dc27f015@mail.gmail.com"type="cite"><pre wrap="">On Fri, Feb 13, 2009 at
10:20AM, Roger Ging <a class="moz-txt-link-rfc2396E"
href="mailto:rging@musicreports.com"><rging@musicreports.com></a>wrote: </pre><blockquote type="cite"><pre
wrap="">Hi,

I'm running vacuum full analyze verbose on a table with 20million rows and
11 indexes.  In top, I'm seeing [pdflush] and postgres: writer process each
using diferent cpu cores, with wait time well above 90% on each of them.The vacuum has been running for several hours,
andthe last thing to show 
on screen, over an hour ago, was :

DETAIL:  8577281 index row versions were removed.
736 index pages have been deleted, 736 are currently reusable.
CPU 7.57s/52.52u sec elapsed 381.70 sec.

That's the last index

The vacuum process itself is using less than 2% of a core.
The pg version is 8.3.1 running on Suse.  Hardware is 2X dual core Opterons,
16 GB RAM, 24 drives in RAID 50

It would seem to me that the system is extremely IO bound, but I don't know
how to find out what specifically is wrong here.  Any advice greatly
appreciated.   </pre></blockquote><pre wrap="">
A couple of questions.
Why Vacuum full as opposed to vacuum (regular)?
Why 8.3.1 which has known bugs, instead of 8.3.latest?
What do "vmstat 10" and iostat -x 10 have to say about your drive
arrays while this vacuum is running? </pre></blockquote><br />