In the past couple of weeks, we have had operational issues related to DB performance. We are asking for volunteers to help 2021-03-18+ to research possible other DB query vectors. This epic is to track the process, what queries have already been researched, and to help refine the process of researching and creating these query investigations. ### How you can help There are many ways you can help, this is just a list of suggestions. - Review the issues in this epic - Select an unassigned issue and dig in. - **If there is not enough information on the chosen issue, please read the details of [this MR](https://gitlab.com/gitlab-com/runbooks/-/merge_requests/3361/diffs).** It provides instructions on how to search for the source of the query calls. - Pair up on an already selected issue - Reach out on the [#volunteer-db-performance-peak](https://gitlab.slack.com/archives/C01S42PL5J5) channel to see if anyone needs any help - Create a new issue for research from the [supporting data](https://gitlab.com/groups/gitlab-org/-/epics/5652#supporting-data) section below - If you create a new issue add it to the spreadsheet so we don't end up with duplicates - Don't let this list of suggestions be a limiting factor. If you know of an area of improvement please add a new issue, link it to this epic and prioritize the work! ### Examples of recent fixes we have made - https://gitlab.com/gitlab-org/gitlab/-/issues/325133 - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/56509 - https://gitlab.com/gitlab-org/gitlab/-/merge_requests/50318 ### Steps to create the issues - **All issues in the top N reports have been reviewed** - If you know of a database call that is too frequent, too slow, pulls too much data then create an issue and add it to this epic. ### Possible outcomes - Reduced frequency of calls. Many of these queries are listed as top 10 total calls. Reducing the number of calls will help to reduce the load on our database servers. - Improved query times. Many of these queries are listed as the top 10 in total time taken during the monitoring period. - Read-only queries are moved off of the primary. We are seeing increasing load on the primary database. We can reduce this load by moving read-only queries to replicas. - A new method was introduced with this MR https://gitlab.com/gitlab-org/gitlab/-/merge_requests/56849/diffs that should prevent switching to primary if write is executed inside the block passed to `without_sticky_writes`. - Lazy loading of data, rather than pre-loading everything - No improvements can be made. In this case update the issues with the research taken so that we have a historical artifact on what was done. ### Supporting data - Database Performance Peak Analysis - Google Sheets - https://docs.google.com/spreadsheets/d/1fWaLzNyVgqheVFOoRMaAEJ_n2MUJB-1IdIRmUmdtUqI/edit#gid=1125583010 - Database performance peak - 01 of March - 14:47 (#12723) - https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12723 - Postgres_checkup report (Top 15 queries) https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2090600#k003-top-15-queries-by-total_time ### Volunteering during friend and Family day If you are willing to trade your friends a family day for a different day and willing to volunteer we will gladly accept help. Please join the #volunteer-DB-performance-peak and let us know you are working this. Also add to the put a comment in this issue.