Multiple DBs vs Multiple instances

What are the pros/cons of have multiple databases in one instance as opposed to having multiple instances with just one database.

Terrence, Archive/Restore would be an issue. Using multiple instances, would be a good method to archive a single DB. On the other hand It would be cumbersome if you must archive all the Production instances for Disaster recovery. HTH, Zev Berezin B&H Photo On 13 Mar 2003, at 12:57, Terrence Mu.... wrote: > What are the pros/cons of have multiple databases in one instance as > opposed to having multiple instances with just one database. > > > > >

This is sometimes a difficult choice. Multiple databases in one instance: On the minus side: all the work on all the db's is done by the same set of virtual processors, and all that work uses a common set of shared memory segments. The thread scheduler in IDS is not terribly sophisticated, and lots of work against one database may slow down work against another database. On the plus side, adminsitration is consolidated: all the db's are in the dbspaces of one instance. This may be a negative. It depends on your attitude about administration. Lastly, all the databases in an instance share the same logical logs. That can affect recovery times since the log records are jumbled together. Multiple instances with just one database per instance: Easier to keep things separated, but you'll have all the processes of each instance running on the system. If the machine has enough CPU's, then this may be just fine. With few CPU's the processes of one instance may starve the processes of other instances. I urge customers to have three separate instances: one each for the production system, the training system, and for the DBA's to play. I urge them to have the production system on a physically separate box from the others, just so that errors in training or testing new versions or the like doesn't affect the production system (since that one is what the whole exercise is really about.) The training and DBA systems can share a box as long as everyone is willing to risk an occasional crash and whatever response time hiccups might occur. My personal preference is for multiple databases in one instance as long as everyone is getting adquate response and throughput. Cheers, Dick Snoke Consulting IT Specialist (certified) IBM Data Management Solutions 404-487-1595 dsnoke@us.ibm.com "Terrence Mu...." <terrence@wagerwo To: ids@iiug.org rks.com> cc: Sent by: Subject: Multiple DBs vs Multiple instances [692] forum.subscriber@ iiug.org 03/13/2003 12:57 PM What are the pros/cons of have multiple databases in one instance as opposed to having multiple instances with just one database.

Ok, I'll stick my oar in. Aside from the previous posts - there is an additional advantage to multiple production instances - if you have different types of processing occurring on each instance - for example one may be a DSS instance and another an OLTP instance. Allows you to tune each database/instance for it's given need. cheers j. ----- Original Message ----- From: "Richard Snoke " <dsnoke@us.ibm.com> To: <ids@iiug.org> Sent: Thursday, March 13, 2003 2:28 PM Subject: Re: Multiple DBs vs Multiple instances [696] > > > > > This is sometimes a difficult choice. > > Multiple databases in one instance: On the minus side: all the work on > all the db's is done by the same set of virtual processors, and all that > work uses a common set of shared memory segments. The thread scheduler in > IDS is not terribly sophisticated, and lots of work against one database > may slow down work against another database. On the plus side, > adminsitration is consolidated: all the db's are in the dbspaces of one > instance. This may be a negative. It depends on your attitude about > administration. Lastly, all the databases in an instance share the same > logical logs. That can affect recovery times since the log records are > jumbled together. > > Multiple instances with just one database per instance: Easier to keep > things separated, but you'll have all the processes of each instance > running on the system. If the machine has enough CPU's, then this may be > just fine. With few CPU's the processes of one instance may starve the > processes of other instances. > > I urge customers to have three separate instances: one each for the > production system, the training system, and for the DBA's to play. I urge > them to have the production system on a physically separate box from the > others, just so that errors in training or testing new versions or the like > doesn't affect the production system (since that one is what the whole > exercise is really about.) The training and DBA systems can share a box as > long as everyone is willing to risk an occasional crash and whatever > response time hiccups might occur. > > My personal preference is for multiple databases in one instance as long as > everyone is getting adquate response and throughput. > > Cheers, > Dick Snoke > Consulting IT Specialist (certified) > IBM Data Management Solutions > 404-487-1595 > dsnoke@us.ibm.com > > > > "Terrence Mu...." > <terrence@wagerwo To: ids@iiug.org > rks.com> cc: > Sent by: Subject: Multiple DBs vs Multiple instances [692] > forum.subscriber@ > iiug.org > > > 03/13/2003 12:57 > PM > > > > > > > What are the pros/cons of have multiple databases in one instance > as opposed to having multiple instances with just one database. > > > > > > > >

Terrance, One of my biggest concerns is the balance between ease of administration vs. separating the needs of the two databases. If you have multiple databases within one instance, and you need to make a tuning change that requires an engine bounce, all databases will need downtime. If all the databases serve mostly the same customers that may not be a big deal, but if you need to schedule downtime with different constituencies in the company, this can be a headache. In addition, a tuning parameter may be more applicable to the processing needs of one of the databases yet be detrimental to others. Multiple instances allow for finer granularity in the uptime and tuning of your databases. However, if you have multiple instances each with one database all on the same machine, you run the risk of one Informix instance clobbering another. There are certainly the risks of one instance eating up memory or CPU cycles that the other or others need. But also, simple things like adding dbspaces or chunks to existing dbspaces require much more rigorous external organization, (i.e. if you accidentally try to add a dbspace or chunk using disk that is already being used by another instance, the first instance can do nothing to stop you.) Plus, of course, you have multiple onconfig files to maintain, multiple archiving policies that must be implemented, possibly sharing the machine's resources, etc. My first choice is to have only one database on a single instance per machine (in production, development can be a zoo). However, budget being what it is, if I was forced into this decision, I would tend to prefer multiple databases in one instance IF and only IF there are no compelling technical/tuning reasons that they need to be separate, and the various user constituencies can live with their database being down when another in the group needs to be down. Hope this helps. --John Bejarano, Shutterfly. > > > > What are the pros/cons of have multiple databases > in one instance > > as opposed to having multiple instances with just > one database. > > > >

Multiple instances wastes resources through duplication of effort and leads to contention. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche >From: "Terrence Mu...." <terrence@wagerworks.com> >To: ids@iiug.org >Subject: Multiple DBs vs Multiple instances [692] Date: Thu, 13 Mar 2003 >12:57:42 -0500 (EST) > >What are the pros/cons of have multiple databases in one instance >as opposed to having multiple instances with just one database. _________________________________________________________________ MSN Messenger - fast, easy and FREE! http://messenger.msn.co.uk

----LNX_Fri_Mar_14_2003_09:11:38_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.03.13 22:33:08 >Sender: Zev Berezin <zevb@bhphotovideo.com> > > Archive/Restore would be an issue. >Using multiple instances, would be a good method to archive a >single DB. On the other hand It would be cumbersome if you must >archive all the Production instances for Disaster recovery. > = I agree with that. If you have one instance you can't restore a single database (unless you take your backups with dbexport locking the database= s). You might be forced to restore the entire instance to another server and extract the needed older version of one database. That's certainly an issue. = On the other side you can provide more ressources (tmp dbspace, logicol logs space, locks, cpu vps etc.) with one big instance. In most situation= s not all applications have their peak loads at the same time. But if an application breaks all barriers (lock table overflow, exclusive= long transaction) all databases will suffer. = I don't advise to prefer one approach over the other. Some differences are a matter of personal preferences. = Regards, Andreas Kutsche = >HTH, >Zev Berezin >B&H Photo >> >>On 13 Mar 2003, at 12:57, Terrence Mu.... wrote: >> >> What are the pros/cons of have multiple databases in one instance as >> opposed to having multiple instances with just one database. ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Fri_Mar_14_2003_09:11:38_V3.33----