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:
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.