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 1rXj9C-00CIjb-BI for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 14:43:35 +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 1rXj9B-00BHuU-A7 for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 14:43:33 +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 1rXj9A-00BHsc-Ll for pgsql-hackers@lists.postgresql.org; Wed, 07 Feb 2024 14:43:33 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXj97-005YJD-TL for pgsql-hackers@lists.postgresql.org; Wed, 07 Feb 2024 14:43:31 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 026155F7B9; Wed, 7 Feb 2024 09:43:23 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1707317004; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=qbIQtFA0fvv+d+zaIVTpxBB5+u0zfkvfzgpJJBMOx8o=; b=UV2ZoGVrwlvyeW1PMAq+QBMAuy8+luGwo8lbdOsHIbzIordsr6dgcqHpl6a80Gm2NAjFeq jdlNWK446kDFf6b008XCAir+NElQqcRw2BNBymewm+YMvH2vgkY6gLo74On/+FQe+sQ+jj ZY0AZzzc07VpIrnRdX+FFhSd4DM76a8= Date: Wed, 7 Feb 2024 09:43:23 -0500 From: Stephen Frost To: Laurenz Albe Cc: Tom Lane , Nathan Bossart , Michael Banck , pgsql-hackers@lists.postgresql.org Subject: Re: Set log_lock_waits=on by default Message-ID: References: <659ffa38.170a0220.eea6a.28fc@mx.google.com> <65b146b9.170a0220.c645b.0891@mx.google.com> <20240206165343.GA3841415@nathanxps13> <524751.1707240550@sss.pgh.pa.us> <3f5c32548cf2db3f156c18f8c43b9804e72be8c7.camel@cybertec.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="1ys8PQfNQp6rmT88" Content-Disposition: inline In-Reply-To: <3f5c32548cf2db3f156c18f8c43b9804e72be8c7.camel@cybertec.at> User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --1ys8PQfNQp6rmT88 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Laurenz Albe (laurenz.albe@cybertec.at) wrote: > On Tue, 2024-02-06 at 12:29 -0500, Tom Lane wrote: > > Nathan Bossart writes: > > > It looks like there are two ideas: > > > [...] > > > * Set log_lock_waits on by default. The folks on this thread seem to > > > support this idea, but given the lively discussion for enabling > > > log_checkpoints by default [0], I'm hesitant to commit something li= ke > > > this without further community discussion. > >=20 > > I was, and remain, of the opinion that that was a bad idea that > > we'll eventually revert, just like we previously got rid of most > > inessential log chatter in the default configuration. So I doubt > > you'll have much trouble guessing my opinion of this one. I think > > the default logging configuration should be chosen with the > > understanding that nobody ever looks at the logs of most > > installations, and we should be more worried about their disk space > > consumption than anything else. Admittedly, log_lock_waits is less > > bad than log_checkpoints, because no such events will occur in a > > well-tuned configuration ... but still, it's going to be useless > > chatter in the average installation. >=20 > Unsurprisingly, I want to argue against that. I tend to agree with this position- log_checkpoints being on has been a recommended configuration for a very long time and is valuable information to have about what's been happening when someone does go and look at the log. Having log_lock_waits on by default is likely to be less noisy and even more useful for going back in time to figure out what happened. > You say that it is less bad than "log_checkpoints =3D on", and I agree. > I can't remember seeing any complaints by users about > "log_checkpoints", and I predict that the complaints about > "log_lock_waits =3D on" will be about as loud. Yeah, agreed. > I am all for avoiding useless chatter in the log. In my personal > experience, that is usually "database typo does not exist" and > constraint violation errors. I always recommend people to enable > "log_lock_waits", and so far I have not seen it spam the logs. I really wish we could separate out the messages about typos and constraint violations from these logs about processes waiting a long time for locks or about checkpoints or even PANIC's or other really important messages. That said, that's a different problem and not something this change needs to concern itself with. As for if we want to separate out log_lock_waits from deadlock_timeout- no, I don't think we do, for the reasons that Tom mentioned. I don't see it as necessary either for enabling log_lock_waits by default. Waiting deadlock_timeout amount of time for a lock certainly is a problem already and once we've waited that amount of time, I can't see the time spent logging about it as being a serious issue. +1 for simply enabling log_lock_waits by default. All that said ... if we did come up with a nice way to separate out the timing for deadlock_timeout and log_lock_waits, I wouldn't necessarily be against it. Perhaps one approach to that would be to set just one timer but have it be the lower of the two, and then set another when that fires (if there's more waiting to do) and then catch it when it happens... Again, I'd view this as some independent improvement though and not a requirement for just enabling log_lock_waits by default. Thanks, Stephen --1ys8PQfNQp6rmT88 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmXDlwkACgkQ7WyKOINH ZFUxUxAAmGajAa/005aQaz/Cu+Iih3idfOzzQzLdG3wl0x/cmIjkY+S9ZbA9ySWJ DpUulCFpUnprDog+T9UybHkBzU50o4RFsebJ0p5gQOu7qGnehdFoDtHNipUyR5zm 6fW+I9XhIs/5gVRrPet7WVHfZZms2I3Y8hMZ37Fx/M4NT9N1I5vLlYS7vXLAEXg/ mQRKPGvEONSTnMCCsmopEK4R1whfQMGOqm60G01UpNqc6gHsU0zEqhnZ/1rvd2g+ Kxk3Us0Kr6b8r7IM2LH4JvYuPLOgOj0ol6Y7JfTQiJthPebtR+1Ueve8G8keOYnP PuY7H6oHZ6CxRu0bj+bT2AF5uFIVX3SzOSLU61QxvqDZieMZXQxIH6fR+AULtH0D fAZsnfRpit49rH9i+oMP9gdpLXlRhZTa3I0tAjr53o3P+s9Icgf3CXraPNCwIl0z zTn1siPiqA+hpSbtG4FRfEZDBVR5Xyvj57oGWQ7FLInBbAujys2JEy7uof0QG1Kh soID2E4BAPu1OizU5mQ7l2/nM+EAQiFylC0Ub8/LDCGt3HqCbivz018R1Z2XrQD2 K4VtixngYT5hgmtmbRaQDxzVaLqTnVFjMPIgxq6BW46EOiMN5YnySVUTazKEGU9N Tt25DmeuVXgam7KZPVseOCXtq5oZ+5nXVoxnmjpspBRKkS9C7b8= =589W -----END PGP SIGNATURE----- --1ys8PQfNQp6rmT88--