From: Murthy Nunna <mnunna@fnal.gov>
To: Ron Johnson <ronljohnsonjr@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: RE: Intermittent Query Performance Issue
Date: Sat, 20 Apr 2024 02:44:57 +0000
Message-ID: <DM8PR09MB6677DA47FC6D842AEEF5D8EAB80C2@DM8PR09MB6677.namprd09.prod.outlook.com> (raw)
In-Reply-To: <CANzqJaCrc92PgnogA7o9qHuG3geAZjou8ZJyvYYE9c-nDGTYJQ@mail.gmail.com>
References: <DM8PR09MB66772143CBEAD3538AB98906B80D2@DM8PR09MB6677.namprd09.prod.outlook.com>
<CANzqJaCrc92PgnogA7o9qHuG3geAZjou8ZJyvYYE9c-nDGTYJQ@mail.gmail.com>
There are no other queries running at the time. pg_stat_activity shows only this long running query.
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Friday, April 19, 2024 9:08 PM
To: pgsql-admin@postgresql.org
Subject: Re: Intermittent Query Performance Issue
[EXTERNAL] – This message is from an external sender
On Fri, Apr 19, 2024 at 1:03 PM Murthy Nunna <mnunna@fnal.gov<mailto:mnunna@fnal.gov>> wrote:
Hi,
I am running pg 14.4
I have a simple query :
select max(c) from tab1 where name = '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”. Obviously, it is reading from file storage and that is why it is taking so long.
What else is happening on that system when it runs very slow? To me, “DataFileRead” screams IO contention.
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 tune 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mnunna@fnal.gov, ronljohnsonjr@gmail.com
Subject: RE: Intermittent Query Performance Issue
In-Reply-To: <DM8PR09MB6677DA47FC6D842AEEF5D8EAB80C2@DM8PR09MB6677.namprd09.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox