Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add missing index to Postgresql database to resolve high CPU usage #3995

Closed
mirogta opened this issue Jun 10, 2019 · 2 comments

Comments

Projects
None yet
3 participants
@mirogta
Copy link

commented Jun 10, 2019

Bug Report

There is a missing index which would have enabled field on the versioned_resources table.

This causes high load for the concurse database.

Steps to Reproduce

Create a pipeline, trigger many builds, have >1M rows in the versioned_resources table for a single pipeline.

Expected Results

Concourse/Postresql have low CPU usage.

Actual Results

Concourse/Postresql have high CPU usage.

Additional Context

See a detailed overview, analysis and a proposed solution in https://discuss.concourse-ci.org/t/concourse-db-high-cpu-load-80-90-all-time/754/8

Note that there need to be two different versions of the index, depending on the version of Postresql.

One index with INCLUDE'd column which is more optimal, for PostreSQL 11.x, and a different one for PostgreSQL 10.x, because the INCLUDE’d columns in an index are only supported on PostgreSQL v11+. I've explained in in the above post as well.

Version Info

  • Concourse version: 4.2.2. I haven't seen the issue in Concourse 5.x so this is likely going to be the issue on the latest version too.
@vito

This comment has been minimized.

Copy link
Member

commented Jun 10, 2019

We no longer use versioned_resources in 5.x - I'd recommend upgrading and seeing if there is a similar bottleneck in the new schema. 🤔

@mirogta

This comment has been minimized.

Copy link
Author

commented Jun 10, 2019

OK, I'll close this issue then because it's specific to version 4.x. If there is a similar problem then a new issue can be created.

@mirogta mirogta closed this Jun 10, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.