From: Murthy Nunna <mnunna@fnal.gov>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Intermittent Query Performance Issue
Thread-Topic: Intermittent Query Performance Issue
Thread-Index: AdqSd/11UObFWsUNRjOPKTxjXtD1VA==
Date: Fri, 19 Apr 2024 17:02:41 +0000
Message-ID: 
 <DM8PR09MB66772143CBEAD3538AB98906B80D2@DM8PR09MB6677.namprd09.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?zHlBA2RPaKXZTyoNYSOxNaP/4GSDlzd2FsnueBpzn9AA1KyIQqRFNGtQjb+8?=
 =?us-ascii?Q?mmohY3HwEF4uP5Ad1IGSx2FSID/XgFArn2WmKCJMiGJrTZE5siL61tMBY3M8?=
 =?us-ascii?Q?w6eKf9cTFRqQnRBuqBeUVVSLUQHqfJS9M/JSH5DDPnli0e4hPmevcn4PgBOL?=
 =?us-ascii?Q?W4CsMPruxIjCTiJyJ43XZPWWEGOLU3DRiTlQ0aF0UdkwYKbmNBtLkKO0Et8A?=
 =?us-ascii?Q?3gvPDo/4JNMw8IoO+jUO8B3z8LmarQZs1HbUt3o6GZK0w+NF0O9d60cKoXpk?=
 =?us-ascii?Q?s7N7Tj1GMvfocnxZJTUCH9EH9ztxxfS/kgA4T3e0jcictyCtdL45UEC1ccHs?=
 =?us-ascii?Q?1exWjReNfLQKAJv3T0C23tX5F/puW5YYu7Mow4byf5CrqWg1RaNSiAu7C6mX?=
 =?us-ascii?Q?MUt/wDb24OSpp/qW547IqoCmK8i3gG7QAjByshA4+rSs2SCrVIoC6kIDrY+U?=
 =?us-ascii?Q?dMRtXA9YBbYozQlplFwlOQyzId5hitwxTlwxfCXWP6xKqlTfZatdtRu5FC4T?=
 =?us-ascii?Q?4xtzs2rqa5drfCT1lzQeaTmsMuThYenKcVTj41HitL9Et45KjLNEcrp/lpWu?=
 =?us-ascii?Q?BP6ITFeoBXB2kxyYMJ78mS0a9nZaLYku0troaldF9z/hXXDer7zoLqFKKc1Q?=
 =?us-ascii?Q?bFTgWfTkxk4gPfAPpXqiowGdBlnRA1wBI+dKQtGV/IOkK6M+sqiiRlyeay2m?=
 =?us-ascii?Q?QCNHJy4A55IaykGgeL+5llG7kRQfl+i3AQkISlnVOabHS7pFh2Y2ue+9p8p2?=
 =?us-ascii?Q?VMheqaNVZBOWsuKD5qMO+N4ju9gr3sh/+vg4MGzp0iRHhVE71rQv+Qsou/Sr?=
 =?us-ascii?Q?W9vDbgkylgmctyHcP0zjLNNnLGWa5awSqAbC7MQdpNAvXo1dx/5lw2Df2IvJ?=
 =?us-ascii?Q?m4iz2bfbUnrn4M8swk8ITO+vUZJeRwrSpymRTOFxpbnE229CaHSqtoue8Fwz?=
 =?us-ascii?Q?xeka+2LS5RORMHMIvapOSxY7Umyhu4AMMl2ksYnwOtIJU4QZhqdFxFNUtNP6?=
 =?us-ascii?Q?fyv7IbTNYwxuXsOE1NI3aVB9p1SuM8phiQDeLOmsZgkqB/SOcL1BdxgR24XP?=
 =?us-ascii?Q?EAGSVD9uk4C2B5OhnoXM/X42Y9jZptaeuOIlACVOl1fQiiVk9VfMyqoXE5Lr?=
 =?us-ascii?Q?fyW38tDjW6msZGnfaFsnYL5trVkZlZzd4R+1Qm6p9ct+5CdsbVuBmdCtocmz?=
 =?us-ascii?Q?6JkG/y3zF9E3W8amnmB4ivJR/TWGtxgzd42+IFjQa7rahv/LBn2BAV4TPEd9?=
 =?us-ascii?Q?3U8thU9NjWKtughx+33gvbwpMvjO7Y1ObLy0Zgw9b6gq+IrHk6V1/JPo9sfb?=
 =?us-ascii?Q?Er7ig56fju+Kj9vl30fyyR9wQaSfkSw/SoXEbAbv/h67jjjHgBjo/h2GQCnq?=
 =?us-ascii?Q?yoq8gp+TrkK9YfEToLO3Hz8j5ws/j1V0ShT/kC5mL3IaSl2BOq8p5YNSwR3Q?=
 =?us-ascii?Q?ZsVX6Rm4yE6O6fiHm2HQGP22lEPQ+vEGf1EiHNdNoz1erH/NoNr//e+Bn+j9?=
 =?us-ascii?Q?oAba4vp65aoWD3p58Jk3ZttWxyreO/10clYyNDxaHF4TmSV89ZVBZtpQDA19?=
 =?us-ascii?Q?cVsqDRYOSX+u7yAAbXuNj/oTCeziDkAsOaqibcCm?=
