For work we have a 8GB Linode, hosting both the Rails 3 App (Passenger, Apache) and Postgres.
The app is a reading heavy app, about 75% reading, and 25% writing. (A conservative estimate would be 90/10.)
The App is getting slow on some of the JOIN and SELECT statement. Upwards of 3000 ms. I know this doesn't really tell much.
Looking at the
postgresql.conf I don't think the Postgres instance was ever optimized.
I read PostgreSQL 9.0 High Performance by Gregory Smith, and came up with the following optimizations.
shared_buffers = 10% of (8GB*1024MB/GB) effective_cache_size = 50% of (8GB*1024MB/GB) checkpoint_segments = 10 work_mem = 25MB wal_buffers = 10MB maintenance_work_mem = 400MB
Would there be any repercussions from the above parameters? I am aware that work_mem may in theory use a lot of memories depending on the queries executed.
Currently our max_connections is 100, and we don't even reach near 100 concurrent clients.
=== CPU Core Summary === cpu MHz : 2266.792 processor : 7 model name : Intel(R) Xeon(R) CPU L5520 @ 2.27GHz cpu MHz : 2266.792
I will suggest to work about the upgrade to the latest version, I did not know that 9.3 has better index scanning features.