Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pblyB-0007R9-Iv for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 17:28:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pblyA-000717-DC for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 17:28:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbly8-00070y-Iz for pgsql-general@lists.postgresql.org; Mon, 13 Mar 2023 17:28:22 +0000 Received: from wout3-smtp.messagingengine.com ([64.147.123.19]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbly5-0006xv-2x for pgsql-general@postgresql.org; Mon, 13 Mar 2023 17:28:19 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 23BFA32001C6; Mon, 13 Mar 2023 13:28:14 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Mon, 13 Mar 2023 13:28:14 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1678728493; x=1678814893; bh=FPLfTH3Rg9hc9gbFV2bw+EVi22PufJBbOe5 2glmYKcs=; b=M2+xEBAWsrNzyVaGcPcwV5CKIbb+a5k9DQ6a229WZD4znNloX4y ARVTyYBTf9NinTnpUJsFrTFOQPBOQ/eWBEHAsi/EEdnqXhMxcscMdiMsK6rwqarh ySnfRdKBY7aX+mdexC4yanqz8RQf/JO8ZcLa5tuTOMEypb9QRY/Ns3GMuG86ywJ8 z9jn95+1hYmnvsZUS/uFsfeyOTxbowRsBjBhn6ugeoBA2gCahAgC07h54NbU36ST IOF641yI8JIOURe9O954VEV/KCX7zQZ4WH94vr6d5ZncmxJagMemBFcwEvZiPoKT xW/zeh5NXos/7zVNfXOP+3ApCcZJYmbAmtA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1678728493; x= 1678814893; bh=FPLfTH3Rg9hc9gbFV2bw+EVi22PufJBbOe52glmYKcs=; b=j 2Itq90/qj9WA1jzTip52dWwCZcDRrYXdBPJIue6EWqyL44JVdSb+mjTqYLwWy8Fp tWtE4RprPNqwP2a9uE4aGHrA3JIy29BWwdsOx6IBsuinhMiHy/i/GKOuNjZwdCfL s6xc01PaSE2RVa4JVaoOvcfAx5qYdig+MgGu6i63xFzKLhUNqgpulwi+mWDMBNqo bleSvrTZzuPKeivOFsG3B+ZbwwsSF5WciGu/wo5FN4cA1nEmkj3s31sFlXSCxJn8 s9bAOcMv4a5MnhkJ1NQ0Pe7GV6xigqdhpbNHiM8x53r15RmixSsTPg+j960dyVtH e5e3RdZBB6rx67fp1M4hA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvddvgedgleelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepuffvfhfhkffffgggjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeduudefjeetveffhefgudehtdfhvdetkeeiueehfeev veduteekleeuheegudehhfenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 13 Mar 2023 13:28:13 -0400 (EDT) Subject: Re: Properly handle OOM death? To: Israel Brewster , PostgreSQL Mailing Lists References: From: Adrian Klaver Message-ID: <17d66a4c-4b05-106e-b2c7-e2babbd31de7@aklaver.com> Date: Mon, 13 Mar 2023 10:28:12 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/13/23 10:21 AM, Israel Brewster wrote: > I’m running a postgresql 13 database on an Ubuntu 20.04 VM that is a bit > more memory constrained than I would like, such that every week or so > the various processes running on the machine will align badly and the > OOM killer will kick in, killing off postgresql, as per the following > journalctl output: > > Mar 12 04:04:23 novarupta systemd[1]: postgresql@13-main.service: A > process of this unit has been killed by the OOM killer. > Mar 12 04:04:32 novarupta systemd[1]: postgresql@13-main.service: Failed > with result 'oom-kill'. > Mar 12 04:04:32 novarupta systemd[1]: postgresql@13-main.service: > Consumed 5d 17h 48min 24.509s CPU time. > > And the service is no longer running. > > When this happens, I go in and restart the postgresql service, and > everything is happy again for the next week or two. > > Obviously this is not a good situation. Which leads to two questions: > > 1) is there some tweaking I can do in the postgresql config itself to > prevent the situation from occurring in the first place? > 2) My first thought was to simply have systemd restart postgresql > whenever it is killed like this, which is easy enough. Then I looked at > the default unit file, and found these lines: > > # prevent OOM killer from choosing the postmaster (individual backends will > # reset the score to 0) > OOMScoreAdjust=-900 > # restarting automatically will prevent "pg_ctlcluster ... stop" from > working, > # so we disable it here. Also, the postmaster will restart by itself on most > # problems anyway, so it is questionable if one wants to enable external > # automatic restarts. > #Restart=on-failure > > Which seems to imply that the OOM killer should only be killing off > individual backends, not the entire cluster to begin with - which should > be fine. And also that adding the restart=on-failure option is probably > not the greatest idea. Which makes me wonder what is really going on? You might want to read: https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT > > Thanks. > > --- > Israel Brewster > Software Engineer > Alaska Volcano Observatory > Geophysical Institute - UAF > 2156 Koyukuk Drive > Fairbanks AK 99775-7320 > Work: 907-474-5172 > cell:  907-328-9145 > -- Adrian Klaver adrian.klaver@aklaver.com