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 1pj9BN-0003VS-Bx for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 01:40:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pj9BL-0004dk-K1 for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 01:40:27 +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 1pj9BL-0004d9-9D for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 01:40:27 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pj9BE-0005zO-Ns for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 01:40:26 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3331eIEJ1090045; Sun, 2 Apr 2023 21:40:18 -0400 From: Tom Lane To: Jason McLaurin cc: pgsql-general@lists.postgresql.org Subject: Re: Very slow queries followed by checkpointer process killed with signal 9 In-reply-to: References: <1077329.1680479022@sss.pgh.pa.us> Comments: In-reply-to Jason McLaurin message dated "Sun, 02 Apr 2023 20:21:40 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1090043.1680486018.1@sss.pgh.pa.us> Date: Sun, 02 Apr 2023 21:40:18 -0400 Message-ID: <1090044.1680486018@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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? regards, tom lane