Tuesday, August 3, 2010

Configuring Informix Virtual Processors



Virtual Processor (VP) is the technical term for an Informix process. Informix forks off a handful of these VPs at engine startup to take advantage of multiple CPUs or cores and increase parallelism. Each VP is multithreaded allowing Informix to efficiently use the CPU time each process is awarded by the OS, letting ready threads do work while other threads are busy waiting on something else to finish.

There are multiple types of VPs, each performing a specific job.  Some VPs are configurable (i.e. the number of VPs and what CPU they should run on, etc.) through the VPCLASS ONCONFIG parameter and some are not.  The 2 VPs you need to be concerned about right now are:
  • CPU - Runs all session threads and some system threads.  Runs KAIO (Kernel Asynchronous I/O) if enabled. This process will consume most of the CPU cycles given to Informix by the OS and is configurable.
  • AIO - Performs all I/O except for physical or logical logging if KAIO is disabled. Performs I/O to cooked chunks if KAIO is enabled and is configurable.
Initial CPUVP Configuration

As the name implies, CPUVPs will take up the bulk of the CPU time allotted to Informix by the operating system.  If you are running on a server that is dedicated to running Informix and nothing else (optimal, IMO) then you don't have to care too much about Informix hogging all of the CPU resources and can configure CPUVPs accordingly, leaving only some CPU cycles for the OS itself.  If you are running additional applications (web server, middleware, etc.) on the same server as Informix you have to be careful to not over configure CPUVPs as this will impact the performance of the other applications.

Follow the following guidelines for ONCONFIG parameters based on the total number of CPU cores available to initially configure CPUVPs on a server dedicted to running the Informix engine.  Process aging by the OS is disabled in all configurations to prevent the OS from reducing the priority of the long running Informix processes.

1 Core (example: Single CPU with a single core)

MULTIPROCESSOR 0
SINGLE_CPU_VP  1
VPCLASS        cpu,num=1,noage

2 Cores (example: Single CPU with 2 cores, 2 CPUs with a single core)

MULTIPROCESSOR 1
SINGLE_CPU_VP  1
VPCLASS        cpu,num=1,noage

4 Cores (example: Single CPU with 4 cores, 2 CPUs with 2 cores)

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=3,noage

8 Cores (example: 2 CPUs with 4 cores, 4 CPUs with 2 cores)

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=7,noage

8 Cores and Innovator-C that is limited to 4 CPUVPs

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=4,noage

See a pattern forming? Set MULTIPROCESSOR to 0 if you only have 1 core and set to 1 for everything else. Only configure 1 CPUVP if you have 2 or fewer cores and set SINGLE_CPU_VP to 1 to prevent additional CPUVPs from being added. Finally, configure CPUVPs = physical cores - 1 for everything else.

Monitoring and Tuning CPUVPs

A new column of output was added to onstat -g glo (the command to display a summary of all VPs) called Eff, for efficiency.  This column will tell us the percentage of time a VP's threads were actually running on a CPU vs the time they where scheduled to run by Informix.  A high percentage indicates that a physical CPU was able to run a thread when Informix asked it too and a low percentage indicates that a thread had to wait for CPU cycles to become available.  Warning: There is a bug with onstat -g glo that is fixed in 11.50.xC6, if you run onstat -z the efficiency is incorrect.

informix> onstat -g glo

IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:18:47 -- 1510960 Kbytes

MT global info:
sessions threads  vps      lngspins
42       182      21       2368

          sched calls     thread switches yield 0   yield n   yield forever
total:    483306514       338088588       149128716 10772763  41926442
per sec:  5085            4404            960       119       447

Virtual processor summary:
 class       vps       usercpu   syscpu    total
 cpu         3         30455.42  1617.50   32072.92
 aio         10        80.52     137.88    218.40
 lio         1         24.49     70.51     95.00
 pio         1         0.12      0.46      0.58
 adm         1         0.12      0.05      0.17
 soc         3         543.29    841.82    1385.11
 msc         1         94.16     62.45     156.61
 fifo        1         0.06      0.10      0.16
 total       21        31198.18  2730.77   33928.95

