Message-ID: <f8abb93a-5b03-4c0e-a69f-1f3cdfd4c4d4@holmsecurity.com>
Date: Tue, 17 Sep 2024 09:40:15 +0200
User-Agent: Mozilla Thunderbird
Subject: Re: Failing to allocate memory when I think it shouldn't
To: Christoph Moench-Tegeder <cmt@burggraben.net>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <PAWPR07MB1009548159C0046E01170C6E6ED652@PAWPR07MB10095.eurprd07.prod.outlook.com>
 <ZuXuDdq32fuzI8pp@elch.exwg.net>
Content-Language: en-US
From: Thomas Ziegler <thomas.ziegler@holmsecurity.com>
In-Reply-To: <ZuXuDdq32fuzI8pp@elch.exwg.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?ODlNNEZ5R0tKSzhFaTAzNllZR2xGdmNIWXhFVnZpUkZxcmpGWDZ0NE9lTWtp?=
 =?utf-8?B?V3NzR2N2MStSZWNobnpEM2Y1MFp5VUlHUk5FUmtCaG4rV2t5azMwOFpXdHZl?=
 =?utf-8?B?Y3NJSGdJZ1BZVUxkek5NQW1IbkFHdXlsZDMzY2F1K3NaV2daYUlMeHpXcWtI?=
 =?utf-8?B?Zkw4VXhLZERYSnltNkhmRVB5bjZYclpsUzhlMzBDZCtSWGFyRnZzRWFZUEI2?=
 =?utf-8?B?TEdmazJQME1Eb29SMkNhOUgwdUF1Y3R0VzhMQVpyTlZrMC9IdVpkOXFYLzZ2?=
 =?utf-8?B?d2pnVGNWOFQ2cXZvT01JL0lzQm81c2ZrVlRBUklyUTArR3JrVWNKMUgwOHBu?=
 =?utf-8?B?UGpibUF5YlN3bG9vNExXSTAwQTU5QWU3M3hmREJITk4yU3JheWk4MzYrdjVi?=
 =?utf-8?B?MEZQYUdwWFhPOTdZbEx0RmFPSFJPanIwcVlPSk0xWTArRGFQck95bGlCenFu?=
 =?utf-8?B?OCtvTzY5WXVGdkV0ZGhEUVFDWWwycHdkNk15cEg3aEdnQThlU0pqVEQ4UXht?=
 =?utf-8?B?TXhIb2lDb0dia3BMYzZzQ0xXWnZYbHI1V3dkd2ladUxDYzIwUnNVZ00zcGxO?=
 =?utf-8?B?Z3RZWERVd0Rxc3NmQVJLNkNIWEtQWkliUW1tQWgvemFhUEJCQUZTZ05rQ1g4?=
 =?utf-8?B?eUNybXE2ZmdBcjBKcDhyT25FL1hBOEZ3bmduVzJKc0o2WDVrL1dkVmRoSFJ4?=
 =?utf-8?B?cktWY0dnMm1ySlJ3UjJXeUJGczNxUEhObmRvWjlWcHpLbTFyRHhlMExLQVRB?=
 =?utf-8?B?MnRoWWM5dWoxTnhJRmxYOXhjTU1EOEZwUFdSUGJueUQ0NkUrc3plVWl6U1VG?=
 =?utf-8?B?a2hJNHpFbjZ5SjVyb3ZkeGtHTGtMZlFwYS82blBMd2I2R0R3SGNGUUJvanpM?=
 =?utf-8?B?cGRGNFFNN3h6Zit1bll3MUF1SFNyQVVCQVNkYkNmbEpXS2ZDcnQzYVVmc2xu?=
 =?utf-8?B?eU5sUm1xVGVOOGcvSjhZZEt6dE9USGJLbTlUaWFPSzJxclVaWnZPTUNCSzEz?=
 =?utf-8?B?UjVqTUh2VDc1MmdYUlVGTWxDQjkrVU5pSFFnZndLQmhzZkozWFBiKzdWbERx?=
 =?utf-8?B?ZngrMmJlRTdydUJBSVh3b3ZMMmxUS0pvQkw3bjV5ZVpqMUc2QlNGWDg1aGFW?=
 =?utf-8?B?cVAzRHZtY3AreEY2aFFFSlZDNUhWYUxKNm50bVZmM1FHRk1sRjRLTzA4YTkv?=
 =?utf-8?B?WnlFblBCQitVQnQvcjg4RG5ZOXlLRVdkMXh4MDVXQmQ3RUgzdzdGeUpYckpw?=
 =?utf-8?B?YjBhVnUwRU5xNUIxZ2d4aENxYjdlNU95czRXK0l4c1FoTDNXV05aYXJ5eE1W?=
 =?utf-8?B?Z3hYbmNjS1IyZ1ZxN2FkN1QxS29JUEc3S3RDbUZMNFFmSU9CUkxNNkNYUlRT?=
 =?utf-8?B?dWJFUThPMHBKSTdkZEJsbFNvME9HQXV6eUJNVGpJdUo5N3ZadDkxT2JteEtL?=
 =?utf-8?B?aDgycGdnanFUbWxxK01nWkVBN3lvajRWb2RINXNnSk9NeENaanMzemN3b2xH?=
 =?utf-8?B?dE9ieEh6NHN5OE02RExKTTUxQXArOGk3MVRnd2JSR0NwenE0ZjJBRmcwdjds?=
 =?utf-8?B?U1pPZUFRRWRWRUtoRmdiV3ZnT0xsRjhwMTNxSWErdDRVQXZRWWpkMHpobHA4?=
 =?utf-8?B?VjVyTExLN204eEJKNU1WczlpcTlDSjgya3kxSjNFaEtjKzVvTlRiUFp3aU1E?=
 =?utf-8?B?MDB6RTEvQ0llTjZyYnhvd3lMTEM1WTVyUThUd01HRTF3RDJSQ3ZBV3ZuN0JE?=
 =?utf-8?B?YVN0bGJvYmQ0RExZRXhTMWtUZ3dCK0FPUUxiUnJUTWpiNERlUVJSQWV2S0s4?=
 =?utf-8?B?VGN2bS9rS2JKaVVRVzMvSjF2NGpLMnZzcWhzUkVOU3A1N3pHNENyNVJTRS9H?=
 =?utf-8?B?Vmk4d2pJQ01xQUk0c09aNHBhUlIrY1ZMVFRxVVRrcEZaRlArVzlvVHV6QTFK?=
 =?utf-8?B?T1BjN0ljMHVDeGMycWhOb21Wc0ZUZHBPUnZRNk5CTTdndWkrZlBVNDZIZFFP?=
 =?utf-8?B?bmg3bGNWcG1DM0RHVXRVeG5DZloyUDZGVW1MY3JOS0FDbC84bVZGT0ZPQjFB?=
 =?utf-8?B?U01GN0Z3cHpwUDVLZUdJUG1zV0NTYVdhZ29EV1pPcG5PKzF5SGxUcDA1eUFF?=
 =?utf-8?B?YnpvMjlFSEFSd1oxNVBGdXZXRlNYekd6cW0wcGhJL0tQNmtOQ2J1aXhzSmRR?=
 =?utf-8?Q?dc5F1bm7/01YTVwB6mbHW4U=3D?=
