Re: Bloody ERP schema

Oxow wrote: > Hi! > > We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and > after > that we have regonised some performance problems. > > DBSERVERNAME sapshm # Name of default database server > DBSERVERALIASES saptcp # List of alternate dbservernames > NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters > NETTYPE soctcp,2,200,CPU # Override sqlhosts nettype parameters You should change shm to CPU and soc to NET. Network listeners on a CPU VP is a bad idea. There was a brilliant explanation of Art Kagel a few weeks ago in this newsgroup. > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) Setting resident to 1 will disable pageout of buffer memory. Setting this to -1 will disable pageout of vitual shm also. > NOAGE 0 # Process aging Setting NOAGE to 1 will disable the OS to lower process priority for oninit. > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors Don't know about AIX, but it might be useful to bind CPU-VPs to physical CPUs with this two params (1, 8). How many physical CPU's are inside this box ? For 8 CPU-VPs you should have at least 9.

All valid suggestions, Frank, but they can't be the cause of his problems ... can they? "Langelage, Frank" <frank@lafr.de> wrote in message news:bab9c0$qs4q2$1@ID-48907.news.dfncis.de... > Oxow wrote: > > Hi! > > > > We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and > > after > > that we have regonised some performance problems. > > > > > DBSERVERNAME sapshm # Name of default database server > > DBSERVERALIASES saptcp # List of alternate dbservernames > > NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters > > NETTYPE soctcp,2,200,CPU # Override sqlhosts nettype parameters> > You should change shm to CPU and soc to NET. > Network listeners on a CPU VP is a bad idea. > There was a brilliant explanation of Art Kagel a few weeks ago in this > newsgroup. > > > > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)> > Setting resident to 1 will disable pageout of buffer memory. > Setting this to -1 will disable pageout of vitual shm also. > > > > NOAGE 0 # Process aging> > Setting NOAGE to 1 will disable the OS to lower process priority for oninit. > > > > AFF_SPROC 0 # Affinity start processor > > AFF_NPROCS 0 # Affinity number of processors> > Don't know about AIX, but it might be useful to bind CPU-VPs to physical > CPUs with this two params (1, 8). > How many physical CPU's are inside this box ? > For 8 CPU-VPs you should have at least 9. >

Neil Truby wrote: > All valid suggestions, Frank, but they can't be the cause of his problems > ... can they? > Who knows ?! But this requested changes will make it better. I definetly would change the NETTYPE params at least and execute a "correct" update statistics. > "Langelage, Frank" <frank@lafr.de> wrote in message > news:bab9c0$qs4q2$1@ID-48907.news.dfncis.de... > >>Oxow wrote: >> >>>Hi! >>> >>>We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and >>>after >>>that we have regonised some performance problems. >>> >> >>>DBSERVERNAME sapshm # Name of default database server >>>DBSERVERALIASES saptcp # List of alternate dbservernames >>>NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters >>>NETTYPE soctcp,2,200,CPU # Override sqlhosts nettype parameters>> >>You should change shm to CPU and soc to NET. >>Network listeners on a CPU VP is a bad idea. >>There was a brilliant explanation of Art Kagel a few weeks ago in this >>newsgroup. >> >> >> >>>RESIDENT 0 # Forced residency flag (Yes = 1, No => > 0) > >>Setting resident to 1 will disable pageout of buffer memory. >>Setting this to -1 will disable pageout of vitual shm also. >> >> >> >>>NOAGE 0 # Process aging>> >>Setting NOAGE to 1 will disable the OS to lower process priority for > > oninit. > >> >>>AFF_SPROC 0 # Affinity start processor >>>AFF_NPROCS 0 # Affinity number of processors>> >>Don't know about AIX, but it might be useful to bind CPU-VPs to physical >>CPUs with this two params (1, 8). >>How many physical CPU's are inside this box ? >>For 8 CPU-VPs you should have at least 9. >> > > >

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:babaet$r95fh$1@ID-162943.news.dfncis.de... > All valid suggestions, Frank, but they can't be the cause of his problems > ... can they? > well NOAGE certainly could be if the CPU VP's are getting aged now! > "Langelage, Frank" <frank@lafr.de> wrote in message > news:bab9c0$qs4q2$1@ID-48907.news.dfncis.de... > > Oxow wrote: > > > Hi! > > > > > > We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and > > > after > > > that we have regonised some performance problems. > > > > > > > > DBSERVERNAME sapshm # Name of default database server > > > DBSERVERALIASES saptcp # List of alternate dbservernames > > > NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters > > > NETTYPE soctcp,2,200,CPU # Override sqlhosts nettypeparameters > > > > You should change shm to CPU and soc to NET. > > Network listeners on a CPU VP is a bad idea. > > There was a brilliant explanation of Art Kagel a few weeks ago in this > > newsgroup. > > > > > > > RESIDENT 0 # Forced residency flag (Yes = 1, No = > 0)> > > > Setting resident to 1 will disable pageout of buffer memory. > > Setting this to -1 will disable pageout of vitual shm also. > > > > > > > NOAGE 0 # Process aging> > > > Setting NOAGE to 1 will disable the OS to lower process priority for > oninit. > > > > > > > AFF_SPROC 0 # Affinity start processor > > > AFF_NPROCS 0 # Affinity number of processors> > > > Don't know about AIX, but it might be useful to bind CPU-VPs to physical > > CPUs with this two params (1, 8). > > How many physical CPU's are inside this box ? > > For 8 CPU-VPs you should have at least 9. > > > >

