Обсуждение: Memory Tuning

I am looking for information on tuning memory usage for Postgres on Linux
(2.2 kernel). In particular I have a lot of memory relative to the size
of my database and am looking to reduce latency in queries.

Searching goegle turned up a few other cases of people asking about memory
tuning, but I didn't see any answers.

I have tried increasing the memory allowed for in memory sorts and the
estimate of available buffer caching. I haven't tried raising the buffer
space per connection as the documenation didn't seem to indicate
that that would help. This seems to have helped a little, but it is
hard to tell since the current delay is about 1 second and the queries
seem to happen faster when they are repeated after a short amount of
time.

For some idea of the specific problem, I am setting up a replacement
web server for my hobby server. The new box has a 1GHz Tbird with 500MB
of memory and a 20G 7200rpm IDE disk. The database is effectively static,
with rows for 300 games, 4000 people and 11000 people/game ratings and
a couple of other miscelaneous tables with a handful of rows each.

I am mostly interested in reducing the latency of requests while maintaining
the flexibility of using the database. Eventually there will be online data
entry so I don't want to switch to writing static files for the most of the
different possible reports.

If you could post the schema of your tables that you do the query against
and an EXPLAIN of the queries you're doing, perhaps we could further tune
your queries in addition to beefing up the memory usage of the backend..

Check this link out too.
http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht
ml


-Mitch
Software development :
You can have it cheap, fast or working. Choose two.

----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 30, 2001 9:45 AM
Subject: Memory Tuning


> I am looking for information on tuning memory usage for Postgres on Linux
> (2.2 kernel). In particular I have a lot of memory relative to the size
> of my database and am looking to reduce latency in queries.
>
> Searching goegle turned up a few other cases of people asking about memory
> tuning, but I didn't see any answers.
>
> I have tried increasing the memory allowed for in memory sorts and the
> estimate of available buffer caching. I haven't tried raising the buffer
> space per connection as the documenation didn't seem to indicate
> that that would help. This seems to have helped a little, but it is
> hard to tell since the current delay is about 1 second and the queries
> seem to happen faster when they are repeated after a short amount of
> time.
>
> For some idea of the specific problem, I am setting up a replacement
> web server for my hobby server. The new box has a 1GHz Tbird with 500MB
> of memory and a 20G 7200rpm IDE disk. The database is effectively static,
> with rows for 300 games, 4000 people and 11000 people/game ratings and
> a couple of other miscelaneous tables with a handful of rows each.
>
> I am mostly interested in reducing the latency of requests while
maintaining
> the flexibility of using the database. Eventually there will be online
data
> entry so I don't want to switch to writing static files for the most of
the
> different possible reports.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

On Fri, Mar 30, 2001 at 10:18:56AM -0500,
  Mitch Vincent <mitch@venux.net> wrote:
> If you could post the schema of your tables that you do the query against
> and an EXPLAIN of the queries you're doing, perhaps we could further tune
> your queries in addition to beefing up the memory usage of the backend..

This is a bit more than I was expecting. People who do this kind of thing
generally paid lots of money.

However, if you really want, all of the information on queries and schema
is available at http://wolff.to/area/ . That is the old box which has a
lot less memory and a much slower processor. The database schema build
script is available as well as the source to the perl scripts that handle
the queries. The especially slow (about 20 seconds before rows are returned
- reduced to about 1 second on the new box) queries are the full lists of
people sorted by name or ID (the ID sort isn't as slow).

Almost all of the data is available. However the people data is accessed
through a view and there is one person whose name is anonymized.

At this point I wasn't as worried about inefficiencies in the queries
themselves, but rather how to tell the database server and/or linux to
best use the memory. The data in the database should easily fit into memory.

>
> Check this link out too.
> http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht
> ml

I will look through that site again. I looked at that previously, but not
specifically looking for efficient use of memory.

If you have a specific query that you're having trouble with, post it and
the table schema and an EXPLAIN of the query when you run it, generally
someone will have some immediate pointers on how to speed things up.. I
don't have time to go through your site looking for the database schema and
such but if you include some specific information in an email to the list
I'd be happy to take a quick look (and I'm sure others would too)..

