Subject: Re: How to analyze of short but heavy intermittent slowdown on BIND
 on production database (or BIND vs log_lock_waits)
To: Maxim Boguk <maxim.boguk@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CAK-MWwSfx7SmUQ=_rB4q61sE8uP6LqH-1wM0DSYyBadjBe9BDw@mail.gmail.com>
 <20221231143203.GK1153@telsasoft.com>
 <CAK-MWwTRVNoDnjeKh-03cpubS9D1hhY-UofwYQChtLM2Y1EwWg@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <2f7a8841-71c7-03e7-1bd5-2b1f95d24c16@sqlexec.com>
Date: Sun, 1 Jan 2023 08:27:34 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.59
MIME-Version: 1.0
In-Reply-To: 
 <CAK-MWwTRVNoDnjeKh-03cpubS9D1hhY-UofwYQChtLM2Y1EwWg@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------7983FAFAF854417F976B0B8E"
Content-Language: en-US
UI-OutboundReport: notjunk:1;M01:P0:Oh9pn/xPb6A=;ReT2Y88oyrMVtkW1zdBfYXWi0SX
 Kr+ELhXczjQTWJe/y1RBeLv5HW4QpNqIxcpLQDat1eF4eGd2g2+xzdUs2Kwf5ZBz7+GXTbes7
 hMHBPgIGucutGmeHFwbAj8HiI6DX8ruLXqCZCNQTvK1oeg5t8t7D92d1E19frlIRlUn5eYTvX
 CT9c5rdRR2SEoR623iRqWk5BpC6jJJEyMNvv53tQP6RxwKYWfKQd37X/N4wTDvy23qnGqZgHF
 /Sq3gP2oul/I/Y1ysmSAmeJKdxOAtzb1S0JKtxQ1+BGFQvtUzoCTZdFTr2QvdOp10elLhESc2
 eKNsoDue02utM/SrEPoT2A4f7JHwBzKQo+omj5ksLUb5J/CfH2+CKyGC+nEZNNoQNcos9hEIm
 eBi1a7P9izrpLPvXWktxGc0SJd1YUUq3P2y3qqnrIXdDMuHfCG339YfOtx11JoJu5gHTmKUta
 n3dXyvpnYg1zWoaqv0NY2GAhaf43UBVWsq0/li1arj2IdArIkbYDsaesalxRsLOHCGaXy9Scp
 3VdlKJ6MqJVMewGvpbRgR2cOMrLu2HyIrr5R2AAMuuD5tQgOn3q/NT35IvrQYcOs9Kd4Sh6xi
 ubidt+DiTFDQQX2tjGkDWcz+QRpzOdjWJ+xOCG/cSu9xArihYDvbtZs6L0G5Zc3Rij45Yefwf
 eZZUeH9ENS79GtJ4YENkTij5Vx3dC/qizlKT55IWLQ==
Archived-At: 
 <https://www.postgresql.org/message-id/2f7a8841-71c7-03e7-1bd5-2b1f95d24c16%40sqlexec.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------7983FAFAF854417F976B0B8E
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Howdy,

Few additional questions:

 1. How many concurrent, active connections are running when these BIND
    problems occur?  select count(*) from pg_stat_activity where state
    in ('active','idle in transaction')
 2. Are the queries using gigantic IN (<big list>) values?
 3. Perhaps unrelated, but islog_temp_files turned on, and if so, do you
    have a lot of logs related to that?

Regards,
Michael Vitale, just another PG DBA


--------------7983FAFAF854417F976B0B8E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html theme="default-light" iconset="color"><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body text="#000000">Howdy,<br>
<br>
Few additional questions:<br>
<ol>
  <li>How many concurrent, active connections are running when these 
BIND problems occur?  select count(*) from pg_stat_activity where state 
in ('active','idle in transaction')</li>
  <li>Are the queries using gigantic IN (&lt;big list&gt;) values?</li>
  <li>Perhaps unrelated, but is<span style="font-weight: bold;"> 
log_temp_</span><span style="font-weight: bold;">files</span> turned on,
 and if so, do you have a lot of logs related to that?</li>
</ol>
<p>Regards,<br>
Michael Vitale, just another PG DBA<br>
</p>
<br>
<br>
<br>
<br>
<br>
<br>
</body></html>

--------------7983FAFAF854417F976B0B8E--