IDS on Solaris(spark server)Multiprocessor Setup

Hello All, I want to set up IDS 10.0(FC4)instance on Sun Spark server, which has one 24 core processor. So i am not sure how should i give the multiprocessor settings in the onconfig files parameters related to that instance. Please find below the parameters about which i need to know the best settings: NETTYPE ipcshm,?,?,CPU # Configure poll thread(s) for nettype NETTYPE tlitcp,?,?,NET # Configure poll thread(s) for nettype RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS ? # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS ? # Affinity number of processors Please guide me with the best setting to have the optimal performance. Thanks in advance. Regards, Omer Saeed Khan

On 10/04/07, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello All, > > I want to set up IDS 10.0(FC4)instance on Sun Spark server, which has one 24 > core processor. So i am not sure how should i give the multiprocessor settings > in the onconfig files parameters related to that instance. Please find below > the parameters about which i need to know the best settings: > > NETTYPE ipcshm,?,?,CPU # Configure poll thread(s) for nettype > NETTYPE tlitcp,?,?,NET # Configure poll thread(s) for nettype > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor > NUMCPUVPS ? # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one > > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS ? # Affinity number of processors> > Please guide me with the best setting to have the optimal performance. > > Thanks in advance. > > Regards, > Omer Saeed Khan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Omer What is the purpose of this server? Will it just (mainly) be running the database, will there be an application running of this box as well or will the application be on a separate server? How many users/sessions will access the instance? Will you need development/test instances on the same server? More details required before even a half intelligent answer can be given. Keith

By the way, what is result of the comand 'sar -P ALL 1 1'

Hello Keith, First of all, Thanks for your response! Yes, this is the dedicated server for the database and i need to run the production instance on this server, and our application would be using another server, this is just the database connection. About the users/sessions that would access this instance, i would say that somewhere between 200-400 concurrant sessions with database instance is expected. This number could increase as well, so if wanted to have the best tuned parameters to get optimal performance from Informix. I will be looking forward for your kind support and answer. Regards, Omer Saeed Khan

Hello Rustem, First of all, thanks for your response! I would like to update you that the command that you asked me to run, did not excute sucessfully: # sar -P ALL 1 1 sar: illegal option -- P usage: sar [-ubdycwaqvmpgrkA][-o file] t [n] sar [-ubdycwaqvmpgrkA] [-s hh:mm][-e hh:mm][-i ss][-f file] However i have changed the command a bit and below is the result. Please verify and suggest me the best settings. Let me know in case if you need any further info. # sar -A 1 1 SunOS DB1 5.10 Generic_118833-22 sun4v 04/10/2007 03:36:58 %usr %sys %wio %idle device %busy avque r+w/s blks/s avwait avserv runq-sz %runocc swpq-sz %swpocc bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s swpin/s bswin/s swpot/s bswot/s pswch/s scall/s sread/s swrit/s fork/s exec/s rchar/s wchar/s iget/s namei/s dirbk/s rawch/s canch/s outch/s rcvin/s xmtin/s mdmin/s proc-sz ov inod-sz ov file-sz ov lock-sz msg/s sema/s atch/s pgin/s ppgin/s pflt/s vflt/s slock/s pgout/s ppgout/s pgfree/s pgscan/s %ufs_ipf freemem freeswap sml_mem alloc fail lg_mem alloc fail ovsz_alloc fail 03:36:59 9 3 0 88 md0 69 0.9 147 15520 0.0 6.4 sd0 38 0.9 269 7822 0.0 3.2 sd0,a 0 0.0 0 0 0.0 0.0 sd0,b 0 0.0 0 0 0.0 0.0 sd0,f 38 0.9 269 7822 0.0 3.2 sd0,g 0 0.0 0 0 0.0 0.0 sd2 0 0.0 0 0 0.0 0.0 sd3 41 1.0 289 7698 0.0 3.5 sd3,a 0 0.0 0 0 0.0 0.0 sd3,b 0 0.0 0 0 0.0 0.0 sd3,c 0 0.0 0 0 0.0 0.0 sd3,e 41 1.0 289 7698 0.0 3.5 sd3,g 0 0.0 0 0 0.0 0.0 sd3,h 0 0.0 0 0 0.0 0.0 ohci0,bu 0 0.0 0 0 0.0 0.0 ohci0,ct 0 0.0 0 0 0.0 0.0 ohci0,in 0 0.0 0 0 0.0 0.0 ohci0,is 0 0.0 0 0 0.0 0.0 ohci0,to 0 0.0 0 0 0.0 0.0 ohci1,bu 0 0.0 0 0 0.0 0.0 ohci1,ct 0 0.0 0 0 0.0 0.0 ohci1,in 0 0.0 0 0 0.0 0.0 ohci1,is 0 0.0 0 0 0.0 0.0 ohci1,to 0 0.0 0 0 0.0 0.0 0.0 0 0.0 0 0 416 100 0 0 100 0 0 0.00 0.0 0.00 0.0 21335 24334 4050 216 0.00 0.00 8229754 330838 0 6 0 0 0 782 0 0 0 709/30000 0 14037/129797 0 7617/7617 0 0/0 0.00 16986.41 955.34 74.76 883.50 2.91 52.43 0.00 0.00 0.00 0.00 0.00 0.00 410556 6637810 150475264 97082210 0 625262592 523692784 0 59367424 0 Regards, Omer Saeed Khan

