strace did show what looked like it was stuck waiting, and it was the same vacuum proc as in the pg_Stat_activity. I had to restart the postmaster and eventually reload the data because no subsequent vacuum would finish properly. Was there anything else I could have done? On that note, if I am just filling tables, extracting the data off, dropping the tables, and then recreating additional tables, do I really need to do a VACUUM? Thanks, Scott On Wed, 2005-08-03 at 09:41, Guido Barosio wrote: > Usually, on this situations, you've got a deadlock or alike. > > What I do is to trace the pid of the vacuum (strace -p pid) and try to > guess if the vacuum > is working or expecting some semaphore to get green (that would be a > problem on the db side). > > If this is the problem, a quick look into the procs running in the > pg_Stat_activity table will give > you an idea of the situation. Prolly, nothing was going on when you > started your vacuum > but somehow, a proc acquired a lock somewhere and the vacuum reached > that somewhere causing > somekinda wait. > > At least, this is very often in my scenery, using 7.4.3 and linux > also. > > Regards, > Guido > > On 8/3/05, Scott Barvick <sbarvick@xxxxxxxxxxx> wrote: > I have an application that may add a couple million rows per > day so I > vacuum nightly. The tables never get to more than about 10 > million rows > before I move off the interesting information to other > media. Somewhat > often, my vacuums don't complete as shown from this ps command > (usually > VACUUMs take a couple minutes) > > 4442 ? R 949:07 postgres: postgres tag > 127.0.0.1(33420) VACUUM > > and the CPU for postmaster (hyperthreaded linux) will be in > the high > 90s. > > The locks look like: > scouts=# select * from pg_locks; > relation | database | transaction | pid | > mode | > granted > ----------+----------+-------------+-------+--------------------------+--------- > | | 12826125 | 11642 | > ExclusiveLock | > t > 16839 | 17230 | | 11642 | > AccessShareLock | > t > 17251 | 17230 | | 4442 | > RowExclusiveLock | > t > 17251 | 17230 | | 4442 | > ShareUpdateExclusiveLock | > t > 17246 | 17230 | | 4442 | > ShareUpdateExclusiveLock | > t > 17246 | 17230 | | 4442 | > ShareUpdateExclusiveLock | > t > | | 12817402 | 4442 | > ExclusiveLock | > t > (7 rows) > > > I all inserts and maintenance through JDBC and may have > inserts going on > while a different java thread calls the VACUUM command. > > Any thoughts? Can I recover without dropping the server? > > Thanks for any help, > Scott > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an > appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so > that your > message can get through to the mailing list cleanly > > > > -- > "Adopting the position that you are smarter than an automatic > optimization algorithm is generally a good way to achieve less > performance, not more" - Tom Lane.
