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

Posted on 2008-10-17
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 46

Accepted Solution

Kent Olsen earned 1000 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 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

764 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