On Thu, 7 Aug 2003, Yaroslav Mazurak wrote:
Shridhar Daithankar wrote:
That's a nice theory, but it doesn't work out that way. About every two months someone shows up wanting postgresql to use all the memory in their box for caching and we wind up explaining that the kernel is better at caching than postgresql is, and how it's better not to push the usage of the memory right up to the limit.
I'm reading this mailing list just few days. :)))
The reason you don't want to use every bit for postgresql is that, if you use add load after that you may make the machine start to swap out and slow down considerably.
What kind of load? PostgreSQL or another? I say that for this PC primary task and critical goal is DBMS and it's performance.
My guess is that this is exactly what's happening to you, you're using so much memory that the machine is running out and slowing down.
Drop shared_buffers to 1000 to 4000, sort_mem to 8192 and start over from there. Then, increase them each one at a time until there's no increase in speed, or stop if it starts getting slower and back off.
bigger is NOT always better.
Let I want to use all available RAM with PostgreSQL. Without executing query (PostgreSQL is running) top say now:
Mem: 71M Active, 23M Inact, 72M Wired, 436K Cache, 48M Buf, 208M Free Swap: 368M Total, 2852K Used, 366M Free
It's right that I can figure that I can use 384M (total RAM) - 72M (wired) - 48M (buf) = 264M for PostgreSQL.
Hence, if I set effective_cache_size to 24M (3072 8K blocks), reasonable value (less than 240M, say 48M) for sort_mem, some value for shared_buffers (i.e. 24M, or 6144 4K blocks (FreeBSD), or 3072 8K blocks (PostgreSQL)), and rest of RAM 264M (total free with OS cache) - 24M (reserved for OS cache) - 48M (sort) - 24M (shared) = 168M PostgreSQL allocate dynamically by himself?
With best regards Yaroslav Mazurak.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
