Thread: kernel memory parms
I am running Debian stable with postgresql 7.2.1. My machine has 4 GB of RAM and I have configured the kernel to recognize it all. I have the following shared mem options set: kernel/shmmni = 4096 kernel/shmall = 3879731200 kernel/shmmax = 3774873600 Based on what I read in the docs I should be able to use at setting of shared_buffers = 454000 in my postgresql.conf file as I want to use about 3600MB of memory for my database. However when I try to use that many segments I get the following error: IpcMemoryCreate: shmat(id=1015811) failed: Invalid argument The highest number of shared buffers it allows me to use is 258169. After starting postgresql with the shared_buffers = 258169 and max_connections = 64 ipcs shows the following: wvcdl:/etc/init.d# ipcs ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 491520 root 600 33554432 6 dest 0x00000000 360449 root 600 92164 6 dest 0x00000000 294914 root 600 33554432 6 dest 0x0052e2c1 1867779 postgres 600 2144206848 3 ------ Semaphore Arrays -------- key semid owner perms nsems status 0x0052e2c1 5734400 postgres 600 17 0x0052e2c2 5767169 postgres 600 17 0x0052e2c3 5799938 postgres 600 17 0x0052e2c4 5832707 postgres 600 17 0x0052e2c5 5865476 postgres 600 17 ------ Message Queues -------- key msqid owner perms used-bytes messages It looks to me as if there is about 2 GB of shared memory in use. Is this a hard limit I can't go past? If so any sugestions on how I can make full use of my mem for database use? Thanks, jth
J.T., > I am running Debian stable with postgresql 7.2.1. My machine has 4 GB of > RAM and I have configured the kernel to recognize it all. I have the > following shared mem options set: First off, if there is a deb package for 7.2.3 or 7.2.4, you should upgrade. 7.2.1 contains several notable bugs. > Based on what I read in the docs I should be able to use at > setting of shared_buffers = 454000 in my postgresql.conf file as I want > to use about 3600MB of memory for my database. No, you don't. If you survey the archives of the PGSQL-PERFORMANCE list, you will find lots of anecdotal evidence that any shared buffer setting beyond 2000-6000 actually decreases performance or at least does not benefit it. Shared buffers are just a "holding area" while data is being fed to the Kernel buffer; beyond a certain size, they in fact rob the kernel of resources. (BTW, you are not a dummy for not knowing this; it is poorly documented if at all in current docs. We'll fix that in the future, really!) > However when I try to use that many segments I get the following error: > > IpcMemoryCreate: shmat(id=1015811) failed: Invalid argument > > The highest number of shared buffers it allows me to use is 258169. After > starting postgresql with the shared_buffers = 258169 and > max_connections = 64 ipcs shows the following: That's because, I believe, that shared_buffers is a 4-byte integer that won't address more than 2.4 billion bytes. Since settings > 6000 are generally viewed as counterproductive, this is not widely viewed as a bug. -- -Josh Berkus Aglio Database Solutions San Francisco
"J.T. Hartzler" <dba@wap.ntelos.com> writes:
> Based on what I read in the docs I should be able to use at
> setting of shared_buffers = 454000 in my postgresql.conf file as I want
> to use about 3600MB of memory for my database.
This is a bad idea in any case, even if your kernel would let you do it.
Try configuring a couple thousand shared_buffers to start out with.
You can find lots of discussion of this topic in the archives, but the
key point is that kernel disk cache is a perfectly effective use of RAM.
You don't need to, and shouldn't try to, make Postgres buffers occupy
all of RAM, or even a very large percentage of it.
regards, tom lane