pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedin-memory sorting
10+ messages / 4 participants
[nested] [flat]
* in-memory sorting
@ 2010-08-19 05:23 Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2010-08-19 05:23 UTC (permalink / raw)
To: pgsql-performance
I've got this explain: http://explain.depesz.com/s/Xh9
And these settings:
default_statistics_target = 50 # pgtune wizard 2010-08-17
maintenance_work_mem = 1GB # pgtune wizard 2010-08-17
constraint_exclusion = on # pgtune wizard 2010-08-17
checkpoint_completion_target = 0.9 # pgtune wizard 2010-08-17
effective_cache_size = 36GB # sam
work_mem = 288MB # pgtune wizard 2010-08-17
wal_buffers = 8MB # pgtune wizard 2010-08-17
#checkpoint_segments = 16 # pgtune wizard 2010-08-17
checkpoint_segments = 30 # sam
shared_buffers = 11GB # pgtune wizard 2010-08-17
max_connections = 80 # pgtune wizard 2010-08-17
cpu_tuple_cost = 0.0030 # sam
cpu_index_tuple_cost = 0.0010 # sam
cpu_operator_cost = 0.0005 # sam
#random_page_cost = 2.0 # sam
I'm not understanding why it is sorting on disk if it would fit within
a work_mem segment - by a fairly wide margin. Is there something else
I can do to get that sort to happen in memory?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 05:45 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:55 ` Re: in-memory sorting Pavel Stehule <pavel.stehule@gmail.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 13:29 ` Re: in-memory sorting Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 10+ messages in thread
From: Samuel Gendler @ 2010-08-19 05:45 UTC (permalink / raw)
To: pgsql-performance
Answered my own question. Cranking work_mem up to 350MB revealed that
the in-memory sort requires more memory than the disk sort.
On Wed, Aug 18, 2010 at 10:23 PM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> I've got this explain: http://explain.depesz.com/s/Xh9
>
> And these settings:
> default_statistics_target = 50 # pgtune wizard 2010-08-17
> maintenance_work_mem = 1GB # pgtune wizard 2010-08-17
> constraint_exclusion = on # pgtune wizard 2010-08-17
> checkpoint_completion_target = 0.9 # pgtune wizard 2010-08-17
> effective_cache_size = 36GB # sam
> work_mem = 288MB # pgtune wizard 2010-08-17
> wal_buffers = 8MB # pgtune wizard 2010-08-17
> #checkpoint_segments = 16 # pgtune wizard 2010-08-17
> checkpoint_segments = 30 # sam
> shared_buffers = 11GB # pgtune wizard 2010-08-17
> max_connections = 80 # pgtune wizard 2010-08-17
> cpu_tuple_cost = 0.0030 # sam
> cpu_index_tuple_cost = 0.0010 # sam
> cpu_operator_cost = 0.0005 # sam
> #random_page_cost = 2.0 # sam
>
> I'm not understanding why it is sorting on disk if it would fit within
> a work_mem segment - by a fairly wide margin. Is there something else
> I can do to get that sort to happen in memory?
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 05:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Pavel Stehule @ 2010-08-19 05:55 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
Hello
>>
>> I'm not understanding why it is sorting on disk if it would fit within
>> a work_mem segment - by a fairly wide margin. Is there something else
>> I can do to get that sort to happen in memory?
>>
Planner working with estimations. So there is some probability so
planner expected a larger result set and used a external sort.
Probably quick sort takes more memory too. Your statistic are probably
out of range - system expecting 0.5 mil rec and get 2 mil rec.
Regards
Pavel
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 06:24 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-08-19 06:24 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> Answered my own question. Cranking work_mem up to 350MB revealed that
> the in-memory sort requires more memory than the disk sort.
Note that unless you run VERY few client connections, it's usually
better to leave work_mem somewhere in the 1 to 32Meg range and have
the connection or user or database that needs 350Meg be set there.
I.e.
<connect>
set work_mem='512MB';
<execute query
OR
alter user memoryhog set work_mem='512MB';
OR
alter database memhogdb set work_mem='512MB';
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-19 06:38 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:52 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2010-08-19 06:38 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Yeah, although with 48GB of available memory and not that much concurrency,
I'm not sure it matters that much. But point taken, I'll see about modifying
the app such that work_mem gets set on a per-query basis.
On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
> <sgendler@ideasculptor.com> wrote:
> > Answered my own question. Cranking work_mem up to 350MB revealed that
> > the in-memory sort requires more memory than the disk sort.
>
> Note that unless you run VERY few client connections, it's usually
> better to leave work_mem somewhere in the 1 to 32Meg range and have
> the connection or user or database that needs 350Meg be set there.
>
> I.e.
>
> <connect>
> set work_mem='512MB';
> <execute query
>
> OR
>
> alter user memoryhog set work_mem='512MB';
>
> OR
>
> alter database memhogdb set work_mem='512MB';
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 06:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 07:06 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2010-08-19 06:52 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
Exactly, it's about the concurrency. I have a server with 128G ram
but it runs dozens of queries at a time for hundreds of clients a
second. The chance that something big for work_mem might jump up and
bite me are pretty good there. Even so, at 16Meg it's not really big
for that machine, and I might test cranking it up. Note that large
work_mem can cause the kernel to flush its cache, which means going to
disk for everybody's data, and all the queries are slow instead of
one. Keep an eye on how high work_mem affects your kernel cache.
On Thu, Aug 19, 2010 at 12:38 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> Yeah, although with 48GB of available memory and not that much concurrency,
> I'm not sure it matters that much. But point taken, I'll see about modifying
> the app such that work_mem gets set on a per-query basis.
>
> On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
>> <sgendler@ideasculptor.com> wrote:
>> > Answered my own question. Cranking work_mem up to 350MB revealed that
>> > the in-memory sort requires more memory than the disk sort.
>>
>> Note that unless you run VERY few client connections, it's usually
>> better to leave work_mem somewhere in the 1 to 32Meg range and have
>> the connection or user or database that needs 350Meg be set there.
>>
>> I.e.
>>
>> <connect>
>> set work_mem='512MB';
>> <execute query
>>
>> OR
>>
>> alter user memoryhog set work_mem='512MB';
>>
>> OR
>>
>> alter database memhogdb set work_mem='512MB';
>
>
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:52 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-08-19 07:06 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 07:14 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 07:16 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Samuel Gendler @ 2010-08-19 07:06 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Incidentally, if I set values on the connection before querying, is there an
easy way to get things back to default values or will my code need to know
the prior value and explicitly set it back? Something like
<get connection from pool>
set work_mem = '512MB'
query
set value = 'default'
<return connection to pool>
or maybe
<get connection from pool>
BEGIN;
set work_mem='512MB'
select query
ROLLBACK;
<return connection to pool>
On Wed, Aug 18, 2010 at 11:52 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> Exactly, it's about the concurrency. I have a server with 128G ram
> but it runs dozens of queries at a time for hundreds of clients a
> second. The chance that something big for work_mem might jump up and
> bite me are pretty good there. Even so, at 16Meg it's not really big
> for that machine, and I might test cranking it up. Note that large
> work_mem can cause the kernel to flush its cache, which means going to
> disk for everybody's data, and all the queries are slow instead of
> one. Keep an eye on how high work_mem affects your kernel cache.
>
> On Thu, Aug 19, 2010 at 12:38 AM, Samuel Gendler
> <sgendler@ideasculptor.com> wrote:
> > Yeah, although with 48GB of available memory and not that much
> concurrency,
> > I'm not sure it matters that much. But point taken, I'll see about
> modifying
> > the app such that work_mem gets set on a per-query basis.
> >
> > On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe <scott.marlowe@gmail.com
> >
> > wrote:
> >>
> >> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
> >> <sgendler@ideasculptor.com> wrote:
> >> > Answered my own question. Cranking work_mem up to 350MB revealed that
> >> > the in-memory sort requires more memory than the disk sort.
> >>
> >> Note that unless you run VERY few client connections, it's usually
> >> better to leave work_mem somewhere in the 1 to 32Meg range and have
> >> the connection or user or database that needs 350Meg be set there.
> >>
> >> I.e.
> >>
> >> <connect>
> >> set work_mem='512MB';
> >> <execute query
> >>
> >> OR
> >>
> >> alter user memoryhog set work_mem='512MB';
> >>
> >> OR
> >>
> >> alter database memhogdb set work_mem='512MB';
> >
> >
>
>
>
> --
> To understand recursion, one must first understand recursion.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:52 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 07:06 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 07:14 ` Samuel Gendler <sgendler@ideasculptor.com>
1 sibling, 0 replies; 10+ messages in thread
From: Samuel Gendler @ 2010-08-19 07:14 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On Thu, Aug 19, 2010 at 12:06 AM, Samuel Gendler
<sgendler@ideasculptor.com>wrote:
> Incidentally, if I set values on the connection before querying, is there
> an easy way to get things back to default values or will my code need to
> know the prior value and explicitly set it back? Something like
>
> <get connection from pool>
> set work_mem = '512MB'
> query
> set value = 'default'
> <return connection to pool>
>
> or maybe
>
> <get connection from pool>
> BEGIN;
> set work_mem='512MB'
> select query
> ROLLBACK;
> <return connection to pool>
>
>
I guess I'm getting the hang of this whole postgres thing because those were
both wild guesses and both of them appear to work.
set work_mem=default sets it to the value in the config file, and setting
within a transaction and rolling back also restores the original value.
>
> On Wed, Aug 18, 2010 at 11:52 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
>
>> Exactly, it's about the concurrency. I have a server with 128G ram
>> but it runs dozens of queries at a time for hundreds of clients a
>> second. The chance that something big for work_mem might jump up and
>> bite me are pretty good there. Even so, at 16Meg it's not really big
>> for that machine, and I might test cranking it up. Note that large
>> work_mem can cause the kernel to flush its cache, which means going to
>> disk for everybody's data, and all the queries are slow instead of
>> one. Keep an eye on how high work_mem affects your kernel cache.
>>
>> On Thu, Aug 19, 2010 at 12:38 AM, Samuel Gendler
>> <sgendler@ideasculptor.com> wrote:
>> > Yeah, although with 48GB of available memory and not that much
>> concurrency,
>> > I'm not sure it matters that much. But point taken, I'll see about
>> modifying
>> > the app such that work_mem gets set on a per-query basis.
>> >
>> > On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe <
>> scott.marlowe@gmail.com>
>> > wrote:
>> >>
>> >> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
>> >> <sgendler@ideasculptor.com> wrote:
>> >> > Answered my own question. Cranking work_mem up to 350MB revealed
>> that
>> >> > the in-memory sort requires more memory than the disk sort.
>> >>
>> >> Note that unless you run VERY few client connections, it's usually
>> >> better to leave work_mem somewhere in the 1 to 32Meg range and have
>> >> the connection or user or database that needs 350Meg be set there.
>> >>
>> >> I.e.
>> >>
>> >> <connect>
>> >> set work_mem='512MB';
>> >> <execute query
>> >>
>> >> OR
>> >>
>> >> alter user memoryhog set work_mem='512MB';
>> >>
>> >> OR
>> >>
>> >> alter database memhogdb set work_mem='512MB';
>> >
>> >
>>
>>
>>
>> --
>> To understand recursion, one must first understand recursion.
>>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:24 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:52 ` Re: in-memory sorting Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 07:06 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 07:16 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2010-08-19 07:16 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
On Thu, Aug 19, 2010 at 1:06 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> Incidentally, if I set values on the connection before querying, is there an
> easy way to get things back to default values or will my code need to know
> the prior value and explicitly set it back? Something like
reset work_mem;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: in-memory sorting
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Re: in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
@ 2010-08-19 13:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2010-08-19 13:29 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-performance
Samuel Gendler <sgendler@ideasculptor.com> writes:
> Answered my own question. Cranking work_mem up to 350MB revealed that
> the in-memory sort requires more memory than the disk sort.
Yeah. The on-disk representation of sortable data is tighter than the
in-memory representation for various reasons, mostly that we're willing
to work at making it small. Datums aren't necessarily properly aligned
for example, and there's also palloc overhead to consider in-memory.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-08-19 13:29 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-19 05:23 in-memory sorting Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:45 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 05:55 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-08-19 06:24 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 06:38 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 06:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 07:06 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 07:14 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-08-19 07:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-19 13:29 ` Tom Lane <tgl@sss.pgh.pa.us>
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