Thread: LAPP server moving from 4 GB RAM to 16 GB - increase shared_buffers?

Hello,

I run a LAPP server (PostgreSQL 8.4 @ CentOS 5.7 / 64 bit;
only 4 GB RAM) with the following config:

postgresql.conf (unix socket only and - ):

    max_connections = 50
    shared_buffers = 1024MB                 # min 128kB

pgbouncer.ini:

   [databases]
   pref = host=/tmp user=XXX password=XXX dbname=XXX

   [pgbouncer]
   logfile = /var/log/pgbouncer.log
   pidfile = /var/run/pgbouncer/pgbouncer.pid
   listen_port = 6432
   unix_socket_dir = /tmp

   auth_type = md5
   auth_file = /var/lib/pgsql/data/global/pg_auth

   pool_mode = session
   server_reset_query = DISCARD ALL;

   server_check_delay = 10
   max_client_conn = 200
   default_pool_size = 16

and httpd.conf (running Drupal 7 + custom PHP scripts,
non-persistent, with mostly "select" queries):

    <IfModule prefork.c>
   StartServers       8
   MinSpareServers    5
   MaxSpareServers   20
   ServerLimit      120
   MaxClients       120
   MaxRequestsPerChild  4000
  </IfModule>

It works ok, but now users have collected some money and
I've purchased a better server with 16 GB and CentOS 6 /64 bit.

I understand, that my question is naive, but on the other side
even if I provide more information, will that really help? -

My question is: should I increase shared_buffers
from 1024MB to 8192MB, does it make any sense?

Thank you
Alex

Alexander Farber <alexander.farber@gmail.com> wrote:
> It works ok, but now users have collected some money and
> I've purchased a better server with 16 GB and CentOS 6 /64 bit.
>
> I understand, that my question is naive, but on the other side
> even if I provide more information, will that really help? -
>
> My question is: should I increase shared_buffers
> from 1024MB to 8192MB, does it make any sense?

I think, 4GB is okay, 8GB too high. You can increase work_mem, but all
depends on your workload...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Tue, Oct 4, 2011 at 9:11 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> I run a LAPP server (PostgreSQL 8.4 @ CentOS 5.7 / 64 bit;
> only 4 GB RAM) with the following config:
>
> postgresql.conf (unix socket only and - ):
>
>    max_connections = 50
>    shared_buffers = 1024MB                 # min 128kB

OK, so a lot of folks think the only way to take advantage of the
extra memory is for postgresql to directly manage and use it.  this is
a rather naive view.  PostgreSQL can use some of it for sorts etc,
index rebuilds and so on, in addition to using it for shared buffers.
Further the kernel uses what's not being used elsewhere for file
system caching, so often there's no real gain from having postgresql
try to cache more data when the OS is already doing it, and for large
amounts of memory, usually doing it better.

On top of that, the dirty buffers have to get written out to disk
eventually, and on a transactional system having more dirty buffers to
manage just makes things slower not faster.