PostgreSQL Discussion - Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Author

Re: Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Simon Riggs

2005-10-31, 7:23 am

On Sun, 2005-10-30 at 23:08 -0500, Tom Lane wrote:
> Vlad <marchenko@gmail.com> writes:
>
> 8.0 can't go past 2Gb of shared memory, and there is really no reason
> to try because its performance will get worse not better with more than
> about 50000 shared buffers.

Unless you turn off the bgwriter, in which case going higher can still
have benefit given the right circumstances.

> 8.1 will relax the 2Gb limit, but it's still far from clear that there's
> any point in it. The conventional wisdom is that you should leave most
> of memory free for kernel disk cache, not try to eat it all in shared
> buffers. I haven't seen any evidence that that's changed in 8.1. It
> might possibly make sense to use several Gb of shared buffers in a
> machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.

I'm not sure we have any good tests of that either way, do we? I'm not
certain why we would trust OS cache any more than we could trust the
shared buffers. But setting it too high would probably overuse backend
memory for most variable query workloads.

> BTW, where did you get the idea that it was sensible to set work_mem
> higher than maintenance_work_mem
? That's just nuts.

Surely if you choose to favour query sort performance say over vacuum
performance that is a reasonable design choice in some specific
circumstances? Not the general case, agreed.

There are no assumptions in the code that work_mem is always smaller.
Tasks are assigned to use maintenance_work_mem
when they are considered
to be "maintenance" tasks.

Best Regards, Simon Riggs

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Martijn van Oosterhout

2005-10-31, 9:23 am

On Mon, Oct 31, 2005 at 12:16:59PM +0000, Simon Riggs wrote:
>
> Unless you turn off the bgwriter, in which case going higher can still
> have benefit given the right circumstances.

Is there any particular reason to turn that off? You want dirty pages
written out. Doing them asyncronously beforehand means you don't have
to wait for it at commit time. It also allows the OS to schedule the
blocks into a better write order.

Anyway, the original writer didn't specify an architechure. If it is a
32bit one it is entirly possible that the memory map simply has no
large contiguous space to map the shared memory.

>
> I'm not sure we have any good tests of that either way, do we? I'm not
> certain why we would trust OS cache any more than we could trust the
> shared buffers. But setting it too high would probably overuse backend
> memory for most variable query workloads.

Well, it comes down to a thought experiment. Any disk blocks you have in
the shared buffers will also be in the system cache. If you give 4GB to
shared buffers, then there will be 4GB of data in the system cache which
is not directly useful. So it seems shared buffers should be large
enough to hold all the info PostgreSQL needs at any particular moment,
anything else is just wasteful. Getting data out of the system cache is
not terribly expensive, I timed it at 50 microseconds per page on my
oldish laptop.

Secondly, you're assuming that PostgreSQLs caching is at least as
efficient as the OS caching, which is more of an assertion than
anything else.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane

2005-10-31, 9:23 am

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote:
[color=darkred]
> Each have different and independent cache replacement...

The real point is that RAM dedicated to shared buffers can't be used for
anything else [1], whereas letting the kernel manage it gives you some
flexibility (for instance, to deal with transient large memory demands
by individual backends, or from stuff unrelated to Postgres). A system
configured to give most of RAM to shared buffers might look good on
sufficiently narrow test cases, but its performance will be horribly
brittle: it will go into swap thrashing on any small provocation. The
extra 50usec or whatever to get stuff from a kernel disk buffer instead
of our own shared buffer is a good tradeoff to get flexibility in the
amount of stuff actually buffered at any one instant.

[1] unless you are on a platform where the kernel doesn't think SysV
shared memory should be locked in RAM. In that case, what you have is a
large arena that is subject to being swapped out ... and a disk buffer
that's been swapped to disk is demonstrably worse than no buffer at all.
(Hint: count the I/Os involved, especially when the page is dirty.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Martijn van Oosterhout

2005-10-31, 9:23 am

On Mon, Oct 31, 2005 at 01:34:12PM +0000, Simon Riggs wrote:
>
> Do you doubt that? Why would shared_buffers be variable otherwise?

Because the optimal hasn't been found and is probably different for
each machine.

There have been tests that demonstrate that you can raise the buffers
to a certain point which is optimal and after that it just doesn't
help [1]. They peg optimal size at 5-10% of memory.

Also, as Tom pointed out, any memory assigned to shared buffers can't
be used for sorts, temporary tables, plain old disk caching, trigger
queues or anything else that isn't shared between backends. There are
far more useful uses of memory than just buffering disk blocks.

Have a nice day,

[1] http://archives.postgresql.org/pgsq...10/msg00110.php
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane

2005-10-31, 9:23 am

Martijn van Oosterhout <kleptog@svana.org> writes:
> There have been tests that demonstrate that you can raise the buffers
> to a certain point which is optimal and after that it just doesn't
> help [1]. They peg optimal size at 5-10% of memory.
> [1] http://archives.postgresql.org/pgsq...10/msg00110.php

Note however that it's reasonable to think that 8.1 may do better than
8.0 did at performing well with large values of shared_buffers,
primarily because we got rid of the StrategyDirtyBufferL
ist overhead:
http://archives.postgresql.org/pgsq...10/msg00112.php

It'd be interesting to repeat the above-mentioned tests with 8.1.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Martijn van Oosterhout

2005-10-31, 9:23 am

On Mon, Oct 31, 2005 at 09:54:39AM -0500, Tom Lane wrote:
> Note however that it's reasonable to think that 8.1 may do better than
> 8.0 did at performing well with large values of shared_buffers,
> primarily because we got rid of the StrategyDirtyBufferL
ist overhead:
> http://archives.postgresql.org/pgsq...10/msg00112.php
>
> It'd be interesting to repeat the above-mentioned tests with 8.1.

Well, OSDL has run tests on PostgreSQL as recently as 20050908 but the
host with the results isn't responding to me, so no idea what the tests
were. Also, they use various tests involving PostgreSQL to test the
scalability of the Linux kernel, so you can see how postgres runs with
various different kernel patches.

http://www.osdl.org/lab_activities/...requests

Enter "postgresql" in the software field, or select one of the pgsql
tests. Maybe someone else will have more luck than me getting the
results...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane

2005-10-31, 8:23 pm

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2005-10-31 at 09:35 -0500, Tom Lane wrote:
[color=darkred]
> Agreed. But that is an argument in favour of more easily controllable
> server memory management, not a definitive argument against setting
> shared_ buffers higher.

Well, as long as shared_buffers is a fixed parameter, it's an argument
against setting shared_buffers higher ;-). But the larger point here
is that Postgres does not have the knowledge needed to make the same
kinds of memory tradeoffs that the kernel does. I think trying to usurp
this kernel functionality would be exactly the wrong design direction
for us to take.

[color=darkred]
> This is a disaster for any database, not just PostgreSQL. But most other
> DBMS do something about this, for example on Linux, Solaris, HP/UX, AIX
> a certain orange DBMS provides additional support for making shared
> memory non-swappable.

Yeah, and we should do that too on platforms where it can be done
reasonably (ie, without root privs).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Martijn van Oosterhout

2005-10-31, 8:24 pm

On Mon, Oct 31, 2005 at 02:50:31PM -0600, Scott Marlowe wrote:
>
> As I understand it, when the last backend referencing a collection of
> data stops referencing it, that the buffers holding that data are
> released, and if, a second later, another backend wants the data, then
> it has to go to the Kernel for it again.
>
> Is this still the case in 8.1?

Depends what you mean. What one backend uses stays in the shared
buffers when it's done. It's only removed to make room for other blocks
that have been requested. Whether it's still there after a second kind
of depends on how much other data you read in the meantime and whether
the caching algorithm decided the data was old enough that you wern't
likely to need it soon.

It's kind of like the kernel cache, once you've been running for a
while it's always full of blocks of data. There's no point forgetting
perfectly good data. The only time you don't need to throw away blocks
is if your database is smaller than your memory,

You mentioned something about those OSDL tests, where can we download
the results? I just get told khack.osdl.org is unreachable...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Tom Lane

2005-10-31, 8:24 pm

Scott Marlowe < smarlowe@g2switchwor
ks.com> writes:
> I was mainly wondering if that behaviour had changed, if, when the data
> are released, they are still held in shared memory until forced out by
> newer / more popular data. Which would make the buffer pool a real
> cache.

Huh? It's always done that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links