agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedQuery out of memory
14+ messages / 8 participants
[nested] [flat]
* Query out of memory
@ 2021-10-18 16:42 aditya desai <admad123@gmail.com>
0 siblings, 4 replies; 14+ messages in thread
From: aditya desai @ 2021-10-18 16:42 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi,
I am running the below query. Table has 21 million records. I get an Out Of
Memory error after a while.(from both pgadmin and psql). Can someone review
DB parameters given below.
select t.*,g.column,a.column from
gk_staging g, transaction t,account a
where
g.accountcodeis not null AND
g.accountcode::text <> '' AND
length(g.accountcode)=13 AND
g.closeid::text=t.transactionid::text AND
subsrting(g.accountcode::text,8)=a.mask_code::text
Below are system parameters.
shared_buffers=3GB
work_mem=2GB
effective_cache_size=10GB
maintenance_work_mem=1GB
max_connections=250
I am unable to paste explain plan here due to security concerns.
Regards,
Aditya.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-18 16:43 aditya desai <admad123@gmail.com>
parent: aditya desai <admad123@gmail.com>
3 siblings, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-10-18 16:43 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Database has 20GB RAM.
On Mon, Oct 18, 2021 at 10:12 PM aditya desai <admad123@gmail.com> wrote:
> Hi,
> I am running the below query. Table has 21 million records. I get an Out
> Of Memory error after a while.(from both pgadmin and psql). Can someone
> review DB parameters given below.
>
> select t.*,g.column,a.column from
> gk_staging g, transaction t,account a
> where
> g.accountcodeis not null AND
> g.accountcode::text <> '' AND
> length(g.accountcode)=13 AND
> g.closeid::text=t.transactionid::text AND
> subsrting(g.accountcode::text,8)=a.mask_code::text
>
> Below are system parameters.
> shared_buffers=3GB
> work_mem=2GB
> effective_cache_size=10GB
> maintenance_work_mem=1GB
> max_connections=250
>
> I am unable to paste explain plan here due to security concerns.
>
> Regards,
> Aditya.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-18 16:48 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: aditya desai <admad123@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Vijaykumar Jain @ 2021-10-18 16:48 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Work memory 2 GB ?
Is this intentional?
How many max active connections do you see?
if you have too many connections. You can try toning it down to
https://pgtune.leopard.in.ua/ to start with.
On Mon, Oct 18, 2021, 10:13 PM aditya desai <admad123@gmail.com> wrote:
> Hi,
> I am running the below query. Table has 21 million records. I get an Out
> Of Memory error after a while.(from both pgadmin and psql). Can someone
> review DB parameters given below.
>
> select t.*,g.column,a.column from
> gk_staging g, transaction t,account a
> where
> g.accountcodeis not null AND
> g.accountcode::text <> '' AND
> length(g.accountcode)=13 AND
> g.closeid::text=t.transactionid::text AND
> subsrting(g.accountcode::text,8)=a.mask_code::text
>
> Below are system parameters.
> shared_buffers=3GB
> work_mem=2GB
> effective_cache_size=10GB
> maintenance_work_mem=1GB
> max_connections=250
>
> I am unable to paste explain plan here due to security concerns.
>
> Regards,
> Aditya.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-18 19:03 aditya desai <admad123@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: aditya desai @ 2021-10-18 19:03 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Not many active connections. Only 30-40.
On Monday, October 18, 2021, Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:
> Work memory 2 GB ?
> Is this intentional?
> How many max active connections do you see?
>
> if you have too many connections. You can try toning it down to
> https://pgtune.leopard.in.ua/ to start with.
>
>
> On Mon, Oct 18, 2021, 10:13 PM aditya desai <admad123@gmail.com> wrote:
>
>> Hi,
>> I am running the below query. Table has 21 million records. I get an Out
>> Of Memory error after a while.(from both pgadmin and psql). Can someone
>> review DB parameters given below.
>>
>> select t.*,g.column,a.column from
>> gk_staging g, transaction t,account a
>> where
>> g.accountcodeis not null AND
>> g.accountcode::text <> '' AND
>> length(g.accountcode)=13 AND
>> g.closeid::text=t.transactionid::text AND
>> subsrting(g.accountcode::text,8)=a.mask_code::text
>>
>> Below are system parameters.
>> shared_buffers=3GB
>> work_mem=2GB
>> effective_cache_size=10GB
>> maintenance_work_mem=1GB
>> max_connections=250
>>
>> I am unable to paste explain plan here due to security concerns.
>>
>> Regards,
>> Aditya.
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-18 21:05 Guillaume Lelarge <guillaume@lelarge.info>
parent: aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Guillaume Lelarge @ 2021-10-18 21:05 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
Le lun. 18 oct. 2021 à 21:03, aditya desai <admad123@gmail.com> a écrit :
> Not many active connections. Only 30-40.
>
This means you can consume up to 60-80 GB. Way much more than the available
RAM. You should lower your work_mem value.
On Monday, October 18, 2021, Vijaykumar Jain <
> vijaykumarjain.github@gmail.com> wrote:
>
>> Work memory 2 GB ?
>> Is this intentional?
>> How many max active connections do you see?
>>
>> if you have too many connections. You can try toning it down to
>> https://pgtune.leopard.in.ua/ to start with.
>>
>>
>> On Mon, Oct 18, 2021, 10:13 PM aditya desai <admad123@gmail.com> wrote:
>>
>>> Hi,
>>> I am running the below query. Table has 21 million records. I get an Out
>>> Of Memory error after a while.(from both pgadmin and psql). Can someone
>>> review DB parameters given below.
>>>
>>> select t.*,g.column,a.column from
>>> gk_staging g, transaction t,account a
>>> where
>>> g.accountcodeis not null AND
>>> g.accountcode::text <> '' AND
>>> length(g.accountcode)=13 AND
>>> g.closeid::text=t.transactionid::text AND
>>> subsrting(g.accountcode::text,8)=a.mask_code::text
>>>
>>> Below are system parameters.
>>> shared_buffers=3GB
>>> work_mem=2GB
>>> effective_cache_size=10GB
>>> maintenance_work_mem=1GB
>>> max_connections=250
>>>
>>> I am unable to paste explain plan here due to security concerns.
>>>
>>> Regards,
>>> Aditya.
>>>
>>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 04:07 aditya desai <admad123@gmail.com>
parent: aditya desai <admad123@gmail.com>
3 siblings, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-10-19 04:07 UTC (permalink / raw)
To: Geri Wright <geri.w123@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
This is how I received a query from the App Team. They have migrated from
Oracle to Postgres. I see in Oracle where it is working fine and also has
the same joins. Some of the developers increased the work_mem. I will try
and tone it down. Will get back to you. Thanks.
On Tue, Oct 19, 2021 at 1:28 AM Geri Wright <geri.w123@gmail.com> wrote:
> Hi,
> It looks like you have Cartesian joins in the query. Try updating your
> where clause to include
>
> And g.columnname = t.columnname
> And t.columnname2 = a.columnname2
>
> On Mon, Oct 18, 2021, 12:43 PM aditya desai <admad123@gmail.com> wrote:
>
>> Hi,
>> I am running the below query. Table has 21 million records. I get an Out
>> Of Memory error after a while.(from both pgadmin and psql). Can someone
>> review DB parameters given below.
>>
>> select t.*,g.column,a.column from
>> gk_staging g, transaction t,account a
>> where
>> g.accountcodeis not null AND
>> g.accountcode::text <> '' AND
>> length(g.accountcode)=13 AND
>> g.closeid::text=t.transactionid::text AND
>> subsrting(g.accountcode::text,8)=a.mask_code::text
>>
>> Below are system parameters.
>> shared_buffers=3GB
>> work_mem=2GB
>> effective_cache_size=10GB
>> maintenance_work_mem=1GB
>> max_connections=250
>>
>> I am unable to paste explain plan here due to security concerns.
>>
>> Regards,
>> Aditya.
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Fwd: Query out of memory
@ 2021-10-19 05:58 aditya desai <admad123@gmail.com>
parent: aditya desai <admad123@gmail.com>
3 siblings, 2 replies; 14+ messages in thread
From: aditya desai @ 2021-10-19 05:58 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Sending to a performance group instead of PLPGSQL.
.
.
Hi,
I am running the below query. Table has 21 million records. I get an Out Of
Memory error after a while.(from both pgadmin and psql). Can someone review
DB parameters given below.
select t.*,g.column,a.column from
gk_staging g, transaction t,account a
where
g.accountcodeis not null AND
g.accountcode::text <> '' AND
length(g.accountcode)=13 AND
g.closeid::text=t.transactionid::text AND
subsrting(g.accountcode::text,8)=a.mask_code::text
Below are system parameters.
shared_buffers=3GB
work_mem=2GB
effective_cache_size=10GB
maintenance_work_mem=1GB
max_connections=250
I am unable to paste explain plan here due to security concerns.
Regards,
Aditya.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 09:54 Adam Brusselback <adambrusselback@gmail.com>
parent: aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Adam Brusselback @ 2021-10-19 09:54 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
That work_mem value could be way too high depending on how much ram your
server has...which would be a very important bit of information to help
figure this out. Also, what Postgres / OS versions?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 09:57 Dave Cramer <davecramer@postgres.rocks>
parent: Adam Brusselback <adambrusselback@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Dave Cramer @ 2021-10-19 09:57 UTC (permalink / raw)
To: Adam Brusselback <adambrusselback@gmail.com>; +Cc: aditya desai <admad123@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Tue, 19 Oct 2021 at 05:54, Adam Brusselback <adambrusselback@gmail.com>
wrote:
> That work_mem value could be way too high depending on how much ram your
> server has...which would be a very important bit of information to help
> figure this out. Also, what Postgres / OS versions?
>
WORK_MEM is definitely too high. With 250 connections there is no way you
could allocate 2G to each one of them if needed
Dave Cramer
www.postgres.rocks
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Fwd: Query out of memory
@ 2021-10-19 10:26 Justin Pryzby <pryzby@telsasoft.com>
parent: aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Justin Pryzby @ 2021-10-19 10:26 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
> I am running the below query. Table has 21 million records. I get an Out Of
> Memory error after a while.(from both pgadmin and psql). Can someone review
Is the out of memory error on the client side ?
Then you've simply returned more rows than the client can support.
In that case, you can run it with "explain analyze" to prove that the server
side can run the query. That returns no data rows to the client, but shows the
number of rows which would normally be returned.
--
Justin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 10:46 aditya desai <admad123@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 14+ messages in thread
From: aditya desai @ 2021-10-19 10:46 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi Justin,
Out of memory on pgadmin and psql. I executed it with explain analyze.
Still going out of memory.
Also currently 250 user connections are not being made. There are hardly
10 connections to database. When I run thi query it is going out of memory.
Also this query is part of a view that gets referred in a
procedure.Transaction table is partitioned table but due to business
requirements partition key is not part of where clause.
Regards,
Aditya.
On Tuesday, October 19, 2021, Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
> > I am running the below query. Table has 21 million records. I get an Out
> Of
> > Memory error after a while.(from both pgadmin and psql). Can someone
> review
>
> Is the out of memory error on the client side ?
> Then you've simply returned more rows than the client can support.
>
> In that case, you can run it with "explain analyze" to prove that the
> server
> side can run the query. That returns no data rows to the client, but
> shows the
> number of rows which would normally be returned.
>
> --
> Justin
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 13:39 Michael Lewis <mlewis@entrata.com>
parent: aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Lewis @ 2021-10-19 13:39 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Check explain plan, change work mem to 100MBs and then check explain plan
again. If it changed, then try explain analyze.
Work mem is limit is used per node in the plan, so especially with
partitioned tables, that limit is way too high.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-19 13:48 aditya desai <admad123@gmail.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 0 replies; 14+ messages in thread
From: aditya desai @ 2021-10-19 13:48 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks Michael. I will check this further.
On Tue, Oct 19, 2021 at 7:09 PM Michael Lewis <mlewis@entrata.com> wrote:
> Check explain plan, change work mem to 100MBs and then check explain plan
> again. If it changed, then try explain analyze.
>
> Work mem is limit is used per node in the plan, so especially with
> partitioned tables, that limit is way too high.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Query out of memory
@ 2021-10-22 14:41 Ninad Shah <nshah.postgres@gmail.com>
parent: aditya desai <admad123@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Ninad Shah @ 2021-10-22 14:41 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Do you see any issue in PostgreSQL log files?
Regards,
Ninad Shah
On Tue, 19 Oct 2021 at 16:17, aditya desai <admad123@gmail.com> wrote:
> Hi Justin,
> Out of memory on pgadmin and psql. I executed it with explain analyze.
> Still going out of memory.
>
> Also currently 250 user connections are not being made. There are hardly
> 10 connections to database. When I run thi query it is going out of memory.
>
> Also this query is part of a view that gets referred in a
> procedure.Transaction table is partitioned table but due to business
> requirements partition key is not part of where clause.
>
> Regards,
> Aditya.
>
> On Tuesday, October 19, 2021, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
>> On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
>> > I am running the below query. Table has 21 million records. I get an
>> Out Of
>> > Memory error after a while.(from both pgadmin and psql). Can someone
>> review
>>
>> Is the out of memory error on the client side ?
>> Then you've simply returned more rows than the client can support.
>>
>> In that case, you can run it with "explain analyze" to prove that the
>> server
>> side can run the query. That returns no data rows to the client, but
>> shows the
>> number of rows which would normally be returned.
>>
>> --
>> Justin
>>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-10-22 14:41 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-18 16:42 Query out of memory aditya desai <admad123@gmail.com>
2021-10-18 16:43 ` aditya desai <admad123@gmail.com>
2021-10-18 16:48 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-10-18 19:03 ` aditya desai <admad123@gmail.com>
2021-10-18 21:05 ` Guillaume Lelarge <guillaume@lelarge.info>
2021-10-19 04:07 ` aditya desai <admad123@gmail.com>
2021-10-19 05:58 ` aditya desai <admad123@gmail.com>
2021-10-19 09:54 ` Adam Brusselback <adambrusselback@gmail.com>
2021-10-19 09:57 ` Dave Cramer <davecramer@postgres.rocks>
2021-10-19 10:26 ` Justin Pryzby <pryzby@telsasoft.com>
2021-10-19 10:46 ` aditya desai <admad123@gmail.com>
2021-10-19 13:39 ` Michael Lewis <mlewis@entrata.com>
2021-10-19 13:48 ` aditya desai <admad123@gmail.com>
2021-10-22 14:41 ` Ninad Shah <nshah.postgres@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox