What is the overhead or multiple db2 instances on the same sever/processor?

With Oracle, SQL Server, MySQL and most other DBMS, I typically encounterone, maybe two instances (if the server is beefy) running on a single server.  I've occasionally seen 3 or 4 in non-production environments for testing and qa.  

Is DB2 any different?  Can someone (estimate or at least explain how to guess) the extra overhead associated with six instances with six databases rather than one instance with six databases?  For the sake of context, assume AIX/POWER architecure, 8gb ram with data stores of 200gb each, 20 concurent users each with moderatly complex queries.

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi goknows,

In a production environment you'd want to be very careful before putting multiple instances on the same server.  Since each instance will require certain dedicated resources and share the remaining resource, tuning issues come into play.

There are several big reasons to put multiple instances on the same server.  The reasoning will be about the same regardless of the DBMS.

1.  Cost.  It's a lot cheaper to install another instance on an existing server than to buy an additional server, buy the necessary software licenses, and absorb the additional overhead of running another server.
2.  Turnkey packages.  If you buy a database package from vendor ABC, you may well want or need their data isolated from your own databases.
3.  Local isolation.  It's often conventient to put different databases on different instances.  Particularly if each instance is really just a test version of a production instance.

The big concern is performance.  If you've got a database application that consumes 60% of the server's resources, putting another database on that same system that also requires 60% of the server could be a problem.  In this case it doesn't really matter whether the two are separate instances.

With all that said, overcommitting resources in a test environment isn't necessarily a bad thing.  Your test environment may not normally exercise all of the databases at production levels simultaneously.    Each instance is configurable to work as it is expected to work in production.

Assume that Instance 1 is tuned to run in 4G.  Instance 2 is tuned to run in 6G, and instance 3 requires all 8G.  As long as only 1 instance is "active", it should run just fine.  If there is extra CPU available, instances 1 and 2 will coexist well since memory isn't overcomitted.

That's the key thing.  Memory.  As long as there is sufficient memory for the instance(s), everything should be fine.  If Query 1 in Instance 1 requires 60 CPU seconds to complete, it shouldn't vary much from 60 seconds if Instance 2 is also active.  The wall time may increase, as there may not be sufficient CPU to meet the real time demands of the queries on both instances, but the 60 CPU seconds won't change much.  If a query on Instance 3 is running when the Instance 1 query starts, all bets are off.  Both instances are tuned to expect a specific amount of memory being available.  When the memory is not available, pages are often swapped and thrashing occurs.  Page faults take measurable time, and require the CPU, so the required resources will go up when there's not enough real memory for the instances to run as they are configured.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
One instance equals X committed memory when the instance starts up. As users connect to each database, more memory is allocated per user and per database. Of course, you can tune the database memory, but you are not in a memory constrained system. However, 6x20=120 users, if all 120 are on at the same time, then KDO's post is correct, eventaully, performance will suffer, especially if you have the databases and the logs on the same disk. But if you expect 10 concurrent users at a time, then performance wsill be ok. Just put each db and its logs on a seperate disk and make sure your i/o subsystem is not creating a bottleneck.
As far as the 'moderatley complex sql', run the statements and get a snapshot for the application id. That will give you an idea of how much memory is required to run the statement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.