Re: [PERFORM] work_mem in high transaction rate database

2009-03-04 Thread Scott Marlowe
On Wed, Mar 4, 2009 at 11:18 AM, Scott Carey wrote: > You may have decreased performance in your batch jobs with the lower > work_mem setting. That would be why I recommended benchmarking queries that need more memory and setting work_mem for those queries alone. > Additionally, the fact that yo

Re: [PERFORM] work_mem in high transaction rate database

2009-03-04 Thread Scott Carey
You may have decreased performance in your batch jobs with the lower work_mem setting. Additionally, the fact that you haven't had swap storm issues so far means that although there is certain risk of an issue, its probably a lot lower than what has been talked about here so far. Without a chang

Re: [PERFORM] work_mem in high transaction rate database

2009-03-04 Thread Flavio Henrique Araque Gurgel
- "Scott Marlowe" escreveu: > Oh my lord, that is a foot gun waiting to go off. Assuming 2k > connections, and somehow a fair number of them went active with big > sorts, you'd be able to exhaust all physical memory with about 8 to > 16 connections. Lower work_mem now. To something like 1

Re: [PERFORM] work_mem in high transaction rate database

2009-03-04 Thread Dimitri Fontaine
Hi, On Wednesday 04 March 2009 02:37:42 Scott Marlowe wrote: > If some oddball query really needs a lot of work_mem, > and benchmarks show something larger work_mem helps, consider raising > the work_mem setting for that one query to something under 1G (way > under 1G) That makes it noticeably fas

Re: [PERFORM] work_mem in high transaction rate database

2009-03-03 Thread Akos Gabriel
Tue, 3 Mar 2009 18:37:42 -0700 -n Scott Marlowe írta: > Oh my lord, that is a foot gun waiting to go off. Assuming 2k > connections, and somehow a fair number of them went active with big I absolutely agree with Scott. Plus set effective_cache_size accordingly, this would help the planner. You

Re: [PERFORM] work_mem in high transaction rate database

2009-03-03 Thread Scott Marlowe
On Tue, Mar 3, 2009 at 5:28 PM, Flavio Henrique Araque Gurgel wrote: > Hello all > > In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5 > we have a database with basically two kinds of transactions: > - short transactions with a couple of updates and inserts that runs all

[PERFORM] work_mem in high transaction rate database

2009-03-03 Thread Flavio Henrique Araque Gurgel
Hello all In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5 we have a database with basically two kinds of transactions: - short transactions with a couple of updates and inserts that runs all the day; - batch data loads with hundreds of inserts that runs several t

Re: [PERFORM] work_mem and shared_buffers

2007-11-18 Thread Scott Marlowe
On Nov 18, 2007 8:29 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > On Fri, 2007-11-09 at 13:12 -0600, Scott Marlowe wrote: > > > 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. B

Re: [PERFORM] work_mem and shared_buffers

2007-11-18 Thread Simon Riggs
On Fri, 2007-11-09 at 13:12 -0600, Scott Marlowe wrote: > 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 1

Re: [PERFORM] work_mem and shared_buffers

2007-11-12 Thread Cédric Villemain
Bill Moran a écrit : On Fri, 9 Nov 2007 12:08:57 -0600 "Campbell, Lance" <[EMAIL PROTECTED]> 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

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Scott Marlowe
On Nov 9, 2007 2:38 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > > > > I imagine in a few years, hardly anyone using postgresql will remember > > the ancient art of having either apostrophes in a row inside your > > plpgsql functions... > > Speaking of that devil, I started working with Postgres m

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Erik Jones
On Nov 9, 2007, at 1:24 PM, Scott Marlowe wrote: On Nov 9, 2007 1:19 PM, Campbell, Lance <[EMAIL PROTECTED]> wrote: It is amazing, how after working with databases very actively for over 8 years, I am still learning things. The fun thing about postgresql is that just when you've got it figu

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Scott Marlowe
On Nov 9, 2007 1:19 PM, Campbell, Lance <[EMAIL PROTECTED]> wrote: > It is amazing, how after working with databases very actively for over 8 > years, I am still learning things. The fun thing about postgresql is that just when you've got it figured out, somebody will come along and improve it in

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Campbell, Lance
: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, November 09, 2007 1:13 PM To: Campbell, Lance Cc: Heikki Linnakangas; pgsql-performance@postgresql.org Subject: Re: [PERFORM] work_mem and shared_buffers On Nov 9, 2007 12:08 PM, Campbell, Lance <[EMAIL PROTECTED]> wrote: > How do you

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Scott Marlowe
On Nov 9, 2007 12:08 PM, Campbell, Lance <[EMAIL PROTECTED]> 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.

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Campbell, Lance
, 2007 2:08 PM To: Campbell, Lance Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] work_mem and shared_buffers On Fri, 9 Nov 2007 12:08:57 -0600 "Campbell, Lance" <[EMAIL PROTECTED]> wrote: > How do you know when you should up the value of work_mem? Just play >

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Bill Moran
On Fri, 9 Nov 2007 12:08:57 -0600 "Campbell, Lance" <[EMAIL PROTECTED]> 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? 8.2 an

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Campbell, Lance
University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Heikki Linnakangas [mailto:[EMAIL PROTECTED] On Behalf Of Heikki Linnakangas Sent: Friday, November 09, 2007 11:57 AM To: Campbell, Lance Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM

Re: [PERFORM] work_mem and shared_buffers

2007-11-09 Thread Heikki Linnakangas
Campbell, Lance wrote: Does the amount of memory allocate to work_mem get subtracted from shared_buffers? Example: If work_mem is 1M and there are 10 connections and shared_buffers is 100M then would the total be 90 M left for shared_buffers? Or does the amount of memory allocated for work_mem

[PERFORM] work_mem and shared_buffers

2007-11-09 Thread Campbell, Lance
Does the amount of memory allocate to work_mem get subtracted from shared_buffers? Example: If work_mem is 1M and there are 10 connections and shared_buffers is 100M then would the total be 90 M left for shared_buffers? Or does the amount of memory allocated for work_mem have nothing to

Re: [PERFORM] work_mem

2007-01-28 Thread Bill Moran
"Campbell, Lance" <[EMAIL PROTECTED]> wrote: > > I have been researching how to improve my overall performance of > postgres. I am a little confused on the reasoning for how work_mem is > used in the postgresql.conf file. The way I understand the > documentation is you define with work_mem how mu