pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitions and work_mem?
15+ messages / 6 participants
[nested] [flat]
* Partitions and work_mem?
@ 2014-10-14 17:08 Dave Johansen <davejohansen@gmail.com>
2014-10-14 18:29 ` Re: Partitions and work_mem? Jeff Janes <jeff.janes@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Dave Johansen @ 2014-10-14 17:08 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-14 18:29 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Jeff Janes @ 2014-10-14 18:29 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-15 17:10 ` Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2014-10-15 17:10 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
@ 2014-10-15 20:05 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 15+ messages in thread
From: Dave Johansen @ 2014-10-15 20:05 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-15 20:08 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Igor Neyman @ 2014-10-15 20:08 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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<mailto: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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
@ 2014-10-15 20:19 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:25 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Dave Johansen @ 2014-10-15 20:19 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-15 20:36 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Igor Neyman @ 2014-10-15 20:36 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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<mailto:ineyman@perceptron.com>> wrote:
From: pgsql-performance-owner@postgresql.org<mailto:pgsql-performance-owner@postgresql.org> [mailto: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<mailto: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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
@ 2014-10-15 20:49 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 15+ messages in thread
From: Dave Johansen @ 2014-10-15 20:49 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-15 21:00 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:58 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Igor Neyman @ 2014-10-15 21:00 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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<mailto:ineyman@perceptron.com>> wrote:
From: Dave Johansen [mailto:davejohansen@gmail.com<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<mailto:ineyman@perceptron.com>> wrote:
From: pgsql-performance-owner@postgresql.org<mailto:pgsql-performance-owner@postgresql.org> [mailto: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<mailto: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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
@ 2014-10-15 22:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-17 06:57 ` Re: Partitions and work_mem? Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2014-10-15 22:58 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Dave Johansen <davejohansen@gmail.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
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.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:58 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-11-17 06:57 ` Magnus Hagander <magnus@hagander.net>
2014-11-17 15:13 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Magnus Hagander @ 2014-11-17 06:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Igor Neyman <ineyman@perceptron.com>; Dave Johansen <davejohansen@gmail.com>; pgsql-performance; Josh Berkus <josh@agliodbs.com>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:58 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
2014-11-17 06:57 ` Re: Partitions and work_mem? Magnus Hagander <magnus@hagander.net>
@ 2014-11-17 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-18 03:34 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2014-11-17 15:13 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Igor Neyman <ineyman@perceptron.com>; Dave Johansen <davejohansen@gmail.com>; pgsql-performance; Josh Berkus <josh@agliodbs.com>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:58 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
2014-11-17 06:57 ` Re: Partitions and work_mem? Magnus Hagander <magnus@hagander.net>
2014-11-17 15:13 ` Re: Partitions and work_mem? Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-11-18 03:34 ` Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Dave Johansen @ 2014-11-18 03:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Magnus Hagander <magnus@hagander.net>; Igor Neyman <ineyman@perceptron.com>; pgsql-performance; Josh Berkus <josh@agliodbs.com>
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
@ 2014-10-15 22:25 ` Josh Berkus <josh@agliodbs.com>
2014-11-16 22:25 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2014-10-15 22:25 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; Igor Neyman <ineyman@perceptron.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Partitions and work_mem?
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 17:10 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Re: Partitions and work_mem? Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Re: Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-15 22:25 ` Re: Partitions and work_mem? Josh Berkus <josh@agliodbs.com>
@ 2014-11-16 22:25 ` Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Dave Johansen @ 2014-11-16 22:25 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Igor Neyman <ineyman@perceptron.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2014-11-18 03:34 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-14 17:08 Partitions and work_mem? Dave Johansen <davejohansen@gmail.com>
2014-10-14 18:29 ` Jeff Janes <jeff.janes@gmail.com>
2014-10-15 17:10 ` Josh Berkus <josh@agliodbs.com>
2014-10-15 20:05 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:08 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:19 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 20:36 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 20:49 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 21:00 ` Igor Neyman <ineyman@perceptron.com>
2014-10-15 22:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-17 06:57 ` Magnus Hagander <magnus@hagander.net>
2014-11-17 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-11-18 03:34 ` Dave Johansen <davejohansen@gmail.com>
2014-10-15 22:25 ` Josh Berkus <josh@agliodbs.com>
2014-11-16 22:25 ` Dave Johansen <davejohansen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox