BOSH operations are slow in an environment with many Director tasks

Products

• Operations Manager

Issue/Introduction

Symptoms:

BOSH operations such as BOSH upload-release, BOSH ssh, and other commands that touch the database run slowly.

BOSH director.stdout.log shows slow database queries such as:

[2019-04-17T18:14:29.452419 #27] [] DEBUG -- Director: (699.990473s) (conn: 47092766912780) SELECT * FROM `tasks` WHERE (state NOT IN ('processing', 'queued') and type='delete_deployment') ORDER BY `id` DESC LIMIT 2 OFFSET 2000

The BOSH database contains many tasks as indicated by the number outputted when running bosh tasks. Typically this becomes a problem after reaching several million tasks.

Environment

Cause

There is a known issue where the BOSH tasks table lacks a database index for column type. This can become a problem as the tasks table grows to a large size. 

Resolution

The solution is to upgrade to Operations Manager (Ops Manager) 2.3.9 or higher, which contains a fix to this issue. This version of Ops Manager updates Director to have an an index in the BOSH Director task table.

Do not attempt to manually add index to the database. Manually updating a BOSH database is a dangerous operation and may cause problems when upgrading in the future.