{"action":"created","issue":{"url":"https://api.github.com/repos/concourse/concourse/issues/2254","repository_url":"https://api.github.com/repos/concourse/concourse","labels_url":"https://api.github.com/repos/concourse/concourse/issues/2254/labels{/name}","comments_url":"https://api.github.com/repos/concourse/concourse/issues/2254/comments","events_url":"https://api.github.com/repos/concourse/concourse/issues/2254/events","html_url":"https://github.com/concourse/concourse/issues/2254","id":328760245,"node_id":"MDU6SXNzdWUzMjg3NjAyNDU=","number":2254,"title":"Large amount of active connections on Wings GCP sql DB that degrade overall performance","user":{"login":"xtremerui","id":1585949,"node_id":"MDQ6VXNlcjE1ODU5NDk=","avatar_url":"https://avatars3.githubusercontent.com/u/1585949?v=4","gravatar_id":"","url":"https://api.github.com/users/xtremerui","html_url":"https://github.com/xtremerui","followers_url":"https://api.github.com/users/xtremerui/followers","following_url":"https://api.github.com/users/xtremerui/following{/other_user}","gists_url":"https://api.github.com/users/xtremerui/gists{/gist_id}","starred_url":"https://api.github.com/users/xtremerui/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xtremerui/subscriptions","organizations_url":"https://api.github.com/users/xtremerui/orgs","repos_url":"https://api.github.com/users/xtremerui/repos","events_url":"https://api.github.com/users/xtremerui/events{/privacy}","received_events_url":"https://api.github.com/users/xtremerui/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-06-02T17:06:53Z","updated_at":"2018-06-04T13:40:07Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"# BUG Report\r\n\r\n## What challenge are you facing?\r\n\r\nRunning the following query on Wings db shows there is a large amount of active connections (about 158 out of 196)\r\n```\r\nselect * from pg_stat_activity where datname = 'atc' and state='active'\r\n```\r\n\r\nAll of them are used for a kind of query that is for getting versioned resource, for example\r\n```\r\nSELECT v.id, v.enabled, v.type, v.version, v.metadata, v.modified_time, v.check_order FROM versioned_resources v, resources r WHERE r.name = 'strabo-properties-s3' AND r.pipeline_id = 5 AND v.resource_id = r.id ORDER BY check_order DESC Limit 1\r\n```\r\nRunning above query manually shows it take about 10 sec on wings db. Using postgres analyze matches the result\r\n```\r\nEXPLAIN ANALYZE\r\nSELECT v.id, v.enabled, v.type, v.version, v.metadata, v.modified_time, v.check_order FROM versioned_resources v, resources r WHERE r.name = 'strabo-properties-s3' AND r.pipeline_id = 5 AND v.resource_id = r.id ORDER BY check_order DESC Limit 1\r\n\r\nLimit  (cost=0.83..492.99 rows=1 width=101) (actual time=10494.315..10494.315 rows=1 loops=1)\r\n  ->  Nested Loop  (cost=0.83..120087.40 rows=244 width=101) (actual time=10494.314..10494.314 rows=1 loops=1)\r\n        Join Filter: (v.resource_id = r.id)\r\n        Rows Removed by Join Filter: 859587\r\n        ->  Index Scan using versioned_resources_check_order on versioned_resources v  (cost=0.42..105257.11 rows=988124 width=105) (actual time=0.013..7873.692 rows=859588 loops=1)\r\n        ->  Materialize  (cost=0.41..8.43 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=859588)\r\n              ->  Index Scan using unique_pipeline_id_name on resources r  (cost=0.41..8.42 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)\r\n                    Index Cond: ((pipeline_id = 5) AND (name = 'strabo-properties-s3'::text))\r\nPlanning time: 0.655 ms\r\nExecution time: 10494.354 ms\r\n```\r\n\r\nWhen this happens on Wings, since there is no available connection for fetching new resource version, the resource checking will fail and causes builds being in pending state much longer.\r\n\r\n## A Modest Proposal\r\n\r\n1. Find out why this query is slow on Wings db i.e. migrations?\r\n1. ~~~If not found~~~, optimize this kind of query to make sure there is no DB performance degradation in a large concourse deployment that has thousands of resources ticking. For example, adding `modified_time` in `order by` for the above slow query, it yields same result but is way fast ( 1 ms vs 10000 ms)\r\n```\r\nEXPLAIN ANALYZE\r\nSELECT v.id, v.enabled, v.type, v.version, v.metadata, v.modified_time, v.check_order FROM versioned_resources v, resources r WHERE r.name = 'strabo-properties-s3' AND r.pipeline_id = 5 AND v.resource_id = r.id ORDER BY check_order DESC, modified_time Limit 1\r\n\r\nLimit  (cost=2262.75..2262.75 rows=1 width=101) (actual time=1.008..1.008 rows=1 loops=1)\r\n  ->  Sort  (cost=2262.75..2263.37 rows=246 width=101) (actual time=1.006..1.006 rows=1 loops=1)\r\n        Sort Key: v.check_order DESC, v.modified_time\r\n        Sort Method: top-N heapsort  Memory: 25kB\r\n        ->  Nested Loop  (cost=0.83..2261.52 rows=246 width=101) (actual time=0.037..0.812 rows=403 loops=1)\r\n              ->  Index Scan using unique_pipeline_id_name on resources r  (cost=0.41..8.42 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)\r\n                    Index Cond: ((pipeline_id = 5) AND (name = 'strabo-properties-s3'::text))\r\n              ->  Index Scan using versioned_resources_resource_id_idx on versioned_resources v  (cost=0.42..2244.60 rows=850 width=105) (actual time=0.020..0.713 rows=403 loops=1)\r\n                    Index Cond: (resource_id = r.id)\r\nPlanning time: 0.326 ms\r\nExecution time: 1.041 ms\r\n```\r\n\r\nConcourse version: v3.13.1-rc.38\r\n"},"comment":{"url":"https://api.github.com/repos/concourse/concourse/issues/comments/394358228","html_url":"https://github.com/concourse/concourse/issues/2254#issuecomment-394358228","issue_url":"https://api.github.com/repos/concourse/concourse/issues/2254","id":394358228,"node_id":"MDEyOklzc3VlQ29tbWVudDM5NDM1ODIyOA==","user":{"login":"vito","id":1880,"node_id":"MDQ6VXNlcjE4ODA=","avatar_url":"https://avatars1.githubusercontent.com/u/1880?v=4","gravatar_id":"","url":"https://api.github.com/users/vito","html_url":"https://github.com/vito","followers_url":"https://api.github.com/users/vito/followers","following_url":"https://api.github.com/users/vito/following{/other_user}","gists_url":"https://api.github.com/users/vito/gists{/gist_id}","starred_url":"https://api.github.com/users/vito/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vito/subscriptions","organizations_url":"https://api.github.com/users/vito/orgs","repos_url":"https://api.github.com/users/vito/repos","events_url":"https://api.github.com/users/vito/events{/privacy}","received_events_url":"https://api.github.com/users/vito/received_events","type":"User","site_admin":false},"created_at":"2018-06-04T13:40:07Z","updated_at":"2018-06-04T13:40:07Z","author_association":"MEMBER","body":"This is coming from `(db.Pipeline).GetLatestVersionedResource`, which is used for the period checks, and so gets called every minute for every resource in every pipeline.\r\n\r\nThis query is hitting a lot of rows for something called so often, and this approach may only get worse with concourse/rfcs#1. Can we reduce it to a simple join by tracking `latest_versioned_resource_id` on the `resources` table instead? We can set this value every time there are new versions to save."}}
{"actor":{"display_login":"vito"}}