PostgreSQL maintenance_work_mem needs to be tuned for larger workloads (#8377) 路 Issues 路 GitLab.org / omnibus-gitlab 路 GitLab

<!DOCTYPE html> <html class="gl-light ui-neutral with-top-bar with-header " lang="en"> <head prefix="og: http://ogp.me/ns#"> <meta charset="utf-8"> <meta content="IE=edge" http-equiv="X-UA-Compatible"> <meta content="width=device-width, initial-scale=1" name="viewport"> <title>PostgreSQL maintenance_work_mem needs to be tuned for larger workloads (#8377) 路 Issues 路 GitLab.org / omnibus-gitlab 路 GitLab</title> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ window.gon={};gon.math_rendering_limits_enabled=true;gon.features={"preserveMarkdown":false,"issuesGridView":false,"serviceDeskTicket":false,"issuesListDrawer":false,"notificationsTodosButtons":false,"glqlIntegration":true,"glqlLoadOnClick":false,"continueIndentedText":false,"workItemsBeta":true,"workItemsAlpha":false,"workItems":true,"epicWidgetEditConfirmation":false,"workItemsViewPreference":false,"customFieldsFeature":false,"okrsMvc":false,"workItemEpics":true};gon.licensed_features={"escalationPolicies":true,"okrs":true};gon.abilities={"summarizeComments":false,"measureCommentTemperature":false}; //]]> </script> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ var gl = window.gl || {}; gl.startup_calls = {"/gitlab-org/omnibus-gitlab/-/issues/8377/related_branches":{},"/gitlab-org/omnibus-gitlab/-/issues/8377/discussions.json?per_page=20":{},"/gitlab-org/omnibus-gitlab/-/issues/8377.json?serializer=sidebar_extras":{}}; gl.startup_graphql_calls = [{"query":"query getDesignList($fullPath: ID!, $iid: String!, $atVersion: DesignManagementVersionID) {\n project(fullPath: $fullPath) {\n __typename\n id\n issue(iid: $iid) {\n __typename\n id\n designCollection {\n __typename\n copyState\n designs(atVersion: $atVersion) {\n __typename\n nodes {\n __typename\n id\n event\n filename\n notesCount\n image\n imageV432x230\n currentUserTodos(state: pending) {\n __typename\n nodes {\n __typename\n id\n }\n }\n }\n }\n versions {\n __typename\n nodes {\n __typename\n id\n sha\n createdAt\n author {\n __typename\n id\n name\n avatarUrl\n }\n }\n }\n }\n }\n }\n}\n","variables":{"fullPath":"gitlab-org/omnibus-gitlab","iid":"8377","atVersion":null}},{"query":"query permissions($fullPath: ID!, $iid: String!) {\n project(fullPath: $fullPath) {\n __typename\n id\n issue(iid: $iid) {\n __typename\n id\n userPermissions {\n __typename\n createDesign\n }\n }\n }\n}\n","variables":{"fullPath":"gitlab-org/omnibus-gitlab","iid":"8377"}}]; if (gl.startup_calls && window.fetch) { Object.keys(gl.startup_calls).forEach(apiCall => { gl.startup_calls[apiCall] = { fetchCall: fetch(apiCall, { // Emulate XHR for Rails AJAX request checks headers: { 'X-Requested-With': 'XMLHttpRequest' }, // fetch won鈥檛 send cookies in older browsers, unless you set the credentials init option. // We set to `same-origin` which is default value in modern browsers. // See https://github.com/whatwg/fetch/pull/585 for more information. credentials: 'same-origin' }) }; }); } if (gl.startup_graphql_calls && window.fetch) { const headers = {"X-CSRF-Token":"GVSOGxzHgbmCyl4YslCr_yFQMai1-nB0ahb1zZtRgB2vbzkskj67OEW-PpM-Ls1FySE9WfrR-ykaJMzh5oy3vw","x-gitlab-feature-category":"team_planning"}; const url = `https://gitlab.com/api/graphql` const opts = { method: "POST", headers: { "Content-Type": "application/json", ...headers, } }; gl.startup_graphql_calls = gl.startup_graphql_calls.map(call => ({ ...call, fetchCall: fetch(url, { ...opts, credentials: 'same-origin', body: JSON.stringify(call) }) })) } //]]> </script> <link rel="stylesheet" href="/assets/application-72526b3bce63d6171d8ffeff22677ae22d837331d123693e14ec8294c4a604dc.css" /> <link rel="stylesheet" href="/assets/page_bundles/design_management-b67fb0c469f6b41e7909fd0a2e77308b626a07ce8d0cb89fde4855f4419925d3.css" /><link rel="stylesheet" href="/assets/page_bundles/incidents-3926481f8144c431239d8789c4d53071733c3fb8e3b13f10943992d3ce982fba.css" /><link rel="stylesheet" href="/assets/page_bundles/issuable-e25480d2326300d5c49ad31dbd847a53e39fc33a5fd595a961dd7238e411b558.css" /><link rel="stylesheet" href="/assets/page_bundles/notes_shared-30de79203a0836dddd3a4cf7364d63afb16a0f2deb0bbc654b00692872696739.css" /><link rel="stylesheet" href="/assets/page_bundles/issues_show-785541c300108b8ecf7c08fd02bd67f4d6b9d59c86918ecfbf91bae5e1c8ffa8.css" /><link rel="stylesheet" href="/assets/page_bundles/work_items-22a76cdd1fe2ae5431b7ff603f86212acaf81b49c4a932f19e3b3222dc1881ee.css" /><link rel="stylesheet" href="/assets/page_bundles/labels-3508460b8d6f839e9d60de1f2065fdaf7f057efab04cf71966acef6a86fe9b41.css" /><link rel="stylesheet" href="/assets/page_bundles/commit_description-1e2cba4dda3c7b30dd84924809020c569f1308dea51520fe1dd5d4ce31403195.css" /> <link rel="stylesheet" href="/assets/application_utilities-58bec0f2dc46133fc9e8548af9854688398e9d7263cc0fd95ec5739f2a069dec.css" /> <link rel="stylesheet" href="/assets/tailwind-2c747966a25e00eb44e26a03a864d65af67d7c7f70691095b4d07ad61388dd7c.css" /> <link rel="stylesheet" href="/assets/fonts-fae5d3f79948bd85f18b6513a025f863b19636e85b09a1492907eb4b1bb0557b.css" /> <link rel="stylesheet" href="/assets/highlight/themes/white-99cce4f4b362f6840d7134d4129668929fde49c4da11d6ebf17f99768adbd868.css" /> <script src="/assets/webpack/runtime.76c85550.bundle.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/main.e0189b14.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/tracker.d8ced242.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/analytics.255cc704.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ window.snowplowOptions = {"namespace":"gl","hostname":"snowplowprd.trx.gitlab.net","cookieDomain":".gitlab.com","appId":"gitlab","formTracking":true,"linkClickTracking":true} gl = window.gl || {}; gl.snowplowStandardContext = {"schema":"iglu:com.gitlab/gitlab_standard/jsonschema/1-1-1","data":{"environment":"production","source":"gitlab-rails","correlation_id":"01JMMKDJ48931N8JRJB25ZRBDN","plan":"gold","extra":{},"user_id":null,"global_user_id":null,"is_gitlab_team_member":null,"namespace_id":9970,"project_id":20699,"feature_enabled_by_namespace_ids":null,"realm":"saas","instance_id":"ea8bf810-1d6f-4a6a-b4fd-93e8cbd8b57f","host_name":"gitlab-cny-webservice-web-7d85c644c6-pg7db","instance_version":"17.10.0","context_generated_at":"2025-02-21T15:53:31.296Z"}} gl.snowplowPseudonymizedPageUrl = "https://gitlab.com/namespace9970/project20699/-/issues/8377"; gl.maskedDefaultReferrerUrl = null; gl.ga4MeasurementId = 'G-ENFH3X7M5Y'; //]]> </script> <link rel="preload" href="/assets/application_utilities-58bec0f2dc46133fc9e8548af9854688398e9d7263cc0fd95ec5739f2a069dec.css" as="style" type="text/css" nonce="IsDnOwDIG4zMZ7sAHrfd+g=="> <link rel="preload" href="/assets/application-72526b3bce63d6171d8ffeff22677ae22d837331d123693e14ec8294c4a604dc.css" as="style" type="text/css" nonce="IsDnOwDIG4zMZ7sAHrfd+g=="> <link rel="preload" href="/assets/highlight/themes/white-99cce4f4b362f6840d7134d4129668929fde49c4da11d6ebf17f99768adbd868.css" as="style" type="text/css" nonce="IsDnOwDIG4zMZ7sAHrfd+g=="> <link crossorigin="" href="https://snowplowprd.trx.gitlab.net" rel="preconnect"> <link as="font" crossorigin="" href="/assets/gitlab-sans/GitLabSans-1e0a5107ea3bbd4be93e8ad2c503467e43166cd37e4293570b490e0812ede98b.woff2" rel="preload"> <link as="font" crossorigin="" href="/assets/gitlab-sans/GitLabSans-Italic-38eaf1a569a54ab28c58b92a4a8de3afb96b6ebc250cf372003a7b38151848cc.woff2" rel="preload"> <link as="font" crossorigin="" href="/assets/gitlab-mono/GitLabMono-08d2c5e8ff8fd3d2d6ec55bc7713380f8981c35f9d2df14e12b835464d6e8f23.woff2" rel="preload"> <link as="font" crossorigin="" href="/assets/gitlab-mono/GitLabMono-Italic-38e58d8df29485a20c550da1d0111e2c2169f6dcbcf894f2cd3afbdd97bcc588.woff2" rel="preload"> <link rel="preload" href="/assets/fonts-fae5d3f79948bd85f18b6513a025f863b19636e85b09a1492907eb4b1bb0557b.css" as="style" type="text/css" nonce="IsDnOwDIG4zMZ7sAHrfd+g=="> <script src="/assets/webpack/sentry.6630f37b.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/commons-pages.groups.analytics.dashboards-pages.groups.harbor.repositories-pages.groups.iteration_ca-fae0f519.59fbfeca.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/commons-pages.groups.new-pages.import.gitlab_projects.new-pages.import.manifest.new-pages.projects.n-44c6c18e.408125a0.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/commons-pages.search.show-super_sidebar.29d39102.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/super_sidebar.8dc08bec.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/commons-pages.projects-pages.projects.activity-pages.projects.alert_management.details-pages.project-68d77824.b5342029.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <script src="/assets/webpack/pages.projects.issues.show.123f5850.chunk.js" defer="defer" nonce="UbOhqz11IVjOReGnu8SOhQ=="></script> <meta content="object" property="og:type"> <meta content="GitLab" property="og:site_name"> <meta content="PostgreSQL maintenance_work_mem needs to be tuned for larger workloads (#8377) 路 Issues 路 GitLab.org / omnibus-gitlab 路 GitLab" property="og:title"> <meta content=" Summary Proposal References Summary Certain maintenance operations like autovacuum need working memory, and by..." property="og:description"> <meta content="https://gitlab.com/uploads/-/system/project/avatar/20699/Omnibus-refresh2.png" property="og:image"> <meta content="64" property="og:image:width"> <meta content="64" property="og:image:height"> <meta content="https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/8377" property="og:url"> <meta content="summary" property="twitter:card"> <meta content="PostgreSQL maintenance_work_mem needs to be tuned for larger workloads (#8377) 路 Issues 路 GitLab.org / omnibus-gitlab 路 GitLab" property="twitter:title"> <meta content=" Summary Proposal References Summary Certain maintenance operations like autovacuum need working memory, and by..." property="twitter:description"> <meta content="https://gitlab.com/uploads/-/system/project/avatar/20699/Omnibus-refresh2.png" property="twitter:image"> <meta property="twitter:label1" content="Author"><meta property="twitter:data1" content="Ben Prescott_"> <meta name="csrf-param" content="authenticity_token" /> <meta name="csrf-token" content="IVlp0h39r4Mkpqnnm0I-ramihGBq-kRRN2Vlj9pUmviXYt7lkwSVAuPSyWwXPFgXQdOIkSXRzwxHV1yjp4mtWg" /> <meta name="csp-nonce" content="UbOhqz11IVjOReGnu8SOhQ==" /> <meta name="action-cable-url" content="/-/cable" /> <link href="/-/manifest.json" rel="manifest"> <link rel="icon" type="image/png" href="/assets/favicon-yellow-018213ceb87b472388095d0264be5b4319ef47471dacea03c83ecc233ced2fd5.png" id="favicon" data-original-href="/assets/favicon-yellow-018213ceb87b472388095d0264be5b4319ef47471dacea03c83ecc233ced2fd5.png" /> <link rel="apple-touch-icon" type="image/x-icon" href="/assets/apple-touch-icon-b049d4bc0dd9626f31db825d61880737befc7835982586d015bded10b4435460.png" /> <link href="/search/opensearch.xml" rel="search" title="Search GitLab" type="application/opensearchdescription+xml"> <meta content=" Summary Proposal References Summary Certain maintenance operations like autovacuum need working memory, and by..." name="description"> <meta content="#ececef" name="theme-color"> </head> <body class="tab-width-8 gl-browser-ie gl-platform-windows" data-group="gitlab-org" data-group-full-path="gitlab-org" data-namespace-id="9970" data-page="projects:issues:show" data-page-type-id="8377" data-project="omnibus-gitlab" data-project-full-path="gitlab-org/omnibus-gitlab" data-project-id="20699"> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ gl = window.gl || {}; gl.GfmAutoComplete = gl.GfmAutoComplete || {}; gl.GfmAutoComplete.dataSources = {"epics":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/epics","iterations":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/iterations","vulnerabilities":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/vulnerabilities","members":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/members?type=Issue\u0026type_id=8377","issues":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/issues","mergeRequests":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/merge_requests","labels":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/labels?type=Issue\u0026type_id=8377","milestones":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/milestones","commands":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/commands?type=Issue\u0026type_id=8377","snippets":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/snippets","contacts":"/gitlab-org/omnibus-gitlab/-/autocomplete_sources/contacts?type=Issue\u0026type_id=8377","wikis":null}; //]]> </script> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ gl = window.gl || {}; gl.client = {"isIe":true,"isWindows":true}; //]]> </script> <header class="header-logged-out" data-testid="navbar"> <a class="gl-sr-only gl-accessibility" href="#content-body">Skip to content</a> <div class="container-fluid"> <nav aria-label="Explore GitLab" class="header-logged-out-nav gl-flex gl-gap-3 gl-justify-between"> <div class="gl-flex gl-items-center gl-gap-1"> <span class="gl-sr-only">GitLab</span> <a title="Homepage" id="logo" class="header-logged-out-logo has-tooltip" aria-label="Homepage" data-track-label="main_navigation" data-track-action="click_gitlab_logo_link" data-track-property="navigation_top" href="/"><svg aria-hidden="true" role="img" class="tanuki-logo" width="25" height="24" viewBox="0 0 25 24" fill="none" xmlns="http://www.w3.org/2000/svg"> <path class="tanuki-shape tanuki" d="m24.507 9.5-.034-.09L21.082.562a.896.896 0 0 0-1.694.091l-2.29 7.01H7.825L5.535.653a.898.898 0 0 0-1.694-.09L.451 9.411.416 9.5a6.297 6.297 0 0 0 2.09 7.278l.012.01.03.022 5.16 3.867 2.56 1.935 1.554 1.176a1.051 1.051 0 0 0 1.268 0l1.555-1.176 2.56-1.935 5.197-3.89.014-.01A6.297 6.297 0 0 0 24.507 9.5Z" fill="#E24329"/> <path class="tanuki-shape right-cheek" d="m24.507 9.5-.034-.09a11.44 11.44 0 0 0-4.56 2.051l-7.447 5.632 4.742 3.584 5.197-3.89.014-.01A6.297 6.297 0 0 0 24.507 9.5Z" fill="#FC6D26"/> <path class="tanuki-shape chin" d="m7.707 20.677 2.56 1.935 1.555 1.176a1.051 1.051 0 0 0 1.268 0l1.555-1.176 2.56-1.935-4.743-3.584-4.755 3.584Z" fill="#FCA326"/> <path class="tanuki-shape left-cheek" d="M5.01 11.461a11.43 11.43 0 0 0-4.56-2.05L.416 9.5a6.297 6.297 0 0 0 2.09 7.278l.012.01.03.022 5.16 3.867 4.745-3.584-7.444-5.632Z" fill="#FC6D26"/> </svg> </a><a class="gl-badge badge badge-pill badge-success canary-badge" data-testid="canary_badge_link" href="https://next.gitlab.com" rel="noopener noreferrer" target="_blank"><span class="gl-badge-content">Next </span></a></div> <ul class="gl-list-none gl-p-0 gl-m-0 gl-flex gl-gap-3 gl-items-center gl-grow"> <li class="header-logged-out-nav-item header-logged-out-dropdown md:gl-hidden"> <button class="header-logged-out-toggle" data-toggle="dropdown" type="button"> <span class="gl-sr-only"> Menu </span> <svg class="s16" data-testid="hamburger-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#hamburger"></use></svg> </button> <div class="dropdown-menu"> <ul> <li> <a href="https://about.gitlab.com/why-gitlab">Why GitLab </a></li> <li> <a href="https://about.gitlab.com/pricing">Pricing </a></li> <li> <a href="https://about.gitlab.com/sales">Contact Sales </a></li> <li> <a href="/explore">Explore</a> </li> </ul> </div> </li> <li class="header-logged-out-nav-item gl-hidden md:gl-inline-block"> <a href="https://about.gitlab.com/why-gitlab">Why GitLab </a></li> <li class="header-logged-out-nav-item gl-hidden md:gl-inline-block"> <a href="https://about.gitlab.com/pricing">Pricing </a></li> <li class="header-logged-out-nav-item gl-hidden gl-inline-block"> <a href="https://about.gitlab.com/sales">Contact Sales </a></li> <li class="header-logged-out-nav-item gl-hidden md:gl-inline-block"> <a class="" href="/explore">Explore</a> </li> </ul> <ul class="gl-list-none gl-p-0 gl-m-0 gl-flex gl-gap-3 gl-items-center gl-justify-end"> <li class="header-logged-out-nav-item"> <a href="/users/sign_in?redirect_to_referer=yes">Sign in</a> </li> <li class="header-logged-out-nav-item"> <a class="gl-button btn btn-md btn-confirm !gl-inline-flex" href="/users/sign_up"><span class="gl-button-text"> Get free trial </span> </a></li> </ul> </nav> </div> </header> <div class="layout-page page-gutter right-sidebar-expanded page-with-super-sidebar"> <aside class="js-super-sidebar super-sidebar super-sidebar-loading" data-command-palette="{"project_files_url":"/gitlab-org/omnibus-gitlab/-/files/master?format=json","project_blob_url":"/gitlab-org/omnibus-gitlab/-/blob/master"}" data-force-desktop-expanded-sidebar="" data-is-saas="true" data-root-path="/" data-sidebar="{"whats_new_most_recent_release_items_count":4,"whats_new_version_digest":"937c934f90f209e92d7ebf87a22c89305dd21799be84d6b7fb21aa907e3e3b8c","is_logged_in":false,"context_switcher_links":[{"title":"Explore","link":"/explore","icon":"compass"}],"current_menu_items":[{"id":"project_overview","title":"omnibus-gitlab","avatar":"/uploads/-/system/project/avatar/20699/Omnibus-refresh2.png","entity_id":20699,"link":"/gitlab-org/omnibus-gitlab","link_classes":"shortcuts-project","is_active":false},{"id":"manage_menu","title":"Manage","icon":"users","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/activity","is_active":false,"items":[{"id":"activity","title":"Activity","link":"/gitlab-org/omnibus-gitlab/activity","link_classes":"shortcuts-project-activity","is_active":false},{"id":"members","title":"Members","link":"/gitlab-org/omnibus-gitlab/-/project_members","is_active":false},{"id":"labels","title":"Labels","link":"/gitlab-org/omnibus-gitlab/-/labels","is_active":false}],"separated":false},{"id":"plan_menu","title":"Plan","icon":"planning","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/issues","is_active":true,"items":[{"id":"project_issue_list","title":"Issues","link":"/gitlab-org/omnibus-gitlab/-/issues","pill_count_field":"openIssuesCount","link_classes":"shortcuts-issues has-sub-items","is_active":true},{"id":"boards","title":"Issue boards","link":"/gitlab-org/omnibus-gitlab/-/boards","link_classes":"shortcuts-issue-boards","is_active":false},{"id":"milestones","title":"Milestones","link":"/gitlab-org/omnibus-gitlab/-/milestones","is_active":false},{"id":"iterations","title":"Iterations","link":"/gitlab-org/omnibus-gitlab/-/cadences","is_active":false},{"id":"requirements","title":"Requirements","link":"/gitlab-org/omnibus-gitlab/-/requirements_management/requirements","is_active":false}],"separated":false},{"id":"code_menu","title":"Code","icon":"code","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/merge_requests","is_active":false,"items":[{"id":"project_merge_request_list","title":"Merge requests","link":"/gitlab-org/omnibus-gitlab/-/merge_requests","pill_count_field":"openMergeRequestsCount","link_classes":"shortcuts-merge_requests","is_active":false},{"id":"files","title":"Repository","link":"/gitlab-org/omnibus-gitlab/-/tree/master","link_classes":"shortcuts-tree","is_active":false},{"id":"branches","title":"Branches","link":"/gitlab-org/omnibus-gitlab/-/branches","is_active":false},{"id":"commits","title":"Commits","link":"/gitlab-org/omnibus-gitlab/-/commits/master?ref_type=heads","link_classes":"shortcuts-commits","is_active":false},{"id":"tags","title":"Tags","link":"/gitlab-org/omnibus-gitlab/-/tags","is_active":false},{"id":"graphs","title":"Repository graph","link":"/gitlab-org/omnibus-gitlab/-/network/master?ref_type=heads","link_classes":"shortcuts-network","is_active":false},{"id":"compare","title":"Compare revisions","link":"/gitlab-org/omnibus-gitlab/-/compare?from=master\u0026to=master","is_active":false},{"id":"file_locks","title":"Locked files","link":"/gitlab-org/omnibus-gitlab/path_locks","is_active":false}],"separated":false},{"id":"deploy_menu","title":"Deploy","icon":"deployments","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/releases","is_active":false,"items":[{"id":"releases","title":"Releases","link":"/gitlab-org/omnibus-gitlab/-/releases","link_classes":"shortcuts-deployments-releases","is_active":false},{"id":"container_registry","title":"Container Registry","link":"/gitlab-org/omnibus-gitlab/container_registry","is_active":false},{"id":"model_registry","title":"Model registry","link":"/gitlab-org/omnibus-gitlab/-/ml/models","is_active":false}],"separated":false},{"id":"operations_menu","title":"Operate","icon":"cloud-pod","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/environments","is_active":false,"items":[{"id":"environments","title":"Environments","link":"/gitlab-org/omnibus-gitlab/-/environments","link_classes":"shortcuts-environments","is_active":false}],"separated":false},{"id":"monitor_menu","title":"Monitor","icon":"monitor","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/incidents","is_active":false,"items":[{"id":"incidents","title":"Incidents","link":"/gitlab-org/omnibus-gitlab/-/incidents","is_active":false}],"separated":false},{"id":"analyze_menu","title":"Analyze","icon":"chart","avatar_shape":"rect","link":"/gitlab-org/omnibus-gitlab/-/value_stream_analytics","is_active":false,"items":[{"id":"cycle_analytics","title":"Value stream analytics","link":"/gitlab-org/omnibus-gitlab/-/value_stream_analytics","link_classes":"shortcuts-project-cycle-analytics","is_active":false},{"id":"contributors","title":"Contributor analytics","link":"/gitlab-org/omnibus-gitlab/-/graphs/master?ref_type=heads","is_active":false},{"id":"repository_analytics","title":"Repository analytics","link":"/gitlab-org/omnibus-gitlab/-/graphs/master/charts","link_classes":"shortcuts-repository-charts","is_active":false},{"id":"code_review","title":"Code review analytics","link":"/gitlab-org/omnibus-gitlab/-/analytics/code_reviews","is_active":false},{"id":"issues","title":"Issue analytics","link":"/gitlab-org/omnibus-gitlab/-/analytics/issues_analytics","is_active":false},{"id":"insights","title":"Insights","link":"/gitlab-org/omnibus-gitlab/insights/","link_classes":"shortcuts-project-insights","is_active":false},{"id":"model_experiments","title":"Model experiments","link":"/gitlab-org/omnibus-gitlab/-/ml/experiments","is_active":false}],"separated":false}],"current_context_header":"Project","support_path":"https://about.gitlab.com/get-help/","docs_path":"/help/docs","display_whats_new":true,"show_version_check":null,"search":{"search_path":"/search","issues_path":"/dashboard/issues","mr_path":"/dashboard/merge_requests","autocomplete_path":"/search/autocomplete","settings_path":"/search/settings","search_context":{"group":{"id":9970,"name":"GitLab.org","full_name":"GitLab.org"},"group_metadata":{"issues_path":"/groups/gitlab-org/-/issues","mr_path":"/groups/gitlab-org/-/merge_requests"},"project":{"id":20699,"name":"omnibus-gitlab"},"project_metadata":{"mr_path":"/gitlab-org/omnibus-gitlab/-/merge_requests","issues_path":"/gitlab-org/omnibus-gitlab/-/issues"},"code_search":false,"scope":"issues","for_snippets":null}},"panel_type":"project","shortcut_links":[{"title":"Snippets","href":"/explore/snippets","css_class":"dashboard-shortcuts-snippets"},{"title":"Groups","href":"/explore/groups","css_class":"dashboard-shortcuts-groups"},{"title":"Projects","href":"/explore/projects/starred","css_class":"dashboard-shortcuts-projects"}],"terms":"/-/users/terms"}"></aside> <div class="content-wrapper"> <div class="broadcast-wrapper"> </div> <div class="alert-wrapper alert-wrapper-top-space gl-flex gl-flex-col gl-gap-3 container-fluid container-limited"> <div class="gl-alert gl-alert-danger gl-alert-not-dismissible" role="alert"> <div class="gl-alert-icon-container"> <svg class="s16 gl-alert-icon gl-alert-icon-no-title" data-testid="error-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#error"></use></svg> </div> <div class="gl-alert-content" role="alert"> <div class="gl-alert-body"> GitLab may not work properly, because you are using an outdated web browser. <br> Please install a <a target="_blank" rel="noopener noreferrer" href="/help/install/requirements.md#supported-web-browsers">supported web browser</a> for a better experience. </div> </div> </div> </div> <div class="top-bar-fixed container-fluid" data-testid="top-bar"> <div class="top-bar-container gl-flex gl-items-center gl-gap-2"> <div class="gl-grow gl-basis-0 gl-flex gl-items-center gl-justify-start gl-gap-3"> <button class="gl-button btn btn-icon btn-md btn-default btn-default-tertiary js-super-sidebar-toggle-expand super-sidebar-toggle -gl-ml-3" aria-controls="super-sidebar" aria-expanded="false" aria-label="Primary navigation sidebar" type="button"><svg class="s16 gl-icon gl-button-icon " data-testid="sidebar-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#sidebar"></use></svg> </button> <script type="application/ld+json"> {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"GitLab.org","item":"https://gitlab.com/gitlab-org"},{"@type":"ListItem","position":2,"name":"omnibus-gitlab","item":"https://gitlab.com/gitlab-org/omnibus-gitlab"},{"@type":"ListItem","position":3,"name":"Issues","item":"https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues"},{"@type":"ListItem","position":4,"name":"#8377","item":"https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/8377"}]} </script> <div data-testid="breadcrumb-links" id="js-vue-page-breadcrumbs-wrapper"> <div data-breadcrumbs-json="[{"text":"GitLab.org","href":"/gitlab-org","avatarPath":"/uploads/-/system/group/avatar/9970/project_avatar.png"},{"text":"omnibus-gitlab","href":"/gitlab-org/omnibus-gitlab","avatarPath":"/uploads/-/system/project/avatar/20699/Omnibus-refresh2.png"},{"text":"Issues","href":"/gitlab-org/omnibus-gitlab/-/issues","avatarPath":null},{"text":"#8377","href":"/gitlab-org/omnibus-gitlab/-/issues/8377","avatarPath":null}]" id="js-vue-page-breadcrumbs"></div> <div id="js-injected-page-breadcrumbs"></div> </div> </div> <div class="gl-flex-none gl-flex gl-items-center gl-justify-center"> <div id="js-advanced-search-modal"></div> </div> <div class="gl-grow gl-basis-0 gl-flex gl-items-center gl-justify-end"> <div id="js-work-item-feedback"></div> </div> </div> </div> <div class="container-fluid container-limited limit-container-width project-highlight-puc"> <main class="content" id="content-body" itemscope itemtype="http://schema.org/SoftwareSourceCode"> <div class="flash-container flash-container-page sticky" data-testid="flash-container"> <div id="js-global-alerts"></div> </div> <div class="issue-details issuable-details js-issue-details"> <div class="detail-page-description content-block js-detail-page-description gl-pt-3 gl-pb-0 gl-border-none"> <div data-header-actions-data="{"can_create_issue":"true","can_create_incident":"false","can_destroy_issue":"false","can_reopen_issue":"false","can_report_spam":"","can_update_issue":"false","is_issue_author":"false","issue_path":"/gitlab-org/omnibus-gitlab/-/issues/8377","new_issue_path":"/gitlab-org/omnibus-gitlab/-/issues/new?add_related_issue=8377","project_path":"gitlab-org/omnibus-gitlab","report_abuse_path":"/-/abuse_reports/add_category","reported_user_id":4624911,"reported_from_url":"https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/8377","submit_as_spam_path":"/gitlab-org/omnibus-gitlab/-/issues/8377/mark_as_spam","issuable_email_address":null,"can_promote_to_epic":"false"}" data-initial="{"endpoint":"/gitlab-org/omnibus-gitlab/-/issues/8377","updateEndpoint":"/gitlab-org/omnibus-gitlab/-/issues/8377.json","canUpdate":false,"canDestroy":false,"issuableRef":"#8377","imported":false,"markdownPreviewPath":"/gitlab-org/omnibus-gitlab/-/preview_markdown?target_id=8377\u0026target_type=Issue","markdownDocsPath":"/help/user/markdown.md","lockVersion":16,"issuableTemplateNamesPath":"/gitlab-org/omnibus-gitlab/description_templates/names/issue","initialTitleHtml":"PostgreSQL maintenance_work_mem needs to be tuned for larger workloads","initialTitleText":"PostgreSQL maintenance_work_mem needs to be tuned for larger workloads","initialDescriptionHtml":"\u003cul class=\"section-nav\"\u003e\u0026#x000A;\u003cli\u003e\u003ca href=\"#summary\"\u003eSummary\u003c/a\u003e\u003c/li\u003e\u0026#x000A;\u003cli\u003e\u003ca href=\"#proposal\"\u003eProposal\u003c/a\u003e\u003c/li\u003e\u0026#x000A;\u003cli\u003e\u003ca href=\"#references\"\u003eReferences\u003c/a\u003e\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e\u0026#x000A;\u0026#x000A;\u003ch3 data-sourcepos=\"10:1-10:11\" dir=\"auto\"\u003e\u0026#x000A;\u003ca href=\"#summary\" aria-hidden=\"true\" class=\"anchor\" id=\"user-content-summary\"\u003e\u003c/a\u003eSummary\u003c/h3\u003e\u0026#x000A;\u003cp data-sourcepos=\"12:1-12:165\" dir=\"auto\"\u003eCertain maintenance operations like \u003ccode data-sourcepos=\"12:38-12:47\"\u003eautovacuum\u003c/code\u003e need working memory, and by default they use the \u003ccode data-sourcepos=\"12:100-12:119\"\u003emaintenance_work_mem\u003c/code\u003e limit of 64mb (PostgreSQL) / 16mb (Omnibus)\u003c/p\u003e\u0026#x000A;\u003cp data-sourcepos=\"14:1-14:299\" dir=\"auto\"\u003eMore memory allows for potentially more efficient work, much like with client queries (which use \u003ccode data-sourcepos=\"14:99-14:106\"\u003ework_mem\u003c/code\u003e) but unlike client queries, \u003ccode data-sourcepos=\"14:138-14:157\"\u003emaintenance_work_mem\u003c/code\u003e can be set higher; \u003ca data-sourcepos=\"14:179-14:299\" href=\"https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003efrom the PostgreSQL docs\u003c/a\u003e\u003c/p\u003e\u0026#x000A;\u003cblockquote data-sourcepos=\"16:1-16:316\" dir=\"auto\"\u003e\u0026#x000A;\u003cp data-sourcepos=\"16:3-16:316\"\u003eSince only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.\u003c/p\u003e\u0026#x000A;\u003c/blockquote\u003e\u0026#x000A;\u003cp data-sourcepos=\"18:1-18:225\" dir=\"auto\"\u003eI can identify three customer incidents where low \u003ccode data-sourcepos=\"18:52-18:71\"\u003emaintenance_work_mem\u003c/code\u003e was identified as problematic, or increasing it was recommended to address the situation their environment was in. See \u003ca data-sourcepos=\"18:194-18:218\" href=\"#references\"\u003ereferences\u003c/a\u003e below.\u003c/p\u003e\u0026#x000A;\u003cp data-sourcepos=\"20:1-20:552\" dir=\"auto\"\u003eThe third case suggests there's a more serious issue on large instances, since \u003ccode data-sourcepos=\"20:81-20:90\"\u003eautovacuum\u003c/code\u003e was unable to cope with the volume of change that had accumulated on the \u003ccode data-sourcepos=\"20:167-20:175\"\u003eci_builds\u003c/code\u003e table. Changes like [a] switching the primary key of \u003ccode data-sourcepos=\"20:233-20:241\"\u003eci_builds\u003c/code\u003e from 32bit to 64 bit, or [b] adding partitioning keys updates creates a 'perfect storm' for \u003ccode data-sourcepos=\"20:337-20:346\"\u003eautovacuum\u003c/code\u003e. Since PostgreSQL operates on a copy-on-write basis, these duplicate every record in the table, creating 'dead tuples' that need to be recovered by \u003ccode data-sourcepos=\"20:499-20:508\"\u003eautovacuum\u003c/code\u003e, with added impact on the indexes as well.\u003c/p\u003e\u0026#x000A;\u003cp data-sourcepos=\"22:1-22:330\" dir=\"auto\"\u003eBased on what I observed, \u003cstrong data-sourcepos=\"22:27-22:75\"\u003ein due course there would have been an outage\u003c/strong\u003e as the database would have gone into read-only mode. It seemed to be taking an unlimited amount of time to vacuum \u003ccode data-sourcepos=\"22:192-22:200\"\u003eci_builds\u003c/code\u003e. What would have happened as the situation persisted (\u003ca data-sourcepos=\"22:258-22:325\" href=\"https://www.percona.com/blog/overcoming-vacuum-wraparound/\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003esource\u003c/a\u003e) is:\u003c/p\u003e\u0026#x000A;\u003cblockquote data-sourcepos=\"24:1-25:96\" dir=\"auto\"\u003e\u0026#x000A;\u003cul data-sourcepos=\"24:3-25:96\"\u003e\u0026#x000A;\u003cli data-sourcepos=\"24:3-24:121\"\u003e10 million transactions before the upper limit is reached, WARNING messages consisting of a countdown will be logged.\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"25:3-25:96\"\u003e1 million transactions before the upper limit is reached, PostgreSQL goes to READ-ONLY mode.\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e\u0026#x000A;\u003c/blockquote\u003e\u0026#x000A;\u003cp data-sourcepos=\"27:1-27:194\" dir=\"auto\"\u003eWe know from another customer's experience, that the environment would run through to the final threshold when the database goes read only - \u003ca href=\"https://gitlab.com/gitlab-org/gitlab/-/issues/381443\" data-reference-type=\"issue\" data-original=\"https://gitlab.com/gitlab-org/gitlab/-/issues/381443\" data-link=\"false\" data-link-reference=\"true\" data-issue=\"118077306\" data-project=\"278964\" data-iid=\"381443\" data-namespace-path=\"gitlab-org/gitlab\" data-project-path=\"gitlab-org/gitlab\" data-issue-type=\"issue\" data-container=\"body\" data-placement=\"top\" title=\"Detect and react to imminent PostgreSQL transaction ID exhaustion\" class=\"gfm gfm-issue\"\u003egitlab#381443\u003c/a\u003e.\u003c/p\u003e\u0026#x000A;\u003ch3 data-sourcepos=\"29:1-29:12\" dir=\"auto\"\u003e\u0026#x000A;\u003ca href=\"#proposal\" aria-hidden=\"true\" class=\"anchor\" id=\"user-content-proposal\"\u003e\u003c/a\u003eProposal\u003c/h3\u003e\u0026#x000A;\u003cp data-sourcepos=\"31:1-31:282\" dir=\"auto\"\u003eWe tune PostgreSQL shared memory based on the memory on the system; we should also automatically tune \u003ccode data-sourcepos=\"31:105-31:124\"\u003emaintenance_work_mem\u003c/code\u003e so that as customer environments scale, PostgreSQL is able to deploy those resources to keep the database healthy and perform other operations more quickly:\u003c/p\u003e\u0026#x000A;\u003cblockquote data-sourcepos=\"33:1-33:87\" dir=\"auto\"\u003e\u0026#x000A;\u003cp data-sourcepos=\"33:3-33:87\"\u003emaintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY\u003c/p\u003e\u0026#x000A;\u003c/blockquote\u003e\u0026#x000A;\u003cp data-sourcepos=\"35:1-35:179\" dir=\"auto\"\u003e\u003ca href=\"https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757\"\u003eThere's another discussion in one of the cited issues.\u003c/a\u003e (internal link)\u003c/p\u003e\u0026#x000A;\u003cp data-sourcepos=\"37:1-37:57\" dir=\"auto\"\u003eI suggest scaling it up (using system RAM) on this basis:\u003c/p\u003e\u0026#x000A;\u003cul data-sourcepos=\"39:1-42:0\" dir=\"auto\"\u003e\u0026#x000A;\u003cli data-sourcepos=\"39:1-39:34\"\u003e8gb, increase the value to 256mb\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"40:1-40:35\"\u003e16gb, increase the value to 512mb\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"41:1-42:0\"\u003e32gb and higher: 1gb.\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e\u0026#x000A;\u003cp data-sourcepos=\"43:1-43:263\" dir=\"auto\"\u003eThere is a case for setting it higher beyond 32gb for non-autovacuum work. 1gb is the limit for \u003ccode data-sourcepos=\"43:98-43:107\"\u003eautovacuum\u003c/code\u003e - we should get a recommendation from our DBREs prior to implementation, though this isn't essential. 1GB is a significant improvement from the defaults.\u003c/p\u003e\u0026#x000A;\u003cp data-sourcepos=\"45:1-45:131\" dir=\"auto\"\u003eThis is roughly how this maps to the \u003ca data-sourcepos=\"45:38-45:130\" href=\"https://docs.gitlab.com/ee/administration/reference_architectures/\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003ereference architectures\u003c/a\u003e:\u003c/p\u003e\u0026#x000A;\u003ctable data-sourcepos=\"47:1-55:22\" dir=\"auto\"\u003e\u0026#x000A;\u003cthead\u003e\u0026#x000A;\u003ctr data-sourcepos=\"47:1-47:57\"\u003e\u0026#x000A;\u003cth data-sourcepos=\"47:2-47:12\"\u003earch size\u003c/th\u003e\u0026#x000A;\u003cth data-sourcepos=\"47:14-47:31\"\u003edb server memory\u003c/th\u003e\u0026#x000A;\u003cth data-sourcepos=\"47:33-47:56\"\u003eproposed mtce work mem\u003c/th\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003c/thead\u003e\u0026#x000A;\u003ctbody\u003e\u0026#x000A;\u003ctr data-sourcepos=\"49:1-49:46\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"49:2-49:5\"\u003e1k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"49:7-49:22\"\u003eshared (7.2GB)\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"49:24-49:45\"\u003edefault; 16mb or 64mb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"50:1-50:39\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"50:2-50:5\"\u003e2k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"50:7-50:14\"\u003e7.5 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"50:16-50:38\"\u003edefault; 16mb or 64mb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"51:1-51:39\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"51:2-51:5\"\u003e3k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"51:7-51:14\"\u003e7.5 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"51:16-51:38\"\u003edefault; 16mb or 64mb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"52:1-52:22\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"52:2-52:5\"\u003e5k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"52:7-52:13\"\u003e15 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"52:15-52:21\"\u003e256mb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"53:1-53:23\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"53:2-53:6\"\u003e10k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"53:8-53:14\"\u003e30 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"53:16-53:22\"\u003e512mb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"54:1-54:21\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"54:2-54:6\"\u003e25k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"54:8-54:14\"\u003e60 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"54:16-54:20\"\u003e1gb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003ctr data-sourcepos=\"55:1-55:22\"\u003e\u0026#x000A;\u003ctd data-sourcepos=\"55:2-55:6\"\u003e50k\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"55:8-55:15\"\u003e120 GB\u003c/td\u003e\u0026#x000A;\u003ctd data-sourcepos=\"55:17-55:21\"\u003e1gb\u003c/td\u003e\u0026#x000A;\u003c/tr\u003e\u0026#x000A;\u003c/tbody\u003e\u0026#x000A;\u003c/table\u003e\u0026#x000A;\u003cp data-sourcepos=\"57:1-57:273\" dir=\"auto\"\u003e*Omnibus configures shared memory to 25% of server memory, so from 2K onwards, PostgreSQL has dedicated servers and memory, but the majority of RAM is being used for client \u003ccode data-sourcepos=\"57:175-57:182\"\u003ework_mem\u003c/code\u003e, with the kernel filling the rest with disk cache (which is itself useful for PostgreSQL)\u003c/p\u003e\u0026#x000A;\u003ch3 data-sourcepos=\"59:1-59:14\" dir=\"auto\"\u003e\u0026#x000A;\u003ca href=\"#references\" aria-hidden=\"true\" class=\"anchor\" id=\"user-content-references\"\u003e\u003c/a\u003eReferences\u003c/h3\u003e\u0026#x000A;\u003cul data-sourcepos=\"62:1-76:0\" dir=\"auto\"\u003e\u0026#x000A;\u003cli data-sourcepos=\"62:1-65:283\"\u003eA customer on a cloud database service; DBREs recommended a higher value in light of apparent issues with \u003ccode data-sourcepos=\"62:110-62:119\"\u003eautovacuum\u003c/code\u003e completing, and in light of the size of their database (especially \u003ccode data-sourcepos=\"62:190-62:198\"\u003eci_builds\u003c/code\u003e). \u003ca href=\"https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757\"\u003eInternal link\u003c/a\u003e\u0026#x000A;\u003cul data-sourcepos=\"63:3-65:283\"\u003e\u0026#x000A;\u003cli data-sourcepos=\"63:3-63:70\"\u003eThis thread has a discussion about this specific \u003cspan class=\"gl-label gl-label-scoped\" style=\"--label-inset-border: inset 0 0 0 2px #009966; color: #009966\"\u003e\u003ca href=\"/gitlab-org/omnibus-gitlab/-/issues?label_name=type%3A%3Afeature\" data-reference-type=\"label\" data-original='~\"type::feature\"' data-link=\"false\" data-link-reference=\"false\" data-label=\"10230929\" data-project=\"20699\" data-html=\"true\" data-container=\"body\" data-placement=\"top\" title=\"\u0026lt;span class='font-weight-bold'\u0026gt;Scoped label\u0026lt;/span\u0026gt;\u0026lt;br\u0026gt;Any issue/MR that contains work to support the implementation of a feature and/or results in an improvement in the user experience. Read more at https://about.gitlab.com/handbook/engineering/metrics/#work-type-classification\" class=\"gfm gfm-label has-tooltip gl-link gl-label-link\"\u003e\u003cspan class=\"gl-label-text gl-label-text-light\" data-container=\"body\" data-html=\"true\" style=\"background-color: #009966\"\u003etype\u003c/span\u003e\u003cspan class=\"gl-label-text-scoped\" data-container=\"body\" data-html=\"true\"\u003efeature\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"64:3-65:283\"\u003e\u0026#x000A;\u003ca href=\"https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1248525233\"\u003eQuote from elsewhere in that issue\u003c/a\u003e, for future reference:\u0026#x000A;\u003cblockquote data-sourcepos=\"65:5-65:283\"\u003e\u0026#x000A;\u003cp data-sourcepos=\"65:7-65:283\"\u003eNote however that vacuum caps it鈥檚 memory use at 1 GB; enough to process about 179 million dead tuples in one pass. Vacuuming a table with more dead tuples than that will require multiple passes through the tables indexes, which can cause vacuum to take significantly longer.\u003c/p\u003e\u0026#x000A;\u003c/blockquote\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"66:1-67:180\"\u003e\u0026#x000A;\u003ca data-sourcepos=\"66:3-66:61\" href=\"https://gitlab.zendesk.com/agent/tickets/278292\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003eA ticket\u003c/a\u003e (internal link)relating to a second customer with on-prem self-managed Omnibus instance (single node, but very large). Time was of the essence, and we wanted to make sure that a constraint validation could access all the resources it could make use of. It was recommended by one of our DBREs\u0026#x000A;\u003cblockquote data-sourcepos=\"67:3-67:180\"\u003e\u0026#x000A;\u003cp data-sourcepos=\"67:5-67:180\"\u003eanother potential way to improve the execution plan would be increasing working memory in the db session where they are running the ALTER TABLE ... VALIDATE CONSTRAINT command.\u003c/p\u003e\u0026#x000A;\u003c/blockquote\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"68:1-76:0\"\u003e\u0026#x000A;\u003ca data-sourcepos=\"68:3-68:71\" href=\"https://gitlab.zendesk.com/agent/tickets/490115\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003eA recent emergency\u003c/a\u003e (internal link) where an upgrade failed as a \u003ccode data-sourcepos=\"68:119-68:127\"\u003eci_builds\u003c/code\u003e migration could not get an exclusive table lock.\u0026#x000A;\u003cul data-sourcepos=\"69:3-76:0\"\u003e\u0026#x000A;\u003cli data-sourcepos=\"69:3-69:243\"\u003eRoot cause was found to be a pre-existing lock by a 11-12 day old \u003ccode data-sourcepos=\"69:72-69:81\"\u003eautovacuum\u003c/code\u003e operation that was performing a \u003ccode data-sourcepos=\"69:117-69:137\"\u003eto prevent wraparound\u003c/code\u003e vacuum operation. This type of operation is not optional, and is more forceful then normal \u003ccode data-sourcepos=\"69:232-69:241\"\u003eautovacuum\u003c/code\u003e.\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"70:3-70:266\"\u003eWhen we ran it manually, we found that 64mb working memory was only sufficient to tackle (it seemed) one dead tuple for each index on the table, per cycle. Each cycle was taking 30+ minutes interactively, and would have been slower when executed by \u003ccode data-sourcepos=\"70:255-70:264\"\u003eautovacuum\u003c/code\u003e.\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"71:3-71:101\"\u003eSee quote above, \u003cem data-sourcepos=\"71:22-71:101\"\u003e\"Vacuuming a table .. will require multiple passes through the tables indexes\"\u003c/em\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"72:3-72:213\"\u003eSee also \u003ca data-sourcepos=\"72:14-72:151\" href=\"https://www.postgresql.org/message-id/CAMkU%3D1w-%3DWqau8Z8V_t-byo4wv9mt%3DPoZUQ1ucD7PGg5UL6qzw%40mail.gmail.com\" rel=\"nofollow noreferrer noopener\" target=\"_blank\"\u003ethis mailing list post\u003c/a\u003e describing the same issue of slow progress vacuuming indexes.\u003c/li\u003e\u0026#x000A;\u003cli data-sourcepos=\"73:3-76:0\"\u003eThey had \u003cem data-sourcepos=\"73:14-73:23\"\u003eat least\u003c/em\u003e hundreds of thousands of dead tuples to recover from the table and indexes, at 30+ minutes per tuple, so this was unlikely to complete.\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e\u0026#x000A;\u003c/li\u003e\u0026#x000A;\u003c/ul\u003e","initialDescriptionText":"[[_TOC_]]\n\n\u003c!--\nRead me first!\n\nBefore you create a new issue, please make sure to search in https://gitlab.com/gitlab-org/omnibus-gitlab/issues,\nto verify that the issue you are about to submit isn't a duplicate.\n--\u003e\n\n### Summary\n\nCertain maintenance operations like `autovacuum` need working memory, and by default they use the `maintenance_work_mem` limit of 64mb (PostgreSQL) / 16mb (Omnibus)\n\nMore memory allows for potentially more efficient work, much like with client queries (which use `work_mem`) but unlike client queries, `maintenance_work_mem` can be set higher; [from the PostgreSQL docs](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)\n\n\u003e Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.\n\nI can identify three customer incidents where low `maintenance_work_mem` was identified as problematic, or increasing it was recommended to address the situation their environment was in. See [references](#references) below.\n\nThe third case suggests there's a more serious issue on large instances, since `autovacuum` was unable to cope with the volume of change that had accumulated on the `ci_builds` table. Changes like [a] switching the primary key of `ci_builds` from 32bit to 64 bit, or [b] adding partitioning keys updates creates a 'perfect storm' for `autovacuum`. Since PostgreSQL operates on a copy-on-write basis, these duplicate every record in the table, creating 'dead tuples' that need to be recovered by `autovacuum`, with added impact on the indexes as well.\n\nBased on what I observed, **in due course there would have been an outage** as the database would have gone into read-only mode. It seemed to be taking an unlimited amount of time to vacuum `ci_builds`. What would have happened as the situation persisted ([source](https://www.percona.com/blog/overcoming-vacuum-wraparound/)) is:\n\n\u003e - 10 million transactions before the upper limit is reached, WARNING messages consisting of a countdown will be logged.\n\u003e - 1 million transactions before the upper limit is reached, PostgreSQL goes to READ-ONLY mode.\n\nWe know from another customer's experience, that the environment would run through to the final threshold when the database goes read only - https://gitlab.com/gitlab-org/gitlab/-/issues/381443.\n\n### Proposal\n\nWe tune PostgreSQL shared memory based on the memory on the system; we should also automatically tune `maintenance_work_mem` so that as customer environments scale, PostgreSQL is able to deploy those resources to keep the database healthy and perform other operations more quickly:\n\n\u003e maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY\n\n[There's another discussion in one of the cited issues.](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757) (internal link)\n\nI suggest scaling it up (using system RAM) on this basis:\n\n- 8gb, increase the value to 256mb\n- 16gb, increase the value to 512mb\n- 32gb and higher: 1gb.\n\nThere is a case for setting it higher beyond 32gb for non-autovacuum work. 1gb is the limit for `autovacuum` - we should get a recommendation from our DBREs prior to implementation, though this isn't essential. 1GB is a significant improvement from the defaults.\n\nThis is roughly how this maps to the [reference architectures](https://docs.gitlab.com/ee/administration/reference_architectures/):\n\n| arch size | db server memory | proposed mtce work mem |\n| -- | -- | -- |\n| 1k | shared (7.2GB) | default; 16mb or 64mb|\n| 2k | 7.5 GB | default; 16mb or 64mb |\n| 3k | 7.5 GB | default; 16mb or 64mb |\n| 5k | 15 GB | 256mb |\n| 10k | 30 GB | 512mb |\n| 25k | 60 GB | 1gb |\n| 50k | 120 GB | 1gb |\n\n*Omnibus configures shared memory to 25% of server memory, so from 2K onwards, PostgreSQL has dedicated servers and memory, but the majority of RAM is being used for client `work_mem`, with the kernel filling the rest with disk cache (which is itself useful for PostgreSQL)\n\n### References\n\n\n- A customer on a cloud database service; DBREs recommended a higher value in light of apparent issues with `autovacuum` completing, and in light of the size of their database (especially `ci_builds`). [Internal link](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757)\n - This thread has a discussion about this specific ~\"type::feature\" \n - [Quote from elsewhere in that issue](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1248525233), for future reference:\n \u003e Note however that vacuum caps it鈥檚 memory use at 1 GB; enough to process about 179 million dead tuples in one pass. Vacuuming a table with more dead tuples than that will require multiple passes through the tables indexes, which can cause vacuum to take significantly longer.\n- [A ticket](https://gitlab.zendesk.com/agent/tickets/278292) (internal link)relating to a second customer with on-prem self-managed Omnibus instance (single node, but very large). Time was of the essence, and we wanted to make sure that a constraint validation could access all the resources it could make use of. It was recommended by one of our DBREs\n \u003e another potential way to improve the execution plan would be increasing working memory in the db session where they are running the ALTER TABLE ... VALIDATE CONSTRAINT command.\n- [A recent emergency](https://gitlab.zendesk.com/agent/tickets/490115) (internal link) where an upgrade failed as a `ci_builds` migration could not get an exclusive table lock.\n - Root cause was found to be a pre-existing lock by a 11-12 day old `autovacuum` operation that was performing a `to prevent wraparound` vacuum operation. This type of operation is not optional, and is more forceful then normal `autovacuum`.\n - When we ran it manually, we found that 64mb working memory was only sufficient to tackle (it seemed) one dead tuple for each index on the table, per cycle. Each cycle was taking 30+ minutes interactively, and would have been slower when executed by `autovacuum`.\n - See quote above, _\"Vacuuming a table .. will require multiple passes through the tables indexes\"_\n - See also [this mailing list post](https://www.postgresql.org/message-id/CAMkU%3D1w-%3DWqau8Z8V_t-byo4wv9mt%3DPoZUQ1ucD7PGg5UL6qzw%40mail.gmail.com) describing the same issue of slow progress vacuuming indexes.\n - They had _at least_ hundreds of thousands of dead tuples to recover from the table and indexes, at 30+ minutes per tuple, so this was unlikely to complete.\n\n\n\n\u003c!-- Provide references related to this proposal --\u003e","initialTaskCompletionStatus":{"count":0,"completed_count":0},"canCreateIncident":false,"fullPath":"gitlab-org/omnibus-gitlab","iid":8377,"issuableId":140855729,"issueType":"issue","isHidden":false,"zoomMeetingUrl":null,"authorId":4624911,"authorName":"Ben Prescott_","authorUsername":"bprescott_","authorWebUrl":"/bprescott_","createdAt":"2024-01-15T15:49:29+00:00","isFirstContribution":false,"serviceDeskReplyTo":null,"registerPath":"/users/sign_up?redirect_to_referer=yes","signInPath":"/users/sign_in?redirect_to_referer=yes","publishedIncidentUrl":null,"slaFeatureAvailable":"false","uploadMetricsFeatureAvailable":"false","projectId":20699,"projectPath":"omnibus-gitlab","projectNamespace":"gitlab-org","updatedAt":"2024-09-11T08:00:59+00:00","updatedBy":{"name":"Ben Prescott_","path":"/bprescott_"},"canAdmin":false,"hasIssueWeightsFeature":true,"hasIterationsFeature":true,"canAdminRelation":false}" id="js-issuable-app"> <div class="title-container"> <h1 class="gl-heading-1 !gl-m-0">PostgreSQL maintenance_work_mem needs to be tuned for larger workloads</h1> </div> <div class="description"> <div class="md"><ul class="section-nav">
<li><a href="#summary">Summary</a></li>
<li><a href="#proposal">Proposal</a></li>
<li><a href="#references">References</a></li>
</ul>

<h3 data-sourcepos="10:1-10:11" dir="auto">
<a href="#summary" aria-hidden="true" class="anchor" id="user-content-summary"></a>Summary</h3>
<p data-sourcepos="12:1-12:165" dir="auto">Certain maintenance operations like <code data-sourcepos="12:38-12:47">autovacuum</code> need working memory, and by default they use the <code data-sourcepos="12:100-12:119">maintenance_work_mem</code> limit of 64mb (PostgreSQL) / 16mb (Omnibus)</p>
<p data-sourcepos="14:1-14:299" dir="auto">More memory allows for potentially more efficient work, much like with client queries (which use <code data-sourcepos="14:99-14:106">work_mem</code>) but unlike client queries, <code data-sourcepos="14:138-14:157">maintenance_work_mem</code> can be set higher; <a data-sourcepos="14:179-14:299" href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM" rel="nofollow noreferrer noopener" target="_blank">from the PostgreSQL docs</a></p>
<blockquote data-sourcepos="16:1-16:316" dir="auto">
<p data-sourcepos="16:3-16:316">Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.</p>
</blockquote>
<p data-sourcepos="18:1-18:225" dir="auto">I can identify three customer incidents where low <code data-sourcepos="18:52-18:71">maintenance_work_mem</code> was identified as problematic, or increasing it was recommended to address the situation their environment was in. See <a data-sourcepos="18:194-18:218" href="#references">references</a> below.</p>
<p data-sourcepos="20:1-20:552" dir="auto">The third case suggests there's a more serious issue on large instances, since <code data-sourcepos="20:81-20:90">autovacuum</code> was unable to cope with the volume of change that had accumulated on the <code data-sourcepos="20:167-20:175">ci_builds</code> table. Changes like [a] switching the primary key of <code data-sourcepos="20:233-20:241">ci_builds</code> from 32bit to 64 bit, or [b] adding partitioning keys updates creates a 'perfect storm' for <code data-sourcepos="20:337-20:346">autovacuum</code>. Since PostgreSQL operates on a copy-on-write basis, these duplicate every record in the table, creating 'dead tuples' that need to be recovered by <code data-sourcepos="20:499-20:508">autovacuum</code>, with added impact on the indexes as well.</p>
<p data-sourcepos="22:1-22:330" dir="auto">Based on what I observed, <strong data-sourcepos="22:27-22:75">in due course there would have been an outage</strong> as the database would have gone into read-only mode. It seemed to be taking an unlimited amount of time to vacuum <code data-sourcepos="22:192-22:200">ci_builds</code>. What would have happened as the situation persisted (<a data-sourcepos="22:258-22:325" href="https://www.percona.com/blog/overcoming-vacuum-wraparound/" rel="nofollow noreferrer noopener" target="_blank">source</a>) is:</p>
<blockquote data-sourcepos="24:1-25:96" dir="auto">
<ul data-sourcepos="24:3-25:96">
<li data-sourcepos="24:3-24:121">10 million transactions before the upper limit is reached, WARNING messages consisting of a countdown will be logged.</li>
<li data-sourcepos="25:3-25:96">1 million transactions before the upper limit is reached, PostgreSQL goes to READ-ONLY mode.</li>
</ul>
</blockquote>
<p data-sourcepos="27:1-27:194" dir="auto">We know from another customer's experience, that the environment would run through to the final threshold when the database goes read only - <a href="https://gitlab.com/gitlab-org/gitlab/-/issues/381443" data-reference-type="issue" data-original="https://gitlab.com/gitlab-org/gitlab/-/issues/381443" data-link="false" data-link-reference="true" data-issue="118077306" data-project="278964" data-iid="381443" data-namespace-path="gitlab-org/gitlab" data-project-path="gitlab-org/gitlab" data-issue-type="issue" data-container="body" data-placement="top" title="Detect and react to imminent PostgreSQL transaction ID exhaustion" class="gfm gfm-issue">gitlab#381443</a>.</p>
<h3 data-sourcepos="29:1-29:12" dir="auto">
<a href="#proposal" aria-hidden="true" class="anchor" id="user-content-proposal"></a>Proposal</h3>
<p data-sourcepos="31:1-31:282" dir="auto">We tune PostgreSQL shared memory based on the memory on the system; we should also automatically tune <code data-sourcepos="31:105-31:124">maintenance_work_mem</code> so that as customer environments scale, PostgreSQL is able to deploy those resources to keep the database healthy and perform other operations more quickly:</p>
<blockquote data-sourcepos="33:1-33:87" dir="auto">
<p data-sourcepos="33:3-33:87">maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY</p>
</blockquote>
<p data-sourcepos="35:1-35:179" dir="auto"><a href="https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757">There's another discussion in one of the cited issues.</a> (internal link)</p>
<p data-sourcepos="37:1-37:57" dir="auto">I suggest scaling it up (using system RAM) on this basis:</p>
<ul data-sourcepos="39:1-42:0" dir="auto">
<li data-sourcepos="39:1-39:34">8gb, increase the value to 256mb</li>
<li data-sourcepos="40:1-40:35">16gb, increase the value to 512mb</li>
<li data-sourcepos="41:1-42:0">32gb and higher: 1gb.</li>
</ul>
<p data-sourcepos="43:1-43:263" dir="auto">There is a case for setting it higher beyond 32gb for non-autovacuum work. 1gb is the limit for <code data-sourcepos="43:98-43:107">autovacuum</code> - we should get a recommendation from our DBREs prior to implementation, though this isn't essential. 1GB is a significant improvement from the defaults.</p>
<p data-sourcepos="45:1-45:131" dir="auto">This is roughly how this maps to the <a data-sourcepos="45:38-45:130" href="https://docs.gitlab.com/ee/administration/reference_architectures/" rel="nofollow noreferrer noopener" target="_blank">reference architectures</a>:</p>
<table data-sourcepos="47:1-55:22" dir="auto">
<thead>
<tr data-sourcepos="47:1-47:57">
<th data-sourcepos="47:2-47:12">arch size</th>
<th data-sourcepos="47:14-47:31">db server memory</th>
<th data-sourcepos="47:33-47:56">proposed mtce work mem</th>
</tr>
</thead>
<tbody>
<tr data-sourcepos="49:1-49:46">
<td data-sourcepos="49:2-49:5">1k</td>
<td data-sourcepos="49:7-49:22">shared (7.2GB)</td>
<td data-sourcepos="49:24-49:45">default; 16mb or 64mb</td>
</tr>
<tr data-sourcepos="50:1-50:39">
<td data-sourcepos="50:2-50:5">2k</td>
<td data-sourcepos="50:7-50:14">7.5 GB</td>
<td data-sourcepos="50:16-50:38">default; 16mb or 64mb</td>
</tr>
<tr data-sourcepos="51:1-51:39">
<td data-sourcepos="51:2-51:5">3k</td>
<td data-sourcepos="51:7-51:14">7.5 GB</td>
<td data-sourcepos="51:16-51:38">default; 16mb or 64mb</td>
</tr>
<tr data-sourcepos="52:1-52:22">
<td data-sourcepos="52:2-52:5">5k</td>
<td data-sourcepos="52:7-52:13">15 GB</td>
<td data-sourcepos="52:15-52:21">256mb</td>
</tr>
<tr data-sourcepos="53:1-53:23">
<td data-sourcepos="53:2-53:6">10k</td>
<td data-sourcepos="53:8-53:14">30 GB</td>
<td data-sourcepos="53:16-53:22">512mb</td>
</tr>
<tr data-sourcepos="54:1-54:21">
<td data-sourcepos="54:2-54:6">25k</td>
<td data-sourcepos="54:8-54:14">60 GB</td>
<td data-sourcepos="54:16-54:20">1gb</td>
</tr>
<tr data-sourcepos="55:1-55:22">
<td data-sourcepos="55:2-55:6">50k</td>
<td data-sourcepos="55:8-55:15">120 GB</td>
<td data-sourcepos="55:17-55:21">1gb</td>
</tr>
</tbody>
</table>
<p data-sourcepos="57:1-57:273" dir="auto">*Omnibus configures shared memory to 25% of server memory, so from 2K onwards, PostgreSQL has dedicated servers and memory, but the majority of RAM is being used for client <code data-sourcepos="57:175-57:182">work_mem</code>, with the kernel filling the rest with disk cache (which is itself useful for PostgreSQL)</p>
<h3 data-sourcepos="59:1-59:14" dir="auto">
<a href="#references" aria-hidden="true" class="anchor" id="user-content-references"></a>References</h3>
<ul data-sourcepos="62:1-76:0" dir="auto">
<li data-sourcepos="62:1-65:283">A customer on a cloud database service; DBREs recommended a higher value in light of apparent issues with <code data-sourcepos="62:110-62:119">autovacuum</code> completing, and in light of the size of their database (especially <code data-sourcepos="62:190-62:198">ci_builds</code>). <a href="https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757">Internal link</a>
<ul data-sourcepos="63:3-65:283">
<li data-sourcepos="63:3-63:70">This thread has a discussion about this specific <span class="gl-label gl-label-scoped" style="--label-inset-border: inset 0 0 0 2px #009966; color: #009966"><a href="/gitlab-org/omnibus-gitlab/-/issues?label_name=type%3A%3Afeature" data-reference-type="label" data-original='~"type::feature"' data-link="false" data-link-reference="false" data-label="10230929" data-project="20699" data-html="true" data-container="body" data-placement="top" title="<span class='font-weight-bold'>Scoped label</span><br>Any issue/MR that contains work to support the implementation of a feature and/or results in an improvement in the user experience. Read more at https://about.gitlab.com/handbook/engineering/metrics/#work-type-classification" class="gfm gfm-label has-tooltip gl-link gl-label-link"><span class="gl-label-text gl-label-text-light" data-container="body" data-html="true" style="background-color: #009966">type</span><span class="gl-label-text-scoped" data-container="body" data-html="true">feature</span></a></span>
</li>
<li data-sourcepos="64:3-65:283">
<a href="https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1248525233">Quote from elsewhere in that issue</a>, for future reference:
<blockquote data-sourcepos="65:5-65:283">
<p data-sourcepos="65:7-65:283">Note however that vacuum caps it鈥檚 memory use at 1 GB; enough to process about 179 million dead tuples in one pass. Vacuuming a table with more dead tuples than that will require multiple passes through the tables indexes, which can cause vacuum to take significantly longer.</p>
</blockquote>
</li>
</ul>
</li>
<li data-sourcepos="66:1-67:180">
<a data-sourcepos="66:3-66:61" href="https://gitlab.zendesk.com/agent/tickets/278292" rel="nofollow noreferrer noopener" target="_blank">A ticket</a> (internal link)relating to a second customer with on-prem self-managed Omnibus instance (single node, but very large). Time was of the essence, and we wanted to make sure that a constraint validation could access all the resources it could make use of. It was recommended by one of our DBREs
<blockquote data-sourcepos="67:3-67:180">
<p data-sourcepos="67:5-67:180">another potential way to improve the execution plan would be increasing working memory in the db session where they are running the ALTER TABLE ... VALIDATE CONSTRAINT command.</p>
</blockquote>
</li>
<li data-sourcepos="68:1-76:0">
<a data-sourcepos="68:3-68:71" href="https://gitlab.zendesk.com/agent/tickets/490115" rel="nofollow noreferrer noopener" target="_blank">A recent emergency</a> (internal link) where an upgrade failed as a <code data-sourcepos="68:119-68:127">ci_builds</code> migration could not get an exclusive table lock.
<ul data-sourcepos="69:3-76:0">
<li data-sourcepos="69:3-69:243">Root cause was found to be a pre-existing lock by a 11-12 day old <code data-sourcepos="69:72-69:81">autovacuum</code> operation that was performing a <code data-sourcepos="69:117-69:137">to prevent wraparound</code> vacuum operation. This type of operation is not optional, and is more forceful then normal <code data-sourcepos="69:232-69:241">autovacuum</code>.</li>
<li data-sourcepos="70:3-70:266">When we ran it manually, we found that 64mb working memory was only sufficient to tackle (it seemed) one dead tuple for each index on the table, per cycle. Each cycle was taking 30+ minutes interactively, and would have been slower when executed by <code data-sourcepos="70:255-70:264">autovacuum</code>.</li>
<li data-sourcepos="71:3-71:101">See quote above, <em data-sourcepos="71:22-71:101">"Vacuuming a table .. will require multiple passes through the tables indexes"</em>
</li>
<li data-sourcepos="72:3-72:213">See also <a data-sourcepos="72:14-72:151" href="https://www.postgresql.org/message-id/CAMkU%3D1w-%3DWqau8Z8V_t-byo4wv9mt%3DPoZUQ1ucD7PGg5UL6qzw%40mail.gmail.com" rel="nofollow noreferrer noopener" target="_blank">this mailing list post</a> describing the same issue of slow progress vacuuming indexes.</li>
<li data-sourcepos="73:3-76:0">They had <em data-sourcepos="73:14-73:23">at least</em> hundreds of thousands of dead tuples to recover from the table and indexes, at 30+ minutes per tuple, so this was unlikely to complete.</li>
</ul>
</li>
</ul></div> </div> <div class="edited-text gl-mt-4 gl-text-subtle gl-text-sm">Edited <time class="js-timeago issue-edited-ago js-issue-edited-ago" title="Sep 11, 2024 8:00am" datetime="2024-09-11T08:00:59Z" tabindex="0" aria-label="Sep 11, 2024 8:00am" data-toggle="tooltip" data-placement="bottom" data-container="body">Sep 11, 2024</time> by <a class="author-link hover:gl-underline gl-text-subtle js-user-link" data-user-id="4624911" data-username="bprescott_" data-name="Ben Prescott_" data-testid="author-link" href="/bprescott_"><span class="">Ben Prescott_</span></a></div> </div> <div class="js-issue-widgets"> <div class="emoji-block emoji-block-sticky"> <div class="row gl-m-0 gl-justify-between"> <div class="js-noteable-awards"> <div class="gl-flex gl-flex-wrap gl-justify-between gl-pt-3"> <div data-can-award-emoji="false" data-path="/api/v4/projects/20699/issues/8377/award_emoji" data-show-default-award-emojis="true" id="js-vue-awards-block"></div> </div> </div> <div class="new-branch-col gl-font-size-0 gl-my-2"> </div> </div> </div> </div> </div> <div class="js-issue-widgets"> <div class="js-design-management" data-issue-iid="8377" data-issue-path="/gitlab-org/omnibus-gitlab/-/issues/8377" data-new-comment-template-paths="[{"text":"Your comment templates","href":"/-/profile/comment_templates"}]" data-project-path="gitlab-org/omnibus-gitlab" data-register-path="/users/sign_up?redirect_to_referer=yes" data-sign-in-path="/users/sign_in?redirect_to_referer=yes"></div> <div class="js-work-item-links-root" data-full-path="gitlab-org/omnibus-gitlab" data-issuable-id="140855729" data-issuable-iid="8377" data-register-path="/users/sign_up?redirect_to_referer=yes" data-sign-in-path="/users/sign_in?redirect_to_referer=yes" data-wi-autocomplete-award-emojis-path="/-/autocomplete/award_emojis" data-wi-can-admin-label="false" data-wi-can-bulk-edit-epics="false" data-wi-can-create-projects="false" data-wi-default-branch="master" data-wi-epics-list-path="/groups/omnibus-gitlab/-/epics" data-wi-full-path="gitlab-org/omnibus-gitlab" data-wi-group-id="9970" data-wi-group-issues-path="/groups/omnibus-gitlab/-/issues" data-wi-group-path="gitlab-org" data-wi-has-epics-feature="true" data-wi-has-issuable-health-status-feature="true" data-wi-has-issue-weights-feature="true" data-wi-has-iterations-feature="true" data-wi-has-linked-items-epics-feature="true" data-wi-has-okrs-feature="true" data-wi-has-quality-management-feature="true" data-wi-has-scoped-labels-feature="true" data-wi-has-subepics-feature="true" data-wi-is-signed-in="false" data-wi-issues-list-path="/gitlab-org/omnibus-gitlab/-/issues" data-wi-labels-fetch-path="/groups/omnibus-gitlab/-/labels.json?include_ancestor_groups=true&only_group_labels=true" data-wi-labels-manage-path="/gitlab-org/omnibus-gitlab/-/labels" data-wi-new-comment-template-paths="[{"text":"Your comment templates","href":"/-/profile/comment_templates"}]" data-wi-new-project-path="/projects/new?namespace_id=9970" data-wi-register-path="/users/sign_up?redirect_to_referer=yes" data-wi-report-abuse-path="/-/abuse_reports/add_category" data-wi-show-new-issue-link="false" data-wi-sign-in-path="/users/sign_in?redirect_to_referer=yes"></div> <div class="js-related-issues-root" data-can-add-related-issues="false" data-endpoint="/gitlab-org/omnibus-gitlab/-/issues/8377/links" data-full-path="gitlab-org/omnibus-gitlab" data-has-issue-weights-feature="true" data-has-iterations-feature="true" data-help-path="/help/user/project/issues/related_issues.md" data-is-group="false" data-issuable-type="issue" data-show-categorized-issues="true" data-wi-autocomplete-award-emojis-path="/-/autocomplete/award_emojis" data-wi-can-admin-label="false" data-wi-can-bulk-edit-epics="false" data-wi-can-create-projects="false" data-wi-default-branch="master" data-wi-epics-list-path="/groups/omnibus-gitlab/-/epics" data-wi-full-path="gitlab-org/omnibus-gitlab" data-wi-group-id="9970" data-wi-group-issues-path="/groups/omnibus-gitlab/-/issues" data-wi-group-path="gitlab-org" data-wi-has-epics-feature="true" data-wi-has-issuable-health-status-feature="true" data-wi-has-issue-weights-feature="true" data-wi-has-iterations-feature="true" data-wi-has-linked-items-epics-feature="true" data-wi-has-okrs-feature="true" data-wi-has-quality-management-feature="true" data-wi-has-scoped-labels-feature="true" data-wi-has-subepics-feature="true" data-wi-is-signed-in="false" data-wi-issues-list-path="/gitlab-org/omnibus-gitlab/-/issues" data-wi-labels-fetch-path="/groups/omnibus-gitlab/-/labels.json?include_ancestor_groups=true&only_group_labels=true" data-wi-labels-manage-path="/gitlab-org/omnibus-gitlab/-/labels" data-wi-new-comment-template-paths="[{"text":"Your comment templates","href":"/-/profile/comment_templates"}]" data-wi-new-project-path="/projects/new?namespace_id=9970" data-wi-register-path="/users/sign_up?redirect_to_referer=yes" data-wi-report-abuse-path="/-/abuse_reports/add_category" data-wi-show-new-issue-link="false" data-wi-sign-in-path="/users/sign_in?redirect_to_referer=yes"></div> <div data-has-closing-merge-request="false" data-iid="8377" data-project-path="gitlab-org/omnibus-gitlab" id="js-related-merge-requests"></div> <div data-url="/gitlab-org/omnibus-gitlab/-/issues/8377/related_branches" id="related-branches"> </div> </div> <div class="js-issue-widgets"> <section class="issuable-discussion js-vue-notes-event"> <div data-can-add-timeline-events="false" data-current-user-data="null" data-new-comment-template-paths="[{"text":"Your comment templates","href":"/-/profile/comment_templates"}]" data-noteable-data="{"id":140855729,"iid":8377,"description":"[[_TOC_]]\n\n\u003c!--\nRead me first!\n\nBefore you create a new issue, please make sure to search in https://gitlab.com/gitlab-org/omnibus-gitlab/issues,\nto verify that the issue you are about to submit isn't a duplicate.\n--\u003e\n\n### Summary\n\nCertain maintenance operations like `autovacuum` need working memory, and by default they use the `maintenance_work_mem` limit of 64mb (PostgreSQL) / 16mb (Omnibus)\n\nMore memory allows for potentially more efficient work, much like with client queries (which use `work_mem`) but unlike client queries, `maintenance_work_mem` can be set higher; [from the PostgreSQL docs](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)\n\n\u003e Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.\n\nI can identify three customer incidents where low `maintenance_work_mem` was identified as problematic, or increasing it was recommended to address the situation their environment was in. See [references](#references) below.\n\nThe third case suggests there's a more serious issue on large instances, since `autovacuum` was unable to cope with the volume of change that had accumulated on the `ci_builds` table. Changes like [a] switching the primary key of `ci_builds` from 32bit to 64 bit, or [b] adding partitioning keys updates creates a 'perfect storm' for `autovacuum`. Since PostgreSQL operates on a copy-on-write basis, these duplicate every record in the table, creating 'dead tuples' that need to be recovered by `autovacuum`, with added impact on the indexes as well.\n\nBased on what I observed, **in due course there would have been an outage** as the database would have gone into read-only mode. It seemed to be taking an unlimited amount of time to vacuum `ci_builds`. What would have happened as the situation persisted ([source](https://www.percona.com/blog/overcoming-vacuum-wraparound/)) is:\n\n\u003e - 10 million transactions before the upper limit is reached, WARNING messages consisting of a countdown will be logged.\n\u003e - 1 million transactions before the upper limit is reached, PostgreSQL goes to READ-ONLY mode.\n\nWe know from another customer's experience, that the environment would run through to the final threshold when the database goes read only - https://gitlab.com/gitlab-org/gitlab/-/issues/381443.\n\n### Proposal\n\nWe tune PostgreSQL shared memory based on the memory on the system; we should also automatically tune `maintenance_work_mem` so that as customer environments scale, PostgreSQL is able to deploy those resources to keep the database healthy and perform other operations more quickly:\n\n\u003e maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY\n\n[There's another discussion in one of the cited issues.](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757) (internal link)\n\nI suggest scaling it up (using system RAM) on this basis:\n\n- 8gb, increase the value to 256mb\n- 16gb, increase the value to 512mb\n- 32gb and higher: 1gb.\n\nThere is a case for setting it higher beyond 32gb for non-autovacuum work. 1gb is the limit for `autovacuum` - we should get a recommendation from our DBREs prior to implementation, though this isn't essential. 1GB is a significant improvement from the defaults.\n\nThis is roughly how this maps to the [reference architectures](https://docs.gitlab.com/ee/administration/reference_architectures/):\n\n| arch size | db server memory | proposed mtce work mem |\n| -- | -- | -- |\n| 1k | shared (7.2GB) | default; 16mb or 64mb|\n| 2k | 7.5 GB | default; 16mb or 64mb |\n| 3k | 7.5 GB | default; 16mb or 64mb |\n| 5k | 15 GB | 256mb |\n| 10k | 30 GB | 512mb |\n| 25k | 60 GB | 1gb |\n| 50k | 120 GB | 1gb |\n\n*Omnibus configures shared memory to 25% of server memory, so from 2K onwards, PostgreSQL has dedicated servers and memory, but the majority of RAM is being used for client `work_mem`, with the kernel filling the rest with disk cache (which is itself useful for PostgreSQL)\n\n### References\n\n\n- A customer on a cloud database service; DBREs recommended a higher value in light of apparent issues with `autovacuum` completing, and in light of the size of their database (especially `ci_builds`). [Internal link](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1298572757)\n - This thread has a discussion about this specific ~\"type::feature\" \n - [Quote from elsewhere in that issue](https://gitlab.com/gitlab-com/dev-sub-department/section-dev-request-for-help/-/issues/54#note_1248525233), for future reference:\n \u003e Note however that vacuum caps it鈥檚 memory use at 1 GB; enough to process about 179 million dead tuples in one pass. Vacuuming a table with more dead tuples than that will require multiple passes through the tables indexes, which can cause vacuum to take significantly longer.\n- [A ticket](https://gitlab.zendesk.com/agent/tickets/278292) (internal link)relating to a second customer with on-prem self-managed Omnibus instance (single node, but very large). Time was of the essence, and we wanted to make sure that a constraint validation could access all the resources it could make use of. It was recommended by one of our DBREs\n \u003e another potential way to improve the execution plan would be increasing working memory in the db session where they are running the ALTER TABLE ... VALIDATE CONSTRAINT command.\n- [A recent emergency](https://gitlab.zendesk.com/agent/tickets/490115) (internal link) where an upgrade failed as a `ci_builds` migration could not get an exclusive table lock.\n - Root cause was found to be a pre-existing lock by a 11-12 day old `autovacuum` operation that was performing a `to prevent wraparound` vacuum operation. This type of operation is not optional, and is more forceful then normal `autovacuum`.\n - When we ran it manually, we found that 64mb working memory was only sufficient to tackle (it seemed) one dead tuple for each index on the table, per cycle. Each cycle was taking 30+ minutes interactively, and would have been slower when executed by `autovacuum`.\n - See quote above, _\"Vacuuming a table .. will require multiple passes through the tables indexes\"_\n - See also [this mailing list post](https://www.postgresql.org/message-id/CAMkU%3D1w-%3DWqau8Z8V_t-byo4wv9mt%3DPoZUQ1ucD7PGg5UL6qzw%40mail.gmail.com) describing the same issue of slow progress vacuuming indexes.\n - They had _at least_ hundreds of thousands of dead tuples to recover from the table and indexes, at 30+ minutes per tuple, so this was unlikely to complete.\n\n\n\n\u003c!-- Provide references related to this proposal --\u003e","title":"PostgreSQL maintenance_work_mem needs to be tuned for larger workloads","time_estimate":0,"total_time_spent":0,"human_time_estimate":null,"human_total_time_spent":null,"state":"opened","milestone_id":null,"updated_by_id":4624911,"created_at":"2024-01-15T15:49:29Z","updated_at":"2025-02-10T13:04:57Z","milestone":null,"labels":[{"id":1672341,"title":"customer","color":"#ad4363","description":"Issues that were reported by Enterprise Edition subscribers. This label should be accompanied by either the 'bug' or 'feature proposal' label","text_color":"#FFFFFF","created_at":"2017-03-10T22:24:59.331Z","updated_at":"2017-03-10T22:24:59.331Z","group_id":9970,"project_id":null,"template":false},{"id":3098698,"title":"database","color":"#428bca","description":"Issues related to GitLab's (ab)use of its database","text_color":"#FFFFFF","created_at":"2017-11-30T19:19:17.188Z","updated_at":"2017-11-30T19:19:17.188Z","group_id":9970,"project_id":null,"template":false},{"id":9852041,"title":"devops::systems","color":"#E44D2A","description":"Issues for the Systems stage of the DevOps lifecycle (Distribution, Geo, Gitaly)","text_color":"#FFFFFF","created_at":"2019-03-05T21:09:28.789Z","updated_at":"2022-07-04T07:12:05.928Z","group_id":9970,"project_id":null,"template":false},{"id":2975007,"title":"feature::enhancement","color":"#f0ad4e","description":"User-facing improvements that refine the initial MVC to make it more useful and usable. Read more at https://handbook.gitlab.com/handbook/product/groups/product-analysis/engineering/metrics/#work-type-classification","text_color":"#1F1E24","created_at":"2017-11-07T21:18:44.387Z","updated_at":"2024-12-19T12:46:55.965Z","group_id":9970,"project_id":null,"template":false},{"id":10690780,"title":"group::distribution","color":"#a8d695","description":"Issues belonging to the Distribution group of the Core Platform stage of the DevOps lifecycle. See https://about.gitlab.com/handbook/product/categories/#distribution-group","text_color":"#1F1E24","created_at":"2019-05-22T20:12:21.034Z","updated_at":"2023-11-01T05:00:19.135Z","group_id":9970,"project_id":null,"template":false},{"id":2936423,"title":"needs investigation","color":"#69D100","description":"Relates to issues raised by the community that need further investigation to triage. Please feel free to help investigate!","text_color":"#1F1E24","created_at":"2017-10-31T10:34:38.105Z","updated_at":"2017-10-31T10:34:58.953Z","group_id":9970,"project_id":null,"template":false},{"id":22597802,"title":"postgresql","color":"#428BCA","description":null,"text_color":"#FFFFFF","created_at":"2021-11-15T22:32:34.222Z","updated_at":"2021-11-15T22:32:34.222Z","group_id":9970,"project_id":null,"template":false},{"id":14918396,"title":"section::core platform","color":"#f0ad4e","description":"Issues related to the Core Platform section","text_color":"#1F1E24","created_at":"2020-05-11T22:13:49.847Z","updated_at":"2023-11-01T04:59:02.371Z","group_id":9970,"project_id":null,"template":false},{"id":10230929,"title":"type::feature","color":"#009966","description":"Any issue/MR that contains work to support the implementation of a feature and/or results in an improvement in the user experience. Read more at https://handbook.gitlab.com/handbook/product/groups/product-analysis/engineering/metrics/#work-type-classification","text_color":"#FFFFFF","created_at":"2019-04-09T12:29:58.492Z","updated_at":"2024-11-08T09:52:52.581Z","group_id":9970,"project_id":null,"template":false}],"lock_version":16,"author_id":4624911,"confidential":false,"discussion_locked":null,"assignees":[],"due_date":null,"project_id":20699,"moved_to_id":null,"duplicated_to_id":null,"web_url":"/gitlab-org/omnibus-gitlab/-/issues/8377","current_user":{"can_create_note":false,"can_create_confidential_note":false,"can_update":false,"can_set_issue_metadata":false,"can_award_emoji":false},"create_note_path":"/gitlab-org/omnibus-gitlab/notes?target_id=140855729\u0026target_type=issue","preview_note_path":"/gitlab-org/omnibus-gitlab/-/preview_markdown?target_id=8377\u0026target_type=Issue","is_project_archived":false,"issue_email_participants":[],"type":"ISSUE","weight":null,"blocked":false,"blocked_by_issues":[]}" data-noteable-type="Issue" data-notes-data="{"noteableType":"issue","noteableId":140855729,"projectId":20699,"groupId":null,"discussionsPath":"/gitlab-org/omnibus-gitlab/-/issues/8377/discussions.json","registerPath":"/users/sign_up?redirect_to_referer=yes","newSessionPath":"/users/sign_in?redirect_to_referer=yes","markdownDocsPath":"/help/user/markdown.md","quickActionsDocsPath":"/help/user/project/quick_actions.md","closePath":"/gitlab-org/omnibus-gitlab/-/issues/8377.json?issue%5Bstate_event%5D=close","reopenPath":"/gitlab-org/omnibus-gitlab/-/issues/8377.json?issue%5Bstate_event%5D=reopen","notesPath":"/gitlab-org/omnibus-gitlab/noteable/issue/140855729/notes","prerenderedNotesCount":10,"lastFetchedAt":1740153211000000,"notesFilter":null}" data-notes-filters="{"Show all activity":0,"Show comments only":1,"Show history only":2}" data-report-abuse-path="/-/abuse_reports/add_category" data-show-timeline-view-toggle="false" data-target-type="issue" id="js-vue-notes"></div> </section> </div> </div> <aside aria-label="issue" aria-live="polite" class="right-sidebar js-right-sidebar js-issuable-sidebar right-sidebar-expanded" data-always-show-toggle data-auto-collapse data-issuable-type="issue"> <div class="issuable-sidebar"> <div class="issuable-sidebar-header"> <button class="gl-button btn btn-md btn-default gutter-toggle gl-float-right js-sidebar-toggle has-tooltip !gl-border-0" type="button" aria-label="Toggle sidebar" title="Collapse sidebar" data-container="body" data-placement="left" data-boundary="viewport" type="button"><span class="gl-button-text"> <span class="js-sidebar-toggle-container gl-button-text" data-is-expanded="true"><svg class="s16 js-sidebar-expand hidden" data-testid="chevron-double-lg-left-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#chevron-double-lg-left"></use></svg><svg class="s16 js-sidebar-collapse " data-testid="chevron-double-lg-right-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#chevron-double-lg-right"></use></svg></span> </span> </button></div> <form class="issuable-context-form inline-update js-issuable-update " action="/gitlab-org/omnibus-gitlab/-/issues/8377.json" accept-charset="UTF-8" data-remote="true" method="post"><div class="block assignee gl-mt-3" data-testid="assignee-block-container"> <div class="js-sidebar-assignees-root" data-field="issue" data-max-assignees="200"> <div class="title hide-collapsed gl-flex gl-justify-between gl-items-center !gl-mb-0"> <span class="gl-font-bold">Assignee</span> <span class="gl-spinner-container" role="status"><span aria-hidden class="gl-spinner gl-spinner-sm gl-spinner-dark !gl-align-text-bottom"></span><span class="gl-sr-only !gl-absolute">Loading</span> </span> </div> </div> </div> <div class="block epic" data-testid="sidebar-epic"> <div class="js-sidebar-epic-widget-root" data-can-edit="false" data-group-path="gitlab-org" data-issue-id="140855729" data-issue-iid="8377" data-project-path="gitlab-org/omnibus-gitlab"></div> </div> <div class="js-sidebar-labels-widget-root" data-allow-label-create="" data-allow-scoped-labels="true" data-can-edit="" data-iid="8377" data-issuable-type="issue" data-labels-fetch-path="/gitlab-org/omnibus-gitlab/-/labels.json?include_ancestor_groups=true" data-labels-manage-path="/gitlab-org/omnibus-gitlab/-/labels" data-project-issues-path="/gitlab-org/omnibus-gitlab/-/issues" data-project-path="gitlab-org/omnibus-gitlab" data-selected-labels="[{"id":1672341,"title":"customer","color":"#ad4363","description":"Issues that were reported by Enterprise Edition subscribers. This label should be accompanied by either the 'bug' or 'feature proposal' label","text_color":"#FFFFFF","created_at":"2017-03-10T22:24:59.331Z","updated_at":"2017-03-10T22:24:59.331Z","group_id":9970,"project_id":null,"template":false},{"id":3098698,"title":"database","color":"#428bca","description":"Issues related to GitLab's (ab)use of its database","text_color":"#FFFFFF","created_at":"2017-11-30T19:19:17.188Z","updated_at":"2017-11-30T19:19:17.188Z","group_id":9970,"project_id":null,"template":false},{"id":9852041,"title":"devops::systems","color":"#E44D2A","description":"Issues for the Systems stage of the DevOps lifecycle (Distribution, Geo, Gitaly)","text_color":"#FFFFFF","created_at":"2019-03-05T21:09:28.789Z","updated_at":"2022-07-04T07:12:05.928Z","group_id":9970,"project_id":null,"template":false},{"id":2975007,"title":"feature::enhancement","color":"#f0ad4e","description":"User-facing improvements that refine the initial MVC to make it more useful and usable. Read more at https://handbook.gitlab.com/handbook/product/groups/product-analysis/engineering/metrics/#work-type-classification","text_color":"#1F1E24","created_at":"2017-11-07T21:18:44.387Z","updated_at":"2024-12-19T12:46:55.965Z","group_id":9970,"project_id":null,"template":false},{"id":10690780,"title":"group::distribution","color":"#a8d695","description":"Issues belonging to the Distribution group of the Core Platform stage of the DevOps lifecycle. See https://about.gitlab.com/handbook/product/categories/#distribution-group","text_color":"#1F1E24","created_at":"2019-05-22T20:12:21.034Z","updated_at":"2023-11-01T05:00:19.135Z","group_id":9970,"project_id":null,"template":false},{"id":2936423,"title":"needs investigation","color":"#69D100","description":"Relates to issues raised by the community that need further investigation to triage. Please feel free to help investigate!","text_color":"#1F1E24","created_at":"2017-10-31T10:34:38.105Z","updated_at":"2017-10-31T10:34:58.953Z","group_id":9970,"project_id":null,"template":false},{"id":22597802,"title":"postgresql","color":"#428BCA","description":null,"text_color":"#FFFFFF","created_at":"2021-11-15T22:32:34.222Z","updated_at":"2021-11-15T22:32:34.222Z","group_id":9970,"project_id":null,"template":false},{"id":14918396,"title":"section::core platform","color":"#f0ad4e","description":"Issues related to the Core Platform section","text_color":"#1F1E24","created_at":"2020-05-11T22:13:49.847Z","updated_at":"2023-11-01T04:59:02.371Z","group_id":9970,"project_id":null,"template":false},{"id":10230929,"title":"type::feature","color":"#009966","description":"Any issue/MR that contains work to support the implementation of a feature and/or results in an improvement in the user experience. Read more at https://handbook.gitlab.com/handbook/product/groups/product-analysis/engineering/metrics/#work-type-classification","text_color":"#FFFFFF","created_at":"2019-04-09T12:29:58.492Z","updated_at":"2024-11-08T09:52:52.581Z","group_id":9970,"project_id":null,"template":false}]"></div> <div class="block milestone" data-testid="sidebar-milestones"> <div class="js-sidebar-milestone-widget-root" data-can-edit="" data-issue-iid="8377" data-project-path="gitlab-org/omnibus-gitlab"></div> </div> <div class="block" data-testid="iteration-container"><div class="js-sidebar-iteration-widget-root" data-can-edit="" data-group-path="gitlab-org" data-issue-id="140855729" data-issue-iid="8377" data-project-path="gitlab-org/omnibus-gitlab"></div> </div> <div class="js-sidebar-weight-widget-root" data-can-edit="" data-issue-iid="8377" data-project-path="gitlab-org/omnibus-gitlab"></div> <div class="js-sidebar-due-date-widget-root"></div> <div class="js-sidebar-time-tracking-root block">  <div class="title hide-collapsed gl-flex gl-justify-between gl-items-center !gl-mb-0"> <span class="gl-font-bold">Time tracking</span> <span class="gl-spinner-container" role="status"><span aria-hidden class="gl-spinner gl-spinner-sm gl-spinner-dark !gl-align-text-bottom"></span><span class="gl-sr-only !gl-absolute">Loading</span> </span> </div> </div> <div class="js-sidebar-health-status-widget-root" data-can-edit="" data-full-path="gitlab-org/omnibus-gitlab" data-iid="8377" data-issuable-type="issue"></div> <script id="js-confidential-issue-data" type="application/json">{"is_confidential":false,"is_editable":null}</script> <div class="block"> <div class="hide-collapsed gl-flex gl-items-center gl-font-bold gl-leading-20 gl-text-default"> Confidentiality </div> <div class="hide-collapsed gl-text-subtle"> Confidentiality controls have moved to the issue actions menu (<svg class="s12 gl-align-middle" data-testid="ellipsis_v-icon"><use href="/assets/icons-aa2c8ddf99d22b77153ca2bb092a23889c12c597fc8b8de94b0f730eb53513f6.svg#ellipsis_v"></use></svg>) at the top of the page. </div> </div> <div class="js-sidebar-participants-widget-root"></div> </form><script class="js-sidebar-options" type="application/json">{"endpoint":"/gitlab-org/omnibus-gitlab/-/issues/8377.json?serializer=sidebar_extras","toggleSubscriptionEndpoint":"/gitlab-org/omnibus-gitlab/-/issues/8377/toggle_subscription","moveIssueEndpoint":"/gitlab-org/omnibus-gitlab/-/issues/8377/move","projectsAutocompleteEndpoint":"/-/autocomplete/projects?project_id=20699","editable":"","currentUser":{},"rootPath":"/","fullPath":"gitlab-org/omnibus-gitlab","iid":8377,"id":140855729,"severity":"unknown","timeTrackingLimitToHours":true,"canCreateTimelogs":null,"createNoteEmail":null,"issuableType":"issue","directlyInviteMembers":"false","weightOptions":["None","Any",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20],"weightNoneValue":"None","multipleApprovalRulesAvailable":null}</script> </div> </aside> </main> </div> </div> </div> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ if ('loading' in HTMLImageElement.prototype) { document.querySelectorAll('img.lazy').forEach(img => { img.loading = 'lazy'; let imgUrl = img.dataset.src; // Only adding width + height for avatars for now if (imgUrl.indexOf('/avatar/') > -1 && imgUrl.indexOf('?') === -1) { const targetWidth = img.getAttribute('width') || img.width; imgUrl += `?width=${targetWidth}`; } img.src = imgUrl; img.removeAttribute('data-src'); img.classList.remove('lazy'); img.classList.add('js-lazy-loaded'); img.dataset.testid = 'js-lazy-loaded-content'; }); } //]]> </script> <script nonce="UbOhqz11IVjOReGnu8SOhQ=="> //<![CDATA[ gl = window.gl || {}; gl.experiments = {}; //]]> </script> </body> </html>

CINXE.COM

PostgreSQL maintenance_work_mem needs to be tuned for larger workloads (#8377) 路 Issues 路 GitLab.org / omnibus-gitlab 路 GitLab