It'll be the stats, or more likely some change in the optimiser for his key processes. It won't be a parameter setting. The only one that makes any difference is BUFFERS :-) "David Williams" <djw@smooth1.fsnet.co.uk> wrote in message news:babfep$uqf$1@news6.svr.pol.co.uk... > > "Neil Truby" <neil.truby@ardenta.com> wrote in message > news:babaet$r95fh$1@ID-162943.news.dfncis.de... > > All valid suggestions, Frank, but they can't be the cause of his problems > > ... can they? > > > > well NOAGE certainly could be if the CPU VP's are getting aged now! > > > "Langelage, Frank" <frank@lafr.de> wrote in message > > news:bab9c0$qs4q2$1@ID-48907.news.dfncis.de... > > > Oxow wrote: > > > > Hi! > > > > > > > > We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and > > > > after > > > > that we have regonised some performance problems. > > > > > > > > > > > DBSERVERNAME sapshm # Name of default database server > > > > DBSERVERALIASES saptcp # List of alternate dbservernames > > > > NETTYPE ipcshm,1,60,NET # Override sqlhosts nettypeparameters > > > > NETTYPE soctcp,2,200,CPU # Override sqlhosts nettype> parameters > > > > > > You should change shm to CPU and soc to NET. > > > Network listeners on a CPU VP is a bad idea. > > > There was a brilliant explanation of Art Kagel a few weeks ago in this > > > newsgroup. > > > > > > > > > > RESIDENT 0 # Forced residency flag (Yes = 1, No = > > 0)> > > > > > Setting resident to 1 will disable pageout of buffer memory. > > > Setting this to -1 will disable pageout of vitual shm also. > > > > > > > > > > NOAGE 0 # Process aging> > > > > > Setting NOAGE to 1 will disable the OS to lower process priority for > > oninit. > > > > > > > > > > AFF_SPROC 0 # Affinity start processor > > > > AFF_NPROCS 0 # Affinity number of processors> > > > > > Don't know about AIX, but it might be useful to bind CPU-VPs to physical > > > CPUs with this two params (1, 8). > > > How many physical CPU's are inside this box ? > > > For 8 CPU-VPs you should have at least 9. > > > > > > > > >

Dear Frank, As you have seen it on our onconfig file we have 8 cpu assigned to informix and we have so 12 cpu available in the box. For your information, it seems for the moment that the problem is maybe due to the asynchronus kernel which was defined by the ERP product. Well, it is what IBM is thinking. So, does someone have already heard something about that ? Thanks all for what you are doing for us. We really appreciate that. Best Regards Oxow "Neil Truby" <neil.truby@ardenta.com> wrote in message news:<babaet$r95fh$1@ID-162943.news.dfncis.de>... > > Don't know about AIX, but it might be useful to bind CPU-VPs to physical > > CPUs with this two params (1, 8). > > How many physical CPU's are inside this box ? > > For 8 CPU-VPs you should have at least 9.

Hi, it is me again. For you information, a Aix specialist has studied the behaviour of the box and he told us that the machine was waiting after informix with (vmstat, top, etc..). He also noticed that several informix jobs were sleeping with the command onstat -g ses <session number>. Concerning the NETTYPE, this one was recommended by an IBM/Informix guy working with the ERP company, he explained us - when he advice us to modified these parmeters - that as we had several servers defined for x clients connected to the database server, it was preferable to comunicate with network listeners instead of shm one. Fot the NOAGE parameter, as I wrote it above the Aix machine is not overloaded by the work so I do not think so that informix take the entire resources of the box. In addition, even if informix would take a majority of the ressources, it will be what we would desire. Like this, it will be easier to fix the situation. So that is the reason why the NOAGE parameter is set to 0. Now, for the AFF_SPROC and the AFF_NPROCS I will thinking on it. Thanks Finally, for what it is the update statistics, even if I run it with the high level I do not understand why if I run it when nobody is working on the database it coould be the core of my problem ? Please explain me that. In addition, I thought that the update stats did not take a lot of ressources? Hoping that I am clear enought. Best Regards Frederic Hornain Informix DBA

On Tue, 20 May 2003 07:22:22 -0400, Oxow wrote: > Hi, it is me again. > > For you information, a Aix specialist has studied the behaviour of the > box and he told us that the machine was waiting after informix with > (vmstat, top, etc..). He also noticed that several informix jobs were > sleeping with the command onstat -g ses <session number>. > > Concerning the NETTYPE, this one was recommended by an IBM/Informix guy > working with the ERP company, he explained us - when he advice us to > modified these parmeters - that as we had several servers defined for x > clients connected to the database server, it was preferable to > comunicate with network listeners instead of shm one. The IBM/Informix guy is wrong, period. ESPECIALLY if most of your connections are through TCP/IP then tcp listeners MUST be run in NET VPs not in CPU VPs! As someone mentioned, I posted a rather long winded explanation of this about a month ago which has prompted a reexamination of the manual's recommendations within IBM. They are testing my assumptions and recommendations now. Meanwhile, dozens of sites are running better for having take the advice. TCP listeners in a few NET VPs and shm poll threads in EVERY CPU VP (so 8 in your case) for best user response time and minimized system impact. > Fot the NOAGE parameter, as I wrote it above the Aix machine is not > overloaded by the work so I do not think so that informix take the > entire resources of the box. In addition, even if informix would take a > majority of the ressources, it will be what we would desire. Like this, > it will be easier to fix the situation. So that is the reason why the > NOAGE parameter is set to 0. It would seem to me that while AIX is not the most aggressive OS at aging long-running processes (that would be HPUX) keeping the engine's priority high by setting NOAGE 1 could only help engine performance and if as you say the engine is not busy enough to affect overall system performance, what's the harm? > Now, for the AFF_SPROC and the AFF_NPROCS I will thinking on it. Thanks > > Finally, for what it is the update statistics, even if I run it with the > high level I do not understand why if I run it when nobody is working on > the database it coould be the core of my problem ? Please explain me > that. > In addition, I thought that the update stats did not take a lot of > ressources? I think it's not the resources of running the stats that folk are on you about. The recommended levels, as implemented by dostats and documented in the Performance Guide, are normally sufficient for the optimizer and are cheaper to produce that full HIGH runs against every column and much cheaper than a HIGH at the table or column level. But if you are doing the HIGH only at the table or database level know that while it provides data distributions that are AT LEAST as good as those produced by dostats, you may not be setting the low level index statistics properly which may cause the optimizer to select a sub-optimal query plan. Either switch to performing the recommended suite of commands; either with dostats, one of the other utilities that implement this protocol, or manually; or additionally run a LOW on the entire key of each index. > Hoping that I am clear enought. You are being clear, but you are also being negative. You asked for help then methodically listed reasons why you do not want to implement any of the suggestions. Give it a try for cripes sake. You must have thought some of us know what we're doing or you would not have posted! Art S. Kagel

