From: Scott Marlowe <scott.marlowe@gmail.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: work_mem and shared_buffers
Date: Fri, 9 Nov 2007 13:12:42 -0600
Message-ID: <dcc563d10711091112r73ef016dp56d01bd6dd04a1d3@mail.gmail.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197DE5@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197DE4@SAB-FENWICK.sab.uiuc.edu>
<47349F4E.5070204@enterprisedb.com>
<B10E6810AC2A2F4EA7550D072CDE8760197DE5@SAB-FENWICK.sab.uiuc.edu>
On Nov 9, 2007 12:08 PM, Campbell, Lance <lance@uiuc.edu> wrote:
> How do you know when you should up the value of work_mem? Just play
> with the number. Is there a query I could do that would tell me if
> PostgreSql is performing SQL that could use more memory for sorting?
Trial and error. Note that you can set work_mem for a given session.
While it may seem that making work_mem bigger will always help, that's
not necessarily the case.
Using this query:
select count(*) from (select * from myreporttable where lasttime >
now() - interval '1 week' order by random() ) as l
I did the following: (I ran the query by itself once to fill the
buffers / cache of the machine with the data)
work_mem Time:
1000kB 29215.563 ms
4000kB 20612.489 ms
8000kB 18408.087 ms
16000kB 16893.964 ms
32000kB 17681.221 ms
64000kB 22439.988 ms
125MB 23398.891 ms
250MB 25461.797 ms
Note that my best time was at around 16 Meg work_mem. This data set
is MUCH bigger than 16 Meg, it's around 300-400 Meg. But work_mem
optimized out at 16 Meg. Btw, I tried it going as high as 768 Meg,
and it was still slower than 16M.
This machine has 2 Gigs ram and is optimized for IO not CPU performance.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, lance@uiuc.edu, heikki@enterprisedb.com
Subject: Re: work_mem and shared_buffers
In-Reply-To: <dcc563d10711091112r73ef016dp56d01bd6dd04a1d3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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