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 1pbm6B-0007v8-Pq for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 17:36:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pbm6A-0006C6-M2 for pgsql-general@arkaria.postgresql.org; Mon, 13 Mar 2023 17:36:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbm69-0006Bx-UF for pgsql-general@lists.postgresql.org; Mon, 13 Mar 2023 17:36:38 +0000 Received: from mail-qt1-x831.google.com ([2607:f8b0:4864:20::831]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pbm66-0001WJ-N1 for pgsql-general@postgresql.org; Mon, 13 Mar 2023 17:36:37 +0000 Received: by mail-qt1-x831.google.com with SMTP id c3so14012836qtc.8 for ; Mon, 13 Mar 2023 10:36:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1678728993; h=content-transfer-encoding:in-reply-to:subject:from:references:to :content-language:user-agent:mime-version:date:message-id:from:to:cc :subject:date:message-id:reply-to; bh=lMnrRu/sQz63LChkeqIxe5hH83zLdVCQhPWV+cDjimA=; b=T9kv2KqBNDTE6FoKyoHG3F/vRAX7QqzQmmk2zaVI5oAfgGN2TbSmMBCcKC5CJuyLg9 a4OMueu+BxDe8jul3EFWBwhv5qymR7g8o0hV3cfH77+PTVxluDBtjphQRnDlJYQSmrk/ iu91cNZsZlJZq82k2U2xhDfVLr8yzWYd+JbCTaBVaxCkfuj0fCW3hhMEApNYS70y3Ngg B2tys2tL44bPcpdbuL8KMYQa0X8ITR56anHk1toLdGOLCJkF7t6Yte3MITdw+A2Fpfxu jFVSFol18tTjQcGdTkG/qmCsW2j6xG4b3bjpTL7ZJAa2OMsaFm9OfHAYk7kFUenLnxvz gVEA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678728993; h=content-transfer-encoding:in-reply-to:subject:from:references:to :content-language:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=lMnrRu/sQz63LChkeqIxe5hH83zLdVCQhPWV+cDjimA=; b=qnsVfILMg//NVfuPORo8IfZ40k51L/AGbA6mXeoIFdtO9NIJ2yUn1c2nTqgCYGjpH0 OCRMRoGwBk2ECKu/mvW97AV+bZP56d2U8Jl2401LGD9ahOuegG0R8GUulanTIEE4nVok qJ7IRshi0oacFrT/qzdosZwowJnq43EDA857j+sgc1krTM7RRNKzzJcUUyvAIbu+w0H5 s5W+ALi5jpYLxdnT/rTsioBgyRO0illoq0b7ZlPyqAYrdMum5qEu6FrOr323cCc5EUlx cg0nD22xr/ejyDe8l9noU3of3Ux3wbv6lsVDstkqZwHPgBgh3hJO6ti9qAadz72LWnTs n3LQ== X-Gm-Message-State: AO0yUKUTwOP7xiX8usKcfwZfCnxSw76DS1uHo9StND/moc4TnkkR/XJm 7pUV+Sg38J/8m3GFNIm8kKvsdA+uevmIIq1xkEU= X-Google-Smtp-Source: AK7set/yj3GKJ8UGxiXdedrxl3PigzmF6tsQeDKSuOVEKLsiN+qp5IRz8ILNriKzY1LNpMr5Yv08DQ== X-Received: by 2002:a05:622a:1d6:b0:3b8:2504:e90a with SMTP id t22-20020a05622a01d600b003b82504e90amr18569263qtw.3.1678728992747; Mon, 13 Mar 2023 10:36:32 -0700 (PDT) Received: from [192.168.4.41] (162-239-31-113.lightspeed.dybhfl.sbcglobal.net. [162.239.31.113]) by smtp.gmail.com with ESMTPSA id s15-20020ac85ecf000000b003b86b962030sm199222qtx.72.2023.03.13.10.36.32 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 13 Mar 2023 10:36:32 -0700 (PDT) Message-ID: <522bcfcb-05d5-9f54-947e-ac4c7a8ba1da@joeconway.com> Date: Mon, 13 Mar 2023 13:36:31 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Content-Language: en-US To: Israel Brewster , PostgreSQL Mailing Lists References: From: Joe Conway Subject: Re: Properly handle OOM death? In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/13/23 13:21, 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? First, are you running with a cgroup memory.limit set (e.g. in a container)? Assuming no, see: https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT That will tell you: 1/ Turn off memory overcommit: "Although this setting will not prevent the OOM killer from being invoked altogether, it will lower the chances significantly and will therefore lead to more robust system behavior." 2/ set /proc/self/oom_score_adj to -1000 rather than -900 (OOMScoreAdjust=-1000): the value -1000 is important as it is a "magic" value which prevents the process from being selected by the OOM killer (see: https://elixir.bootlin.com/linux/latest/source/include/uapi/linux/oom.h#L6) whereas -900 just makes it less likely. All that said, even if the individual backend gets killed, the postmaster will still go into crash recovery. So while technically postgres does not restart, the effect is much the same. So see #1 above as your best protection. HTH, Joe -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com