I have some queries that have significan't slowed down in the last
couple days. It's gone from 10 seconds to over 2 mins.
The cpu has never gone over 35% in the servers lifetime, but the load
average is over 8.0 right now. I'm assuming this is probably due to
disk io.
I need some help setting up postgres so that it doesn't need to go to
disk. I think the shared_buffers and effective_cache_size values are
the one's I need to look at.
Would setting shmmax and smmall to 90% or so of available mem and
putting a lot for postgres be helpful?
Effective cach size says this:
Sets the planner's assumption about the effective size of the disk
cache (that is, the portion of the kernel's disk cache that will be
used for PostgreSQL data files).
Does that mean the total available ram? Or what's left over from shared_buffers?
I've tried different things and not much has been working. Is there a
good way to ensure that most of the tables accessed in postgres will
be cached in mem and not have to go to disk?
If I'm joining a lot of tables, should the sort_mem be set high also?
Do shared_buffers, effective_cache_size, and sort_mem all use
different mem? Or are they seperate?
I've looked for information and haven't found any useful pages about this.
Any help would be greatly appreciated.
Thanks.
-Josh
