pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Intermittent Query Performance Issue 5+ messages / 4 participants [nested] [flat]
* Intermittent Query Performance Issue @ 2024-04-19 17:02 Murthy Nunna <mnunna@fnal.gov> 0 siblings, 2 replies; 5+ messages in thread From: Murthy Nunna @ 2024-04-19 17:02 UTC (permalink / raw) To: pgsql-admin 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. 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. Thank you all! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Intermittent Query Performance Issue @ 2024-04-20 02:08 Ron Johnson <ronljohnsonjr@gmail.com> parent: Murthy Nunna <mnunna@fnal.gov> 1 sibling, 1 reply; 5+ messages in thread From: Ron Johnson @ 2024-04-20 02:08 UTC (permalink / raw) To: pgsql-admin On Fri, Apr 19, 2024 at 1:03 PM Murthy Nunna <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. > ^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Intermittent Query Performance Issue @ 2024-04-20 02:44 Murthy Nunna <mnunna@fnal.gov> parent: Ron Johnson <ronljohnsonjr@gmail.com> 0 siblings, 1 reply; 5+ messages in thread From: Murthy Nunna @ 2024-04-20 02:44 UTC (permalink / raw) To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Intermittent Query Performance Issue @ 2024-04-20 03:10 Scott Ribe <scott_ribe@elevated-dev.com> parent: Murthy Nunna <mnunna@fnal.gov> 0 siblings, 0 replies; 5+ messages in thread From: Scott Ribe @ 2024-04-20 03:10 UTC (permalink / raw) To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin I wonder if it could be cache eviction? You query runs fast when relevant parts of the table are in cache, and other times, other queries (or system activity) have forced that data out of cache. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Intermittent Query Performance Issue @ 2024-04-20 12:26 Jeff Janes <jeff.janes@gmail.com> parent: Murthy Nunna <mnunna@fnal.gov> 1 sibling, 0 replies; 5+ messages in thread From: Jeff Janes @ 2024-04-20 12:26 UTC (permalink / raw) To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin On Fri, Apr 19, 2024 at 1:02 PM Murthy Nunna <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. > It sounds like your query is walking down an index on "c", then stopping once it finds a single row where name = 'xxx’. How long this will take depends on how high the max value within 'xxx' is relative to all the other values. If that is the case, then the constant value for 'xxx' should be slow every time, until the data changes. This could be fixed by having a multicolumn index on (name, c). An alternative explanation could be a huge chunk of rows with a high value of c have recently been deleted, or have been inserted but not committed. Then your query would need to wage through all those invisible rows looking for one visible one. Cheers, Jeff > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-04-20 12:26 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-19 17:02 Intermittent Query Performance Issue Murthy Nunna <mnunna@fnal.gov> 2024-04-20 02:08 ` Ron Johnson <ronljohnsonjr@gmail.com> 2024-04-20 02:44 ` Murthy Nunna <mnunna@fnal.gov> 2024-04-20 03:10 ` Scott Ribe <scott_ribe@elevated-dev.com> 2024-04-20 12:26 ` Jeff Janes <jeff.janes@gmail.com>
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