Degraded frontend performance

[GergelyKalmar]

Bug Report

The concourse frontend loads very slowly due to some queries taking a long time (see e.g. resources with >4s):

I also see some console errors, not sure if this is related:

Steps to Reproduce

Load the frontend.

Expected Results

The frontend loads quick as usual.

Actual Results

The frontend is very laggy.

Version Info

• Concourse version: 5.6.0
• Deployment type (BOSH/Docker/binary): binary
• Infrastructure/IaaS: AWS
• Browser (if applicable): Chromium 77.0.3865.90 (Official Build) Built on Ubuntu, running on Ubuntu 16.04 (64-bit)
• Did this used to work? Yes

[jamieklassen]

@GergelyKalmar can you provide any further details of your scenario? Is it reproducible? Is it always the same page that causes it? Can you share any relevant pipeline configs, or describe any other context on the running cluster? I'm guessing this has to do with database performance.

What page were you looking at when you took that screenshot? seeing an events call suggests it was a build page... so then the slow resources call is probably a BuildResources action. The underlying database queries look pretty darn expensive, traversing and deserializing things row by row. Instinctively I want to say this is going to be cheaper with the Algorithm v3 work that should be shipped with v6.0 but I looked at that branch and the code looks pretty similar. So I'll encourage @concourse/core-api to weigh in here.

[GergelyKalmar]

@pivotal-jamie-klassen Thank you for your reply! Yes, it seems to be a persistent issue on pretty much all pages. Here is an example from the front page:

Another one in a build page:

Indeed it seems to be related to the upgrade from 5.5.x to 5.6.0, which also coincides with the checks being stored in the database, and fair enough, I see some heavier database usage since the upgrade (higher RAM, higher queue depth).

It also seems that the number of concurrent connections to the database is very high (>75) since the update (which I was stupid enough to leave out from monitoring), which seems to explain the performance issues:

2019-10-29 13:00:53 UTC:172.31.24.144(50740):concourse@concourse:[7296]:FATAL: remaining connection slots are reserved for non-replication superuser connections 2019-10-29 13:00:54 UTC:172.31.42.33(57148):concourse@concourse:[7289]:FATAL: remaining connection slots are reserved for non-replication superuser connections 2019-10-29 13:00:54 UTC:172.31.42.33(57152):concourse@concourse:[7295]:FATAL: remaining connection slots are reserved for non-replication superuser connections 2019-10-29 13:00:55 UTC:172.31.24.144(50742):concourse@concourse:[7315]:FATAL: remaining connection slots are reserved for non-replication superuser connections 2019-10-29 13:01:12 UTC:172.31.24.144(50744):concourse@concourse:[7316]:FATAL: remaining connection slots are reserved for non-replication superuser connections 2019-10-29 13:01:13 UTC:172.31.42.33(57172):concourse@concourse:[7614]:FATAL: sorry, too many clients already

Is this expected behavior or can I do something to mitigate this?

[xtremerui]

Is your deployment enabling LIDAR (aka. the new resource scanner/checker) ? The slowness is coming from slow DB response as connections seems packed.

[GergelyKalmar]

No, I haven't changed the cluster configuration when updating to 5.6.0 because it said this is an experimental feature. Do you think that could resolve the problem with the connection count?

It seems others haven't hit this issue yet, or at least I can't see any similar GitHub issues, so I'm wondering if this is a general problem or I have some peculiarity that triggers this behavior.

[GergelyKalmar]

Enabling LIDAR seems to have improved the situation, the number of connections decreased. I also upgraded the database to allow for more connections and migrated to concourse 5.7.0, these seem to have solved the issue for now.

Does CONCOURSE_ENABLE_LIDAR=1 need to be added only to the workers or also for the web nodes? It is not clear from the changelog.

[xtremerui]

@GergelyKalmar CONCOURSE_ENABLE_LIDAR only needs to add to web node.

[GergelyKalmar]

@xtremerui Great, thanks!