On 10/04/07, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello All, > > I want to set up IDS 10.0(FC4)instance on Sun Spark server, which has one 24 > core processor. So i am not sure how should i give the multiprocessor settings > in the onconfig files parameters related to that instance. Please find below > the parameters about which i need to know the best settings: > > NETTYPE ipcshm,?,?,CPU # Configure poll thread(s) for nettype > NETTYPE tlitcp,?,?,NET # Configure poll thread(s) for nettype > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor > NUMCPUVPS ? # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one > > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS ? # Affinity number of processors> > Please guide me with the best setting to have the optimal performance. > > Thanks in advance. > > Regards, > Omer Saeed Khan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Omar Try these settings to start with:- NETTYPE ipcshm,1,10,CPU # Configure poll thread(s) for nettype NETTYPE tlitcp,6,600,NET # Configure poll thread(s) for nettype RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 10 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one NOAGE 1 # Process aging AFF_SPROC 5 # Affinity start processor AFF_NPROCS 10 # Affinity number of processors This will give you a few shared memory connections for on-server use and 600 connections for application use. The server shared memory (buffers) will be held resident in physical memory and there will be 10 virtual processors affiniated to physical processors (numbers 5 thru 14). Once you are running live you can tweak these (would need a database bounce, I assume you will manage some admin down time !!) if you are finding problems, however with the small number of users and such a chunky machine I don't think you will have any !! Keith

Hi, Omer. Try to use 'mpstat -P ALL'.

Hello Keith, What about using all the processors power? would it give me more prowerful and quick response? because this is my dedicated database machine and i want to expolit it to the full potential. Also let me know, what about setting the RESIDENT -1?? some experts told me this could increase response time, i would like to let u know that i have 16GB of RAM availble as well. I also wanted to assign atleast 50-100 connections for the shared memory. Also i would like to get the optimal speed while using dbaccess by directly login to the server. Looking forward for response! Regards, Omer Saeed Khan

