Thread: Partitions and work_mem?

Partitions and work_mem?

From
Dave Johansen
Date:
I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how work_mem and partitions interact.

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
The above wiki states that "if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem." If I have a table that is partitioned does each partition count as a "table" and get its on work_mem?

For example, say I have the following table partitioned by the time column:
CREATE TABLE values (time TIMESTAMP, value INTEGER);
If I do the following query will it require 1 work_mem or N work_mem's (where N is the number of partitions)?
SELECT * FROM values ORDER BY time;

Thanks,
Dave

Re: Partitions and work_mem?

From
Jeff Janes
Date:
On Tue, Oct 14, 2014 at 10:08 AM, Dave Johansen <davejohansen@gmail.com> wrote:
I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how work_mem and partitions interact.

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
The above wiki states that "if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem." If I have a table that is partitioned does each partition count as a "table" and get its on work_mem?

For example, say I have the following table partitioned by the time column:
CREATE TABLE values (time TIMESTAMP, value INTEGER);
If I do the following query will it require 1 work_mem or N work_mem's (where N is the number of partitions)?
SELECT * FROM values ORDER BY time;

The specific query you show should do the append first and then the sort on the result, and so would only use 1 work_mem.

However, other queries could cause it to use one (or more) sorts per partition, for example a self-join which it decides to run as a sort-merge join.
 
Cheers,

Jeff

Re: Partitions and work_mem?

From
Josh Berkus
Date:
On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

BTW, 8.4 is EOL.  Maybe time to upgrade?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Partitions and work_mem?

From
Dave Johansen
Date:
On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:
On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

Thanks for the feedback. That's very helpful.
 
BTW, 8.4 is EOL.  Maybe time to upgrade?

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

Re: Partitions and work_mem?

From
Igor Neyman
Date:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

Igor Neyman

Re: Partitions and work_mem?

From
Dave Johansen
Date:
On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).


Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

Re: Partitions and work_mem?

From
Igor Neyman
Date:

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:20 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

 

I don’t think that RedHat can maintain Postgres version which was EOLed.

Postgres 8.4 is not supported by PostgreSQL community.

 

Igor Neyman

 

 

Re: Partitions and work_mem?

From
Dave Johansen
Date:
On Wed, Oct 15, 2014 at 1:36 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:20 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

 

I don’t think that RedHat can maintain Postgres version which was EOLed.

Postgres 8.4 is not supported by PostgreSQL community.


This conversation has probably become a bit off topic, but my understanding is that what you're paying RedHat for is a stable platform for a long period of time. That means creating/backporting of fixes for security and other critical issues for packages that have been EOLed.

