Обсуждение: Configuring postmaster to use all available memory

Hello Psqlers,

I have noticed that postmaster usually doesn't take up that much
memory.  I'm glad it is coded for efficiency, but, I have it running on
a dedicated machine which does absolutely nothing more than allow me to
log in via ssh and run postmaster.  I want postmaster to consume
basically all available RAM, so any query which is a read (not an
updating query) shouldn't have to touch disk at all.  Is there a way to
do this?  I looked at the -B option, and set it as -B 1024 but it didn't
seem to do much.  Should I crank it further up, or are there other
things to do?

Thanks!

Hi Joe,

Have you looked into tuning the postgresql.conf file?

It's the one in the PostgreSQL data directory.  The exact settings
needed depend on your situation, but by taking some time you'll be able
to figure out decent values.

Bruce Momjian has written a document that goes into the theory behind
it, and gives good ideas:

http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/

Also, there is a lot of discussion about this on the PostgreSQL
"Performance" mailing list so it would be useful to check through the
mailing list archives for this.

Hope that helps.

Regards and best wishes,

Justin Clift


Joe Tomcat wrote:
> Hello Psqlers,
>
> I have noticed that postmaster usually doesn't take up that much
> memory.  I'm glad it is coded for efficiency, but, I have it running on
> a dedicated machine which does absolutely nothing more than allow me to
> log in via ssh and run postmaster.  I want postmaster to consume
> basically all available RAM, so any query which is a read (not an
> updating query) shouldn't have to touch disk at all.  Is there a way to
> do this?  I looked at the -B option, and set it as -B 1024 but it didn't
> seem to do much.  Should I crank it further up, or are there other
> things to do?
>
> Thanks!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

> I have noticed that postmaster usually doesn't take up that much
> memory.  I'm glad it is coded for efficiency, but, I have it running on
> a dedicated machine which does absolutely nothing more than allow me to
> log in via ssh and run postmaster.  I want postmaster to consume
> basically all available RAM, so any query which is a read (not an
> updating query) shouldn't have to touch disk at all.  Is there a way to
> do this?  I looked at the -B option, and set it as -B 1024 but it didn't
> seem to do much.  Should I crank it further up, or are there other
> things to do?

    Sure, there's a way to keep it from touching disk.  There are two
ways, actually.  One is to allocate a huge amount of memory for your
shared buffers.  Another is to simply let your kernel cache the entire
data directory.

    In my case, because I trust my machine, I've turned off fsync() in PG,
so that even updates don't need to hit the disk, at least not until the
kernel gets bored and decides to flush things out.  Also, -B 1024 isn't a
whole lot - it's 1,000 8K blocks, so you're allocating about 8 megabytes.
A decent place to start is about a quarter of your RAM, so if you have two
gigs, about 512 megs of shared buffers would be good - so you'd use -B
65536 .  You may have to fiddle with your OS to be able to allocate such a
large shared memory segment.

   You can also increase the amount of memory used for sorting by using
the "-S" parameter.  Be aware that multiple sorts can occur for each
backend, amplifying the amount of memory that is used.  In other words, if
you put "-S 98304", you'll use 96 megs for a single sort operation.
There can be a good deal of sorts in a single query, each taking up to 96
megs - and multiple backends executing similar queries at the same time.

steve

Joe Tomcat <tomcat@mobile.mp> writes:
> I want postmaster to consume
> basically all available RAM,

This is generally considered a bad idea.

> so any query which is a read (not an
> updating query) shouldn't have to touch disk at all.

You do not need to mess with Postgres' settings to make that happen.
The kernel should buffer recently-accessed disk blocks in free RAM,
without you having to do anything special.

See the list archives for much more on the theory and practice of
buffer tuning.

            regards, tom lane