Postgres Pro
Downloads
Home   >   mailing lists

Thread: When Update balloons memory

When Update balloons memory

From
Klaudie Willis
Date:
About the system:
Ubuntu 20.04, 64GB ram, 16GB shared buffer, 500 MB working mem, Postgresql 14.1

Core issue:
The following statement below, when not divided up into chunks, but run across all 800M rows, did trigger an OOM-kill from the OS.
I have looked into it by kernel logs as well as postgresql logs. The postgresql just says it was killed, and the OS killed it due to the fact that all mem including swap was exhausted.
Looking at TOP while updating, I can see the RSS column of a single postgresql process (the connection I assume), just grow and grow until it chokes the system.

Statement:
Update table alfa
set x = beta.x
from beta where beta.id=alpha.id and x <> beta.x

alpha is a wide table (40 columns), partitioned into 5 equally partitions by year. Total row count 800M rows
beta is a 10 column 40M rows table.
the updated field x is non-indexed varchar; the id fields are indexed.
there are no triggers

I am well aware that huge updates have general issues, like locking the table etc, and it is perhaps discouraged.  And I did solve it by batching it in 1M and 1M rows.
However, my curiosity still remains of what is really happening here.  Why do Postgresql run out of memory? Exactly what is it storing in that memory?  I am aware of the work_mem danger, but that is not what is happening here.  I can replicate this with 32MB work mem as well; This is a low connection database.
Any help is appreciated.

Klaudie

track_activity_query_size = 4096                                                 
synchronous_commit = off                                                         
full_page_writes = off                                                           
#wal_compression = on                                                            
wal_level = minimal                                                              
max_wal_senders = 0                                                              
                                                                                 
log_min_duration_statement = 1000                                                
idle_in_transaction_session_timeout = '300s'    # in milliseconds, 0 is disabled 
tcp_keepalives_idle = '300s'                                                     
max_connections = 50                                                             
shared_buffers = 16GB                                                            
effective_cache_size = 48GB                                                      
maintenance_work_mem = 2GB                                                       
checkpoint_completion_target = 0.9                                               
min_wal_size = 4GB                                                               
max_wal_size = 16GB                                                              
#wal_buffers = 16MB                                                              
default_statistics_target = 1000                                                 
random_page_cost = 1.1                                                           
effective_io_concurrency = 200                                                   
work_mem = 1000MB                                                                
max_worker_processes = 8                                                         
max_parallel_workers = 8                                                         
max_parallel_workers_per_gather = 4                                              
max_parallel_maintenance_workers = 4                                             
cpu_tuple_cost = 0.03
                                                


Re: When Update balloons memory

From
Francisco Olarte
Date:
This has no solution for the issue but...

On Tue, 7 Dec 2021 at 10:16, Klaudie Willis
<Klaudie.Willis@protonmail.com> wrote:
> Ubuntu 20.04, 64GB ram, 16GB shared buffer, 500 MB working mem, Postgresql 14.1
...
> shared_buffers = 16GB
> effective_cache_size = 48GB
... You are not going to have total ram - shared buffers in the cache,
os, postgres, work mem, other processess and all sort of different
things eat ram. I would suggest looking at free/top/whatever too size
this ( it should not OOM, just distort pg estimates ).

Francisco Olarte.

Re: When Update balloons memory

From
Tom Lane
Date:
Klaudie Willis <Klaudie.Willis@protonmail.com> writes:
> The following statement below, when not divided up into chunks, but run across all 800M rows, did trigger an OOM-kill
fromthe OS. 

An UPDATE should only result in memory bloat if it's queuing trigger
events to be processed at end-of-statement.  You claim there are
no triggers, but are you sure? (what about foreign keys?)

Otherwise, it seems possible that you've identified a memory leak,
but there's not enough detail here to investigate.  Can you create
a reproducible test case?

            regards, tom lane

Re: When Update balloons memory

From
Klaudie Willis
Date:
Thanks for the insight!

I have recreated the problem on a different machine and installation where I was more free to experiment to isolate
whatcauses this. 
So, it seems like the index is central cog here:
> create index ind1 on alpha ((deltatime::date));
where "alpha" is a partition tableset partitioned by (deltatime::date)
The general and simple updates like:
> update alphatable set gamma=gamma || "#postfix#"
makes the process memory balloon to the point of OOM.

If I remove the ind1 index on "deltatime::date", and just add another one on a random column, the problem disappears.
Soit seems like the index on the partition key is relevant. 
Additional info, alphatable is a 200M evenly distributed row across the partitions, and I haven't tried to see if the
::datecasting is relevant for the problem. No there are no triggers here; I can't vouch for what the system creates
behindmy back though. 

Is this a feature or a bug?

--
Klaudie

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Tuesday, December 7th, 2021 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Klaudie Willis Klaudie.Willis@protonmail.com writes:
>
> > The following statement below, when not divided up into chunks, but run across all 800M rows, did trigger an
OOM-killfrom the OS. 
>
> An UPDATE should only result in memory bloat if it's queuing trigger
>
> events to be processed at end-of-statement. You claim there are
>
> no triggers, but are you sure? (what about foreign keys?)
>
> Otherwise, it seems possible that you've identified a memory leak,
>
> but there's not enough detail here to investigate. Can you create
>
> a reproducible test case?
>
> regards, tom lane