Assuming the above is true, (which I beleve to be the case https://access.redhat.com/support/policy/updates/errata ), I don't see what would prevent RedHat from making a patch and applying it to the latest 8.4 release to resolve any newly discovered issues. Isn't that the whole point of open source and RedHat being able to do with the code what it wishes as long as it meets the requirements of the license? So are you claiming that RedHat doesn't/won't do this? Is incapable of doing this? Or am I missing something?

Re: Partitions and work_mem?

From
Igor Neyman
Date:

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:49 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:36 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:20 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

 

I don’t think that RedHat can maintain Postgres version which was EOLed.

Postgres 8.4 is not supported by PostgreSQL community.

 

This conversation has probably become a bit off topic, but my understanding is that what you're paying RedHat for is a stable platform for a long period of time. That means creating/backporting of fixes for security and other critical issues for packages that have been EOLed.

Assuming the above is true, (which I beleve to be the case https://access.redhat.com/support/policy/updates/errata ), I don't see what would prevent RedHat from making a patch and applying it to the latest 8.4 release to resolve any newly discovered issues. Isn't that the whole point of open source and RedHat being able to do with the code what it wishes as long as it meets the requirements of the license? So are you claiming that RedHat doesn't/won't do this? Is incapable of doing this? Or am I missing something?

 

 

Tom Lane is probably better authority on this issue.

Let’s wait and see what he says.

 

 

Re: Partitions and work_mem?

From
Josh Berkus
Date:
On 10/15/2014 01:19 PM, Dave Johansen wrote:
> Sorry I don't understand what you mean by that. My understanding is that
> RedHat maintains fixes for security and other major issues for packages
> that have been EOLed. Are you implying that that's not the case? Or
> something else?

RH probably backpatches our fixes as they come out.  They did in the
past, anyway.

I just had the impression from your original post that this was a new
system; if so, it would make sense to build it on a version of Postgres
which wasn't already EOL.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Partitions and work_mem?

From
Tom Lane
Date:
Igor Neyman <ineyman@perceptron.com> writes:
> From: Dave Johansen [mailto:davejohansen@gmail.com]
> This conversation has probably become a bit off topic, but my understanding is that what you're paying RedHat for is
astable platform for a long period of time. That means creating/backporting of fixes for security and other critical
issuesfor packages that have been EOLed. 
> Assuming the above is true, (which I beleve to be the case https://access.redhat.com/support/policy/updates/errata ),
Idon't see what would prevent RedHat from making a patch and applying it to the latest 8.4 release to resolve any newly
discoveredissues. Isn't that the whole point of open source and RedHat being able to do with the code what it wishes as
longas it meets the requirements of the license? So are you claiming that RedHat doesn't/won't do this? Is incapable of
doingthis? Or am I missing something? 

> Tom Lane is probably better authority on this issue.
> Let’s wait and see what he says.

That is in fact exactly what people pay Red Hat to do, and it was my job
to do it for Postgres when I worked there.  I don't work there any more,
but I'm sure my replacement is entirely capable of back-patching fixes as
needed.

            regards, tom lane


Re: Partitions and work_mem?

From
Dave Johansen
Date:
On Wed, Oct 15, 2014 at 3:25 PM, Josh Berkus <josh@agliodbs.com> wrote:
On 10/15/2014 01:19 PM, Dave Johansen wrote:
> Sorry I don't understand what you mean by that. My understanding is that
> RedHat maintains fixes for security and other major issues for packages
> that have been EOLed. Are you implying that that's not the case? Or
> something else?

RH probably backpatches our fixes as they come out.  They did in the
past, anyway.

I just had the impression from your original post that this was a new
system; if so, it would make sense to build it on a version of Postgres
which wasn't already EOL.

Sorry for not being more clear in the original post. This is a system that has been running for just over a year and the beta for RHEL 7 hadn't even been released when we started things so 8.4 was the only real option.

Having said all of that, we recently had an increase in the number of users and we had experienced database restarts because the Linux Out of Memory Killer would kill a query on occassion. I just wanted to make sure that the changes we were making were based on sound logic and we wouldn't be experiencing these restarts anymore.

Thanks everyone for the help,
Dave

Re: Partitions and work_mem?

From
Magnus Hagander
Date:


On Oct 16, 2014 12:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>
> Igor Neyman <ineyman@perceptron.com> writes:
> > From: Dave Johansen [mailto:davejohansen@gmail.com]
> > This conversation has probably become a bit off topic, but my understanding is that what you're paying RedHat for is a stable platform for a long period of time. That means creating/backporting of fixes for security and other critical issues for packages that have been EOLed.
> > Assuming the above is true, (which I beleve to be the case https://access.redhat.com/support/policy/updates/errata ), I don't see what would prevent RedHat from making a patch and applying it to the latest 8.4 release to resolve any newly discovered issues. Isn't that the whole point of open source and RedHat being able to do with the code what it wishes as long as it meets the requirements of the license? So are you claiming that RedHat doesn't/won't do this? Is incapable of doing this? Or am I missing something?
>
> > Tom Lane is probably better authority on this issue.
> > Let’s wait and see what he says.
>
> That is in fact exactly what people pay Red Hat to do, and it was my job
> to do it for Postgres when I worked there.  I don't work there any more,
> but I'm sure my replacement is entirely capable of back-patching fixes as
> needed.
>

Do they backpatch everything, or just things like security issues? (in sure they can do either, but do you know what the policy says?)

Either way it does also mean that the support requests for such versions would need to go to redhat rather than the community lists at some point - right now their 8.4 would be almost the same as ours, but down the road they'll start separating more and more of course.

For the op - of you haven't already, is suggest you take a look at yum.postgresql.org which will get you a modern, supported, postgresql version for rhel 6. Regardless of the support, you get all the other improvements in postgresql.

/Magnus

Re: Partitions and work_mem?

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> On Oct 16, 2014 12:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> That is in fact exactly what people pay Red Hat to do, and it was my job
>> to do it for Postgres when I worked there.  I don't work there any more,
>> but I'm sure my replacement is entirely capable of back-patching fixes as
>> needed.

> Do they backpatch everything, or just things like security issues? (in sure
> they can do either, but do you know what the policy says?)

Security issues are high priority to fix, otherwise it takes (usually)
complaints from paying customers and/or effective lobbying from the
package's maintainer.  They have finite bandwidth for package updates,
and they also take seriously the idea that a RHEL release series is
supposed to be a stable platform.  When I was there I was usually able
to get them to update to new PG minor releases only when said releases
involved security fixes, otherwise the can got kicked down the road...

> Either way it does also mean that the support requests for such versions
> would need to go to redhat rather than the community lists at some point -
> right now their 8.4 would be almost the same as ours, but down the road
> they'll start separating more and more of course.

If you want a fix in Red Hat's version of 8.4, you need to be talking to
them *now*, not "at some point".  The community lost any input into that
when we stopped updating 8.4.

> For the op - of you haven't already, is suggest you take a look at
> yum.postgresql.org which will get you a modern, supported, postgresql
> version for rhel 6. Regardless of the support, you get all the other
> improvements in postgresql.

Yeah.  Also, Red Hat is shipping a newer version (I think 9.2.something)
as part of their "software collections" packaging initiative.  I do not
know whether that's included in a standard RHEL subscription or costs
extra.

            regards, tom lane


Re: Partitions and work_mem?

From
Dave Johansen
Date:
On Mon, Nov 17, 2014 at 8:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
> On Oct 16, 2014 12:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> That is in fact exactly what people pay Red Hat to do, and it was my job
>> to do it for Postgres when I worked there.  I don't work there any more,
>> but I'm sure my replacement is entirely capable of back-patching fixes as
>> needed.

> Do they backpatch everything, or just things like security issues? (in sure
> they can do either, but do you know what the policy says?)

Security issues are high priority to fix, otherwise it takes (usually)
complaints from paying customers and/or effective lobbying from the
package's maintainer.  They have finite bandwidth for package updates,
and they also take seriously the idea that a RHEL release series is
supposed to be a stable platform.  When I was there I was usually able
to get them to update to new PG minor releases only when said releases
involved security fixes, otherwise the can got kicked down the road...

> Either way it does also mean that the support requests for such versions
> would need to go to redhat rather than the community lists at some point -
> right now their 8.4 would be almost the same as ours, but down the road
> they'll start separating more and more of course.

If you want a fix in Red Hat's version of 8.4, you need to be talking to
them *now*, not "at some point".  The community lost any input into that
when we stopped updating 8.4.

> For the op - of you haven't already, is suggest you take a look at
> yum.postgresql.org which will get you a modern, supported, postgresql
> version for rhel 6. Regardless of the support, you get all the other
> improvements in postgresql.

Yeah.  Also, Red Hat is shipping a newer version (I think 9.2.something)
as part of their "software collections" packaging initiative.  I do not
know whether that's included in a standard RHEL subscription or costs
extra.

We've looked into both the repos at yum.postgresql.org and Red Hat's SCL, but as most people are already aware, the problem is just that it takes a LONG time to move a production system to a new version of a major component, if it ever happens at all.

On a side note, the SCL stuff does require the right type of subsciption ( https://access.redhat.com/solutions/472793 ) and has a MUCH shorter life cycle than the rest of RHEL ( https://access.redhat.com/support/policy/updates/rhscl ) so it's honestly kind of hard to use in most production environments.