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 1pjJNN-0000Pj-Kw for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 12:33:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjJNL-0000NG-84 for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 12:33:31 +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 1pjJNK-0000N7-Fl for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 12:33:30 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pjJNC-000d2G-K5 for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 12:33:29 +0000 Received: by mail-yb1-xb36.google.com with SMTP id n125so34490781ybg.7 for ; Mon, 03 Apr 2023 05:33:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1680525200; x=1683117200; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=n+TlFH6gYfwMqqllZXKQtwqnnw5u4bz3uPlBCJ+2PLI=; b=OznR5cCcFzzdTKV3Cd6AuLAS+ClVVOHw/WM1Vdt5aRs6GCwxt4fFyq/699VtfGgXFV axN0k8NvdV6hvt4+9ruJOirXJe7ToPluXDNvpltzEIPqV7BSZALObBKzJXf30eBEPhPb VlfX5qzi8uWEvYAfNQwa4gFMVsX/j2/yx+bt3xGiquME7S7nMjRFceDGSvXkEPNRH9CC W2J6gAFPRSNelySDFbx0J6Z2hzmMZWfqKLGmb31kyF28IORUFi765HtV8n2IMnPNM1Wv 4MTsje9mCyWzWAfWtzTq8J+BEadyXPRIHjdlssgMbndIXmNCB+vFssmc/08oZFedRkBb M3cQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680525200; x=1683117200; h=content-transfer-encoding:in-reply-to:from:references:cc: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=n+TlFH6gYfwMqqllZXKQtwqnnw5u4bz3uPlBCJ+2PLI=; b=u7AqMoIhNjd9DbZ8iEBIKgSaObfO6X39DlYPug/0WqmyNfPT98tD+c/pLSM8MHTa7U fWGA7gYgARzbCshqigASnxVFy/iX8QadbOsh5xVMrQt5/5Tf10GhyQS77lZKiCKfHfbp XNhA+pBc9gHnvZXNBI064pIbC3RfTiDCSlGYazzAGSFIherOaGHk77YBSKHex4aoUwtJ lE2+FgTUC62S1V1huol1Qk7Xo+isCKV9aR0rJ4T6u/NIlWpcVrgnszLRVMg7eGY+6r7y NoeIsR3rnV53DIcqhkQkzpJ1QAXyUDhloaNLxSBUiunGVfPDlZ1U6JmX/s0eTuWA3+UZ Gm8g== X-Gm-Message-State: AAQBX9ex6Vj2g54YOcuxP/L+7CfcBtlH21iMKjNWYv3lZNOyyKt1JKWg 2l6ysP4cxWq0nFtLP28Ol5fPHg== X-Google-Smtp-Source: AKy350apavt9f15hwqhLaotedcIK5rhjAgODm3rMkr9BZt2Grmn1FDgs93cMr8AxZkht4b0s9wo3kQ== X-Received: by 2002:a25:b119:0:b0:b44:ab4f:ba6c with SMTP id g25-20020a25b119000000b00b44ab4fba6cmr31907847ybj.41.1680525200348; Mon, 03 Apr 2023 05:33:20 -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 18-20020a250212000000b00b7767ca746dsm2531721ybc.10.2023.04.03.05.33.19 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 03 Apr 2023 05:33:20 -0700 (PDT) Message-ID: Date: Mon, 3 Apr 2023 08:33:19 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: Very slow queries followed by checkpointer process killed with signal 9 Content-Language: en-US To: Tom Lane , Jason McLaurin Cc: pgsql-general@lists.postgresql.org References: <1077329.1680479022@sss.pgh.pa.us> <1090044.1680486018@sss.pgh.pa.us> From: Joe Conway In-Reply-To: <1090044.1680486018@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/2/23 21:40, Tom Lane wrote: > Jason McLaurin writes: >> Is there anywhere you'd suggest we start looking for hints? I'd be >> interested in increasing relevant logging verbosity so that we can see when >> key background processes are running, both in Postgres core and Timescale. > > It might be helpful to try to identify which wait events the slow > queries are blocking on (pg_stat_activity.wait_event_type and > .wait_event). I'm not sure if you're going to be able to extract > useful data, because your query on pg_stat_activity is likely to > be slow too. But it's a place to start. > > Also, given that you're evidently incurring the wrath of the OOM > killer, you should try to understand why the kernel thinks it's > under memory pressure. Do you have too many processes, or perhaps > you've configured too much shared memory? Given this: > This is Postgres 14.5 running in the TimescaleDB Docker image. Possibly the docker image is running with a cgroup memory.limit set? The OOM killer will trigger on any cgroup limit even if the host has plenty of free memory. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com