Re: Major performance problems with Informix Online

In article <344efd22.9330992@news.tel.hr>, Ivica Smolic <ismolic@open.hr> writes >On Mon, 20 Oct 1997 07:21:54 -0400, Tim Schaefer ><tschaefe@mindspring.com> wrote: > >>I <was> curious to some of the tuning information, were these recommendations >>purely for a Siemens machine or were these for 7.2x in general? >> >>A couple of the recommendations were interesting ( yes experience here ) and >>I want to know a little more reasoning, if you can elaborate. I'm in learning >>mode here. :-) >> >>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? > >>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? ^^^^ Bind CPU VPS means that the oninit process will only run on that CPU. It does not mean that that CPU will only run the oninit process. Having said that if you are running other programs apart from Online on the machine then you want to allow them some CPU time E.g. - UNIX mail Clearly UNIX mail requires some CPU time hence having Online grab all the CPU time on the machine when it is under peak load would mean e-mail becomes very slow. This is generally not acceptable. - 4GL applications. If Online uses all the CPU time, my 4GL process does not get to run. Hence interative performance becomes poor and users get frustrated. E.g. user types "David Wllliams" into a field and the characters appear on the screen REALLLLY SLOWWWWLY! - Client/server applications Clearly the TCP/IP code in the kernel needs some CPU time to send network packets back to client machines! (Number of CPUS-1) means Online gets most of the machine but other necessary things like the kernel and user applications are also guaranteed some cpu time since there will always be at lest 1 CPU ^^^^^^^^^^ available to run them. > 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. > >>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. 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. 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. > >>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. > No comment. > >>5. Using OSTIME, and using the system instead of internal. Does this apply to >> other machines? > All machines. > You should use OSTIME only when you need fraction of a second. >(Try to check this somewhere else too.) > >>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. > >>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. > >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