I was wondering about the RESIDENT parameter in AIX. A couple of years ago our release notes said to set this to 0 as AIX does this automatically. This fact was documented in the release notes directory of our install. I looked in the release notes of our current version and this is no longer documented. Has something changed? "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.05.20.12.04.16.815201.15473@bloomberg.net... > On Tue, 20 May 2003 07:22:22 -0400, Oxow wrote: > > > Hi, it is me again. > > > > For you information, a Aix specialist has studied the behaviour of the > > box and he told us that the machine was waiting after informix with > > (vmstat, top, etc..). He also noticed that several informix jobs were > > sleeping with the command onstat -g ses <session number>. > > > > Concerning the NETTYPE, this one was recommended by an IBM/Informix guy > > working with the ERP company, he explained us - when he advice us to > > modified these parmeters - that as we had several servers defined for x > > clients connected to the database server, it was preferable to > > comunicate with network listeners instead of shm one. > > The IBM/Informix guy is wrong, period. ESPECIALLY if most of your > connections are through TCP/IP then tcp listeners MUST be run in NET VPs > not in CPU VPs! As someone mentioned, I posted a rather long winded > explanation of this about a month ago which has prompted a reexamination > of the manual's recommendations within IBM. They are testing my > assumptions and recommendations now. Meanwhile, dozens of sites are > running better for having take the advice. TCP listeners in a few NET > VPs and shm poll threads in EVERY CPU VP (so 8 in your case) for best > user response time and minimized system impact. > > > Fot the NOAGE parameter, as I wrote it above the Aix machine is not > > overloaded by the work so I do not think so that informix take the > > entire resources of the box. In addition, even if informix would take a > > majority of the ressources, it will be what we would desire. Like this, > > it will be easier to fix the situation. So that is the reason why the > > NOAGE parameter is set to 0.> > It would seem to me that while AIX is not the most aggressive OS at aging > long-running processes (that would be HPUX) keeping the engine's priority > high by setting NOAGE 1 could only help engine performance and if as you > say the engine is not busy enough to affect overall system performance, > what's the harm? > > > Now, for the AFF_SPROC and the AFF_NPROCS I will thinking on it. Thanks > > > > Finally, for what it is the update statistics, even if I run it with the > > high level I do not understand why if I run it when nobody is working on > > the database it coould be the core of my problem ? Please explain me > > that. > > In addition, I thought that the update stats did not take a lot of > > ressources? > > I think it's not the resources of running the stats that folk are on you > about. The recommended levels, as implemented by dostats and documented > in the Performance Guide, are normally sufficient for the optimizer and > are cheaper to produce that full HIGH runs against every column and much > cheaper than a HIGH at the table or column level. But if you are doing > the HIGH only at the table or database level know that while it provides > data distributions that are AT LEAST as good as those produced by > dostats, you may not be setting the low level index statistics properly > which may cause the optimizer to select a sub-optimal query plan. Either > switch to performing the recommended suite of commands; either with > dostats, one of the other utilities that implement this protocol, or > manually; or additionally run a LOW on the entire key of each index. > > > Hoping that I am clear enought. > > You are being clear, but you are also being negative. You asked for help > then methodically listed reasons why you do not want to implement any of > the suggestions. Give it a try for cripes sake. You must have thought > some of us know what we're doing or you would not have posted! > > Art S. Kagel

