On Fri, Feb 13, 2009 at 2:56 PM, Roger Ging <rging@xxxxxxxxxxxxxxxx> wrote: > Scott, > > 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: > > INFO: "license": found 257 removable, 20265895 nonremovable row versions in > 1088061 pages > DETAIL: 0 dead row versions cannot be removed yet. > There were 18434951 unused item pointers. > 687274 pages contain useful free space. > 0 pages are entirely empty. > CPU 38.15s/37.02u sec elapsed 621.19 sec. > WARNING: relation "licensing.license" contains more than "max_fsm_pages" > pages with useful free space > HINT: Consider using VACUUM FULL on this relation or increasing the > configuration parameter "max_fsm_pages". Yeah, that's pretty bad. ~2 Million live rows and ~18 Million dead ones is a pretty badly bloated table. Vacuum full is one way to reclaim that lost space. You can also dump and restore that one table, inside a drop / create restraints in a transaction during maintenance if you can. A Vacuum full is quite intrusive, so avoid it during normal working hours. Dumping and reloading the whole db may be faster than either a vacuum full or a cluster. A common trick is to do something like: begin; select * into ordermydata from bigbloatedtable order by some_field; delete * from bigbloatedtable; insert into bigbloatedtable select * from ordermydata; commit; This will both put your table in some order which might help, and remove the bloat. > 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. Sounds like either autovacuum isn't running right, or it was turned off and no decent vacuum schedule was set up by the dba. Or the FSM is too small, but judging by how bloated this table is, I'd guess vacuuming got turned off. > The machine has been under heavy load 24/7 for a couple of months, so I have > not been able to upgrade versions. I am taking it offline this weekend and > will install the latest. I'll try to re-create the scenario I had going on > yesterday over the weekend and get some io statistics. Definitely look into the check_postgresql.pl script for nagios or something similar to keep track of bloated tables. Every friday I log into my production servers and fire off a few of the queries from that script to check for bloat and make sure everything's running fine. Another useful trick is to email yourself a copy of the last 20 or 30 lines of vacuum verbose for the whole db every so often (once a week) and pay attention to your fsm usage. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
