PostgreSQL + FreeBSD memory configuration, and an issue

From: Gipsz Jakab <clausewitz45(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PostgreSQL + FreeBSD memory configuration, and an issue
Date: 2011-04-08 08:00:23
Message-ID: BANLkTikQN3vgQtQY_Y=s3fkGTu01a0NsBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I've got an IBM x3200 server, with 1,8 Ghz dual core processor, and with 4
GB RAM. I've installed a FreeBSD 8.2, PostgreSQL 9.0.3, Apache22, with
php5.3.5 and extensions for postgre, session,pdf and others.

After the install, I recieved lot of "too many user" in the postgresql.log,
and after that, I reconfigured the postgresql.conf with some parameters:

max connection = 200
shared buffers = 512 MB
work mem = 1 MB
maintenance_work_mem = 128 MB
checkpoint_segments = 32
checkpoint_timeout = 10min
deadlock_timeout = 1s
max_lock_per_transaction = 64

I've saw in the postgresql manual, that I have to reconfigure the kernel,
with these parameters in sysctl.conf:

kern.ipc.shmall=262144
kern.ipc.shmmax=1073741824
kern.ipc.semmap=256

and loader.conf:
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256

My question is the following: if this is a dedicated database server, with
maximum 30 users (but they are using ODBC with Microsoft Acces, and each of
them generating 4-6 connection at the same time), and other 200 people will
use this server through drupal, php, apache not in daily basis, but weekly,
what is the ideal memory configuration?

After the settings in the postgresql.conf our system is much faster, and no
more error messages in the postgres.log, but If I try to drop a table, or
add a new one, our system is stopping, until I kill the process, which is
dropping or adding a table.

Thank you in advance, the config files can be found here:
http://kissceg.sitefarm.hu/conf_files.tar
regards,
Carl

Responses

Browse pgsql-general by date

  From Date Subject
Next Message gmb 2011-04-08 10:57:21 Using Function returning setof record in JOIN
Previous Message Szymon Guz 2011-04-08 07:41:32 Re: why autocommit mode is slow?