RE: CPUvps

In TechNotes Vol8 No3 (great article), the internal global variable "FetBufSize" and the environment variable "FET_BUF_SIZE" are mentioned as tuning variables for the SQL Cursor Buffer. I set the environment variable, FET_BUF_SIZE, to 32000, w/ result of no impact on performance. I then tried setting the internal variable FetBufSize but received variable not defined error in compiling the 4GL code. My read from the article was that this variable would be a global. The ".c" and ".ec" code doesn't refer to this variable anywhere. We're running 7.3 IDS. Anyone know what I am doing wrong? Thanks -----Original Message----- From: PC User [mailto:kagel@bloomberg.net] Sent: Thursday, April 06, 2000 2:57 PM To: informix-list@iiug.org Subject: Re: CPUvps "Parker, Jeff" wrote: > I haven't seen much doc on PSORT_NPROCS. > Check my article on Performance Tuning in TechNotes Vol8 No3 for details. This is a favorite subject of mine. > > Feedback would be appreciated - thanks. > > I have PDQPRIORITY=0 > > I assume PSORT_NPROCS is set in the user environment? > Yes. > > What would be the range of values for PSORT_NPROCS for a given user (? 1 to > number of CPU's) Informix docs say values from 1-10 are effective and that values above 10 are the same as 10. My experience differs. I have seen performance gains in sorting, especially index building and UPDATE STATS for values up to 50 with PDQPRIORITY >= 25. Since all of the sort threads will be waiting for I/O frequently I would expect that the number of CPUs is irrelevant. > > Given a large user load, is it possible to penalize performance by making > this parm too high > (since I've read that online reserves a small amount of fixed space for sort > mem and this is divided between PSORT_NPROCS)? > The sorting will hog I/O, CPU and memory resources during the sort, but, like the rest of Informix parallelism you have to balance the increased impact with the reduced duration of impact and improved response times. > > The only benefit would be when executing queries where sorting was necessary > - correct? i.e. order by doesn't match index used for filtering. > Wellllll. Since the IDS 7/9 optimizer has no qualms about using a sort and filtering using a non-matchin index to speed things along there are more queries that can take advantage than one would expect. In 5.xx this is absolutely true but not in 7.xx or 9.xx. > > Are there any negatives? > No. > > Is it dependent on any other onconfig parms (such as the DSS parms)? > PSORT_DBTEMP, if set, specifies a list of OS filesystems to use for sort-work files rather than the dbspaces listed in DBSPACETEMP. Setting this has the advantage of reducing the impact of the sort on the Informix buffer pool and takes advantage of additional resources such as the OS buffer cache and the possibility that you can list filesystems on drives/controllers that will not impact other Informix I/O. If set use AT LEAST 3, and preferably 4, filesystems. Due to the way Informix merges temp files this is the best configuration. > > How can I tell from using "onstat" commands if it is helping? or do I just > measure response time? Onstat -g ses for the sessions taking advantage will show increased numbers of sort threads and sort memory blocks. Art S. Kagel > > -----Original Message----- > From: Art S. Kagel & Family [mailto:kagel@erols.com] > Sent: Monday, April 03, 2000 12:47 PM > To: informix-list@iiug.org > Subject: Re: CPUvps > > David Williams wrote: > > > > In article <38E26B6D.C3B089BA@erols.com>, Art S. Kagel & Family > > <kagel@erols.com> writes > > >Just two things to add. You can improve the distribution of work and > > >improve responsiveness if you have shm poll threads on all CPU VPs. > > > > > >On the subject of CPU VP > CPUs, there is some evidence that for > > >systems with processors faster then about 400MHZ, on HP/PA and Sun/ > > >Sparc at least, having up to 2 CPU VPs per CPU can improve throughput. > > >I do not know Steven's model or CPU speed but he may be trying to take > > >advantage of this, so far anecdotal, evidence. In general Mark is > > >correct CPU VPs should be #CPUs - 1. > > > > > > > I have found that PSORT_NPROCS > CPUs does improve performance > > even on Sun 170Mhz processors!! > > Maxing out PSORT_NPROCS always helps, yes. That is a separate issue > from the value for NUMCPUVPS. > > -- > Art S. Kagel & Family > kagel@erols.com