Re: Instance Tradeoff

In article <33DCF5CB.7CBD@bloomberg.com>, "Art S. Kagel" <kagel@bloomberg.com> writes >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). > Agreed. >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 True. >b) 7.xx utilizes the resource it has better for better performance and > less contention between databases for resources. > Yes. >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 Yes, whith one engine shared memory can be used which us much much faster than a TCP/IP (even localhost) connection. >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. > Exactly, 7 performed excellent load balancing and one instance is always what I use. >K.I.S.S. - Go with one instance. > Yep, "genius is making the complex simple" as someone once said. The only thing I would add is that for testing I normally go with a "demo" instance as well as a live instance. That way if you crash the "demo" instance whilst testing a new release of software (which happened on a project of one friends is working one) you do not affect the "live" instance. If you crash online informix tech support will generally ask you to reproduce the problem. Not what you want to do on a live system. With a test instance you can keep on crashing it until Informix find the bug and fix it. Also when Informix have fixed the bug you can try to crash the fixed version of Online without risking the "live" system. Finally on a demo instance you can do better performance monitoring as you can isolate single queries, transactions and monitor Online wittout other users on the system. >Art S. Kagel -- David Williams