Nov 6: The Reality Gap (3) - A Single Instance per Server
By coincidence, this question and answer appeared on asktom yesterday. At first I thought it was an incredible coincidence because I was planning to write about this last weekend, but in retrospect, this subject is something Tom had been banging on about for some time. As the first follow-up comment highlights, you can find many more examples of Tom discussing this question in the past with a simple search. In fact if you search for the precise string "Single Instance Per Server" you'll get a couple of results!
Tom's recommendation is that you have a single Oracle instance per server. There are several good reasons that spring to my mind, but I recommend you read Tom's own thoughts on the subject.
- You can allocate one large pot of memory to the single instance and allow Oracle to use it more efficiently, depending on which application needs it. If you have multiple, smaller instances, you're limiting what each pot can be used for. As a general systems management approach, I prefer bigger pots as long as they remain efficient.
- Not only is resource usage more efficient, fewer instances reduce system maintenance overhead, which is always a good thing. We have enough instances to look after, thanks!
- If you don't use a single instance per server, then you're not going to get the best out of Resource Manager (although I'm still hearing anecdotal evidence of bugs there).
I've worked at several sites who do this to some degree. With the growing popularity of tools such as APEX, companies are keen to implement small 'tactical apps' (not my phrase) to meet simple business requirements. A General Purpose database is a good home for them and I work on some at the moment.
However, here are some problems with this approach.
- When you want to upgrade to a new version of Oracle, every application support or project team has to agree. The companies I've worked with over the past few years are buying off-the-shelf applications by the bucket-load and developing less of their own. Which means we need multiple companies to agree that their application can run on the new version. I've heard of a vendor recently who refuses to certify their app to 9.2.0.8. They agree to 9.2.0.3 (!) or 10.2.0.3. Brilliant - we can move to 10g! Erm, no actually, because one of the other vendors isn't certified on 10.2 yet. It's incredibly frustrating. Of course, we have this problem anyway and having multiple different instances on different versions will make our lives more difficult, but at least we don't have every application sitting on an old, unsupported version of Oracle because of one piddling little app.
- When you want an outage for anything, you have to speak to all of the app owners to get it. If one of the app owners don't ok it, for whatever reason, we have to wait ... and wait ...
- What happens when one application vendor wants you to set an instance parameter to a specific value and another doesn't? How many vendors do you think state that they are happy to support their application co-existing with another in the same database instance? It really doesn't matter whether I think they're wrong (in fact I've shown more than one vendor that it was achievable with their app), the business will always listen to the vendor. They're paying them money for (cough, splutter ...) support.
This could go on for some time but you get the picture. If this was just a technical decision, then I'd have no argument, but it isn't. It's a business decision and will be dictated by business needs in the end. You might think that it's my job as an IT professional to offer the best solution, explain the reasons and implement it and I attempt that on a day to day basis but I see IT departments playing that role less often these days and just responding to whatever the business tell us to do.
As I will keep repeating during this short series, this is not about the technical merits of the accepted wisdom, it's about the complete gap between recommendations like 'a single instance per server' and the systems I see daily. Remember, too, that as a contractor I didn't build all of these systems; these sites are not following my personal mantra and they aren't all idiots. So, should anyone suggest that I should go and work at a good site, please point me in the right direction.To finish this blog I tried to think of the last time I saw a server with a single Oracle instance on it. The only one I could think of is the ISP4400 downstairs in my house. Seriously, I can't think of a server that has a single instance on it.
(Updated later - While getting ready for work, I remembered one from my time at Pythian - a client with a two-node RAC cluster.)
(Updated yet again - Oh, and Data Warehouses tend to be on their own server)
Horror of horrors, I see servers with over 20 instances on them regularly.
Sorry, Tom. I don't disagree with the gist of your argument, but the reality I witness is miles apart.
* But maybe not? If you talk to DBAs who work with other DBMS, they often combine application databases into server-wide instances. So maybe I am imagining all of this and it's about how I'm used to working with Oracle? I genuinely don't know. Maybe because the business know that they can have a seperate instance and DBAs have encouraged it in some way, then we've created this problem for ourselves?
Updated later - Again, it took me a while to find a related link I remembered vaguely. Over on Howard's site there was a discussion about multiple instances on a server.
#1 - Alex Gorbachev said:
2007-11-06 08:17 - (Reply)
I can observe both - over-consolidated applications on a single database when applications are completely different by workload, life-cycle and operational requirements. Another extreme is to add new database for every logical block of the same application or geographical area and host them on the same server.
#3 - Nigel Thomas said:
2007-11-06 09:28 - (Reply)
I think Tom Kute's preference is far too simplistic. Sure, it can be convenient to keep database instances one for one on separate servers. But in real life it's not always practical or cost effective. Alternative valid models include use of system partitions / LPARs where available (or even VMWare); multiple co-resident databases (or RAC instances), etc.
The argument about performance applies to different classes of user across "a single application" as well as across separate applications. The database/application architect needs to make a properly considered choice, rather than blindly following a rule of thumb.
Cheers Nigel
#4 - Mathew Butler 2007-11-06 09:58 - (Reply)
Interesting post.
In the past I have argued for one server one instance, and as a result saved a huge amount of money on hardware and ongoing administration costs. In this case though the systems were being developed by us, and so we were in control of the code and configuration. It still required a fight to get it agreed though. I think that in the end the cash saving won the argument rather than any technical merits ( the performance benefits here in terms of saved CPU were huge ).
The exception was a third party application, which had it's own requirements for DB version/patching and was performance critical, which we placed on seperate hardware.
There are always exceptions which provide sound reasons for seperating hardware. I'd expect Tom Kyte to agree - but whatever his view I'm sure it will add to the debate.
BTW - in our current development system we employ this approach and have some 40 plus application schemas ( one for each developer ) all in one instance. Imagine the hardware required, if this was one schema per instance...
#5 - Niall Litchfield said:
2007-11-06 10:40 - (Reply)
nearly all the production databases that I have seen have been one instance per server - they've also been one application per instance! If you need a new app you get a new server - this is in the intel world of course where a new server might cost 5k.
You are right about vendor support, and to be honest I have some sympathy with the vendors here, how do you support something that you do not have some control over. But it does lead to server proliferation. I had some hopes that virtualisation might be the way forward, but vendors won't certify on that either - and so what I see there is new servers built for the vmware farm - and then the old servers remain in service anyway!
#6 - Thomas Kyte said:
2007-11-06 13:45 - (Reply)
What people seem to miss is the "one instance per server/host"
Virtualization.
When you have a packaged application, sure the database is basically a black box - part of the package, the vendor dictates what happens, what version.
does that mean you have to load up a singer HOST with 50 instances and let them have at eachother?
No, not at all - you can use many smaller machines (we do, lots of 2/4 cpu tiny machines in a rack) or you can virtualize/domain the machine out into smaller machine (eg like solaris 10 containers, or vmware, whatever)
You do not want to have more than an instance per host.
Nigel was half right with this comment:
. Alternative valid models include use of system partitions / LPARs where available (or even VMWare); multiple co-resident databases (or RAC instances), etc....
I never ruled out his first "alternative" model (in fact, I've stated it as an option, a very good on but the multiple co-resident databases - no.
I didn't understand his 'or RAC instances', but if you are running more than one RAC instance on the same host, you have missed the point entirely.
#7 - Doug Burns said:
2007-11-06 16:53 - (Reply)
Sorry about that Tim - second one today I've heard of.
This was Tim's comment, let's see if I run into the same problem.
I know exactly what you mean. I've worked in both styles of environment. In my current position we have one instance per server and it's cool, but upgrades are hard to manage.
Personally, I'm not a fan of having multiple versions of Oracle on a single server. I've had a couple of nasty experiences, especially with RAC. So if I had several applications on different versions of Oracle I would press for separate servers.
In a previous job we used the single instance per server rule, but each server was actually a virtual server, so really there were many instances on a single box. Yes, the overhead of running many virtual machines is higher than just several instances on a single server, but the issue with upgrades etc is resolved quite nicely. Also, if you use something like GSX server, you have fine control over the resources allocated to each virtual server.
I think its worth promoting "ideals" such as one instance per server, even if you aren't able to meet them. We all have to make compromises due to business needs or cash constraints, but it doesn't stop us understand or aiming for the ideal solution.
#8 - Doug Burns said:
2007-11-06 17:19 - (Reply)
Tons of interesting comments - thanks. I think Tim was right
I think its worth promoting "ideals" such as one instance per server, even if you aren't able to meet them.
I was just pointing out how far away a lot of sites are from these ideals. Why I think the gap in itself is important is likely to be addressed in the last posting in the series.
The other comment that jumped out at me was part of Niall's
I have some sympathy with the vendors here
Do you know what? I don't any more. I think the software vendors need to take a good hard look at the shoddy product they deliver, their complete avoidance of the operational needs of large IT departments, the ....
Sigh, I could go on *forever*. Time after time I see in-house developments knock the purchased alternative for 6, but unfortunately the in-house version costs much more and the salesmen aren't as good, so DBAs get landed with shoddy workmanship. I find it faintly ridiculous that I should have to explain to a vendor why their key product, the thing their business is based on, is a steaming pile of ****.
Blunt, I know, but I'm tired of software vendors. (Except for Oracle, of course, who are utterly marvellous)
#9 - Pavel Ruzicka 2007-11-06 20:38 - (Reply)
Dough
I see all your posts in this “Reality Gap” series being “spot on”.
I currently work in environment with hundreds of Oracle servers. In most cases we have zero control over lifecycle and design of the applications (like “certification” against versions/patchsets, physical design etc.), some of them run under hosting/housing like SLAs.
I see many reasons for operating multiple database instances in single box. As said before, this is topic has both technical, organizational and financial aspects. If you look on this subject only from technical aspect you might be missing the whole picture.
I think I do understand the argument for “running single Instance per server” but.. let’s have look at the following issues:
1. Physical design of application P is so great that it has hard-coded schema name XXXX for each “application instance”. We operate this application for tens of customers, each having number of “locations/warehouses” -> each location/warehouse = “application instance”, each “application instance” subsequently means => separate db instance.
Should we run single box for each “application instance” (license cost, maintenance overhead, energy consumption, floor space, cooling..)? In some cases we are talking about 1..20 concurrent users, 10G..50G of user data, peaks only couple of hours per typical business day.
The preferred deployment design – consolidate all db instances into n-node failover cluster, averaging 4..8 instances per node, memory sizing to cater for failover situation n–1 nodes.
2. Level of isolation – unfortunately, the “single box, single instance, multiple db schemas” does not give us “required level of isolation”. We do have requirements for different application and db versions, different NLS settings (characterset), db parameters etc.
3. Downtime “orchestration”, separate application support groups and change management process
Example: Application P has multiple “application instances” deployed in different countries. Some of them are based in countries where concept of weekend is slightly different from what we are used to (Friday non-business, Sunday business day).
Some “application instances” have are supported (from application support point of view) by third parties and at the same time some of the “application instances” are supported internally. In these circumstances – it is very difficult to consolidate into “single instance, single box, multiple schemas”.
Open questions:
1. What gives you bigger maintenance overhead – 21 servers with 21 instances or three node failover cluster with 7 instances each? Of course, the best thing is to have “single instance, single box, multiple schemas” to minimize the storage, memory, process overhead but.. the above mentioned restrictions apply..
2. Tom Kyte says “..you can use many smaller machines..” and Niall Litchfield says at the same time “..this is in the intel world of course where a new server might cost 5k..”
My comment is – If we talk about smaller PHYSICAL machines then I do not think that the number on the “server purchase order” is the “total cost of ownership”. Who’s paying for the electricity, cooling, server support, licenses (no only Oracle other “overhead SW” like backup clients, monitoring)
3. How do you charge back your services in “single instance, single box, multiple schemas” or “single clustered database, multiple db services, grid”?
What statistics/metrics are available to measure db service “resource consumption? Are these metrics/numbers cumulative and PERSISTENT across instance restarts?
My conclusions are:
1. The virtualization/consolidation is not about hype. It is not “SOA”, it has real, tangible and enumerable driving factors.
2. There are better and worse ways of doing the virtualization/consolidation but it is not possible to stick to rigid “single instance, single box, multiple schemas” or “single clustered database, multiple db services, grid” mantra..
2. Tom Kyte says “..you can use many smaller machines..” and Niall Litchfield says at the same time “..this is in the intel world of course where a new server might cost 5k..”
My comment is – If we talk about smaller PHYSICAL machines then I do not think that the number on the “server purchase order” is the “total cost of ownership”. Who’s paying for the electricity, cooling, server support, licenses (no only Oracle other “overhead SW” like backup clients, monitoring)
My conclusions are:
1. The virtualization/consolidation is not about hype. It is not “SOA”, it has real, tangible and enumerable driving factors.
2. There are better and worse ways of doing the virtualization/consolidation but it is not possible to stick to rigid “single instance, single box, multiple schemas” or “single clustered database, multiple db services, grid” mantra..
#10 - Dominic Delmolino said:
2007-11-07 14:58 - (Reply)
Interesting debate. I tend to lean toward the one instance per server / vm / lpar / suncontainer direction just because I find it easier to monitor and manage:
1. Multiple instances cloud server-based accounting for CPU / IO. I routinely watch CPU / IO per server since that's easy for my operations people to provide. Based on that, I can see which database is having / causing problems. My ops people also keep history so I can see what happened last night, last week, etc. Yes, I can build / buy my own tools to do that per database, but why should I?
2. I tend to like fewer databases so I don't have to repeat patching across "100's" of database. I understand the outage argument, but in the end I like making sure I can feel confident in my patch levels.
3. For chargeback I like to use segement statistics for the schemas making up the application. This doesn't work for objects shared across applications, but I could probably figure out a way around it.
4. On outage orchestration -- thank goodness for the Pacific Ocean. I find that there's a natural downtime in most systems (unless they're running huge batch jobs all the time -- in which case can I come tune them for you?).
5. Common sense comes into play here too -- combining apps into one database that have wildly different workload characteristics is not a recipe for success. But the concept of one database per server doesn't eliminate the need for workload assessment.
#11 - Doug Burns said:
2007-11-07 18:49 - (Reply)
Mmmm, Tim had a another problem with the spam blocker, which I'll need to investigate 
For now, here is Tim Hall's comments from today ...
Pavel Ruzicka:
I think your points have already been answered in the previous posts. There is always the option of virtualization, giving the effect of one instance per server.
General:
I have a certain sympathy for vendors because it's hard to certify a product for many versions of the same engine, let alone a multi-engine product. Let's remember that many of Oracle's own products have limited certification. When we first started using Oracle Collaboration Suite (OCS) we were forced to buy a different set of servers because it was not certified to run on our database version. Same with eBusiness Suite... If Oracle can't keep on top of this stuff, how can we expect others too?
Remember, one instance per server is not the law! 
#12 - chris slattery 2007-11-07 22:55 - (Reply)
I wonder; everybody is assuming a 0% impact from virtualization /lpars over real machines
and a couple of people are not factoring in licencing costs.
#13 - Doug Burns said:
2007-11-07 22:59 - (Reply)
All good points, but I'm happy to sit back and watch any debate develop 
#14 - Paul MAtuszyk 2007-11-28 17:26 - (Reply)
200% agreed with Doug. I am taken aback that Tom had such thesis and tried to defend it.
It is not only a problem of the database but also a platform. One application vendor certified their solution for a specific hardware platform and you can do nothing about it - consolidation? Theory, theory, theory
Nice blog, Doug anyway.
Paul
#15 - Noons said:
2007-11-29 11:48 - (Reply)
I can see the strength of Tom's argument and certainly the advantages. But there are two very strong arguments against what he puts forward:
1- Peoplesoft for example - one of Oracle's own stable nowadays - very clearly recommends multiple instances per host for various sections of its product. For example, if you install EPM and HCM, 999 times out of 1000 you'll find them on two separate instances in the same db server. Why? Ask them, I won't go into the details here. But it has to do with 2 below...
2- Having multiple applications in the same instance increases the risk of hitting the (in)famous "update of the wrong table" bug. This has been around since 9i and it's a testament to the inertia of Oracle support and development that this bug is not yet fixed. Basically, if you are unlucky enough to have two tables with the same name in two different schemas, you incur the risk of having updates for one be directed to the other.
Absolutely revolting that this is not yet fixed even in 10.2.0.2, but it's a fact. No dba in his right mind will risk this happening. And no, I won't provide the bug number nor the Metalink note describing the problem: go search for it, I've talked about this many times online.
In my current site case, the main argument for multiple instances per node is very simple:
1- VM technology does not yet partition systems with enough resilience and fine degree of control to allow multiple db servers per node without some interference in IO prioritization.
2- Having two or more applications per instance is asking for trouble when it comes time to upgrade Oracle: in most cases, one app can't or won't be certified at same level as the other(s). This effectively and practically restricts us to one app per instance.
3- Oracle's instances are not as well isolated against multiple application interference as for example SQL Server's. By this I mean: all apps will share the same UNDO and the same REDO areas, for example.
With SQL Server, each database in a single instance has its own temp, undo and log areas which we can then apportion to disks according to needs of each application.
Sorry "Oracle-heads", but it's a fact. And no, I don't pine only for SS. Nor do I have a special "like" for it. Quite the opposite, in fact!
Thanks for the excellent series, Doug.
