Re: Major performance problems with Informix Online

Tim asked the original questions, David responds. David Williams wrote: > Questions about the responses ( Koens and Davids ): > >> > >>1. The logging information was a bit confusing. > > > > ??? > Sorry I've been away for a while - what logging info? > You guys each had an opinion about the logs, and the sizes. I was curious where the methodology came from. > > > >>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. Agreed. I thought maybe you were following some other kind of formula. I understood the basic reasoning, I had thought maybe there was something else in mind here. <,clipped,> > > If your database server is used only for that porpose, or have > >wery litle other jobs you can put NUMCPUVPS=_num_of_CPUs_ , and > >without affinity. If you have only two or three CPUs you can try this > >even with computer running DB and some other serious jobs. > > OK I'll take that under advisement. FWIW, I had noticed on HP systems if the NUMCPUVPS is set higher than the num_of_CPUs, it can crash the server. No, I didn't try this, but saw it happen. :-) long story... > >>3. NOAGE and affinity can cause problems on some systems, I'd turn them on > >last. > > > I would turn them on at the start, especially NOAGE. > > NOAGE - On some versions of UNIX if the UNIX scheduler notices a > process is using a lot of CPU time (e.g. CPU VPS) then it will REDUCE > the priority of the process. Hence the process gets to run LESS OFTEN. > This artifical slowing down of the process is to stop a rogue process > grabbing all the cpu time. However that is exactly what you want > Online to do, you don't want Online to be slowed down. > If NOAGE is set and it is not support you just get a message in your > online.log as Online starts up. Nothing bad happens. > OK, I can believe this for UNIX, but I wouldn't trust it for NT. NT seems to be highly unpredictable when it comes to tuning. I tend to tune on NT with everything turned off, and turn things on one at a time to see the results. NT is extremely fickle. Especially with regards to BUFFERS. NOAGE can be very unpredictable on NT. > 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. > Understood that already but thanks for the info. :-) NT offers affinity at the OS level on multi-CPU systems. By going into the task mgr you can actually turn it off/on on the fly, however I don't trust it. It is another unreliable feature of the OS. On Unix I would tend to believes it when I sees it. > E.g. 2 Physiclal CPUS CPU 1 and CPU 2, 2 processes CPU VP 1 and CPU VP > 2. CPU VP 1 last ran on CPU 1, CPU VP 2 last ran on CPU 2. > > Both CPUs are available to run processes and both CPU VP's are waiting > to run. Clearly if they both ran on the same CPUs as before then that > maximizes the use of the Level 1 and Level 2 caches on the CPUS. > (CPu caches and MMU maps do not need to be flushed and reloaded onto > the othe CPU). > > Since online CPU VPS tend to be active a lot and at the same time and > scheduling choice like these happen lots of times per second this can > make a difference. > > > They are not supported on some systems, as well as RESIDENT. > > OK again I'll take it under advisement. :-) > > >>4. I wouldn't diddle with PHYSBUF OR LOGSBUF sizes. Why would you change > >these? > > > If you are using BUFFERED logging then increasing these may improve > performance by reducing the number of write online has to do. > > If you are using UNBUFFERED logging then reducing these will free > some memory. I have also heard that online always writes out the full > buffer even if it is not all used hence reduce these can reduce the > amount of data written to disk. > OK. > > No comment. > > > >>5. Using OSTIME, and using the system instead of internal. Does this apply to > >> other machines? > > > All machines. > I think I'd defer to leaving it off unless needed. > > You should use OSTIME only when you need fraction of a second. > >(Try to check this somewhere else too.) > > Agreed. > >>6. RA_PAGES. What's your theory here? :-) > > > > The command "onstat -p" can give you some interesting numbers > >in the last row. > > If RA-pgused / (ixda-RA + idx-RA + da-RA ) is not high > >enough you should use lower values for RA_PAGES and RA_TRESHOLD. > > So if RA's are not being used, setting RA_PAGES high means too much caching, not enough writing. > >>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. > > I understand. Thanks. I had heard about this being a bug on HP, or some other platform, so have been setting it off. Just wanted more background. > >Ivica Smolic > > Chief of computer maintenance (or something like that) > > > > > >Company Address: > > Medimurska banka > > Valenta Morandinija 37 > > 40000 Cakovec > > Croatia > > > >tel.: > > 385 40 810638 > > > >fax.: > > 385 40 810623 > > > >E-mail: > > ismolic@open.hr > > -- > David Williams Thanks David, and of course Ivica... Tim -- Tim Schaefer \\\\|// tschaefe@mindspring.com 6 6 -------------------------oOOo---( )---o00o---------------------- http://www.inxutil.com - My Ezine http://www.informix.com - Informix Software Inc. http://www.iiug.org - International Informix User Group/FAQ news://comp.databases.informix - Newsgroup for Informix Users ================================================================