Josh Close wrote: >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? > > Setting shared buffers above something like 10-30% of memory is counter productive.
>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? > > > Increasing sort_mem can help with various activities, but increasing it too much can cause you to swap, which kills performance. The caution is that you will likely use at least 1 sort_mem per connection, and can likely use more than one if the query is complicated. effective_cache_size changes how Postgres plans queries, but given the same query plan, it doesn't change performance at all. >I've looked for information and haven't found any useful pages about this. > >Any help would be greatly appreciated. > >Thanks. > >-Josh > > John =:->
signature.asc
Description: OpenPGP digital signature
