Re: Question about memory allocations

From: Tom Lane
Subject: Re: Question about memory allocations
Date: ,
Msg-id: 8524.1176352288@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Question about memory allocations  (Steve)
Responses: Re: Question about memory allocations  (Steve)
List: pgsql-performance

Tree view

how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
 Re: how to efficiently update tuple in many-to-many relationship?  ("Merlin Moncure", )
 Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
  Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
   Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
    Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
     Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
      Re: how to efficiently update tuple in many-to-many relationship?  (Drew Wilson, )
       Re: how to efficiently update tuple in many-to-many relationship?  (Tom Lane, )
        Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Tom Lane, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Greg Smith, )
          Re: Question about memory allocations  (Steve, )
         Re: Question about memory allocations  (Andrew McMillan, )
          Re: Question about memory allocations  (Steve, )
           Re: Question about memory allocations  (Ron, )
            Re: Question about memory allocations  (Tom Lane, )
           Re: Question about memory allocations  (Carlos Moreno, )
            Re: Question about memory allocations  ("Jan de Visser", )

Steve <> writes:
> - What is temp_buffers used for exactly?

Temporary tables.  Pages of temp tables belonging to your own backend
don't ever get loaded into the main shared-buffers arena, they are read
into backend-local memory.  temp_buffers is the max amount (per backend)
of local memory to use for this purpose.

> - Do full_page_writes and wal_buffers settings matter AT ALL for a machine
> where fysnc = off ?

Yes.

> - What does wal_buffers mean and does increasing this value actually help
> anything?

It's the amount of space available to buffer WAL log data that's not
been written to disk.  If you have a lot of short transactions then
there's not much benefit to increasing it (because the WAL will be
getting forced to disk frequently anyway) but I've heard reports that
for workloads involving long single transactions bumping it up to 64
or 100 or so helps.

> - Any idea if this is a smart configuration for this machine?

Um ... you didn't mention which PG version?

> # This value is going to probably set off cries of using this as a set
> # command instead of a big global value;

No kidding.  You do NOT want work_mem that high, at least not without an
extremely predictable, simple workload.

> wal_buffers = 512MB

I haven't heard any reports that there's a point in values even as high
as 1 meg for this.

            regards, tom lane


pgsql-performance by date:

From: Jeff Frost
Date:
Subject: Re: Slow Postgresql server
From: Ron
Date:
Subject: Re: Slow Postgresql server