current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite

I'm consolidating multiple Linux boxes running Postgres into one box.

Given the way Postgres handles resource utilization, would one single server service handling all the databases provide a much better performance than multiple server services (each one listening to different IP:port) running on the same host?

Thanks

share|improve this question
1  
Nobody will able to answer you without knowing about your databases some details, like how many connections they get, how much memory/temp disk space they use, how they are interconnected by queries/application logic and so on. From a pure resource consumption the best thing to have is one physical machine for every single database but there are other factors, too. –  dezso Jun 19 '14 at 16:35
    
Having one physical machine per database is not an option. They want to go exactly the other way around (Thermal issues on the datacenter). The question I have is who would do a better job handling resources: 1) Postgres, or 2) Linux (with multiple Postgres server services running at the same time) –  Diego Puertas Jun 19 '14 at 16:38
    
You'll need to provide a fair bit more info on what they propose to consolidate. Database sizes, machine sizes, workloads and utilisation levels. –  Craig Ringer Jun 20 '14 at 1:21
    
Copied & pasted to serverfault.com/q/606497/102814. –  Craig Ringer Jun 20 '14 at 1:42

1 Answer 1

up vote 2 down vote accepted

In general, a single instance is more efficient, but has inferior performance isolation between databases.

If you want maximum throughput and best resource utilisation, put them all in a single instance. One busy DB will be able to affect other DBs more easily this way, especially when it comes to things like high rates of small write transactions. Features like async commit, commit delay, etc, help. If you group them all on one instance you'll also have to do any physical backups and streaming replication / PITR for the whole set of DBs, you can't isolate just one DB.

If you want to reduce the impact one busy/overloaded DB has on another DB, isolate them into their own instances and put each on a separate file system, perferably on separate underlying storage. You'll pay a price in wasted shared_buffers space, extra fsync()s, etc, and you'll find things like user management a major pain, but you'll be able to do streaming replication / physical backups of just one DB and you'll be more able to manage their relative resource allocations. If they're separate instances you can use things like nice, ionice, etc more effectively and can manage storage quotas somewhat better.

share|improve this answer
    
Thanks Craig, very illustrative answer. Do you know if there is something like "nice" within Postgres? –  Diego Puertas Jun 20 '14 at 20:03
    
@DiegoPuertas Not built-in. You can use OS tools, but you have to be aware of priority inversion problems with locking and that things like the WAL + bgwriter are shared. I have some funded work scheduled on this but it might slip because of other priorities, and even if I get it done it won't land before 9.5. –  Craig Ringer Jun 21 '14 at 3:43

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.