BUG #3942: Related to BUG #2568 and BUG #2859: VACUUM process hangs and does not respond to the kill signals - Mailing list pgsql-bugs
| From | Valentine Gogichashvili |
|---|---|
| Subject | BUG #3942: Related to BUG #2568 and BUG #2859: VACUUM process hangs and does not respond to the kill signals |
| Date | |
| Msg-id | 200802081621.m18GLiHk042777@wwwmaster.postgresql.org Whole thread Raw |
| Responses |
Re: BUG #3942: Related to BUG #2568 and BUG #2859: VACUUM process hangs and does not respond to the kill signals
("Heikki Linnakangas" <heikki@enterprisedb.com>)
|
| List | pgsql-bugs |
The following bug has been logged online:
Bug reference: 3942
Logged by: Valentine Gogichashvili
Email address: valgog@gmail.com
PostgreSQL version: 8.2.3
Operating system: Linux 2.6.8 SMP
Description: Related to BUG #2568 and BUG #2859: VACUUM process hangs
and does not respond to the kill signals
Details:
We are running several servers with the 'same' configuration.
Recently two of many servers started to experience similar problems. It is
quite difficult to reproduce. So I am providing the information that I have
managed to get by now. The problem on the second server was to be seen only
once. The problem on the first server is happening once a day or so. It does
not seem to actually depend on the server load.
The problem is normally noticed when the update sessions on one of the
biggest table are hanging. The server has a 'statement_timeout' parameter
set globally to 20 seconds and cronjob vacuum jobs are being run every 2
hours like:
"set statement_timeout to 0; VACUUM VERBOSE ANALYZE;", but never the less
the update sessions are not killed for hours.
I cannot kill VACUUM session explicitly as well.
More info:
rumata@dbserver-04:~$ ps -ef | grep VACUUM | grep -v grep
postgres 24359 21321 0 13:47 ? 00:00:12 postgres: postgres
rumataindex [local] VACUUM
rumata@dbserver-04:~$ sudo su - postgres
postgres@dbserver-04:~$ psql rumataindex
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
rumataindex=# select * from pg_locks where pid = 24359;
locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode
| granted
---------------+----------+----------+------+-------+---------------+-------
--+-------+----------+-------------+-------+--------------------------+-----
----
relation | 17717 | 18917 | | | |
| | | 1045306585 | 24359 | ShareUpdateExclusiveLock | t
relation | 17717 | 1892961 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94216 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94213 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94214 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94206 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 185865 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 952119 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94212 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94215 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94207 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 185867 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94217 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
transactionid | | | | | 1045306585 |
| | | 1045306585 | 24359 | ExclusiveLock | t
relation | 17717 | 94208 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94211 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
(16 rows)
rumataindex=# \q
postgres@dbserver-04:~$ kill 24359
postgres@dbserver-04:~$ psql rumataindex
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
rumataindex=# select * from pg_locks where pid = 24359;
locktype | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid | mode
| granted
---------------+----------+----------+------+-------+---------------+-------
--+-------+----------+-------------+-------+--------------------------+-----
----
relation | 17717 | 18917 | | | |
| | | 1045306585 | 24359 | ShareUpdateExclusiveLock | t
relation | 17717 | 1892961 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94216 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94213 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94214 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94206 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 185865 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 952119 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94212 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94215 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94207 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 185867 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94217 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
transactionid | | | | | 1045306585 |
| | | 1045306585 | 24359 | ExclusiveLock | t
relation | 17717 | 94208 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
relation | 17717 | 94211 | | | |
| | | 1045306585 | 24359 | RowExclusiveLock | t
(16 rows)
rumataindex=# \q
In addition, I want to mention, that "VACUUM VERBOSE ANALYZE;" session owned
locks are the oldest ones, when the issue is occurring.
The only way to restart the server is to force immediate server shutdown
like:
postgres@dbserver-04:~/pgsql/data$ pg_ctl -D . stop -m f
waiting for server to shut
down............................................................... failed
postgres@dbserver-04:~/pgsql/data$ pg_ctl -D . stop -m f
waiting for server to shut
down............................................................... failed
pg_ctl: server does not shut down
postgres@dbserver-04:~/pgsql/data$ pg_ctl -D . stop -m i
waiting for server to shut down.... done
server stopped
postgres@dbserver-04:~/pgsql/data$ pg_ctl -D . start
server starting
There are no errors or warning related to VACUUM to be noticed in the log
files. Or any logging related to the sessions, that are still hanging.
With best regards,
-- Valentine Gogichashvili
pgsql-bugs by date: