1--- 2stage: none 3group: unassigned 4info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments 5--- 6 7# Cached queries guidelines 8 9Rails provides an [SQL query cache](https://guides.rubyonrails.org/caching_with_rails.html#sql-caching) 10which is used to cache the results of database queries for the duration of a request. 11When Rails encounters the same query again within the same request, it uses the cached 12result set instead of running the query against the database again. 13 14The query results are only cached for the duration of that single request, and 15don't persist across multiple requests. 16 17## Why cached queries are considered bad 18 19Cached queries help by reducing the load on the database, but they still: 20 21- Consume memory. 22- Require Rails to re-instantiate each `ActiveRecord` object. 23- Require Rails to re-instantiate each relation of the object. 24- Make us spend additional CPU cycles to look into a list of cached queries. 25 26Although cached queries are cheaper from a database perspective, they are potentially 27more expensive from a memory perspective. They could mask 28[N+1 query problems](https://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations), 29so you should treat them the same way you treat regular N+1 queries. 30 31In cases of N+1 queries masked by cached queries, the same query is executed N times. 32It doesn't hit the database N times but instead returns the cached results N times. 33This is still expensive because you need to re-initialize objects each time at a 34greater expense to the CPU and memory resources. Instead, you should use the same 35in-memory objects whenever possible. 36 37When you introduce a new feature, you should: 38 39- Avoid N+1 queries. 40- Minimize the [query count](merge_request_performance_guidelines.md#query-counts). 41- Pay special attention to ensure 42 [cached queries](merge_request_performance_guidelines.md#cached-queries) are not 43 masking N+1 problems. 44 45## How to detect cached queries 46 47### Detect potential offenders by using Kibana 48 49GitLab.com, logs entries with the number of executed cached queries in the 50`pubsub-redis-inf-gprd*` index as 51[`db_cached_count`](https://log.gprd.gitlab.net/goto/77d18d80ad84c5df1bf1da5c2cd35b82). 52You can filter by endpoints that have a large number of executed cached queries. For 53example, an endpoint with a `db_cached_count` greater than 100 can indicate an N+1 problem which 54is masked by cached queries. You should investigate this endpoint further to determine 55if it is indeed executing duplicated cached queries. 56 57For more Kibana visualizations related to cached queries, read 58[issue #259007, 'Provide metrics that would help us to detect the potential N+1 CACHED SQL calls'](https://gitlab.com/gitlab-org/gitlab/-/issues/259007). 59 60### Inspect suspicious endpoints using the Performance Bar 61 62When building features, use the 63[performance bar](../administration/monitoring/performance/performance_bar.md) 64to view the list of database queries, including cached queries. The 65performance bar shows a warning when the number of total executed and cached queries is 66greater than 100. 67 68To learn more about the statistics available to you, read the 69[Performance Bar documentation](../administration/monitoring/performance/performance_bar.md). 70 71## What to look for 72 73Using [Kibana](#detect-potential-offenders-by-using-kibana), you can look for a large number 74of executed cached queries. Endpoints with a large `db_cached_count` could suggest a large number 75of duplicated cached queries, which often indicates a masked N+1 problem. 76 77When you investigate a specific endpoint, use 78the [performance bar](#inspect-suspicious-endpoints-using-the-performance-bar) 79to identify similar and cached queries, which may also indicate an N+1 query issue 80(or a similar kind of query batching problem). 81 82### An example 83 84For example, let's debug the "Group Members" page. In the left corner of the 85performance bar, **Database queries** shows the total number of database queries 86and the number of executed cached queries: 87 88 89 90The page included 55 cached queries. Clicking the number displays a modal window 91with more details about queries. Cached queries are marked with the `cached` label 92below the query. You can see multiple duplicate cached queries in this modal window: 93 94 95 96Click **...** to expand the actual stack trace: 97 98```ruby 99[ 100 "app/models/group.rb:305:in `has_owner?'", 101 "ee/app/views/shared/members/ee/_license_badge.html.haml:1", 102 "app/helpers/application_helper.rb:19:in `render_if_exists'", 103 "app/views/shared/members/_member.html.haml:31", 104 "app/views/groups/group_members/index.html.haml:75", 105 "app/controllers/application_controller.rb:134:in `render'", 106 "ee/lib/gitlab/ip_address_state.rb:10:in `with'", 107 "ee/app/controllers/ee/application_controller.rb:44:in `set_current_ip_address'", 108 "app/controllers/application_controller.rb:493:in `set_current_admin'", 109 "lib/gitlab/session.rb:11:in `with_session'", 110 "app/controllers/application_controller.rb:484:in `set_session_storage'", 111 "app/controllers/application_controller.rb:478:in `set_locale'", 112 "lib/gitlab/error_tracking.rb:52:in `with_context'", 113 "app/controllers/application_controller.rb:543:in `sentry_context'", 114 "app/controllers/application_controller.rb:471:in `block in set_current_context'", 115 "lib/gitlab/application_context.rb:54:in `block in use'", 116 "lib/gitlab/application_context.rb:54:in `use'", 117 "lib/gitlab/application_context.rb:21:in `with_context'", 118 "app/controllers/application_controller.rb:463:in `set_current_context'", 119 "lib/gitlab/jira/middleware.rb:19:in `call'" 120] 121``` 122 123The stack trace shows an N+1 problem, because the code repeatedly executes 124`group.has_owner?(current_user)` for each group member. To solve this issue, 125move the repeated line of code outside of the loop, passing the result to each rendered member instead: 126 127```erb 128- current_user_is_group_owner = @group && @group.has_owner?(current_user) 129 130= render partial: 'shared/members/member', 131 collection: @members, as: :member, 132 locals: { membership_source: @group, 133 group: @group, 134 current_user_is_group_owner: current_user_is_group_owner } 135``` 136 137After [fixing the cached query](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/44626/diffs#27c2761d66e496495be07d0925697f7e62b5bd14), the performance bar now shows only 1386 cached queries: 139 140 141 142## How to measure the impact of the change 143 144Use the [memory profiler](performance.md#using-memory-profiler) to profile your code. 145For [this example](#an-example), wrap the profiler around the `Groups::GroupMembersController#index` action. Before the fix, the application had 146the following statistics: 147 148- Total allocated: 7133601 bytes (84858 objects) 149- Total retained: 757595 bytes (6070 objects) 150- `db_count`: 144 151- `db_cached_count`: 55 152- `db_duration`: 303ms 153 154The fix reduced the allocated memory, and the number of cached queries. These 155factors help improve the overall execution time: 156 157- Total allocated: 5313899 bytes (65290 objects), 1810KB (25%) less 158- Total retained: 685593 bytes (5278 objects), 72KB (9%) less 159- `db_count`: 95 (34% less) 160- `db_cached_count`: 6 (89% less) 161- `db_duration`: 162ms (87% faster) 162 163## For more information 164 165- [Metrics that would help us detect the potential N+1 Cached SQL calls](https://gitlab.com/gitlab-org/gitlab/-/issues/259007) 166- [Merge Request performance guidelines for cached queries](merge_request_performance_guidelines.md#cached-queries) 167- [Improvements for biggest offenders](https://gitlab.com/groups/gitlab-org/-/epics/4508) 168
