Обсуждение: is there a way to firmly cap postgres worker memory consumption?

Поиск

Список

Период

Сортировка

is there a way to firmly cap postgres worker memory consumption?

От

Steve Kehlet

Дата:

08 апреля 2014 г., 20:49:21

I'm running into some scenarios where errant postgres queries are consuming all memory and bringing down production boxes. I'm running Postgres 9.3.4 on CentOS 5.x VMs under XenServer. My custom config is here: https://gist.github.com/skehlet/9984666

The incidents may be related to reports of an array_agg memory leak, and another recent incident seems like it was due to the xpath() function provided by --with-xml. There's some more information on the former in my post at: http://dba.stackexchange.com/questions/62587/why-does-this-query-cause-the-postgres-worker-process-to-consume-so-much-memory, if it's interesting. I'd like to dig into that further, maybe on another thread.

But for now, the devs are asking me for some way to put a cap on a postgres query's total memory consumption. I'm familiar with the available settings on the "Resource Consumption" docs (and you can see my settings in my gist above, including work_mem turned way down to 1MB for testing), but it seems like there are things like Materialize that remain uncappable, since they're not constrained by the shared_buffers and work_mem limits. For example in my post to dba.stackexchange above, I found by doing a "set enable_material=false;" I could stop a particularly nasty query from consuming all memory, narrowing the problem somewhat, but that query is one we have to live with in the near term.

With all that laid out, can anyone help with the following questions:

- Is there any way to set to total memory cap on a worker processes memory consumption?

- Are there other (even undocumented) settings to cap memory usage?

- Other ideas?

In the meantime, to stop the bleeding I'm looking into using the postgres feature to coerce the OOM killer to kill rogue child processes first (-DLINUX_OOM_ADJ). Unfortunately, another setback, even the latest kernel on CentOS 5.x doesn't allow non-root processes to write to /proc/self/oom_adj, so it isn't working. So I'll either need to patch the CentOS kernel and rebuild, move to a newer kernel, or maybe do something hacky like run a background process to reassign the oom_adj value for all postgres workers found.

Thanks for any help/ideas!

Re: is there a way to firmly cap postgres worker memory consumption?

От

Amador Alvarez

Дата:

08 апреля 2014 г., 21:41:57

Hi Steve,

Did you check if your OS allows you to limit memory usage per user / process basis?

Did you either profiled or debugged it to see what is going on?

When memory consumption grows , Doesn't ever swap out?

Thanks,

A.A.

On Tue, Apr 8, 2014 at 10:48 AM, Steve Kehlet <steve.kehlet@gmail.com> wrote:

I'm running into some scenarios where errant postgres queries are consuming all memory and bringing down production boxes. I'm running Postgres 9.3.4 on CentOS 5.x VMs under XenServer. My custom config is here: https://gist.github.com/skehlet/9984666

The incidents may be related to reports of an array_agg memory leak, and another recent incident seems like it was due to the xpath() function provided by --with-xml. There's some more information on the former in my post at: http://dba.stackexchange.com/questions/62587/why-does-this-query-cause-the-postgres-worker-process-to-consume-so-much-memory, if it's interesting. I'd like to dig into that further, maybe on another thread.

But for now, the devs are asking me for some way to put a cap on a postgres query's total memory consumption. I'm familiar with the available settings on the "Resource Consumption" docs (and you can see my settings in my gist above, including work_mem turned way down to 1MB for testing), but it seems like there are things like Materialize that remain uncappable, since they're not constrained by the shared_buffers and work_mem limits. For example in my post to dba.stackexchange above, I found by doing a "set enable_material=false;" I could stop a particularly nasty query from consuming all memory, narrowing the problem somewhat, but that query is one we have to live with in the near term.

With all that laid out, can anyone help with the following questions:

- Is there any way to set to total memory cap on a worker processes memory consumption?
- Are there other (even undocumented) settings to cap memory usage?
- Other ideas?

In the meantime, to stop the bleeding I'm looking into using the postgres feature to coerce the OOM killer to kill rogue child processes first (-DLINUX_OOM_ADJ). Unfortunately, another setback, even the latest kernel on CentOS 5.x doesn't allow non-root processes to write to /proc/self/oom_adj, so it isn't working. So I'll either need to patch the CentOS kernel and rebuild, move to a newer kernel, or maybe do something hacky like run a background process to reassign the oom_adj value for all postgres workers found.

Thanks for any help/ideas!

Re: is there a way to firmly cap postgres worker memory consumption?

От

Steve Kehlet

Дата:

08 апреля 2014 г., 21:58:51

