Hi,
My Postgres hits the max permitted memory under the load (500MB) and runs 14 processes. Once load is over, Postgres still keeps the allocated memory and runs 14 processes. Since I have Apache and Tomcat running on the same machine, I'd like to Postgresql release the allocated memory. Is it possible?
Thanks!
Except a few common processes, PostgreSQL runs process per connection so you should probably take a look on your application (or connection pooler) settings - if the connections are released if not used for some time.
But I don't expect this will help you a lot in the context of memory cause major part of PG allocated memory are shared buffers and this part of memory will never be released.
Postgres using lots of memory usually is a good thing, because it will have to go to disk less. If it can store the indexes and most popular data block in memory you can achieve very high cache hit ratios which translates in snappy performance and less trouble in the Java app server with requests which block while waiting for a transaction to complete.
So in general you would want for postgresql's cache to be warmed up quickly and stay in memory.
That being said, you of course also need memory for your other apps to run. You can reduce the per request memory consumption by reducing the maximum number of connections on your connection pool. This in turn will limit the number of processes postgres fires up to handle the requests and store the session based temporary data.
You can of course tune down the shared buffers for a smaller cache.
An alternative aproach is to reduce the number of concurrent requests the Apache and tomcat are allowed to handle. If you have snappy response times you might be able to get a higher throughput with less concurrent requests and queuing the requests in the apache. You then limit the memory consumption in the complete stack, and by placing a cap on the load, you'll keep the request handling time relatively constant.
In an ideal setup you would never have your database sitting on your web server and application server. For that matter each one of those systems would probably be on their own server.
Basically, you need to look at your system memory, particularly Postgres memory, in two ways.
The first, is the memory necessary to run the applications. This is the, essentially, static memory load for all of the applications while they're running in production. If you don't have enough memory at the desired load, then you don't have enough memory. Modern systems can utilize swap in times of "crisis", but that's all. Simply, if you're using swap, you have a memory crisis and you should hope it goes away soon.
Once you have the base memory necessary for the applications, all of the remaining system memory is basically dedicated to disk cache.
With a system hosting Postgres, you have two kinds of disk cache. You have the kernels file system cache, and you have Postgres internal cache.
Postgres internal cache is not going to be released. That's not how it works. You told it in your configuration that it could use XXX amount of RAM for its purposes, and it's going to keep it. In this state, Postgres doesn't care about what else is on the system.
If the cache was kernel cache, and you had some sudden spike in file system activity that is NOT Postgres, then the kernel will cache the recent pages and flush the older pages. The kernel cache will see the entire system, whereas Postgres only sees DB activity.
So.
In the Postgres world view, kernel cache is competing with it's buffer cache. Consider this scenario. Postgres asks for a block of disk. The kernel grabs that block and caches it. At the same time, Postgres grabs takes that block from the kernel, and also caches it. Now, that block is redundantly cached. If the kernel find a better use for that block of cache memory, it will flush it out the Postgres block, and load the new one. Meanwhile, Postgres will retain that block in its internal cache.
If you have a dedicated Postgres machine, there's little reason to have much kernel cache. Since all of the disk I/O will be Postgres I/O, the kernel cache is redundant, and less efficient, than the Postgres cache. When Postgres caches a block, it must marshal the bytes in, update it's internal structures and whatever else it does. Once cached, it no longer has to do any of that. So in that way, a block cached by Postgres is more efficient than the same block cached by the kernel, as there is some expense in moving the block from the kernel cache in to Postgres.
However, if you have a mixed use machine, then the kernel and Postges caches are going have to fight it out. If you have a small enough DB that fits most of the daily operational data into RAM, then you should have enough buffer space to handle that within Postgres so most of its operations from memory. In that way, Postgres will load up its normal, "busy" pages once, and cache them, and then never ask the kernel for them again. Once that is done, the kernel can use its buffer cache to handle all of the ancillary other system requests.
At the other extreme, you give Postgres very little dedicated buffer cache, and have it rely solely on the kernels cache. In this way, each block is a little more expensive coming from the kernel cache each time, but it's far far cheaper than reading it from disk each time. In this way, the kernel can judge which processes are more worthy of cache attention.
In practice, decide on a good operational, steady state for Postgres and leave it at that. Any spikes in activity (say a big table scan for a report or whatever) will be mitigated by the kernels cache, and when that spike is over, the kernel can recover that memory for other uses.
So, bottom line, Postgres isn't going to give back any memory. Only give as much as you afford to dedicate to it.