Debugging slow jobs call

Hi

We’re on 5.2.0 and finding that concourse is becoming slower and slower to load.

I want some guidance - where should I look to understand what could be slow? I’ve checked Postgres and Server stats and they look healthy.

Thanks

Is it just the jobs call that is slow, or are all calls slow? One possibility is slow database queries due to certain tables growing large, which might explain why Concourse is getting slower over time. You can try turning on postgres query and duration logging to check this.

Also, is the database located in the same location as the web/ATC server? If they are in separate locations/datacenters, the increased latency might be impacting performance too.

Right now my logs are just filled with:

2019-08-07 15:47:00 UTC:10.0.1.187(39492):concourse@concourse:[23287]:ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes"
2019-08-07 15:47:00 UTC:10.0.1.187(39492):concourse@concourse:[23287]:DETAIL:  Key (id, state)=(12398780, created) is still referenced from table "volumes".
2019-08-07 15:47:00 UTC:10.0.1.187(39492):concourse@concourse:[23287]:STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4))
2019-08-07 15:47:05 UTC:10.0.1.187(39322):concourse@concourse:[23208]:ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes"
2019-08-07 15:47:05 UTC:10.0.1.187(39322):concourse@concourse:[23208]:DETAIL:  Key (id, state)=(12399078, created) is still referenced from table "volumes".
2019-08-07 15:47:05 UTC:10.0.1.187(39322):concourse@concourse:[23208]:STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4))
2019-08-07 15:47:09 UTC:10.0.1.187(40098):concourse@concourse:[23470]:ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes"
2019-08-07 15:47:09 UTC:10.0.1.187(40098):concourse@concourse:[23470]:DETAIL:  Key (id, state)=(12399497, created) is still referenced from table "volumes".
2019-08-07 15:47:09 UTC:10.0.1.187(40098):concourse@concourse:[23470]:STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4))
2019-08-07 15:47:13 UTC:10.0.1.187(39824):concourse@concourse:[23353]:ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes"
2019-08-07 15:47:13 UTC:10.0.1.187(39824):concourse@concourse:[23353]:DETAIL:  Key (id, state)=(12398832, created) is still referenced from table "volumes".

Which I think isn’t relevant. I’m going to turn on log_min_duration_statement and report back.

At the same time as doing this upgrade I increase our instance size. Despite there being no metrics that looked “bad” for the database, this appears to have resolved the slow APIs.

2 Likes