Rustem, Still no luck :( # mpstat -P ALL Usage: mpstat [-aq] [-p | -P processor_set] [interval [count]] this is also giving the same response. Let me know if you need anything more. Regards, Omer Saeed Khan

Try mpstat without options Bruce Simms Data Base Services TALX Corporation 2330 Ball Drive St. Louis, MO 63146 Phone (314) 214-7703 FAX (314) 983-3238 bsimms@talx.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of OMER KHAN Sent: Tuesday, April 10, 2007 6:32 AM To: ids@iiug.org Subject: Re: IDS on Solaris(spark server)Multiprocessor Set [8797] Rustem, Still no luck :( # mpstat -P ALL Usage: mpstat [-aq] [-p | -P processor_set] [interval [count]] this is also giving the same response. Let me know if you need anything more. Regards, Omer Saeed Khan ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

On 10/04/07, OMER KHAN <oskhan@i2cinc.com> wrote: > Hello Keith, > > What about using all the processors power? would it give me more prowerful and > quick response? because this is my dedicated database machine and i want to > expolit it to the full potential. > > Also let me know, what about setting the RESIDENT -1?? some experts told me > this could increase response time, i would like to let u know that i have 16GB > of RAM availble as well. > > I also wanted to assign atleast 50-100 connections for the shared memory. Also > i would like to get the optimal speed while using dbaccess by directly login > to the server. > > Looking forward for response! > > Regards, > Omer Saeed Khan > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Omer Personnaly I think 10 processors will be more than sufficient and that you will hit problems with disk access first. With this number of users I assume this is an OLTP application rather than a data-warehouse. Do you have a large number of data-blades or database procedures or call-outs to scripts or procedures? In any case it would be simple to add further CPUs after some testing and monitoring. Performance tuning is an iterative and on-going prcess rather than an exact science. RESIDENT -1 locks the Virtual Segment(s) as well as the Resident segments, however if the engine is basically the only application on the server there will be nothing else to knock it out of memory. There might be a marginal improvement but possibly not measurable. 16 Gb of RAM is good, plenty of BUFFERS I hope (at least 600000) with 127 LRUs and CLEANERS (if IDS9 or greater for IDS10). Shared memory connections arenot really needed for dbaccess, it will use tcp quite happily (even on the same box) there might be milli-seconds difference in connection time, but once connected there will not be any noticable difference in performance. I have run 600 sessions on an 8 processor (much slower) server entirely on TCP connections (no shm at all) with the application on the same box as the server with no issues.

Hello Rustem, This might be the result that you want: # mpstat CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 0 0 42 207 103 6 0 0 0 0 1 0 0 0 100 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 100 2 0 0 0 1 0 0 0 0 0 0 0 0 0 0 100 3 0 0 0 1 0 0 0 0 0 0 0 0 0 0 100 4 0 0 0 3 0 3 0 0 0 0 0 0 0 0 100 5 0 0 5 17 4 24 0 0 0 0 0 0 0 0 100 6 0 0 2 11 0 19 0 0 0 0 0 0 0 0 100 7 0 0 0 2 0 2 0 0 0 0 0 0 0 0 100 8 0 0 0 2 0 2 0 0 0 0 0 0 0 0 100 9 0 0 0 2 0 2 0 0 0 0 0 0 0 0 100 10 0 0 0 2 0 1 0 0 0 0 0 0 0 0 100 11 0 0 0 2 0 1 0 0 0 0 0 0 0 0 100 12 0 0 0 3 0 4 0 0 0 0 0 0 0 0 100 13 0 0 0 2 0 3 0 0 0 0 0 0 0 0 100 14 0 0 0 3 0 4 0 0 0 0 0 0 0 0 100 15 0 0 0 3 0 4 0 0 0 0 0 0 0 0 100 16 0 0 1 3 0 5 0 0 0 0 0 0 0 0 100 17 0 0 0 1 0 1 0 0 0 0 0 0 0 0 100 18 0 0 0 1 0 1 0 0 0 0 0 0 0 0 100 19 0 0 0 2 0 2 0 0 0 0 0 0 0 0 100 20 0 0 0 3 0 4 0 0 0 0 0 0 0 0 100 21 0 0 0 2 0 1 0 0 0 0 0 0 0 0 100 22 0 0 0 3 0 4 0 0 0 0 0 0 0 0 100 23 0 0 0 3 0 3 0 0 0 0 0 0 0 0 100 Regards, Omer

Omer, Yes, I expected this exact result - all 'virtual' CPUs

Keith, Yes, i will be running an OLTP database on this server, which has extensive number database procedures,queries and read/writes in many concurrant sessions. Also i would be running around 20 instances of almost same transaction load on this machine, so please suggest me that should i give 600000 BUFFERS and 127 LRUs and CLENERS for all 20 instances or not? Regards, Omer Saeed Khan

On 4/10/07, OMER KHAN <oskhan@i2cinc.com> wrote: > Yes, i will be running an OLTP database on this server, which has extensive > number database procedures,queries and read/writes in many concurrant > sessions. > > Also i would be running around 20 instances of almost same transaction load on > this machine, so please suggest me that should i give 600000 BUFFERS and 127 > LRUs and CLENERS for all 20 instances or not? OK - let's have the full story, shall we? You have a 24 CPU machine, and it will only be running IDS (nothing else of significance). There will be initially up to about 600 users connecting to the machine. At the moment, you are thinking there should be 20 instances of IDS. I would strongly counsel you to reconsider having that many instances of IDS. Why do you think it a good idea? The usual answer would be "I need 20 different databases (perhaps for 20 different subordinate organizations), but the application always connects to the same database name". The best solution to that is to fix the application so it can connect to the database of your choice. If you have 20 instances, they won't all be equally busy all the time, but each will have resources dedicated to it. You would be better off dedicating the resources to one (or at most two or three) instances, and then letting the databases share the system. You can then configure 20-23 of the CPUs to the main (sole) instance, leaving 1 free to serve the o/s - network daemons, etc. If you must have 20 instances, then each instance will be smaller, probably configured with one or two CPU VPs, probably configured with non-resident shared memory, and configured with less space available. You'd get less chance for PDQ - not a major issue in an OLTP system anyway. I don't remember you saying how much main memory you have available. If you have more than 1 GB, you can probably configure IDS to use 75% of it, maybe even more than that. That will largely govern how many buffers you should configure - and, of course, you have to worry about splitting that space into 20 if you must use 20 instances. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.