Priority - identify and address underperforming SQL queries
In the light of recent database performance issues we have created an epic to address the queries that are either top 10 in time taken or top 10 in the frequency of calls during the monitoring period - Volunteer Research Database Performance Peak queries and create issues (&5652)
Now we are asking teams to review their backlogs (and possibly tribal knowledge) to proactively prioritize and address known query performance issues that are not captured within this epic. This is an urgent request to help reduce the load on our database.
If there are no queries that immediately come to mind for your teams, please take some time to brainstorm some areas of improved efficiencies and reducing database pressure on our primary. What to look for?
- Known n+1 issues
- Read-only queries that can be redirected to read-only replicas
- Opportunities for caching data on frequent reads
- Lazy loading data (example Lazily load pipeline artifact in project pipelines index page (#324003))
- Removing or not exact caching costly but not super needed information totally or through a feature flag (so we only turn it off for .com for the time being). See comment below.
-
Unknown unknowns: please think creatively about ways to find and optimize queries, or otherwise relieve DB load!
- Use Elasticsearch with PostgreSQL slow logs to collect more information when you have suspicious queries.
- Look at this dashboard for endpoints that have a high number of SQL calls
To Do
- Create issues for each query optimization and add to the Volunteer Research Database Performance Peak queries and create issues epic.
- Prioritize these efforts as severity2 and ~availability using the Availability Severity SLO timeline of resolution within 7 days
Instructions
- Please follow the guidance described above.
- Create an issue for your team, here are examples - https://gitlab.com/gitlab-com/create-stage/create-engineering-managers/-/issues/131, https://gitlab.com/gitlab-com/create-stage/create-engineering-managers/-/issues/130
- Add your team's issue to the table below under
Team Audit Issue Link
Edited by Fabien Catteau