Re: Major performance problems with Informix Online

In article <3455e6fc.27199369@news.tel.hr>, Ivica Smolic <ismolic@open.hr> writes >On Sat, 25 Oct 1997 09:36:44 +0100, David Williams ><djw@smooth1.demon.co.uk> wrote: > > ... cut ... > >>>>2. NUMCPUVPS looked ok, even low, I've heard use (CPUs - 1) as the rule? >>> >>> If your computer have something else to do except to run the >>>database you should leave some procesors for that. If you are binding >>>CPU_VP-s on procesors you must leave at least one procesor to run OS. >> ?? Why, MUST you leave on physical CPU to run the OS? >> ^^^^ > > From the release notes of Informix OnLine 7.23 for DEC Alpha: >"It is not recommended to set AFF_SPROC to 0. CPU 0 is a master CPU >and DIGITAL does not recommend to bind process to this processor 0." > I don't konw about other platforms. > I didn't think this might be a problem. Having one CPU 'special' like this sounds like a master-slave kernel (the kernel runs on one CPU, the master and user processes run on the other CPUS, the slaves). This is inefficient as the master CPU becomes a botleneck and does not scale well. I would have though that DEC would have produced a kernel which is true SMP not master-slave. > ... cut ... > >> AFFINITY is binding processes to CPUs. It basically improves the >> use of the Level 1 and Level2 caches on the CPU by always running the >> process on the same CPU therefore maximizing the caching effect. > > Are you shure that it is all? > Yes. > ... cut ... > >>>7. OPCOMPIND is ok to use with 7.2x?? I heard this was a showstopper. > >> Set it to 0 to allow index usage otherwise you get dynamic hash joins >> and sequential scans. > > Can you tell more about this. (I have OPCOMPIND=2) OPTCOMPIND means OPTimizer COMPare the cost of INDicies. OPTCOMPIND=0 Online tends to prefer the pre 6.x way (nested loop joins) i.e. index usage. OPTCOMPIND=2. Online deciedes whether or not to use an index based on the cost cmputed by the optimizer and allows the use of Dynamic Hash Joins. This is where both tables to be joined are sequential scanned. The first table is read into memory and a hash table built from the columns you are joining on. The second table is sequential scanned and the join columns are hashed and looked up in the hash table. i.e. both tables to be joined are sequential scanned. Put simply the Online optimizer gets the costs wrong! It tends to prefer these Dynamic Hash Joins. This means sequential scans are done rather than using indexes. Setting OPTCOMPIND=0 means indexes will almost certainly be used where they should be. >########################################################## > >Ivica Smolic > Chief of computer maintenance (or something like that) > >E-mail: > ismolic@open.hr > >Company Address: > Medimurska banka > Valenta Morandinija 37 > 40000 Cakovec > Croatia > ><URL=http://www.open.hr/com/mb>; > >tel.: > 385 40 810638 > >fax.: > 385 40 810623 > >########################################################## -- David Williams