Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

Bill

share|improve this question
4  
Start with "use a current version". 9.0.2? Seriously? Upgrade urgently to 9.0.17, then start planning your move to 9.3 promptly. Especially with a read-heavy workload you're likely to benefit a lot from the index-only scans added in PostgreSQL 9.2. Also, what's your max_connections? How many concurrent clients do you have? On a system with what I/O subsystem and CPUs? –  Craig Ringer Mar 21 at 4:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.