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

Posted on 2008-10-17
Last Modified: 2012-05-05
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
Question by:goknows
LVL 45

Accepted Solution

Kent Olsen earned 250 total points
ID: 22743778
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.

LVL 13

Assisted Solution

ghp7000 earned 250 total points
ID: 22753021
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question