Good luck!

-Mitch
Software development :
You can have it cheap, fast or working. Choose two.

----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 30, 2001 2:01 PM
Subject: Re: Memory Tuning


> On Fri, Mar 30, 2001 at 10:18:56AM -0500,
>   Mitch Vincent <mitch@venux.net> wrote:
> > If you could post the schema of your tables that you do the query
against
> > and an EXPLAIN of the queries you're doing, perhaps we could further
tune
> > your queries in addition to beefing up the memory usage of the backend..
>
> This is a bit more than I was expecting. People who do this kind of thing
> generally paid lots of money.
>
> However, if you really want, all of the information on queries and schema
> is available at http://wolff.to/area/ . That is the old box which has a
> lot less memory and a much slower processor. The database schema build
> script is available as well as the source to the perl scripts that handle
> the queries. The especially slow (about 20 seconds before rows are
returned
> - reduced to about 1 second on the new box) queries are the full lists of
> people sorted by name or ID (the ID sort isn't as slow).
>
> Almost all of the data is available. However the people data is accessed
> through a view and there is one person whose name is anonymized.
>
> At this point I wasn't as worried about inefficiencies in the queries
> themselves, but rather how to tell the database server and/or linux to
> best use the memory. The data in the database should easily fit into
memory.
>
> >
> > Check this link out too.
> >
http://postgresql.readysetnet.com/devel-corner/docs/user/performance-tips.ht
> > ml
>
> I will look through that site again. I looked at that previously, but not
> specifically looking for efficient use of memory.
>

On Fri, Mar 30, 2001 at 01:52:42PM -0500,
  Mitch Vincent <mitch@venux.net> wrote:
> If you have a specific query that you're having trouble with, post it and
> the table schema and an EXPLAIN of the query when you run it, generally
> someone will have some immediate pointers on how to speed things up.. I

I am currently interested in the general issue of how to use memory
efficiently rather than speeding up specific queries. I may revisit
that later to see if controlling join order would help.

Right now I am interested in such things such as:

Should I leave postgres tuning alone and let Linux use all of the memory
for buffer caching?

Is there any good reason to increase the number of buffers per backend over
the default of 2?

If I don't anticipate a lot of simultaneous queries, what fraction of memory
should I let in core sorts take up?

To set the estimate for bucher caching, should I just look at /proc/meminfo
to see how much memory is being used for caching or should I get this
estimate some other way?

Why I am not seeing consitant wall clock times for queries? Presumably
there is some caching going on, but I am not sure if it is in postgres
or in the OS.

> don't have time to go through your site looking for the database schema and
> such but if you include some specific information in an email to the list

Including the pointers to the site was a semi joke. I don't expect people
to go and give me specific answers. And I am not as interested in that
as I am in general ideas in how to figure things out.

This might include useful rules of thumb for (for my current issue) memory
tuning, where to read about performance measuring tools.

> I'd be happy to take a quick look (and I'm sure others would too)..

I appreciate the help, but I think so far you are offering to answer a
different question than I have at this point.

> I am currently interested in the general issue of how to use memory
> efficiently rather than speeding up specific queries. I may revisit
> that later to see if controlling join order would help.
>
> Right now I am interested in such things such as:
>
> Should I leave postgres tuning alone and let Linux use all of the memory
> for buffer caching?

  We have a server with 512 MB of RAM, and I've tried pretty hard to get
PostgreSQL to use it all - but it just doesn't need it.  My startup line
goes like this:

su postgres -c '/usr/local/pgsql/bin/postmaster -i -S -B 8192 -N 64 -d
3 -D/usr/local/pgsql/data -o "-S 65536"'

  Now I know that I could set those numbers higher, but doing so does me no
