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.
