Re: Vacuum doesn't end
| От | Guido Barosio |
|---|---|
| Тема | Re: Vacuum doesn't end |
| Дата | |
| Msg-id | f7f6b4c705080306414f7fa30e@mail.gmail.com обсуждение исходный текст |
| Ответ на | Vacuum doesn't end (Scott Barvick <sbarvick@comcast.net>) |
| Ответы |
Re: Vacuum doesn't end
(Scott Barvick <sbarvick@comcast.net>)
|
| Список | pgsql-admin |
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
--
"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.
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@comcast.net> 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@postgresql.org 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.
В списке pgsql-admin по дате отправления: