От: Kevin Grittner
Тема: Re: Memory Allocation
Дата: ,
Msg-id: 49301C3A.EE98.0025.0@wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Memory Allocation  (Scott Carey)
Список: pgsql-performance

Скрыть дерево обсуждения

Memory Allocation  ("Ryan Hansen", )
 Re: Memory Allocation  (Alan Hodgson, )
 Re: Memory Allocation  (Carlos Moreno, )
 Re: Memory Allocation  (Tom Lane, )
 Re: Memory Allocation  (Scott Carey, )
  Re: Memory Allocation  ("Kevin Grittner", )
   Re: Memory Allocation  (Scott Carey, )
    Re: Memory Allocation  ("Kevin Grittner", )

I'm hoping that through compare/contrast we might help someone start
closer to their own best values....

>>> Scott Carey <> wrote:
> Tests with writes can trigger it earlier if combined with bad
dirty_buffers
> settings.

We've never, ever modified dirty_buffers settings from defaults.

> The root of the problem is that the Linux paging algorithm estimates
that
> I/O for file read access is as costly as I/O for paging.  A
reasonable
> assumption for a desktop, a ridiculously false assumption for a large

> database with high capacity DB file I/O and a much lower capability
swap
> file.

Our swap file is not on lower speed drives.

> If you do have enough other applications that are idle that take up
RAM that
> should be pushed out to disk from time to time (perhaps your programs
that
> are doing the bulk loading?) a higher value is useful.

Bulk loading was ssh cat | psql.

> The more RAM you have and the larger your postgres memory usage, the
lower
> the swappiness value should be.

I think the test environment had 8 GB RAM with 256 MB in
shared_buffers.  For the conversion we had high work_mem and
maintenance_work_mem settings, and we turned fsync off, along with a
few other settings we would never using during production.

> I currently use a value of 1, on a 32GB machine, and about 600MB of
'stuff'
> gets paged out normally, 1400MB under heavy load.

Outside of bulk load, we've rarely seen anything swap, even under
load.

> ***For a bulk load database, one is optimizing for _writes_ and extra
page
> cache doesn't help writes like it does reads.***

I'm thinking that it likely helps when indexing tables for which data
has recently been loaded.  It also might help minimize head movement
and/or avoid the initial disk hit for a page which subsequently get
hint bits set .

> Like all of these settings, tune to your application and test.

We sure seem to agree on that.

-Kevin


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: Memory Allocation
От: "Andrus"
Дата:
Сообщение: Re: Increasing GROUP BY CHAR columns speed