Re: Questions re NUMCPUVPS, PDQPRIORTY, AFF_NPROCS and AFF_SPROC

Hi 1) As for my experience if you have only DB on your server and you do not have large WAIT IO define all cpu to informix. If you have a lot of users and you are using NET VP check there cpu usage, maybe you should spare one or two cpu for them. 2) Probably the first SQL use indexes and did not use sequential scan. The second sql probably didn't. Check there explain. Uri Reeves, Dam.... wrote: >Hi, > >Not sure if anyone can shed some light on a couple of questions I have about >Dynamic Server and NUMCPUVPS, PDQPRIORTY, AFF_NPROCS and AFF_SPROC ? > >Firstly our setup: > >We are running a data replication setup (2 systems each running Dynamic >Server 9.21-FC3x-1 on HP-UX 11.0). > >The primary server has 6 CPU's and has the following configuration >parameters: > >SHMTOTAL 0 >SHMADD 96000 >MULTIPROCESSOR 1 >NUMCPUVPS 6 >AFF_SPROC 0 >AFF_NPROCS 0 >MAX_PDQPRIORITY 100 >DS_MAX_QUERIES 1280 >DS_TOTAL_MEMORY 163840 >DS_MAX_SCANS 1048576> >The secondary read-only server has 4 CPU's and has the following >configuration parameters: > >SHMTOTAL 0 >SHMADD 96000 >MULTIPROCESSOR 1 >NUMCPUVPS 4 >AFF_SPROC 0 >AFF_NPROCS 0 >MAX_PDQPRIORITY 100 >DS_MAX_QUERIES 1280 >DS_TOTAL_MEMORY 163840 >DS_MAX_SCANS 1048576> > >We are not using processor affinity. CPU VP's can be serviced/executed by >any of the 6 physical CPU's on the primary server. >We have 900+ users accessing the primary server and we don't appear to have >many issues - things seem to run quite well (although I am sure >someone out there could tune the system a little more !). > >We are not implementing Kernel AIO, and all chunks are RAW. > > >Question 1: Based on real-world experience, can anyone let me know how to >decide what the NUMCPUVPS should be set at ? > >We currently have NUMCPUVPS = # Physical CPU's......and I repeat there do >not seem to be any issues. > >I have read many articles on setting the NUMCPUVP parameter for various >versions of Dynamic Server from 7.21 to 9.21 and these articles have >mentioned: > >a) NUMCPUVPS should be set equal to the # of physical CPU's, or > >b) NUMCPUVPS should be set to 1 less than the # of physical CPU's, or > >c) NUMCPUVPS can be set to > than the # of physical CPU's (for older >versions of Dynamic Server on HP-UX 10.2 that had bugs with using the >NOAGE, AFF_NPROCS and AFF_SPROC parameters). > > > >Question 2: How do we determine when the engine determines a query uses PDQ >and when it doesn't ? > >a) When we execute the following SQL script and display stats for the >memory grant manager using "onstat -g mgm | more", we see that engine >does NOT appear to be using PDQ: > >set isolation to dirty read; >set pdqpriority 60;>select > sum(f.original_loan_amt) >from table_name_1 a, > table_name_2 b, > table_name_3 c, > table_name_4 f >where > f.credit_appl_id = a.credit_appl_id and > b.credit_appl_id = a.credit_appl_id and > c.operator_code = a.alt_ch_operator_cd and > b.person_id = 1 and > a.alt_ch_branch_num LIKE '032448%'; > > >Details from "onstat -g mgm | more" show: > >Informix Dynamic Server 2000 Version 9.21.FC3X1 -- On-Line (Prim) -- Up 15 >days 11:10:56 -- 796972 Kbytes > >Memory Grant Manager (MGM) >-------------------------- > >MAX_PDQPRIORITY: 100 >DS_MAX_QUERIES: 1280 >DS_MAX_SCANS: 1048576 >DS_TOTAL_MEMORY: 163840 KB > >Queries: Active Ready Maximum > 0 0 1280 > >Memory: Total Free Quantum >(KB) 163840 163840 128 > >Scans: Total Free Quantum > 1048576 1048576 1 > >Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) > Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 >(Queue Length) 0 0 0 0 0 > >Active Queries: None > >Ready Queries: None > >Free Resource Average # Minimum # >-------------- --------------- --------- >Memory 11946.7 +- 10067.9 0 >Scans 1048575.0 +- 0.0 1048575 > >Queries Average # Maximum # Total # >-------------- --------------- --------- ------- >Active 1.0 +- 0.0 1 6 >Ready 0.0 +- 0.0 0 0 > >Resource/Lock Cycle Prevention count: 0 > > >b) However, when we modify the above SQL script to: > >set isolation to dirty read; >set pdqpriority 60;>select > f.credit_appl_id >from > table_name_1 a, > table_name_2 b, > table_name_3 c, > table_name_4 f > >where > f.credit_appl_id = a.credit_appl_id > and b.credit_appl_id = a.credit_appl_id > and c.operator_code = a.alt_ch_operator_cd > and b.person_id = 1; > > >Then re-execute it and check using "onstat -g mgm | more" we see the engine >is using PDQ: > >Informix Dynamic Server 2000 Version 9.21.FC3X1 -- On-Line (Prim) -- Up 15 >days 11:18:40 -- 796972 Kbytes > >Memory Grant Manager (MGM) >-------------------------- > >MAX_PDQPRIORITY: 100 >DS_MAX_QUERIES: 1280 >DS_MAX_SCANS: 1048576 >DS_TOTAL_MEMORY: 163840 KB > >Queries: Active Ready Maximum > 1 0 1280 > >Memory: Total Free Quantum >(KB) 163840 163840 128 > >Scans: Total Free Quantum > 1048576 1048575 1 > >Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit) > Gate 1 Gate 2 Gate 3 Gate 4 Gate 5 >(Queue Length) 0 0 0 0 0 > >Active Queries: >--------------- >Session Query Priority >Thread Memory Scans Gate > 79493 c00000001c89cbd0 60 >c0000000175096e0 0/0 1/1 - > >Ready Queries: None > >Free Resource Average # Minimum # >-------------- --------------- --------- >Memory 13165.7 +- 9740.2 0 >Scans 1048575.0 +- 0.0 1048575 > >Queries Average # Maximum # Total # >-------------- --------------- --------- ------- >Active 1.0 +- 0.0 1 7 >Ready 0.0 +- 0.0 0 0 > >Resource/Lock Cycle Prevention count: 0 > > > >Any help would be great !!!!! > >Thanks, > >Damion Reeves >Informix Database Administrator >EDS Australia >Adelaide Solution Centre > > > > > >