Individual virtual processors:
 vp    pid       class       usercpu   syscpu    total     Thread    Eff
 1     2204      cpu         12208.16  776.47    12984.63  13890.89  93%
 2     2230      adm         0.12      0.05      0.17      0.00       0%
 3     2231      cpu         11919.15  558.55    12477.70  12477.70 100%
 4     2232      cpu         6328.11   282.48    6610.59   6610.59  100%
 5     2233      lio         24.49     70.51     95.00     1800.72    5%
 6     2236      pio         0.12      0.46      0.58      33.14      1%
 7     2240      aio         57.64     94.05     151.69    4804.89    3%
 8     2244      msc         94.16     62.45     156.61    198.29    78%
 9     2248      fifo        0.06      0.10      0.16      0.00       0%
 10    2252      aio         9.56      21.31     30.87     2620.68    1%
 11    2253      aio         3.86      5.81      9.67      1469.70    0%
 12    2254      aio         1.96      3.33      5.29      1170.85    0%
 13    2256      aio         1.57      2.76      4.33      1092.83    0%
 14    2257      aio         1.41      2.55      3.96      1047.00    0%
 15    2258      aio         1.28      2.25      3.53      1019.12    0%
 16    2260      aio         1.23      2.07      3.30      980.02     0%
 17    2262      aio         1.08      2.05      3.13      934.60     0%
 18    2263      aio         0.93      1.70      2.63      896.20     0%
 19    2264      soc         179.83    272.86    452.69    NA         NA
 20    2265      soc         197.81    310.80    508.61    NA         NA
 21    2267      soc         165.65    258.16    423.81    NA         NA
                 tot         31198.18  2730.77   33928.95


Take a look at the cpu output of the Individual virtual processors section. If you have one or more CPUVPs with an efficiency at or near 100% then each time this CPUVP asked a CPU to do something the CPU was available.  This is an indication that there are enough free CPU cycles that can handle additional CPUVPs.

Just because you can add more CPUVPs doesn't mean it needs to be done.  If your CPUVPs are handling the current load then there is no need to add more. Monitor the ready queue via onstat -g rea during peak times to determine if more CPUVPs will help.  Threads that appear in this output are waiting for a CPUVP to become available so it can run.

informix> onstat -r 1 -g rea # -r 1 will will repeat the onstat -g rea command every second

IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:29 -- 1510960 Kbytes

Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name
 29246867e41290d0         e12f9f30         1    ready                   4cpu         sqlexec

IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:30 -- 1510960 Kbytes

Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name

IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:31 -- 1510960 Kbytes

Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name
In the first onstat -g rea output there is 1 sqlexec thread waiting for a free CPUVP.  The occasional thread in the ready queue isn't cause for much alarm, but if there were constantly threads in the queue I would consider increasing CPUVPs if I have free CPU cycles.

You must also use OS utilities (top and sar, etc.) to determine if there are available CPU cycles.  Very low % CPU Idle and high Load Averages are a good indication that additional CPU resources are not available.

