Обсуждение: Intermittent Query Performance Issue

Поиск
Список
Период
Сортировка

Intermittent Query Performance Issue

От
Murthy Nunna
Дата:

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!

Re: Intermittent Query Performance Issue

От
Ron Johnson
Дата:
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.



RE: Intermittent Query Performance Issue

От
Murthy Nunna
Дата:

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> 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.

 

 

Re: Intermittent Query Performance Issue

От
Scott Ribe
Дата:
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.

Re: Intermittent Query Performance Issue

От
Jeff Janes
Дата:
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