With a little help from Helen Wong I was able to find that they do still have this documented. It is just done in a different way. There is this one liner in the release notes. 17. Shared Memory Residency feature is NOT supported on this platform. "Beefman" <beefrunt@hotmail.com> wrote in message news:_ruya.250002$kYH.109112@news01.bloor.is.net.cable.rogers.com... > I was wondering about the RESIDENT parameter in AIX. A couple of years ago > our release notes said to set this to 0 as AIX does this automatically. This > fact was documented in the release notes directory of our install. I looked > in the release notes of our current version and this is no longer > documented. Has something changed? > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:pan.2003.05.20.12.04.16.815201.15473@bloomberg.net... > > On Tue, 20 May 2003 07:22:22 -0400, Oxow wrote: > > > > > Hi, it is me again. > > > > > > For you information, a Aix specialist has studied the behaviour of the > > > box and he told us that the machine was waiting after informix with > > > (vmstat, top, etc..). He also noticed that several informix jobs were > > > sleeping with the command onstat -g ses <session number>. > > > > > > Concerning the NETTYPE, this one was recommended by an IBM/Informix guy > > > working with the ERP company, he explained us - when he advice us to > > > modified these parmeters - that as we had several servers defined for x > > > clients connected to the database server, it was preferable to > > > comunicate with network listeners instead of shm one. > > > > The IBM/Informix guy is wrong, period. ESPECIALLY if most of your > > connections are through TCP/IP then tcp listeners MUST be run in NET VPs > > not in CPU VPs! As someone mentioned, I posted a rather long winded > > explanation of this about a month ago which has prompted a reexamination > > of the manual's recommendations within IBM. They are testing my > > assumptions and recommendations now. Meanwhile, dozens of sites are > > running better for having take the advice. TCP listeners in a few NET > > VPs and shm poll threads in EVERY CPU VP (so 8 in your case) for best > > user response time and minimized system impact. > > > > > Fot the NOAGE parameter, as I wrote it above the Aix machine is not > > > overloaded by the work so I do not think so that informix take the > > > entire resources of the box. In addition, even if informix would take a > > > majority of the ressources, it will be what we would desire. Like this, > > > it will be easier to fix the situation. So that is the reason why the > > > NOAGE parameter is set to 0.> > > > It would seem to me that while AIX is not the most aggressive OS at aging > > long-running processes (that would be HPUX) keeping the engine's priority > > high by setting NOAGE 1 could only help engine performance and if as you > > say the engine is not busy enough to affect overall system performance, > > what's the harm? > > > > > Now, for the AFF_SPROC and the AFF_NPROCS I will thinking on it. Thanks > > > > > > Finally, for what it is the update statistics, even if I run it with the > > > high level I do not understand why if I run it when nobody is working on > > > the database it coould be the core of my problem ? Please explain me > > > that. > > > In addition, I thought that the update stats did not take a lot of > > > ressources? > > > > I think it's not the resources of running the stats that folk are on you > > about. The recommended levels, as implemented by dostats and documented > > in the Performance Guide, are normally sufficient for the optimizer and > > are cheaper to produce that full HIGH runs against every column and much > > cheaper than a HIGH at the table or column level. But if you are doing > > the HIGH only at the table or database level know that while it provides > > data distributions that are AT LEAST as good as those produced by > > dostats, you may not be setting the low level index statistics properly > > which may cause the optimizer to select a sub-optimal query plan. Either > > switch to performing the recommended suite of commands; either with > > dostats, one of the other utilities that implement this protocol, or > > manually; or additionally run a LOW on the entire key of each index. > > > > > Hoping that I am clear enought. > > > > You are being clear, but you are also being negative. You asked for help > > then methodically listed reasons why you do not want to implement any of > > the suggestions. Give it a try for cripes sake. You must have thought > > some of us know what we're doing or you would not have posted! > > > > Art S. Kagel > >