Re: When Update balloons memory

From
Tom Lane
Date:
Klaudie Willis <Klaudie.Willis@protonmail.com> writes:
> So, it seems like the index is central cog here:
>> create index ind1 on alpha ((deltatime::date));
> where "alpha" is a partition tableset partitioned by (deltatime::date)
> The general and simple updates like:
>> update alphatable set gamma=gamma || "#postfix#"
> makes the process memory balloon to the point of OOM.

That seems like a bug, but please supply a self-contained test case
rather than expecting other people to reverse-engineer one.

            regards, tom lane

Re: When Update balloons memory

From
Klaudie Willis
Date:
Hi,

Turns out the base case is simpler than I thought. Not involving partitions at all

CREATE TABLE public.part_main (
    txid bigint,
    actiondate timestamp without time zone NOT NULL
);

insert into part_main
select x, '2019-06-01'::timestamp + x%365 * interval '1 day'
from generate_series(1, 30 * 1E6) as x;

CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date);  -- mem bug?
-- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
-- mem runaway follows
update part_main set txid = txid + 1;

Hope you can replicate it.

best regards
Klaudie

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Tuesday, December 14th, 2021 at 12:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Klaudie Willis Klaudie.Willis@protonmail.com writes:
>
> > So, it seems like the index is central cog here:
> >
> > > create index ind1 on alpha ((deltatime::date));
> > >
> > > where "alpha" is a partition tableset partitioned by (deltatime::date)
> > >
> > > The general and simple updates like:
> > >
> > > update alphatable set gamma=gamma || "#postfix#"
> > >
> > > makes the process memory balloon to the point of OOM.
>
> That seems like a bug, but please supply a self-contained test case
>
> rather than expecting other people to reverse-engineer one.
>
> regards, tom lane

Re: When Update balloons memory

From
Vincent Veyron
Date:
On Tue, 14 Dec 2021 08:16:08 +0000
Klaudie Willis <Klaudie.Willis@protonmail.com> wrote:

> CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date);  -- mem bug?

Nope, syntax error

ERROR:  syntax error at or near "::"
LINE 1: ...indx_1 ON public.part_main USING btree ((actiondate)::date);
                                                               ^


> -- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
> -- mem runaway follows
> update part_main set txid = txid + 1;
>
> Hope you can replicate it.
>

Can't replicate on my Intel(R) Core(TM) i5 CPU M 520  @ 2.40GHz with 2Go of RAM

time psql -c 'update part_main set txid = txid + 1' vv
UPDATE 31000000

real    24m39.594s
user    0m0.121s
sys    0m0.036s

--
                                        Bien à vous, Vincent Veyron

https://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance

Re: When Update balloons memory

From
Klaudie Willis
Date:
So sorry about that;
I'll repost it here, corrected, for others to use who wants to exhaust their memory:

--PG-14.1

CREATE TABLE public.part_main (
    txid bigint,
    actiondate timestamp without time zone NOT NULL
);

insert into part_main
select x, '2019-06-01'::timestamp + x%365 * interval '1 day'
from generate_series(1, 30 * 1E6) as x;

CREATE INDEX partindx ON public.part_main USING btree ((actiondate::date));  -- mem bug?
-- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
-- mem runaway follows
update part_main set txid = txid + 1;

Klaudie

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Tuesday, December 14th, 2021 at 16:58, Vincent Veyron <vv.lists@wanadoo.fr> wrote:

> On Tue, 14 Dec 2021 08:16:08 +0000
>
> Klaudie Willis Klaudie.Willis@protonmail.com wrote:
>
> > CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date); -- mem bug?
>
> Nope, syntax error
>
> ERROR: syntax error at or near "::"
>
> LINE 1: ...indx_1 ON public.part_main USING btree ((actiondate)::date);
>
> ^
>
> > -- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
> >
> > -- mem runaway follows
> >
> > update part_main set txid = txid + 1;
> >
> > Hope you can replicate it.
>
> Can't replicate on my Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz with 2Go of RAM
>
> time psql -c 'update part_main set txid = txid + 1' vv
>
> UPDATE 31000000
>
> real 24m39.594s
>
> user 0m0.121s
>
> sys 0m0.036s
>
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                                         Bien à vous, Vincent Veyron
>
>
> https://marica.fr
>
> Gestion des contentieux juridiques, des contrats et des sinistres d'assurance

Re: When Update balloons memory

From
Tom Lane
Date:
Klaudie Willis <Klaudie.Willis@protonmail.com> writes:
> I'll repost it here, corrected, for others to use who wants to exhaust their memory:
> --PG-14.1

This leak is new in v14, possibly that's why Vincent didn't reproduce it.

            regards, tom lane

Re: When Update balloons memory

From
Vincent Veyron
Date:
On Tue, 14 Dec 2021 11:18:07 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> This leak is new in v14, possibly that's why Vincent didn't reproduce it.

Indeed, I'm on v11





--
                                        Bien à vous, Vincent Veyron

https://marica.fr
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance