Optimal memory settings? SHMMAX?

[cvc]

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 received 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 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 Access, 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, I'm attaching the 3 configuration files.
Carl

 

[cvc]

Just an update: I received a tip from the postgresql mailing list, that the shared_buffers need to be set the 1/4 of the total system memory. So I need to reconfigure the

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

I think I can keep these settings for 1024 shared buffers. Or not? Help

 

[donduq]

Despite the topic's age I think this is a very interesting question. I want to learn about this subject because FreeBSD's maturity has fully won me over. I didn't have to deal with these settings on my Linux servers although I'll admit this is only an assumption. Yes, I know of which things Assumption is the mother. :e

At any rate, I am not used to "sysadminning" these types of settings so does anyone care to shed some light on this? I'd like to know what shared memory is and what the above sysctls do (and cost).

This subject also made me wonder: why are sysctls like these set to static values, as opposed to e.g. something in the kernel that is able to detect what the (root) user's program requires and then tweaks these values on the fly?

And lastly; considering my earlier assumption I'd also like to know how Linux deals with these 'things' for lack of a better term. :stud

 

[SirDice]

At any rate, I am not used to "sysadminning" these types of settings so does anyone care to shed some light on this? I'd like to know what shared memory is and what the above sysctls do (and cost).

Lets see if I can keep this as simple as possible. Normally an application gets it's own "memorymap", it's a virtual map. On 32 bit operating systems this is usually 4GB. This virtual map gets 'converted' to physical memory by the MMU. Now suppose you have two separate applications that need to exchange data. They can't access each other's memory, the system (mostly the MMU) won't allow it. This is where 'shared memory' comes in. An application can request a certain amount of it and another application is able to access that shared bit. Now both applications can exchange and share data.

This subject also made me wonder: why are sysctls like these set to static values, as opposed to e.g. something in the kernel that is able to detect what the (root) user's program requires and then tweaks these values on the fly?

A good question, unfortunately I can't answer it.

And lastly; considering my earlier assumption I'd also like to know how Linux deals with these 'things' for lack of a better term. :stud

In a similar fashion. Linux also has "shared memory", they just implemented it differently.