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 1pvHkp-0007bf-Bx for pgsql-general@arkaria.postgresql.org; Sat, 06 May 2023 13:15:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pvHkm-0003HF-JG for pgsql-general@arkaria.postgresql.org; Sat, 06 May 2023 13:15:12 +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 1pvHkl-0003H5-RF for pgsql-general@lists.postgresql.org; Sat, 06 May 2023 13:15:12 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pvHkj-000rJn-4l for pgsql-general@lists.postgresql.org; Sat, 06 May 2023 13:15:10 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-b9a6f17f2b6so18160498276.1 for ; Sat, 06 May 2023 06:15:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1683378908; x=1685970908; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=vD9OTik6QZWnxW/V+/uB8jvSTdtqXJ3ttbLPhiAFyeo=; b=HbdI1FFg1lueJdrqgrjIx33JWQXYAuNgFxJTeDOaNU1c7Ff9jA2EHi2iCS82CnV3cr NsfPnRLnubAOe0RIDpjy44UdJaQcfEjYEHfyDqgEp70XNV3IOlx0KQkt51tOnmSzwLbF gDbPRBxuU7adzYa6TeyqtVsROguxgMo6eh1Fbd1n2HlqgbyW8IhT5ikeVMFfLqzLwkhP 5dwHpvrhqKiirikIHr/QG1nRnhouksCHhWYwD88f6p79aPqdwJwX1QD8TTuwOcVxXhef IeBmZDBb8s+hwsjwHCykC91cv179/jelrl7Q1L3Wh9ZHeX27m0NbJ7S81iBMhLjivcxh Qftw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683378908; x=1685970908; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=vD9OTik6QZWnxW/V+/uB8jvSTdtqXJ3ttbLPhiAFyeo=; b=NpA/CVlSZxetxUS54L9PSTzXb+3HglNIRgSWNgEA4wa27OwTnRPTkO05DPXAWoJhWM QpZ5wabAjPpRdAhlgISSA1o3VBfae6oKIoMjFxoZWAaCMYUCshd3mdabXa5/yDg64Qwe at1XM0zEur+yPxiKlCW1U7APNeeNsIMTkctD+0rUoNEfK7OtwzLxQCpI3Q37uhA+B9Ta 0vQ9eJr26vDz2r4CG7Q5zJIXm3HSaN8DH/eXOELin2duZY75rshHbCu1ZOK6TLV9LQEe uw4H8Ig8DuAZ52iZ1Dk0QbHow+vVAJ4+pTF1Ay7nhjT0J8mfaGzQuJpdG2BzDsEicUqP Q+yA== X-Gm-Message-State: AC+VfDz1maLgRExr9D/PFCxhuyuUGiP/3ry6dIjnJjFl62FUWI3dgmcw poONd3rb4t/VG+VH7YGNM0t2dYZTWE8= X-Google-Smtp-Source: ACHHUZ5uk9kOCppKw9kF82/yk5GMpIwtsiuGapPMsz0RN/GRrP1W9wkYxAaPgQHiCPM2VfDoaDDrbg== X-Received: by 2002:a25:3452:0:b0:b9e:6d19:306a with SMTP id b79-20020a253452000000b00b9e6d19306amr7241459yba.16.1683378907987; Sat, 06 May 2023 06:15:07 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:9997:2896:95fb:af3b? ([2600:1700:bd2c:b810:9997:2896:95fb:af3b]) by smtp.googlemail.com with ESMTPSA id x75-20020a25ce4e000000b00b9e2ef25f1asm1088184ybe.44.2023.05.06.06.15.07 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 06 May 2023 06:15:07 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------EA8ZxHDj2Ekmbr0N6hDTZeS8" Message-ID: <38505370-ecbd-3af3-bcff-4cf2ec4903ff@gmail.com> Date: Sat, 6 May 2023 08:15:04 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Death postgres Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <20230506074625.ve4wbvt57cup63li@hjp.at> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------EA8ZxHDj2Ekmbr0N6hDTZeS8 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/6/23 07:19, Marc Millas wrote: > > > Le sam. 6 mai 2023 à 09:46, Peter J. Holzer a écrit : > > On 2023-05-06 03:14:20 +0200, Marc Millas wrote: > > postgres 14.2 on Linux redhat > > > > temp_file_limit set around 210 GB. > > > > a select request with 2 left join have crashed the server (oom > killer) after > > the postgres disk occupation did grow from 15TB to 16 TB. > "15TB" and "16TB" are pretty low-resolution.  For example, 15.4TB rounds *down* to 15TB, while 15.6TB rounds *up* to 16TB, while they are in fact only 200GB apart. Heck, even 15.4TB and 15.6TB are low-resolution.  temp_file_limit may actually be working. > > temp_file_limit limits the space a process may use on disk while the OOM > killer gets activated when the system runs out of RAM. So these seem to > be unrelated. > >         hp > > Its clear that oom killer is triggered by RAM and temp_file is a disk > thing... > But the sudden growth of disk space usage and RAM did happen exactly at > the very same time, with only one user connected, and only one query > running... If your question is about temp_file_limit, don't distract us with OOM issues. -- Born in Arizona, moved to Babylonia. --------------EA8ZxHDj2Ekmbr0N6hDTZeS8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 5/6/23 07:19, Marc Millas wrote:


Le sam. 6 mai 2023 à 09:46, Peter J. Holzer <hjp-pgsql@hjp.at> a écrit :
On 2023-05-06 03:14:20 +0200, Marc Millas wrote:
> postgres 14.2 on Linux redhat
>
> temp_file_limit set around 210 GB.
>
> a select request with 2 left join have crashed the server (oom killer) after
> the postgres disk occupation did grow from 15TB to 16 TB.

"15TB" and "16TB" are pretty low-resolution.  For example, 15.4TB rounds down to 15TB, while 15.6TB rounds up to 16TB, while they are in fact only 200GB apart.

Heck, even 15.4TB and 15.6TB are low-resolution.  temp_file_limit may actually be working.


temp_file_limit limits the space a process may use on disk while the OOM
killer gets activated when the system runs out of RAM. So these seem to
be unrelated.

        hp
Its clear that oom killer is triggered by RAM and temp_file is a disk thing... 
But the sudden growth of disk space usage and RAM did happen exactly at the very same time, with only one user connected, and only one query running...

If your question is about temp_file_limit, don't distract us with OOM issues.

--
Born in Arizona, moved to Babylonia.
--------------EA8ZxHDj2Ekmbr0N6hDTZeS8--