Did you check if your OS allows you to limit memory usage per user / process basis?

I'll definitely dig more into this. I found some stuff saying it isn't as easy as one would expect, i.e. not just a ulimit and done. Or maybe it is, in this case. I will play around.

Did you either profiled or debugged it to see what is going on?

I would love to learn more about how to do this, to get to the bottom of the memory usage. I can google around, or can you suggest any reads?

When memory consumption grows , Doesn't ever swap out?

Yes, it consumes all swap.

Re: is there a way to firmly cap postgres worker memory consumption?

От

Tom Lane

Дата:

08 апреля 2014 г., 22:06:20

Steve Kehlet <steve.kehlet@gmail.com> writes:
> But for now, the devs are asking me for some way to put a cap on a postgres
> query's total memory consumption. I'm familiar with the available settings
> on the "Resource Consumption" docs (and you can see my settings in my gist
> above, including work_mem turned way down to 1MB for testing), but it seems
> like there are things like Materialize that remain uncappable, since
> they're not constrained by the shared_buffers and work_mem limits.

Materialize nodes should honor the work_mem limit.  That's per node of
course, but you only have half a dozen of them in this query, so I doubt
that's where the problem is.  You mentioned array_agg ... how are you
using that exactly, and how large a result are you expecting it to create?
If you had an array_agg being used in a HashAggregate plan node, I'd
figure for sure that was the source of your problem, but the explain
output shows it's a GroupAggregate so there should only be one array_agg
active at a time.

Anyway, what I would try doing is starting the postmaster with a "ulimit
-d" value corresponding to the max per-process data segment size you want.
Something in the range of 50-100MB would probably be reasonable if your
queries aren't too complex.

            regards, tom lane

Re: is there a way to firmly cap postgres worker memory consumption?

От

Tom Lane

Дата:

08 апреля 2014 г., 22:24:02

Steve Kehlet <steve.kehlet@gmail.com> writes:
>> Did you either profiled or debugged it to see what is going on?

> I would love to learn more about how to do this, to get to the bottom of
> the memory usage. I can google around, or can you suggest any reads?

Once you've got a ulimit in place so that malloc eventually fails with
ENOMEM, the backend process should print a memory context dump on stderr
when it hits that.  Make sure your logging setup captures the process
stderr someplace (logging_collector works for this, syslog does not).
Post the dump here when you've got it.

            regards, tom lane

Re: is there a way to firmly cap postgres worker memory consumption?

От

Steve Kehlet

Дата:

09 апреля 2014 г., 02:09:12

On Tue, Apr 8, 2014 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Once you've got a ulimit in place so that malloc eventually fails with
ENOMEM, the backend process should print a memory context dump on stderr
when it hits that. Make sure your logging setup captures the process
stderr someplace (logging_collector works for this, syslog does not).
Post the dump here when you've got it.

Thank you. For some reason I couldn't get it to trip with "ulimit -d 51200", but "ulimit -v 1572864" (1.5GiB) got me this in serverlog. I hope this is readable, if not it's also here: https://gist.github.com/skehlet/10206741

TopMemoryContext: 123760 total in 16 blocks; 2720 free (7 chunks); 121040 used

TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used

Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used

Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used

TopTransactionContext: 8192 total in 1 blocks; 7368 free (0 chunks); 824 used

MessageContext: 4186112 total in 9 blocks; 633960 free (19 chunks); 3552152 used

JoinRelHashTable: 8192 total in 1 blocks; 624 free (0 chunks); 7568 used

Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

smgr relation table: 24576 total in 2 blocks; 9808 free (4 chunks); 14768 used

TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used

Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used

PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used

PortalHeapMemory: 1024 total in 1 blocks; 816 free (0 chunks); 208 used

ExecutorState: 566208 total in 12 blocks; 195256 free (2 chunks); 370952 used

TupleSort: 17817440 total in 36 blocks; 9326832 free (8801 chunks); 8490608 used

TupleSort: 17500568 total in 86 blocks; 12016040 free (2896 chunks); 5484528 used

TupleSort: 17616584 total in 86 blocks; 9975520 free (3649 chunks); 7641064 used

TupleSort: 10825056 total in 14 blocks; 3896400 free (45082 chunks); 6928656 used

TupleSort: 17459720 total in 67 blocks; 8495600 free (2226 chunks); 8964120 used

TupleSort: 10899360 total in 23 blocks; 2112448 free (23706 chunks); 8786912 used

TupleSort: 11908776 total in 15 blocks; 8344064 free (145642 chunks); 3564712 used

printtup: 0 total in 0 blocks; 0 free (0 chunks); 0 used