X-OriginatorOrg: holmsecurity.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 31a712d0-a433-4dd0-8104-08dcd6ebf9ae
X-MS-Exchange-CrossTenant-AuthSource: PAWPR07MB10095.eurprd07.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 17 Sep 2024 07:40:17.5841
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: f885fa0a-a6c8-41a6-bab6-52b51e6e2fe6
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: kQHf6uyP24oUQj8LUiyf/fiSl9jpjv55ohUwCtMVG6T94TSElrFXdYXakDkTHoA9cBmnmSObajc3am35jiQ5GVBdf6VlDB1DZZNHz+cGzZE=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM9PR07MB7810
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/f8abb93a-5b03-4c0e-a69f-1f3cdfd4c4d4%40holmsecurity.com>
Precedence: bulk

Hello Christoph,

Thanks for your answer and the suggestions, it already helped me out a lot!

On 2024-09-14 22:11, Christoph Moench-Tegeder wrote:
> Hi,
>
> ## Thomas Ziegler (thomas.ziegler@holmsecurity.com):
>
> There's a lot of information missing here. Let's start from the top.
>
>> I have had my database killed by the kernel oom-killer. After that I
>> set turned off memory over-committing and that is where things got weird.
> What exactly did you set? When playing with vm.overcommit, did you
> understand "Committed Address Space" and the workings of the
> overcommit accounting? This is the document:
> https://git.kernel.org/pub/scm/linux/kernel/git/stable/linux.git/tree/Documentation/mm/overcommit-accounting.rst
> Hint: when setting overcommit_memory=2 you might end up with way
> less available adress space than you thought you would. Also keep
> an eye on /proc/meminfo - it's sometimes hard to estimate "just off
> your cuff" what's in memory and how it's mapped. (Also, anything
> else on that machine which might hog memory?).

I set overcommit_memory=2, but completely missed 'overcommit_ratio'. 
That is most probably why the database got denied the RAM a lot sooner 
than I expected.

> Finally, there's this:
>> 2024-09-12 05:18:36.073 UTC [1932776] LOG:  background worker "parallel worker" (PID 3808076) exited with exit code 1
>> terminate called after throwing an instance of 'std::bad_alloc'
>>    what():  std::bad_alloc
>> 2024-09-12 05:18:36.083 UTC [1932776] LOG:  background worker "parallel worker" (PID 3808077) was terminated by signal 6: Aborted
> That "std::bad_alloc" sounds a lot like C++ and not like the C our
> database is written in. My first suspicion would be that you're using
> LLVM-JIT (unless you have other - maybe even your own - C++ extensions
> in the database?) and that in itself can use a good chunk of memory.
> And it looks like that exception bubbled up as a signal 6 (SIGABRT)
> which made the process terminate immediately without any cleanup,
> and after that the server has no other chance than to crash-restart.

Except for pgAudit, I don't have any extensions, so it is probably the 
JIT. I had no idea there was a JIT, even it should have been obvious. 
Thanks for pointing this out!

Is the memory the JIT takes limited by 'work_mem' or will it just take 
as much memory as it needs?

> I recommend starting with understanding the actual memory limits
> as set by your configuration (personally I believe that memory
> overcommit is less evil than some people think). Have a close look
> at /proc/meminfo and if possible disable JIT and check if it changes
> anything. Also if possible try starting with only a few active
> connections and increase load carefully once a steady state (in
> terms of memory usage) has been reached.

Yes, understanding the memory limits is what I was trying to do.
I was questioning my understanding but it seems it was Linux that 
tripped me,
or more like my lack of understanding there, rather than the database.
Memory management and /proc/meminfo still manages to confuse me.

Again, thanks for your help!

Cheers,
Thomas

p.s.: To anybody who stumbles upon this in the future,
if you set `overcommit_memory=2`, don't forget `overcommit_ratio`.