Re: Upgrade to dual processor machine? - Mailing list pgsql-performance

Henrik,

First off, I'm moving this discussion to the PGSQL-PERFORMANCE list,
where it belongs.   To subscribe, send the message "subscribe
pgsql-perform your@email.address" to "majordomo@postgresql.org".

> This is was I figured out now:
>
> 1) RAM available: 1024 MB, there's nothing else but postgres on this
>    machine, so if I calculate 128 MB for Linux, there are 896 MB left
>    for Postgres.
>
> 2) 70 % of 896 MB is 627 MB
>
> Now, if I follow your instructions:
>
> 250K +
> 8.2K * 128 (shared_buffers) = 1049,6K +
> 14.2K * 64 (max_connections) = 908,8K +
> 1024K * 5000 (average number of requests per minute) = 5120000K
> ===============================================================
> 5122208.4K ==> 5002.16 MB
>
> this is a little bit more than I have available, isn't it? :(((
>
> sure that this has got to be the "average number of requests per
> minute"
> and not "per second" ? seems so much, doesn't it?
>
> what am I supposed to do now?

Well, now it gets more complicated.   You need to determine:
A) The median processing time of each of those requests.
B) The amount of Sort_mem actually required for each request.

I reccommend "per minute" because that's an easy over-estimate ... few
requests last a full minute, and as a result
average-requests-per-minute gives you a safe guage of maximum
concurrent requests (in transactional database environments), which is
really what we are trying to determine.

Um, you do know that I'm talking about *database* requests -- that is,
queries -- and not web page requests, yes?  If you're using server-side
caching, there can be a *huge* difference.

If you have 5000 requests per minute, and only 64 connections, then I
can hypothesize that:
1) you are doing some kind of connection pooling;
2) those are exclusively *read-only* requests;
3) those are very simple requests, or at least processed very quickly.

If all of the above is true, then you can probably base you calculation
on requests-per-second, rather than requests-per-minute.

Then, of course, it becomes an interactive process.  You change the
settings, re-start the database server, and watch the memory used by
the postgreSQL processes.   Your goal is to have that memory usage
hover around 700mb during heavy usage periods (any less, and you are
throttling the database through scarcity of RAM)  but to never, ever,
force usage of Swap memory, which will slow down the server 10-fold.

If you see the RAM only at half that, but the processor at 90%+, then
you should consider upgrading your processor.  But you're more likely
to run out of RAM first.    I believe that you haven't already because
with your low shared-buffer settings, most of the potential sort_mem is
going unused.

BTW, if you are *really* getting 5000 queries per minute, I would
strongly reccomend doubling your RAM.

-Josh Berkus


>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: <pgsql-general@postgresql.org>
> Cc: <steffen@city-map.de>
> Sent: Tuesday, November 12, 2002 9:05 PM
> Subject: Re: Upgrade to dual processor machine?
>
>
> Heinrik,
>
> "So, where do i find and change shmmax shmall settings ??
> What should I put there?
>
> What is a recommended value for shared buffers in postgresql.conf ?"
>
> There is no "recommended value."   You have to calculate this
> relatively:
>
> 1) Figure out how much RAM your server has available for PostgreSQL.
>  For
> example, I have one server on which I allocate 256 mb for Apache, 128
> mb for
> linux, and thus have 512mb available for Postgres.
>
> 2) Calculate out the memory settings to use 70% of that amount of Ram
> in
> regular usage.   Please beware that sort_mem is *not* shared, meaning
> that it
> will be multiplied by the number of concurrent requests requiring
> sorting.
> Thus, your calculation (in K) should be:
>
> 250K  +
> 8.2K * shared_buffers +
> 14.2K * max_connections +
> sort_mem * average number of requests per minute
> =====================================
> memory available to postgresql in K * 0.7
>
> You will also have to set SHMMAX and SHMMALL to accept this memory
> allocation.
> Since shmmax is set in bytes, then I generally feel safe making it:
> 1024 * 0.5 * memory available to postgresql in K
>
> Setting them is done simply:
> $ echo 134217728 >/proc/sys/kernel/shmall
> $ echo 134217728 >/proc/sys/kernel/shmmax
>
> This is all taken from the postgresql documentation, with some
> experience:
>
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco