Database Query Timeout happens when accessing Environment page
Problem
When you access to the environment page on www-gitlab-com
project, you could encounter an 500 error due to database query timeout.
Query encountered timeout
SELECT
DISTINCT ON (environment_id) deployments.*
FROM
"deployments"
WHERE
"deployments"."status" = 2
AND "deployments"."environment_id" IN (137, 8098355)
ORDER BY
environment_id,
deployments.id DESC
This is executed when last_deployment
is preloaded for EnvironmentSerializer.
Confirmed with the following queries:
# Timeout
project.environments.preload(:last_deployment).with_state(:available).where(id: [137, 8098355])
# OK
project.environments.with_state(:available).where(id: [137, 8098355])
Current Query Performance
Time: 2.783 min
- planning: 3.673 ms
- execution: 2.783 min
- I/O read: 2.719 min
- I/O write: 0.000 ms
Unique (cost=293258.52..294295.37 rows=130226 width=144) (actual time=166763.118..166922.576 rows=1 loops=1)
Buffers: shared hit=170937 read=208434 dirtied=5223
I/O Timings: read=163141.890 write=0.000
-> Sort (cost=293258.52..293776.94 rows=207370 width=144) (actual time=166763.114..166852.336 rows=554610 loops=1)
Sort Key: deployments.environment_id, deployments.id DESC
Sort Method: external merge Disk: 79232kB
Buffers: shared hit=170937 read=208434 dirtied=5223
I/O Timings: read=163141.890 write=0.000
-> Index Scan using index_deployments_on_environment_id_status_and_finished_at on public.deployments (cost=0.57..274945.83 rows=207370 width=144) (actual time=6.004..165051.207 rows=554610 loops=1)
Index Cond: ((deployments.environment_id = ANY ('{137,8098355}'::integer[])) AND (deployments.status = 2))
Buffers: shared hit=170934 read=208434 dirtied=5223
I/O Timings: read=163141.890 write=0.000
https://gitlab.slack.com/archives/CLJMDRD8C/p1636957713446900
Proposal
Create the following index:
exec CREATE INDEX super_efficient_index_2 ON deployments USING btree (environment_id, id DESC) WHERE status = 2;
Sentry
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
https://sentry.gitlab.net/gitlab/gitlabcom/issues/2921774/?referrer=gitlab_plugin
PG::QueryCanceled: ERROR: canceling statement due to statement timeout
lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `block in read_using_load_balancer'
connection.send(...)
lib/gitlab/database/load_balancing/load_balancer.rb:55:in `read'
return yield connection
lib/gitlab/database/load_balancing/connection_proxy.rb:110:in `read_using_load_balancer'
@load_balancer.read do |connection|
lib/gitlab/database/load_balancing/connection_proxy.rb:55:in `select_all'
read_using_load_balancer(:select_all, arel, name, binds)
app/serializers/environment_serializer.rb:57:in `batch_load'
resource.all.to_a.tap do |environments|
...
(215 additional frame(s) were not displayed)
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout
Edited by Shinya Maeda