good - PostgreSQL simply doesn't need that much memory for what we do.
They used to be set at about half of their current levels (buffers, etc.),
and I doubled them just because I could, but saw no further performance
increases or memory usage increases.   The machine generally runs with
around 275 megs doing nothing but disk cache, even under moderate usage.
The limiting factor is the CPU speed, now.  Having watched the lights on the
disks, I feel that's enough cache - the lights only give a quick blink
occasionally, when we do an insert/update.  Other than that, everything runs
out of cache.

steve

--- Bruno Wolff III <bruno@wolff.to> wrote:
> I am looking for information on tuning memory usage for
> Postgres on Linux
> (2.2 kernel). In particular I have a lot of memory relative to
> the size
> of my database and am looking to reduce latency in queries.

<snip>

> For some idea of the specific problem, I am setting up a
> replacement
> web server for my hobby server. The new box has a 1GHz Tbird
> with 500MB
> of memory and a 20G 7200rpm IDE disk. The database is
> effectively static,
> with rows for 300 games, 4000 people and 11000 people/game
> ratings and
> a couple of other miscelaneous tables with a handful of rows
> each.

Your problem may not be the fault of poor memory management.  By
default most (if not all) Linux distributions set IDE drives to
the safest and slowest settings possible.  By tweaking the
settings using the hdparm utility you can reduce CPU utilization
and increase disk speed at the same time.  Read this article for
some more information on the subject,
http://www.oreillynet.com/lpt/a/272 .  Note: SCSI hard drives
often get excellent performance without any of the
aforementioned tuning.

Brent

__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/?.refer=text

Bruno Wolff III <bruno@wolff.to> writes:
> Should I leave postgres tuning alone and let Linux use all of the memory
> for buffer caching?

You shouldn't try to make Postgres consume *all* of memory for buffers;
for one thing, if the shmem region is too large then (at least on some
Unixen, not sure about Linux) the kernel might decide to swap out parts
of it.  That'd be counterproductive.  However:

> Is there any good reason to increase the number of buffers per backend over
> the default of 2?

Yes.  Particularly so under 7.1 --- WAL likes to have a lot of buffers
so that it doesn't have to write data to data files too often.  I'd
recommend perhaps a few thousand buffers depending on how much RAM you
have (maybe 10-20% of your physical RAM would be a reasonable upper
limit on how much space for buffers).

> Why I am not seeing consitant wall clock times for queries? Presumably
> there is some caching going on, but I am not sure if it is in postgres
> or in the OS.

Both: we have our buffer area, and then the kernel has its own buffers.
We can make good use of kernel-level buffering as well as our shared-memory
buffers, so there's no reason to try to starve the kernel of buffer space.

            regards, tom lane

> Bruno Wolff III <bruno@wolff.to> writes:
> > Should I leave postgres tuning alone and let Linux use all of the memory
> > for buffer caching?
>
> You shouldn't try to make Postgres consume *all* of memory for buffers;
> for one thing, if the shmem region is too large then (at least on some
> Unixen, not sure about Linux) the kernel might decide to swap out parts
> of it.  That'd be counterproductive.  However:

If it pages out, it also maps that shared virtual memory into every
forked backend.  That can be lots of page tables.

>
> > Is there any good reason to increase the number of buffers per backend over
> > the default of 2?
>
> Yes.  Particularly so under 7.1 --- WAL likes to have a lot of buffers
> so that it doesn't have to write data to data files too often.  I'd
> recommend perhaps a few thousand buffers depending on how much RAM you
> have (maybe 10-20% of your physical RAM would be a reasonable upper
> limit on how much space for buffers).
>
> > Why I am not seeing consitant wall clock times for queries? Presumably
> > there is some caching going on, but I am not sure if it is in postgres
> > or in the OS.
>
> Both: we have our buffer area, and then the kernel has its own buffers.
> We can make good use of kernel-level buffering as well as our shared-memory
> buffers, so there's no reason to try to starve the kernel of buffer space.

Our shared buffers allow us to modify them without copying them in/out
of the kernel.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruno Wolff III wrote:

> I am looking for information on tuning memory usage for Postgres on Linux

    What about
http://www.postgresql.org/devel-corner/docs/postgres/kernel-resources.html?



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com