Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGHeo-00EW1a-B5 for pgsql-general@arkaria.postgresql.org; Thu, 21 Dec 2023 11:56:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rGHem-007cRU-JQ for pgsql-general@arkaria.postgresql.org; Thu, 21 Dec 2023 11:56:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGHem-007cRM-77 for pgsql-general@lists.postgresql.org; Thu, 21 Dec 2023 11:56:04 +0000 Received: from mail-wm1-x32c.google.com ([2a00:1450:4864:20::32c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rGHei-00BUdD-Nq for pgsql-general@lists.postgresql.org; Thu, 21 Dec 2023 11:56:02 +0000 Received: by mail-wm1-x32c.google.com with SMTP id 5b1f17b1804b1-40d3dfcc240so5186425e9.1 for ; Thu, 21 Dec 2023 03:56:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1703159759; x=1703764559; darn=lists.postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=0I2aC1cNAuCEX/+50x+w5FXcfS3fwPiei1QIwidhPZo=; b=GC1ffehyLdDO7+LtGdqlmZFsFjIDYC1ofgHR/glhheJYzNZv3s7A5okpV6twkMq7Gm 8dyS5tMu+y1vLvooXNHRSj87qV9ZNDGeqjQEbKl9ehV0O7kFH0t2Jxy/87cr6Uw1jwSb h65zQWY9Nfeg1Cs0/Sl3xWQ1TRV0zSo+JY4qveqzmyszU4GodQODRYRvzAXuKHNs8QWW T6GayJ0QDM4yR9PE6yHh8QsV1IR4EuceCTbX0/ZkNUOVMYSmDnpJfZI+g2DfAsYUxvSL RV6BTlsLZqgUC8nrMZBXEm/7FaEpkL+NN5wgjAqkQa3k4u0oLrR9ESXmMcEfjkpuV5C4 LTeA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1703159759; x=1703764559; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=0I2aC1cNAuCEX/+50x+w5FXcfS3fwPiei1QIwidhPZo=; b=ZLBD+NeXBvOaWX2VcbPNLGZoyeibSF0y/nVEUNYPLZjfNvZI2Ol4UO1rzesaPEleXK LbGlpZPdprh2PuCRDdbz/sPZUGAi05n8GvBEZnAubYvT2M0k0s1aKEmI9E2dFdLny2Jw BCsOG29zsKEw/acJ9iU34qW/DGaI+mfNqWFgpZ1YJgPxq4Q8UkVQMDZTYQk/E56qqzsq I8cQy2hBqCJoSyj3xFYtbMsYGrECTk78aIUQqhQZZJNo0ggkFHM5amNmypt0A8nhs5VJ Gl0E6USpyKIhL2VxI5jb0uK7GPCSmFynAI6Sc4Ig0TjU3WNFCYp+aoJWNV7ZIPIBJyaT Z+gQ== X-Gm-Message-State: AOJu0YytwdxGCAtP/jSSjjCl4s3fTq/pAQpDs6d/Cif0c+Fb91WQEmBV vURTmD9VF1FcGq1M4xy2Lc4= X-Google-Smtp-Source: AGHT+IHjgAD6udCtCUznJPmj+hJBr+JZYl6ZMbq+WsmHr5biuEvHrnmZFzQdm4XB1e/nlboOqQi96w== X-Received: by 2002:a7b:cbd5:0:b0:40c:6e31:98f6 with SMTP id n21-20020a7bcbd5000000b0040c6e3198f6mr484273wmi.83.1703159758978; Thu, 21 Dec 2023 03:55:58 -0800 (PST) Received: from jrouhaud ([37.171.244.94]) by smtp.gmail.com with ESMTPSA id ay38-20020a05600c1e2600b0040d37e93cc5sm5373794wmb.27.2023.12.21.03.55.57 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 21 Dec 2023 03:55:58 -0800 (PST) Date: Thu, 21 Dec 2023 12:55:55 +0100 From: Julien Rouhaud To: Dominique Devienne Cc: pgsql-general@lists.postgresql.org Subject: Re: Help understanding server-side logging (and more...) Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On Thu, Dec 21, 2023 at 12:05:41PM +0100, Dominique Devienne wrote: > Hi. Another team (than mine) has been trying to troubleshoot hang issues in > their Node/JS/TS-based mid-tier services, > after a switch from MySQL to PostgreSQL. They run PostgreSQL in a Linux > container (see PID [1] below), unlike us. > They started looking at the PostgreSQL server-side logs, but with little to > no experience reading them, we'd like to > confirm the extract below looks normal, notably whether the various > exit-related messages are OK or not. All those messages are at DEBUG level. Anything abnormal would be emitted at a higher level. > > > FWIW, the symptoms they are experiencing is that initially all queries are > fast, then they start to get longer and longer, > to the point all services accessing PostgreSQL grind to a halt and > basically hang. While that happens, one can connect > to the DB from a shell (that cluster has a single DB) w/o issues, and run > queries just fine (they have little data, > in the few dozens MBs spread in 2 schemas and probably 2 or 3 dozen tables > max), thus I personally think the problem > is more on the client Node.JS side, but given that the same code worked > fine with MySQL, maybe I'm wrong. Yeah, "everything works normally except inside the app" is usually a good indication that it's unlikely to be a postgres problem. Maybe they have some locking issue, transactions that are opened for too long or something else but it's impossible to say without much more information. One sure thing is that running with logs at DEBUG level is going to add noticeable overhead.