IBM Support

Vacuum may block access to certain databases for a while after system startup

Question & Answer


Question

Why can't we connect to a particular database for about 1 hour after system start up?

Cause


In the startup.log, we can see
2015-05-09 17:13:11 CST: Database EDW needs to be reindexed...


In pg.log, we can see system is performing vacuum for about 1 hour:
Immediate Shutdown request at Sat May 9 17:11:52 2015
2015-05-09 18:34:40.237342 CST [14522] DEBUG: CheckPointTime = 300
2015-05-09 18:34:40.257332 CST [14523] NOTICE: database system was shut
down at 2015-05-09 18:34:39 CST
2015-05-09 18:35:46.688269 CST [18384] DEBUG: connection: host=127.0.0.1
user=xxx database=xxx remotepid=18383 fetype=1
[...]
2015-05-09 18:35:46.725697 CST [18384] DEBUG: QUERY: set enable_lazy_vacuum=true; vacuum
2015-05-09 18:35:46.737358 CST [18384] DEBUG: VACUUM: Opened all system relations.
[...]
2015-05-09 19:58:10.996341 CST [18384] DEBUG: Analyzing...
2015-05-09 19:58:11.017569 CST [18384] DEBUG: disconnect: host=127.0.0.1
user=xxx database=xxx remotepid=18383 fetype=1

While the database is being vacuumed, the vacuum session holds exclusive locks on some of the system objects for that database (to prevent other sessions from modifying the tables the vacuum process is rewriting).

When normal sessions connect to a database they attempt to place shared locks on some of the same objects. Because the vacuum session's locks are exclusive locks, the shared lock attempts fail and other sessions cannot connect to that database.

The locks are database specific, so user can connect to other databases.
After the vacuum process completes, its locks are removed and users can connect again:

Answer


There is no safe way to vacuum database tables without having exclusive access, so it is inevitable that other users will have periods of no access. The performance benefits of databases that have been vacuumed outweigh the inconvenience of brief loss of access.

A strategy to minimise the impact on end users is to periodically schedule an outage to run a manual vacuum with nz_manual_vacuum. That will mean there is much less work for the lazy vacuum (which occurs automatically when NPS starts) to do.

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"Standard","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 October 2019

UID

swg21961244