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

Kdo 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 (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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: (…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now