Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1114
  • Last Modified:

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
0
goknows
Asked:
goknows
2 Solutions
 
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.


Kent
0
 
ghp7000Commented:
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now