The following bug has been logged online:
Bug reference: 3942
Logged by: Valentine Gogichashvili
Email address: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED]:~$ ps -ef | grep VACUUM | grep -v grep
postgres 24359 21321 0 13:47 ? 00:00:12 postgres: postgres
rumataindex [local] VACUUM
[EMAIL PROTECTED]:~$ sudo su - postgres
[EMAIL PROTECTED]:~$ 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
[EMAIL PROTECTED]:~$ kill 24359
[EMAIL PROTECTED]:~$ 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:
[EMAIL PROTECTED]:~/pgsql/data$ pg_ctl -D . stop -m f
waiting for server to shut
down............................................................... failed
[EMAIL PROTECTED]:~/pgsql/data$ pg_ctl -D . stop -m f
waiting for server to shut
down............................................................... failed
pg_ctl: server does not shut down
[EMAIL PROTECTED]:~/pgsql/data$ pg_ctl -D . stop -m i
waiting for server to shut down.... done
server stopped
[EMAIL PROTECTED]:~/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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