If your CPUVP efficiency is low (oh I don't know, less than 70%?  Just a guess, this is a relatively new metric) then this can mean you have too many CPUVPs configured or your actual CPUs are overloaded and you might want to reduce CPUVPs or add additional CPUs to the system.

Initial AIOVP Configuration

If KAIO is disabled (another topic for another day) all I/O except for physical and logical logging will be performed by an AIO VP. I like to use 1 AIOVP per physical disk containing database chunks + 2 AIOVPs to handle writing to the message log, sqexplain.out, sort files in PSORT_DBTEMP, etc.

To configure 5 AIOVPs for an engine using 3 disks for chunks uncomment the VPCLASS aio ONCONFIG parameter (or modify it if is already uncommented).

VPCLASS aio,num=5,noage
Monitoring and Tuning AIOVPs

The good news is there really isn't too much penalty for having too many AIOVPs.  If an AIOVP isn't needed it will just sit there idle, no biggie.  But wait, there's more!  If the AUTO_AIOVPS ONCONFIG parameter is 1 the engine will dynamically add AIOVPs on the fly for you if they are needed.

For a more hands on approach to tuning AIOVPs, use onstat -g iov to monitor the number of I/Os per Wakeup for each AIOVP.  We are looking for the most active AIOVP to have an I/O per Wakeup of 1.0 which means each time this AIOVP was woken up to do some I/O there was only 1 I/O request needing to be processed.  If there are no requests backing up in the queue then our AIOVPs are keeping up with the demand.  The other AIOVPs will likely have more than 1.0 I/O per Wakeup, this is OK.

informix> onstat -g iov

IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 11:11:11 -- 1510960 Kbytes

AIO I/O vps:
class/vp/id s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup  errors tempops
 fifo  9  0 i   0.0        0        0        0        0        0   0.0       0        0
  msc  8  0 i   0.2   343147        0        0        0   343081   1.0       0   343170
  aio  7  0 i  62.0  4761188  3052788   102515        0  4680405   1.0       0    23338
  aio 10  1 i   7.9   604355   487107    38564        0   513321   1.2       0     1036
  aio 11  2 i   3.0   227818   166585    30099        0   180650   1.3       0      222
  aio 12  3 i   1.7   130962    98601    26027        0    88725   1.5       0        7
  aio 13  4 i   1.4   107353    81510    24218        0    69842   1.5       0        5
  aio 14  5 i   1.2    95549    72486    22105        0    61630   1.6       0        5
  aio 15  6 i   1.1    87313    65094    21449        0    57238   1.5       0        3
  aio 16  7 i   1.1    82456    61836    19979        0    54071   1.5       0        3
  aio 17  8 i   1.0    77195    57215    19367        0    49859   1.5       0        4
  aio 18  9 i   0.9    70738    51898    18305        0    45489   1.6       0        5
  pio  6  0 i   0.1     4326        0     4326        0     4326   1.0       0    37156
  lio  5  0 i  31.3  2405192        0  2405192        0  2396927   1.0       0 19652116
If adding AIOVPs does not help the most active AIOVP reach 1.0 I/O per Wakeup this is a good indication that your disks are saturated and increasing AIOVPs won't help improve performance.

8 comments:

  1. Hello, I used to use Informix at the default setting. I have moved upgraded to a new Informix v11.10 UC2, on a Xeon X3430 4-core cpu. Onconfig has these
    MULTIPROCESSOR 1
    NUMCPUVPS 3
    SINGLE_CPU_VP 0
    #VPCLASS jvp,num=1

    should I add
    VPCLASS cpu,num=3,noage

    I use Java program to insert and update records. Should I uncomment #VPCLASS jvp,num=1 ?

    Thank you!

    ReplyDelete
  2. Yes, you should start to use the VPCLASS ONCONFIG parameter and comment/remove the NUMCPUVPS ONCONFIG parameter. NUMCPUVPS will work fine, but the parameter is deprecated. Same goes for AIOVPS.

    The jvp VP will be used by User Defined Routines written in Java, it won't have any affect on the Java client you have that inserts/updates records so there probably isn't a need for you to uncomment this ONCONFIG parameter.

    ReplyDelete
    Replies
    1. When KAIO is used, how should I configure the CPUVP's then since KAIO-threads are executed by the CPUVP's? Should the same rule apply?

      Delete
    2. Same rules for CPUVP configuration apply if you are using AIOVPs or KAIO for I/O

      Delete
  3. What if I am using a cool-threaded hardware. My server has 8 cores and then 8 threads per core, gives me 64 VCPUs. In that case, should I use the core based informix configurations or should I use the thread based informix configurations (if available)?

    ReplyDelete
    Replies
    1. I believe in this instance you should treat each thread (VCPU) as a core. So you would configure the engine as if the hardware had 64 cores.

      Delete
  4. Ok...But these threads are not actual cores. If I have 8Threads/Core it means Core can execute 8 processes/software threads in parallel (correct me if I am wrong but this is what internet says). I want to know which way will be more efficient? Allocating the CPU resources to informix on the basis of physical cores or just on allocating the basis of a process management technique?

    ReplyDelete
    Replies
    1. The initial configuration guidelines are just a starting point that allow you to take a guess at how the CPUVPs should be configured. The only way to find what configuration works optimally for your system would be to monitor the CPU efficiency via the onstat -g glo command over time. In this instance it might be a good idea to start with 7 CPUVPs and monitor CPU efficiency. After finding this baseline you can dynamically add an additional CPUVP and see if this has a positive or negative impact on CPU efficiency and overall performance of the system. Rinse and repeat this process until you find the optimal number of configured CPUVPs for your system.

      It may very well be the case that you see no performance improvement between 7 CPUVPs and 64 CPUVPs if your workload isn't demanding. If you only have enough work to keep 3 CPUVPs busy, then going from 7 to 64 isn't going to make things any more efficient. You just need to pick a reasonable starting configuration and tune from there.

      Delete