diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2021-05-07 15:10:27 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2021-05-07 15:10:27 +0300 |
commit | 53f456b167f19877d663ee6ed510673cebee0f91 (patch) | |
tree | fcc0bb52b79c195bf0eda100cc5d7e7a16dc0c0b /doc | |
parent | e8a31d8dc2afd673ca50d74d26edab0a0fec83ca (diff) |
Add latest changes from gitlab-org/gitlab@master
Diffstat (limited to 'doc')
-rw-r--r-- | doc/api/graphql/reference/index.md | 1 | ||||
-rw-r--r-- | doc/ci/unit_test_reports.md | 23 | ||||
-rw-r--r-- | doc/ci/yaml/README.md | 25 | ||||
-rw-r--r-- | doc/development/database/index.md | 2 | ||||
-rw-r--r-- | doc/development/database/pagination_guidelines.md | 315 | ||||
-rw-r--r-- | doc/development/database/pagination_performance_guidelines.md | 325 | ||||
-rw-r--r-- | doc/development/elasticsearch.md | 2 | ||||
-rw-r--r-- | doc/development/graphql_guide/pagination.md | 4 | ||||
-rw-r--r-- | doc/development/i18n/externalization.md | 42 | ||||
-rw-r--r-- | doc/development/img/offset_pagination_ui_v13_11.jpg | bin | 0 -> 5030 bytes | |||
-rw-r--r-- | doc/development/img/project_issues_pagination_v13_11.jpg | bin | 0 -> 51436 bytes | |||
-rw-r--r-- | doc/development/merge_request_performance_guidelines.md | 2 |
12 files changed, 697 insertions, 44 deletions
diff --git a/doc/api/graphql/reference/index.md b/doc/api/graphql/reference/index.md index 28f91c7139e..d9d6c6d5fe7 100644 --- a/doc/api/graphql/reference/index.md +++ b/doc/api/graphql/reference/index.md @@ -11790,6 +11790,7 @@ Returns [`Tree`](#tree). | <a id="repositoryblobname"></a>`name` | [`String`](#string) | Blob name. | | <a id="repositorybloboid"></a>`oid` | [`String!`](#string) | OID of the blob. | | <a id="repositoryblobpath"></a>`path` | [`String!`](#string) | Path of the blob. | +| <a id="repositoryblobplaindata"></a>`plainData` | [`String`](#string) | Blob plain highlighted data. | | <a id="repositoryblobrawblob"></a>`rawBlob` | [`String`](#string) | The raw content of the blob. | | <a id="repositoryblobrawpath"></a>`rawPath` | [`String`](#string) | Web path to download the raw blob. | | <a id="repositoryblobrawsize"></a>`rawSize` | [`Int`](#int) | Size (in bytes) of the blob, or the blob target if stored externally. | diff --git a/doc/ci/unit_test_reports.md b/doc/ci/unit_test_reports.md index 8980d32cc99..c71d455670c 100644 --- a/doc/ci/unit_test_reports.md +++ b/doc/ci/unit_test_reports.md @@ -333,32 +333,17 @@ If parsing JUnit report XML results in an error, an indicator is shown next to t ## Viewing JUnit screenshots on GitLab -> - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/202114) in GitLab 13.0. -> - It's deployed behind a feature flag, disabled by default. -> - To use it in GitLab self-managed instances, ask a GitLab administrator to [enable it](#enabling-the-junit-screenshots-feature). **(FREE SELF)** - -WARNING: -This feature might not be available to you. Check the **version history** note above for details. - -When [this issue](https://gitlab.com/gitlab-org/gitlab/-/issues/6061) is complete, the attached file is visible on the pipeline details page. +> - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/202114) in GitLab 13.0 behind the `:junit_pipeline_screenshots_view` feature flag, disabled by default. +> - The feature flag was removed and was [made generally available](https://gitlab.com/gitlab-org/gitlab/-/issues/216979) in GitLab 13.12. If JUnit report format XML files contain an `attachment` tag, GitLab parses the attachment. -Upload your screenshots as [artifacts](yaml/README.md#artifactsreportsjunit) to GitLab. The `attachment` tag **must** contain the absolute path to the screenshots you uploaded. - ```xml <testcase time="1.00" name="Test"> <system-out>[[ATTACHMENT|/absolute/path/to/some/file]]</system-out> </testcase> ``` -### Enabling the JUnit screenshots feature **(FREE SELF)** - -This feature comes with the `:junit_pipeline_screenshots_view` feature flag disabled by default. - -To enable this feature, ask a GitLab administrator with [Rails console access](../administration/feature_flags.md#how-to-enable-and-disable-features-behind-flags) to run the -following command: +Upload your screenshots as [artifacts](yaml/README.md#artifactsreportsjunit) to GitLab. The `attachment` tag **must** contain the absolute path to the screenshots you uploaded. -```ruby -Feature.enable(:junit_pipeline_screenshots_view) -``` +A link to the test case attachment will appear in the test case details in [the pipeline test report](#viewing-unit-test-reports-on-gitlab). diff --git a/doc/ci/yaml/README.md b/doc/ci/yaml/README.md index 23cdce8187b..858e738e750 100644 --- a/doc/ci/yaml/README.md +++ b/doc/ci/yaml/README.md @@ -2970,11 +2970,7 @@ You can specify a [fallback cache key](#fallback-cache-key) to use if the specif ##### Multiple caches > - [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/32814) in GitLab 13.10. -> - [Deployed behind a feature flag](../../user/feature_flags.md), disabled by default. -> - [Enabled by default](https://gitlab.com/gitlab-org/gitlab/-/issues/321877) in GitLab 13.11. -> - Enabled on GitLab.com. -> - Recommended for production use. -> - For GitLab self-managed instances, GitLab administrators can opt to [disable it](#enable-or-disable-multiple-caches). **(FREE SELF)** +> - [Feature Flag removed](https://gitlab.com/gitlab-org/gitlab/-/issues/321877), in GitLab 13.12. You can have a maximum of four caches: @@ -3001,25 +2997,6 @@ test-job: If multiple caches are combined with a [Fallback cache key](#fallback-cache-key), the fallback is fetched multiple times if multiple caches are not found. -##### Enable or disable multiple caches **(FREE SELF)** - -The multiple caches feature is under development but ready for production use. -It is deployed behind a feature flag that is **enabled by default**. -[GitLab administrators with access to the GitLab Rails console](../../administration/feature_flags.md) -can opt to disable it. - -To enable it: - -```ruby -Feature.enable(:multiple_cache_per_job) -``` - -To disable it: - -```ruby -Feature.disable(:multiple_cache_per_job) -``` - #### Fallback cache key > [Introduced](https://gitlab.com/gitlab-org/gitlab-runner/-/merge_requests/1534) in GitLab Runner 13.4. diff --git a/doc/development/database/index.md b/doc/development/database/index.md index 01f6753e7a0..b61a71ffb8e 100644 --- a/doc/development/database/index.md +++ b/doc/development/database/index.md @@ -60,6 +60,8 @@ info: To determine the technical writer assigned to the Stage/Group associated w - [Updating multiple values](setting_multiple_values.md) - [Constraints naming conventions](constraint_naming_convention.md) - [Query performance guidelines](../query_performance.md) +- [Pagination guidelines](pagination_guidelines.md) + - [Pagination performance guidelines](pagination_performance_guidelines.md) ## Case studies diff --git a/doc/development/database/pagination_guidelines.md b/doc/development/database/pagination_guidelines.md new file mode 100644 index 00000000000..aa3915cd4b6 --- /dev/null +++ b/doc/development/database/pagination_guidelines.md @@ -0,0 +1,315 @@ +--- +stage: Enablement +group: Database +info: 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 +--- + +# Pagination guidelines + +This document gives an overview of the current capabilities and provides best practices for paginating over data in GitLab, and in particular for PostgreSQL. + +## Why do we need pagination? + +Pagination is a popular technique to avoid loading too much data in one web request. This usually happens when we render a list of records. A common scenario is visualizing parent-children relations (has many) on the UI. + +Example: listing issues within a project + +As the number of issues grows within the project, the list gets longer. To render the list, the backend does the following: + +1. Loads the records from the database, usually in a particular order. +1. Serializes the records in Ruby. Build Ruby (ActiveRecord) objects and then build a JSON or HTML string. +1. Sends the response back to the browser. +1. The browser renders the content. + +We have two options for rendering the content: + +- HTML: backend deals with the rendering (HAML template). +- JSON: the client (client-side JavaScript) transforms the payload into HTML. + +Rendering long lists can significantly affect both the frontend and backend performance: + +- The database will need to read a lot of data from the disk. +- The result of the query (records) will eventually be transformed to Ruby objects which increases memory allocation. +- Large responses will take more time to send over the wire, to the user's browser. +- Rendering long lists might freeze the browser (bad user experience). + +With pagination, the data is split into equal pieces (pages). On the first visit, the user receives only a limited number of items (page size). The user can see more items by paginating forward which results in a new HTTP request and a new database query. + +![Project issues page with pagination](../img/project_issues_pagination_v13_11.jpg) + +## General guidelines for paginating + +### Pick the right approach + +Let the database handle the pagination, filtering, and data retrieval. Implementing in-memory pagination on the backend (`paginate_array` from kaminari) or on the frontend (JavaScript) might work for a few hundreds of records. If application limits are not defined, things can get out of control quickly. + +### Reduce complexity + +When we list records on the page we often provide additional filters and different sort options. This can complicate things on the backend side significantly. + +For the MVC version, consider the following: + +- Reduce the number of sort options to the minimum. +- Reduce the number of filters (dropdown, search bar) to the minimum. + +To make sorting and pagination efficient, for each sort option we need at least two database indexes (ascending, descending order). If we add filter options (by state or by author), we might need more indexes to maintain good performance. Note that indexes are not free, they can significantly affect the `UPDATE` query timings. + +It's not possible to make all filter and sort combinations performant, so we should try optimizing the performance by usage patterns. + +### Prepare for scaling + +Offset-based pagination is the easiest way to paginate over records, however, it does not scale well for large tables. As a long-term solution, keyset pagination is preferred. The tooling around keyset pagination is not as mature as for offset pagination so currently, it's easier to start with offset pagination and then switch to keyset pagination. + +To avoid losing functionality and maintaining backward compatibility when switching pagination methods, it's advised to consider the following approach in the design phase: + +- Avoid presenting total counts, prefer limit counts. + - Example: count maximum 1001 records, and then on the UI show 1000+ if the count is 1001, show the actual number otherwise. + - See the [badge counters approach](../merge_request_performance_guidelines.md#badge-counters) for more information. +- Avoid using page numbers, use next and previous page buttons. + - Keyset pagination doesn't support page numbers. +- For APIs, advise against building URLs for the next page by "hand". + - Promote the usage of the [`Link` header](../../api/README.md#pagination-link-header) where the URLs for the next and previous page are provided by the backend. + - This way changing the URL structure is possible without breaking backward compatibility. + +NOTE: +Infinite scroll can use keyset pagination without affecting the user experience since there are no exposed page numbers. + +## Options for pagination + +### Offset pagination + +The most common way to paginate lists is using offset-based pagination (UI and REST API). It's backed by the popular [kaminari](https://github.com/kaminari/kaminari) Ruby gem, which provides convenient helper methods to implement pagination on ActiveRecord queries. + +Offset-based pagination is leveraging the `LIMIT` and `OFFSET` SQL clauses to take out a specific slice from the table. + +Example database query when looking for the 2nd page of the issues within our project: + +```sql +SELECT issues.* FROM issues WHERE project_id = 1 ORDER BY id LIMIT 20 OFFSET 20 +``` + +1. Move an imaginary pointer over the table rows and skip 20 rows. +1. Take the next 20 rows. + +Notice that the query also orders the rows by the primary key (`id`). When paginating data, specifying the order is very important. Without it, the returned rows are non-deterministic and can confuse the end-user. + +#### Page numbers + +Example pagination bar: + +![Page selector rendered by kaminari](../img/offset_pagination_ui_v13_11.jpg) + +The kaminari gem renders a nice pagination bar on the UI with page numbers and optionally quick shortcuts the next, previous, first, and last page buttons. To render these buttons, kaminari needs to know the number of rows, and for that, a count query is executed. + +```sql +SELECT COUNT(*) FROM issues WHERE project_id = 1 +``` + +#### Performance + +##### Index coverage + +To achieve the good performance, the `ORDER BY` clause needs to be covered by an index. + +Assuming that we have the following index: + +```sql +CREATE INDEX index_on_issues_project_id ON issues (project_id); +``` + +Let's try to request the first page: + +```sql +SELECT issues.* FROM issues WHERE project_id = 1 ORDER BY id LIMIT 20; +``` + +We can produce the same query in Rails: + +```ruby +Issue.where(project_id: 1).page(1).per(20) +``` + +The SQL query will return a maximum of 20 rows from the database. However, it doesn't mean that the database will only read 20 rows from the disk to produce the result. + +This is what will happen: + +1. The database will try to plan the execution in the most efficient way possible based on the table statistics and the available indexes. +1. The planner knows that we have an index covering the `project_id` column. +1. The database will read all rows using the index on `project_id`. +1. The rows at this point are not sorted, so the database will need to sort the rows. +1. The database returns the first 20 rows. + +In case the project has 10_000 rows, the database will read 10_000 rows and sort them in memory (or on disk). This is not going to scale well in the long term. + +To fix this we need the following index: + +```sql +CREATE INDEX index_on_issues_project_id ON issues (project_id, id); +``` + +By making the `id` column part of the index, the previous query will read maximum 20 rows. The query will perform well regardless of the number of issues within a project. So with this change, we've also improved the initial page load (when the user loads the issue page). + +NOTE: +Here we're leveraging the ordered property of the b-tree database index. Values in the index are sorted so reading 20 rows will not require further sorting. + +#### Limitations + +##### `COUNT(*)` on a large dataset + +Kaminari by default executes a count query to determine the number of pages for rendering the page links. Count queries can be quite expensive for a large table, in an unfortunate scenario the queries will simply time out. + +To work around this, we can run kaminari without invoking the count SQL query. + +```ruby +Issue.where(project_id: 1).page(1).per(20).without_count +``` + +In this case, the count query will not be executed and the pagination will no longer render the page numbers. We'll see only the next and previous links. + +##### `OFFSET` on a large dataset + +When we paginate over a large dataset, we might notice that the response time will get slower and slower. This is due to the `OFFSET` clause that seeks through the rows and skips N rows. + +From the user point of view, this might not be always noticeable. As the user paginates forward, the previous rows might be still in the buffer cache of the database. If the user shares the link with someone else and it's opened after a few minutes or hours, the response time might be significantly higher or it would even time out. + +When requesting a large page number, the database needs to read `PAGE * PAGE_SIZE` rows. This makes offset pagination **unsuitable for large database tables**. + +Example: listing users on the Admin page + +Listing users with a very simple SQL query: + +```sql +SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 20 OFFSET 0 +``` + +The query execution plan shows that this query is efficient, the database only read 20 rows from the database (`rows=20`): + +```plaintext + Limit (cost=0.43..3.19 rows=20 width=1309) (actual time=0.098..2.093 rows=20 loops=1) + Buffers: shared hit=103 + -> Index Scan Backward using users_pkey on users (cost=0.43..X rows=X width=1309) (actual time=0.097..2.087 rows=20 loops=1) + Buffers: shared hit=103 + Planning Time: 0.333 ms + Execution Time: 2.145 ms +(6 rows) +``` + +See the [Understanding EXPLAIN plans](../understanding_explain_plans.md) to find more information about reading execution plans. + +Let's visit the 50_000th page: + +```sql +SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT 20 OFFSET 999980; +``` + +The plan shows that the database reads 1_000_000 rows to return 20 rows, with a very high execution time (5.5 seconds): + +```plaintext +Limit (cost=137878.89..137881.65 rows=20 width=1309) (actual time=5523.588..5523.667 rows=20 loops=1) + Buffers: shared hit=1007901 read=14774 written=609 + I/O Timings: read=420.591 write=57.344 + -> Index Scan Backward using users_pkey on users (cost=0.43..X rows=X width=1309) (actual time=0.060..5459.353 rows=1000000 loops=1) + Buffers: shared hit=1007901 read=14774 written=609 + I/O Timings: read=420.591 write=57.344 + Planning Time: 0.821 ms + Execution Time: 5523.745 ms +(8 rows) +``` + +We can argue that a normal user will not be going to visit these pages, however, API users could easily navigate to very high page numbers (scraping, collecting data). + +### Keyset pagination + +Keyset pagination addresses the performance concerns of "skipping" previous rows when requesting a large page, however, it's not a drop-in replacement for offset-based pagination. Keyset pagination is used only in the [GraphQL API](../graphql_guide/pagination.md) + +Consider the following `issues` table: + +|`id`|`project_id`| +|-|-| +|1|1| +|2|1| +|3|2| +|4|1| +|5|1| +|6|2| +|7|2| +|8|1| +|9|1| +|10|2| + +Let's paginate over the whole table ordered by the primary key (`id`). The query for the first page is the same as the offset pagination query, for simplicity, we use 5 as the page size: + +```sql +SELECT "issues".* FROM "issues" ORDER BY "issues"."id" ASC LIMIT 5 +``` + +Notice that we didn't add the `OFFSET` clause. + +To get to the next page, we need to extract values that are part of the `ORDER BY` clause from the last row. In this case, we just need the `id`, which is 5. Now we construct the query for the next page: + +```sql +SELECT "issues".* FROM "issues" WHERE "issues"."id" > 5 ORDER BY "issues"."id" ASC LIMIT 5 +``` + +Looking at the query execution plan, we can see that this query read only 5 rows (offset-based pagination would read 10 rows): + +```plaintext + Limit (cost=0.56..2.08 rows=5 width=1301) (actual time=0.093..0.137 rows=5 loops=1) + -> Index Scan using issues_pkey on issues (cost=0.56..X rows=X width=1301) (actual time=0.092..0.136 rows=5 loops=1) + Index Cond: (id > 5) + Planning Time: 7.710 ms + Execution Time: 0.224 ms +(5 rows) +``` + +#### Limitations + +##### No page numbers + +Offset pagination provides an easy way to request a specific page. We can simply edit the URL and modify the `page=` URL parameter. Keyset pagination cannot provide page numbers because the paging logic might depend on different columns. + +In the previous example, the column is the `id`, so we might see something like this in the `URL`: + +```plaintext +id_after=5 +``` + +In GraphQL, the parameters are serialized to JSON and then encoded: + +```plaintext +eyJpZCI6Ijk0NzMzNTk0IiwidXBkYXRlZF9hdCI6IjIwMjEtMDQtMDkgMDg6NTA6MDUuODA1ODg0MDAwIFVUQyJ9 +``` + +NOTE: +Pagination parameters will be visible to the user, so we need to be careful about which columns we order by. + +Keyset pagination can only provide the next, previous, first, and last pages. + +##### Complexity + +Building queries when we order by a single column is very easy, however, things get more complex if tie-breaker or multi-column ordering is used. The complexity increases if the columns are nullable. + +Example: ordering by `id` and `created_at` where `created_at` is nullable, query for getting the second page: + +```sql +SELECT "issues".* +FROM "issues" +WHERE (("issues"."id" > 99 + AND "issues"."created_at" = '2021-02-16 11:26:17.408466') + OR ("issues"."created_at" > '2021-02-16 11:26:17.408466') + OR ("issues"."created_at" IS NULL)) +ORDER BY "issues"."created_at" DESC NULLS LAST, "issues"."id" DESC +LIMIT 20 +``` + +##### Tooling + +Using keyset pagination outside of GraphQL is not straightforward. We have the low-level blocks for building keyset pagination database queries, however, the usage in application code is still not streamlined yet. + +#### Performance + +Keyset pagination provides stable performance regardless of the number of pages we moved forward. To achieve this performance, the paginated query needs an index that covers all the columns in the `ORDER BY` clause, similarly to the offset pagination. + +### General performance guidelines + +See the [pagination general performance guidelines page](pagination_performance_guidelines.md). diff --git a/doc/development/database/pagination_performance_guidelines.md b/doc/development/database/pagination_performance_guidelines.md new file mode 100644 index 00000000000..ade1e853027 --- /dev/null +++ b/doc/development/database/pagination_performance_guidelines.md @@ -0,0 +1,325 @@ +--- +stage: Enablement +group: Database +info: 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 +--- + +# Pagination performance guidelines + +The following document gives a few ideas for improving the pagination (sorting) performance. These apply both on [offset](pagination_guidelines.md#offset-pagination) and [keyset](pagination_guidelines.md#keyset-pagination) paginations. + +## Tie-breaker column + +When ordering the columns it's advised to order by distinct columns only. Consider the following example: + +|`id`|`created_at`| +|-|-| +|1|2021-01-04 14:13:43| +|2|2021-01-05 19:03:12| +|3|2021-01-05 19:03:12| + +If we order by `created_at`, the result would likely depend on how the records are located on the disk. + +Using the tie-breaker column is advised when the data is exposed via a well defined interface and its consumed +by an automated process, such as an API. Without the tie-breaker column, the order of the rows could change +(data is re-imported) which could cause problems that are hard to debug, such as: + +- An integration comparing the rows to determine changes breaks. +- E-tag cache values change, which requires a complete re-download. + +```sql +SELECT issues.* FROM issues ORDER BY created_at; +``` + +We can fix this by adding a second column to `ORDER BY`: + +```sql +SELECT issues.* FROM issues ORDER BY created_at, id; +``` + +This change makes the order distinct so we have "stable" sorting. + +NOTE: +To make the query efficient, we need an index covering both columns: `(created_at, id)`. The order of the columns **should match** the columns in the `ORDER BY` clause. + +## Ordering by joined table column + +Oftentimes, we want to order the data by a column on a joined database table. The following example orders `issues` records by the `first_mentioned_in_commit_at` metric column: + +```sql +SELECT issues.* FROM issues +INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id +WHERE issues.project_id = 2 +ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issues.id DESC +LIMIT 20 +OFFSET 0 +``` + +With PostgreSQL version 11, the planner will first look up all issues matching the `project_id` filter and then join all `issue_metrics` rows. The ordering of rows will happen in memory. In case the joined relation is always present (1:1 relationship), the database will read `N * 2` rows where N is the number of rows matching the `project_id` filter. + +For performance reasons, we should avoid mixing columns from different tables when specifying the `ORDER BY` clause. + +In this particular case there is no simple way (like index creation) to improve the query. We might think that changing the `issues.id` column to `issue_metrics.issue_id` will help, however, this will likely make the query perform worse because it might force the database to process all rows in the `issue_metrics` table. + +One idea to address this problem is denormalization. Adding the `project_id` column to the `issue_metrics` table will make the filtering and sorting efficient: + +```sql +SELECT issues.* FROM issues +INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id +WHERE issue_metrics.project_id = 2 +ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issue_metrics.issue_id DESC +LIMIT 20 +OFFSET 0 +``` + +NOTE: +The query will require an index on `issue_metrics` table with the following column configuration: `(project_id, first_mentioned_in_commit_at DESC, issue_id DESC)`. + +## Filtering + +### By project + +Filtering by a project is a very common use case since we have many features on the project level. Examples: merge requests, issues, boards, iterations. + +These features will have a filter on `project_id` in their base query. Loading issues for a project: + +```ruby +project = Project.find(5) + +# order by internal id +issues = project.issues.order(:iid).page(1).per(20) +``` + +To make the base query efficient, there is usually a database index covering the `project_id` column. This significantly reduces the number of rows the database needs to scan. Without the index, the whole `issues` table would be read (full table scan) by the database. + +Since `project_id` is a foreign key, we might have the following index available: + +```sql +"index_issues_on_project_id" btree (project_id) +``` + +GitLab 13.11 has the following index definition on the `issues` table: + +```sql +"index_issues_on_project_id_and_iid" UNIQUE, btree (project_id, iid) +``` + +This index fully covers the database query and the pagination. + +### By group + +Unfortunately, there is no efficient way to sort and paginate on the group level. The database query execution time will increase based on the number of records in the group. + +Things get worse when group level actually means group and its subgroups. To load the first page, the database needs to look up the group hierarchy, find all projects and then look up all issues. + +The main reason behind the inefficient queries on the group level is the way our database schema is designed; our core domain models are associated with a project, and projects are associated with groups. This doesn't mean that the database structure is bad, it's just in a well-normalized form that is not optimized for efficient group level queries. We might need to look into denormalization in the long term. + +Example: List issues in a group + +```ruby +group = Group.find(9970) + +Issue.where(project_id: group.projects).order(:iid).page(1).per(20) +``` + +The generated SQL query: + +```sql +SELECT "issues".* +FROM "issues" +WHERE "issues"."project_id" IN + (SELECT "projects"."id" + FROM "projects" + WHERE "projects"."namespace_id" = 5) +ORDER BY "issues"."iid" ASC +LIMIT 20 +OFFSET 0 +``` + +The execution plan shows that we read significantly more rows than requested (20), and the rows are sorted in memory: + +```plaintext + Limit (cost=10716.87..10716.92 rows=20 width=1300) (actual time=1472.305..1472.308 rows=20 loops=1) + -> Sort (cost=10716.87..10717.03 rows=61 width=1300) (actual time=1472.303..1472.305 rows=20 loops=1) + Sort Key: issues.iid + Sort Method: top-N heapsort Memory: 41kB + -> Nested Loop (cost=1.00..10715.25 rows=61 width=1300) (actual time=0.215..1331.647 rows=177267 loops=1) + -> Index Only Scan using index_projects_on_namespace_id_and_id on projects (cost=0.44..3.77 rows=19 width=4) (actual time=0.077..1.057 rows=270 loops=1) + Index Cond: (namespace_id = 9970) + Heap Fetches: 25 + -> Index Scan using index_issues_on_project_id_and_iid on issues (cost=0.56..559.28 rows=448 width=1300) (actual time=0.101..4.781 rows=657 loops=270) + Index Cond: (project_id = projects.id) + Planning Time: 12.281 ms + Execution Time: 1472.391 ms +(12 rows) +``` + +#### Columns in the same database table + +Filtering by columns located in the same database table can be improved with an index. In case we want to support filtering by the `state_id` column, we can add the following index: + +```sql +"index_issues_on_project_id_and_state_id_and_iid" UNIQUE, btree (project_id, state_id, iid) +``` + +Example query in Rails: + +```ruby +project = Project.find(5) + +# order by internal id +issues = project.issues.opened.order(:iid).page(1).per(20) +``` + +SQL query: + +```sql +SELECT "issues".* +FROM "issues" +WHERE + "issues"."project_id" = 5 + AND ("issues"."state_id" IN (1)) +ORDER BY "issues"."iid" ASC +LIMIT 20 +OFFSET 0 +``` + +Keep in mind that the index above will not support the following project level query: + +```sql +SELECT "issues".* +FROM "issues" +WHERE "issues"."project_id" = 5 +ORDER BY "issues"."iid" ASC +LIMIT 20 +OFFSET 0 +``` + +#### Special case: confidential flag + +In the `issues` table, we have a boolean field (`confidential`) that marks an issue confidential. This makes the issue invisible (filtered out) for non-member users. + +Example SQL query: + +```sql +SELECT "issues".* +FROM "issues" +WHERE "issues"."project_id" = 5 +AND "issues"."confidential" = FALSE +ORDER BY "issues"."iid" ASC +LIMIT 20 +OFFSET 0 +``` + +We might be tempted to add an index on `project_id`, `confidential`, and `iid` to improve the database query, however, in this case it's probably unnecessary. Based on the data distribution in the table, confidential issues are rare. Filtering them out does not make the database query significantly slower. The database might read a few extra rows, the performance difference might not even be visible to the end-user. + +On the other hand, if we would implement a special filter where we only show confidential issues, we will surely need the index. Finding 20 confidential issues might require the database to scan hundreds of rows or in the worst case, all issues in the project. + +NOTE: +Be aware of the data distribution and the table access patterns (how features work) when introducing a new database index. Sampling production data might be necessary to make the right decision. + +#### Columns in a different database table + +Example: filtering issues in a project by an assignee + +```ruby +project = Project.find(5) + +project + .issues + .joins(:issue_assignees) + .where(issue_assignees: { user_id: 10 }) + .order(:iid) + .page(1) + .per(20) +``` + +```sql +SELECT "issues".* +FROM "issues" +INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id" +WHERE "issues"."project_id" = 5 + AND "issue_assignees"."user_id" = 10 +ORDER BY "issues"."iid" ASC +LIMIT 20 +OFFSET 0 +``` + +Example database (oversimplified) execution plan: + +1. The database parses the SQL query and detects the `JOIN`. +1. The database splits the query into two subqueries. + - `SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."user_id" = 10` + - `SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 5` +1. The database estimates the number of rows and the costs to run these queries. +1. The database executes the cheapest query first. +1. Using the query result, load the rows from the other table (from the other query) using the JOIN column and filter the rows further. + +In this particular example, the `issue_assignees` query would likely be executed first. + +Running the query in production for the GitLab project produces the following execution plan: + +```plaintext + Limit (cost=411.20..411.21 rows=1 width=1300) (actual time=24.071..24.077 rows=20 loops=1) + -> Sort (cost=411.20..411.21 rows=1 width=1300) (actual time=24.070..24.073 rows=20 loops=1) + Sort Key: issues.iid + Sort Method: top-N heapsort Memory: 91kB + -> Nested Loop (cost=1.00..411.19 rows=1 width=1300) (actual time=0.826..23.705 rows=190 loops=1) + -> Index Scan using index_issue_assignees_on_user_id on issue_assignees (cost=0.44..81.37 rows=92 width=4) (actual time=0.741..13.202 rows=215 loops=1) + Index Cond: (user_id = 4156052) + -> Index Scan using issues_pkey on issues (cost=0.56..3.58 rows=1 width=1300) (actual time=0.048..0.048 rows=1 loops=215) + Index Cond: (id = issue_assignees.issue_id) + Filter: (project_id = 278964) + Rows Removed by Filter: 0 + Planning Time: 1.141 ms + Execution Time: 24.170 ms +(13 rows) +``` + +The query looks up the `assignees` first, filtered by the `user_id` (`user_id = 4156052`) and it finds 215 rows. Using that 215 rows, the database will look up the 215 associated issue rows by the primary key. Notice that the filter on the `project_id` column is not backed by an index. + +In most cases, we are lucky that the joined relation will not be going to return too many rows, therefore, we will end up with a relatively efficient database query that accesses low number of rows. As the database grows, these queries might start to behave differently. Let's say the number `issue_assignees` records for a particular user is very high (millions), then this join query will not perform well, and it will likely time out. + +A similar problem could be a double join, where the filter exists in the 2nd JOIN query. Example: `Issue -> LabelLink -> Label(name=bug)`. + +There is no easy way to fix these problems. Denormalization of data could help significantly, however, it has also negative effects (data duplication and keeping the data up to date). + +Ideas for improving the `issue_assignees` filter: + +- Add `project_id` column to the `issue_assignees` table so when JOIN-ing, the extra `project_id` filter will further filter the rows. The sorting will likely happen in memory: + + ```sql + SELECT "issues".* + FROM "issues" + INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id" + WHERE "issues"."project_id" = 5 + AND "issue_assignees"."user_id" = 10 + AND "issue_assignees"."project_id" = 5 + ORDER BY "issues"."iid" ASC + LIMIT 20 + OFFSET 0 + ``` + +- Add the `iid` column to the `issue_assignees` table. Notice that the `ORDER BY` column is different and the `project_id` filter is gone from the `issues` table: + + ```sql + SELECT "issues".* + FROM "issues" + INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id" + WHERE "issue_assignees"."user_id" = 10 + AND "issue_assignees"."project_id" = 5 + ORDER BY "issue_assignees"."iid" ASC + LIMIT 20 + OFFSET 0 + ``` + +The query now performs well for any number of `issue_assignees` records, however, we pay a very high price for it: + +- Two columns are duplicated which increases the database size. +- We need to keep the two columns in sync. +- We need more indexes on the `issue_assignees` table to support the query. +- The new database query is very specific to the assignee search and needs complex backend code to build it. + - If the assignee is filtered by the user, then order by a different column, remove the `project_id` filter, etc. + +NOTE: +Currently we're not doing these kinds of denormalization at GitLab. diff --git a/doc/development/elasticsearch.md b/doc/development/elasticsearch.md index b2f5c11a28b..3e466512c79 100644 --- a/doc/development/elasticsearch.md +++ b/doc/development/elasticsearch.md @@ -285,7 +285,7 @@ defer it to another release if there is risk of important data loss. Follow these best practices for best results: - When working in batches, keep the batch size under 9,000 documents - and `throttle_delay` over 3 minutes. The bulk indexer is set to run + and `throttle_delay` for at least 3 minutes. The bulk indexer is set to run every 1 minute and process a batch of 10,000 documents. These limits allow the bulk indexer time to process records before another migration batch is attempted. diff --git a/doc/development/graphql_guide/pagination.md b/doc/development/graphql_guide/pagination.md index 55ff7942418..5db9238faed 100644 --- a/doc/development/graphql_guide/pagination.md +++ b/doc/development/graphql_guide/pagination.md @@ -12,6 +12,10 @@ GitLab uses two primary types of pagination: **offset** and **keyset** (sometimes called cursor-based) pagination. The GraphQL API mainly uses keyset pagination, falling back to offset pagination when needed. +### Performance considerations + +See the [general pagination guidelines section](../database/pagination_guidelines.md) for more information. + ### Offset pagination This is the traditional, page-by-page pagination, that is most common, diff --git a/doc/development/i18n/externalization.md b/doc/development/i18n/externalization.md index a9eb2f4ca9a..f3d09903108 100644 --- a/doc/development/i18n/externalization.md +++ b/doc/development/i18n/externalization.md @@ -491,6 +491,48 @@ To avoid this error, use the applicable HTML entity code (`<` or `>`) inst // => 'In < 1 hour' ``` +### Numbers + +Different locales may use different number formats. To support localization of numbers, we use `formatNumber`, +which leverages [`toLocaleString()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/toLocaleString). + +`formatNumber` formats numbers as strings using the current user locale by default. + +- In JavaScript + +```javascript +import { formatNumber } from '~/locale'; + +// Assuming "User Preferences > Language" is set to "English": + +const tenThousand = formatNumber(10000); // "10,000" (uses comma as decimal symbol in English locale) +const fiftyPercent = formatNumber(0.5, { style: 'percent' }) // "50%" (other options are passed to toLocaleString) +``` + +- In Vue templates + +```html +<script> +import { formatNumber } from '~/locale'; + +export default { + //... + methods: { + // ... + formatNumber, + }, +} +</script> +<template> +<div class="my-number"> + {{ formatNumber(10000) }} <!-- 10,000 --> +</div> +<div class="my-percent"> + {{ formatNumber(0.5, { style: 'percent' }) }} <!-- 50% --> +</div> +</template> +``` + ### Dates / times - In JavaScript: diff --git a/doc/development/img/offset_pagination_ui_v13_11.jpg b/doc/development/img/offset_pagination_ui_v13_11.jpg Binary files differnew file mode 100644 index 00000000000..d17acc20dcb --- /dev/null +++ b/doc/development/img/offset_pagination_ui_v13_11.jpg diff --git a/doc/development/img/project_issues_pagination_v13_11.jpg b/doc/development/img/project_issues_pagination_v13_11.jpg Binary files differnew file mode 100644 index 00000000000..3f3c268cd16 --- /dev/null +++ b/doc/development/img/project_issues_pagination_v13_11.jpg diff --git a/doc/development/merge_request_performance_guidelines.md b/doc/development/merge_request_performance_guidelines.md index 6ce372ebc0d..543ca809f45 100644 --- a/doc/development/merge_request_performance_guidelines.md +++ b/doc/development/merge_request_performance_guidelines.md @@ -426,6 +426,8 @@ Take into consideration the following when choosing a pagination strategy: The database has to sort and iterate all previous items, and this operation usually can result in substantial load put on database. +You can find useful tips related to pagination in the [pagination guidelines](database/pagination_guidelines.md). + ## Badge counters Counters should always be truncated. It means that we don't want to present |