Re: Instance Tradeoff

Clem Akins wrote: > > >I'm going to be putting six or seven databases on the same machine (IBM > >RS/6000 w/4 SMP CPU's and AIX running Informix v7.2x). My question is: > >what are the tradeoffs between putting each database in its own instance, > >vs. putting them all in the same instance. I will need to run reports > >from more than one of these databases concurrently (i.e. select ... from > >db1:xxx, db2:yyy etc.). Using Ver 5.0x I habitually set up multiple instances to isolate databases that did not have to communicate and/or which had differing resource needs (high update -vs- high query rate) or to get access to more resources (ie more chunks than can fit on a 5.0x chunk table page). In 7.xx I am merging these back into a single instance. I find that a) 7.xx required more resources than 5.0x in terms of memory and CPU, but b) 7.xx utilizes the resource it has better for better performance and less contention between databases for resources. In addition, if you have to make frequent queries accross databases then the tradeoff becomes even easier. The communications cost of connecting between multiple engines is just too high. The single instance will process these reports MUCH faster. Especially since the optimizer can optimize both (all?) sides of the remote joins whereas even passing cost information between instances the local optimizer cannot do as good a job as it could if it managed the whole shebang. Keep in mind that with two instances (worse with more) you either each set of CPU VPS and AIO VPS will assume that it has the entire machine and the two instances will continually cause each other to become suspended, or if you keep each of the instances to a subset of the available CPUs you may be starving one instance which may temporarily have a load situation which could take advantage of another CPU VP while the other instance is idle. Unless the load patterns of the several instances are identical you will have to manage the number of VPs in each instance constantly to keep each engine fed as needed. K.I.S.S. - Go with one instance. Art S. Kagel