Thread: Understanding was terminated by signal 9: Killed
In an RDS instance with 16GB RAM, I ran a long query which started by setting temp_buffers to 16GB, so I think I plum ran out of memory, but can anyone point me in a different direction if the following log messages indicate something else is awry?
2021-10-19 21:01:41 UTC::@:[24755]:LOG: checkpoint complete: wrote 110375 buffers (22.3%); 0 WAL file(s) added, 0 removed, 14 recycled; write=269.809 s, sync=0.208 s, total=270.089 s; sync files=30, longest=0.200 s, average=0.007 s; distance=1020117 kB, estimate=1020117 kB
2021-10-19 21:07:11 UTC::@:[24755]:LOG: checkpoint starting: time
2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) was terminated by signal 9: Killed
2021-10-19 21:10:37 UTC::@:[24752]:DETAIL: Failed process was running: refresh materialized view concurrently stats.vwm_d with data;
2021-10-19 21:10:37 UTC::@:[24752]:LOG: terminating any other active server processes
2021-10-19 21:10:37 UTC::@:[24758]:WARNING: terminating connection because of crash of another server process
2021-10-19 21:10:37 UTC::@:[24758]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-10-19 21:10:37 UTC::@:[24758]:HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-10-19 21:10:37 UTC::@:[24752]:LOG: archiver process (PID 24759) exited with exit code 2
2021-10-19 21:10:37 UTC::@:[24752]:LOG: all server processes terminated; reinitializing
2021-10-19 21:10:37 UTC::@:[23697]:LOG: database system was interrupted; last known up at 2021-10-19 21:01:41 UTC
2021-10-19 21:10:37 UTC::@:[23697]:LOG: database system was not properly shut down; automatic recovery in progress
2021-10-19 21:10:37 UTC::@:[23697]:LOG: redo starts at 611/C8668F30
2021-10-19 21:10:42 UTC::@:[23697]:LOG: invalid record length at 612/310: wanted 24, got 0
2021-10-19 21:10:42 UTC::@:[23697]:LOG: redo done at 612/2D8
2021-10-19 21:10:42 UTC::@:[23697]:LOG: checkpoint starting: end-of-recovery immediate
2021-10-19 21:10:45 UTC:127.0.0.1(28416):rdsadmin@rdsadmin:[23907]:FATAL: the database system is in recovery mode
2021-10-19 21:10:45 UTC:127.0.0.1(28418):rdsadmin@rdsadmin:[23908]:FATAL: the database system is in recovery mode
2021-10-19 21:10:46 UTC::@:[23697]:LOG: checkpoint complete: wrote 88487 buffers (17.8%); 0 WAL file(s) added, 0 removed, 2 recycled; write=3.986 s, sync=0.015 s, total=4.015 s; sync files=40, longest=0.008 s, average=0.001 s; distance=910940 kB, estimate=910940 kB
2021-10-19 21:10:46 UTC::@:[24752]:LOG: database system is ready to accept connections
2021-10-19 21:15:46 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:15:46 UTC::@:[23909]:LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.366 s, sync=0.005 s, total=0.383 s; sync files=2, longest=0.003 s, average=0.003 s; distance=5 kB, estimate=5 kB
2021-10-19 21:20:47 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:20:52 UTC::@:[23909]:LOG: checkpoint complete: wrote 51 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=4.987 s, sync=0.004 s, total=5.004 s; sync files=29, longest=0.004 s, average=0.001 s; distance=65682 kB, estimate=65682 kB
2021-10-19 21:25:47 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:25:51 UTC::@:[23909]:LOG: checkpoint complete: wrote 47 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=4.587 s, sync=0.011 s, total=4.630 s; sync files=27, longest=0.011 s, average=0.001 s; distance=65528 kB, estimate=65667 kB
2021-10-19 21:29:44 UTC::@:[23909]:LOG: checkpoint starting: wal
--
2021-10-19 21:07:11 UTC::@:[24755]:LOG: checkpoint starting: time
2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) was terminated by signal 9: Killed
2021-10-19 21:10:37 UTC::@:[24752]:DETAIL: Failed process was running: refresh materialized view concurrently stats.vwm_d with data;
2021-10-19 21:10:37 UTC::@:[24752]:LOG: terminating any other active server processes
2021-10-19 21:10:37 UTC::@:[24758]:WARNING: terminating connection because of crash of another server process
2021-10-19 21:10:37 UTC::@:[24758]:DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-10-19 21:10:37 UTC::@:[24758]:HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-10-19 21:10:37 UTC::@:[24752]:LOG: archiver process (PID 24759) exited with exit code 2
2021-10-19 21:10:37 UTC::@:[24752]:LOG: all server processes terminated; reinitializing
2021-10-19 21:10:37 UTC::@:[23697]:LOG: database system was interrupted; last known up at 2021-10-19 21:01:41 UTC
2021-10-19 21:10:37 UTC::@:[23697]:LOG: database system was not properly shut down; automatic recovery in progress
2021-10-19 21:10:37 UTC::@:[23697]:LOG: redo starts at 611/C8668F30
2021-10-19 21:10:42 UTC::@:[23697]:LOG: invalid record length at 612/310: wanted 24, got 0
2021-10-19 21:10:42 UTC::@:[23697]:LOG: redo done at 612/2D8
2021-10-19 21:10:42 UTC::@:[23697]:LOG: checkpoint starting: end-of-recovery immediate
2021-10-19 21:10:45 UTC:127.0.0.1(28416):rdsadmin@rdsadmin:[23907]:FATAL: the database system is in recovery mode
2021-10-19 21:10:45 UTC:127.0.0.1(28418):rdsadmin@rdsadmin:[23908]:FATAL: the database system is in recovery mode
2021-10-19 21:10:46 UTC::@:[23697]:LOG: checkpoint complete: wrote 88487 buffers (17.8%); 0 WAL file(s) added, 0 removed, 2 recycled; write=3.986 s, sync=0.015 s, total=4.015 s; sync files=40, longest=0.008 s, average=0.001 s; distance=910940 kB, estimate=910940 kB
2021-10-19 21:10:46 UTC::@:[24752]:LOG: database system is ready to accept connections
2021-10-19 21:15:46 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:15:46 UTC::@:[23909]:LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.366 s, sync=0.005 s, total=0.383 s; sync files=2, longest=0.003 s, average=0.003 s; distance=5 kB, estimate=5 kB
2021-10-19 21:20:47 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:20:52 UTC::@:[23909]:LOG: checkpoint complete: wrote 51 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=4.987 s, sync=0.004 s, total=5.004 s; sync files=29, longest=0.004 s, average=0.001 s; distance=65682 kB, estimate=65682 kB
2021-10-19 21:25:47 UTC::@:[23909]:LOG: checkpoint starting: time
2021-10-19 21:25:51 UTC::@:[23909]:LOG: checkpoint complete: wrote 47 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=4.587 s, sync=0.011 s, total=4.630 s; sync files=27, longest=0.011 s, average=0.001 s; distance=65528 kB, estimate=65667 kB
2021-10-19 21:29:44 UTC::@:[23909]:LOG: checkpoint starting: wal
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes:
> In an RDS instance with 16GB RAM, I ran a long query which started by
> setting temp_buffers to 16GB, so I think I plum ran out of memory, but can
> anyone point me in a different direction if the following log messages
> indicate something else is awry?
Yeah, this:
> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) was
> terminated by signal 9: Killed
almost certainly indicates the Linux OOM killer at work. If you were
running your own system I'd point you to [1], but I doubt that RDS
lets you put your hands on the relevant knobs.
regards, tom lane
[1] https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily. Sent from my iPad > On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Wells Oliver <wells.oliver@gmail.com> writes: >> In an RDS instance with 16GB RAM, I ran a long query which started by >> setting temp_buffers to 16GB, so I think I plum ran out of memory, but can >> anyone point me in a different direction if the following log messages >> indicate something else is awry? > > Yeah, this: > >> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) was >> terminated by signal 9: Killed > > almost certainly indicates the Linux OOM killer at work. If you were > running your own system I'd point you to [1], but I doubt that RDS > lets you put your hands on the relevant knobs. > > regards, tom lane > > [1] https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT > >
Be careful! I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter... -- Michel SALAIS -----Message d'origine----- De : John Scalia <jayknowsunix@gmail.com> Envoyé : mercredi 20 octobre 2021 01:12 À : Tom Lane <tgl@sss.pgh.pa.us> Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org> Objet : Re: Understanding was terminated by signal 9: Killed You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily. Sent from my iPad > On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Wells Oliver <wells.oliver@gmail.com> writes: >> In an RDS instance with 16GB RAM, I ran a long query which started by >> setting temp_buffers to 16GB, so I think I plum ran out of memory, >> but can anyone point me in a different direction if the following log >> messages indicate something else is awry? > > Yeah, this: > >> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) >> was terminated by signal 9: Killed > > almost certainly indicates the Linux OOM killer at work. If you were > running your own system I'd point you to [1], but I doubt that RDS > lets you put your hands on the relevant knobs. > > regards, tom lane > > [1] > https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME > MORY-OVERCOMMIT > >
For posterity: RDS will definitely allow you to downgrade instance type.
On Wed, Oct 20, 2021 at 1:27 PM Michel SALAIS <msalais@msym.fr> wrote:
Be careful!
I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter...
--
Michel SALAIS
-----Message d'origine-----
De : John Scalia <jayknowsunix@gmail.com>
Envoyé : mercredi 20 octobre 2021 01:12
À : Tom Lane <tgl@sss.pgh.pa.us>
Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org>
Objet : Re: Understanding was terminated by signal 9: Killed
You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily.
Sent from my iPad
> On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Wells Oliver <wells.oliver@gmail.com> writes:
>> In an RDS instance with 16GB RAM, I ran a long query which started by
>> setting temp_buffers to 16GB, so I think I plum ran out of memory,
>> but can anyone point me in a different direction if the following log
>> messages indicate something else is awry?
>
> Yeah, this:
>
>> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813)
>> was terminated by signal 9: Killed
>
> almost certainly indicates the Linux OOM killer at work. If you were
> running your own system I'd point you to [1], but I doubt that RDS
> lets you put your hands on the relevant knobs.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME
> MORY-OVERCOMMIT
>
>
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
That would be news to me. Where I currently work we’ve had to do that on several occasions as many of our dbs are in themulti- multi- terabyte range, but some are just references without a lot of traffic. Sent from my iPad > On Oct 20, 2021, at 4:27 PM, Michel SALAIS <msalais@msym.fr> wrote: > > Be careful! > I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter... > > -- > Michel SALAIS > > -----Message d'origine----- > De : John Scalia <jayknowsunix@gmail.com> > Envoyé : mercredi 20 octobre 2021 01:12 > À : Tom Lane <tgl@sss.pgh.pa.us> > Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org> > Objet : Re: Understanding was terminated by signal 9: Killed > > You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily. > > Sent from my iPad > >> On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Wells Oliver <wells.oliver@gmail.com> writes: >>> In an RDS instance with 16GB RAM, I ran a long query which started by >>> setting temp_buffers to 16GB, so I think I plum ran out of memory, >>> but can anyone point me in a different direction if the following log >>> messages indicate something else is awry? >> >> Yeah, this: >> >>> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813) >>> was terminated by signal 9: Killed >> >> almost certainly indicates the Linux OOM killer at work. If you were >> running your own system I'd point you to [1], but I doubt that RDS >> lets you put your hands on the relevant knobs. >> >> regards, tom lane >> >> [1] >> https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME >> MORY-OVERCOMMIT >> >> > >
I'm 100% certain you can downsize given the selected instance type is compatible with your engine version. Process is the same as scaling up and will result in downtime. I don't see anything in documentation regarding AWS RDS Console, API's or Terraform documentation that would lead me to believe it cares about anything more than having valid parameters combinations regarless of engine / size.
Just to be certain, I created a t3.medium on my personal account with Postgres 13.3 and resized to a t3.micro immediately after it was provisioned. No issues.
Summary of modifications
You are about to submit the following modifications. Only values that will change are displayed. Carefully verify your changes and click Modify DB Instance.
| Attribute | Current value | New value |
|---|---|---|
| DB instance class | db.t3.medium | db.t3.micro |
No issues Modifying.
Best regards,
Dan Smith
On Wed, Oct 20, 2021 at 1:27 PM Michel SALAIS <msalais@msym.fr> wrote:
Be careful!
I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter...
--
Michel SALAIS
-----Message d'origine-----
De : John Scalia <jayknowsunix@gmail.com>
Envoyé : mercredi 20 octobre 2021 01:12
À : Tom Lane <tgl@sss.pgh.pa.us>
Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org>
Objet : Re: Understanding was terminated by signal 9: Killed
You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily.
Sent from my iPad
> On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Wells Oliver <wells.oliver@gmail.com> writes:
>> In an RDS instance with 16GB RAM, I ran a long query which started by
>> setting temp_buffers to 16GB, so I think I plum ran out of memory,
>> but can anyone point me in a different direction if the following log
>> messages indicate something else is awry?
>
> Yeah, this:
>
>> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813)
>> was terminated by signal 9: Killed
>
> almost certainly indicates the Linux OOM killer at work. If you were
> running your own system I'd point you to [1], but I doubt that RDS
> lets you put your hands on the relevant knobs.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME
> MORY-OVERCOMMIT
>
>
Best regards,
Dan Smith
Dan Smith
Can confirm this.
On 10/20/21 4:00 PM, Dan Smith wrote:
I'm 100% certain you can downsize given the selected instance type is compatible with your engine version. Process is the same as scaling up and will result in downtime. I don't see anything in documentation regarding AWS RDS Console, API's or Terraform documentation that would lead me to believe it cares about anything more than having valid parameters combinations regarless of engine / size.Just to be certain, I created a t3.medium on my personal account with Postgres 13.3 and resized to a t3.micro immediately after it was provisioned. No issues.Summary of modifications
You are about to submit the following modifications. Only values that will change are displayed. Carefully verify your changes and click Modify DB Instance.
Attribute Current value New value DB instance class db.t3.medium db.t3.micro No issues Modifying.Best regards,Dan SmithOn Wed, Oct 20, 2021 at 1:27 PM Michel SALAIS <msalais@msym.fr> wrote:Be careful!
I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter...
--
Michel SALAIS
-----Message d'origine-----
De : John Scalia <jayknowsunix@gmail.com>
Envoyé : mercredi 20 octobre 2021 01:12
À : Tom Lane <tgl@sss.pgh.pa.us>
Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org>
Objet : Re: Understanding was terminated by signal 9: Killed
You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily.
Sent from my iPad
> On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Wells Oliver <wells.oliver@gmail.com> writes:
>> In an RDS instance with 16GB RAM, I ran a long query which started by
>> setting temp_buffers to 16GB, so I think I plum ran out of memory,
>> but can anyone point me in a different direction if the following log
>> messages indicate something else is awry?
>
> Yeah, this:
>
>> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813)
>> was terminated by signal 9: Killed
>
> almost certainly indicates the Linux OOM killer at work. If you were
> running your own system I'd point you to [1], but I doubt that RDS
> lets you put your hands on the relevant knobs.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME
> MORY-OVERCOMMIT
>
>--Best regards,
Dan Smith
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Hello,
I had an RDS instance with a “lot” of CPU and a “lot” of memory. I was studying tuning parameters for a particular application. I wanted to change instance type to have less CPU and less memory. There were no way to do it. The instance had to be erased an recreated. At the beginning, It was possible to choose an instance type with bigger RAM and more CPUs.
Regards
Michel SALAIS
Consultant Oracle, PostgreSQL
MSYM
23 rue du Départ
Boîte 37
75014 Paris
+33 6 68 94 19 60
De : Wells Oliver <wells.oliver@gmail.com>
Envoyé : mercredi 20 octobre 2021 22:36
À : Michel SALAIS <msalais@msym.fr>
Cc : John Scalia <jayknowsunix@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin <pgsql-admin@postgresql.org>
Objet : Re: Understanding was terminated by signal 9: Killed
For posterity: RDS will definitely allow you to downgrade instance type.
On Wed, Oct 20, 2021 at 1:27 PM Michel SALAIS <msalais@msym.fr> wrote:
Be careful!
I am not sure but upgrade for a larger instance can't be temporary. I think you can't downgrade to the original instance class thereafter...
--
Michel SALAIS
-----Message d'origine-----
De : John Scalia <jayknowsunix@gmail.com>
Envoyé : mercredi 20 octobre 2021 01:12
À : Tom Lane <tgl@sss.pgh.pa.us>
Cc : Wells Oliver <wells.oliver@gmail.com>; pgsql-admin <pgsql-admin@postgresql.org>
Objet : Re: Understanding was terminated by signal 9: Killed
You could always change the size of the RDS instance to something larger than what it currently is running, even just temporarily.
Sent from my iPad
> On Oct 19, 2021, at 6:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Wells Oliver <wells.oliver@gmail.com> writes:
>> In an RDS instance with 16GB RAM, I ran a long query which started by
>> setting temp_buffers to 16GB, so I think I plum ran out of memory,
>> but can anyone point me in a different direction if the following log
>> messages indicate something else is awry?
>
> Yeah, this:
>
>> 2021-10-19 21:10:37 UTC::@:[24752]:LOG: server process (PID 25813)
>> was terminated by signal 9: Killed
>
> almost certainly indicates the Linux OOM killer at work. If you were
> running your own system I'd point you to [1], but I doubt that RDS
> lets you put your hands on the relevant knobs.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-ME
> MORY-OVERCOMMIT
>
>
--
Wells Oliver
wells.oliver@gmail.com