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 1rGcqs-00GFzH-41 for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Dec 2023 10:33:58 +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 1rGcqq-005Fuh-Om for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Dec 2023 10:33:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGcqq-005FuX-FQ for pgsql-hackers@lists.postgresql.org; Fri, 22 Dec 2023 10:33:56 +0000 Received: from feynman.df7cb.de ([195.49.152.168]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGcqo-00DKqD-1B for pgsql-hackers@lists.postgresql.org; Fri, 22 Dec 2023 10:33:55 +0000 Received: from msg.df7cb.de (unknown [IPv6:2a02:908:1482:3660:b7d0:f584:a987:bc23]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-256) server-signature ECDSA (P-384) server-digest SHA384) (Client did not present a certificate) by feynman.df7cb.de (Postfix) with ESMTPSA id 4SxNv517h4z3G0M; Fri, 22 Dec 2023 11:33:53 +0100 (CET) Date: Fri, 22 Dec 2023 11:33:52 +0100 From: Christoph Berg To: Robert Haas Cc: Laurenz Albe , pgsql-hackers@lists.postgresql.org Subject: Re: Set log_lock_waits=on by default Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Re: Robert Haas > On Thu, Dec 21, 2023 at 8:29 AM Laurenz Albe wrote: > > Here is a patch to implement this. > > Being stuck behind a lock for more than a second is almost > > always a problem, so it is reasonable to turn this on by default. > > I think it depends somewhat on the lock type, and also on your > threshold for what constitutes a problem. For example, you can wait > for 1 second for a relation extension lock pretty easily, I think, > just because the I/O system is busy. Or I think also a VXID lock held > by some transaction that has a tuple locked could be not particularly > exciting. A conflict on a relation lock seems more likely to represent > a real issue, but I guess it's all kind of a judgement call. A second > isn't really all that long on an overloaded system, and I see an awful > lot of overloaded systems (because those are the people who call me). If a system is so busy that it's waiting so long for the disk, I would like PG to tell me about it. Likewise, if my transactions are slow because they are waiting for each other, I'd also like PG to tell me. Especially as the 2nd condition can't be seen by "it's slow because CPU or IO is at 100%". In any case, setting log_lock_waits=on by default helps. In fact, everyone I talked to was wondering why log_checkpoints was turned on by default, and not this parameter. The info provided by log_lock_waits is much more actionable than the stream of log_checkpoint messages. > Just a random idea but what if we separated log_lock_waits from > deadlock_timeout? Say, it becomes time-valued rather than > Boolean-valued, but it has to be >= deadlock_timeout? Because I'd > probably be more interested in hearing about a lock wait that was more > than say 10 seconds, but I don't necessarily want to wait 10 seconds > for the deadlock detector to trigger. That's also a good point, but I'd like to see log_lock_waits default to 'on' independently from having this extra change. > In general, I do kind of like the idea of trying to log more problem > situations by default, so that when someone has a major issue, you > don't have to start by having them change all the logging settings and > then wait until they get hosed a second time before you can > troubleshoot anything. I'm just concerned that 1s might be too > sensitive for a lot of users who aren't as, let's say, diligent about > keeping the system healthy as you probably are. I don't think 1s would be too sensitive by default. Christoph