Content-Type: multipart/alternative;
	boundary="_000_DM8PR09MB66772143CBEAD3538AB98906B80D2DM8PR09MB6677namp_"
MIME-Version: 1.0
X-OriginatorOrg: fnal.gov
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DM8PR09MB6677.namprd09.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 61568817-dd6e-4295-ac83-08dc609286a4
X-MS-Exchange-CrossTenant-originalarrivaltime: 19 Apr 2024 17:02:41.9687
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MN2PR09MB4794
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/DM8PR09MB66772143CBEAD3538AB98906B80D2%40DM8PR09MB6677.namprd09.prod.outlook.com>
Precedence: bulk

--_000_DM8PR09MB66772143CBEAD3538AB98906B80D2DM8PR09MB6677namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

I am running pg 14.4

I have a simple query :
select max(c) from tab1 where name =3D 'xxx' ;

This query runs some times very slow. It takes about 40 minutes.
Most of the time it completes in few seconds.
When it runs slow, wait_event in pg_stat_activity shows "DataFileRead". Obv=
iously, it is reading from file storage and that is why it is taking so lon=
g.

This is a big table 512GB. It has some child tables.

Is there a way I can reproduce the slowness at will, so I can attempt to tu=
ne it? Or any ideas I can try to fix the issue? I hate to try something not=
 knowing if the problem is fixed or not. That is the reason I would like to=
 reproduce the issue first.

Thank you all!

--_000_DM8PR09MB66772143CBEAD3538AB98906B80D2DM8PR09MB6677namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Hi,<o:p></o:p></spa=
n></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">I am running pg 14.=
4<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">I have a simple que=
ry :<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">select max(c) from =
tab1 where name =3D 'xxx&#8217; ;<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">This query runs som=
e times very slow. It takes about 40 minutes.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Most of the time it=
 completes in few seconds.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">When it runs slow, =
wait_event in pg_stat_activity shows &#8220;DataFileRead&#8221;. Obviously,=
 it is reading from file storage and that is why it is taking so long.<o:p>=
</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">This is a big table=
 512GB. It has some child tables.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Is there a way I ca=
n reproduce the slowness at will, so I can attempt to tune it? Or any ideas=
 I can try to fix the issue? I hate to try something not knowing if the pro=
blem is fixed or not. That is the reason
 I would like to reproduce the issue first.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:12.0pt">Thank you all!<o:p>=
</o:p></span></p>
</div>
</body>
</html>

--_000_DM8PR09MB66772143CBEAD3538AB98906B80D2DM8PR09MB6677namp_--