This command gives scheduling statistics about the individual virtual processors in the Informix instance.
$ onstat -g sch Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:12:27 -- 18464 Kbytes VP Scheduler Statistics: vp pid class semops busy waits spins/wait 1 825 cpu 0 0 0 2 826 adm 0 0 0 3 827 lio 0 0 0 4 828 pio 0 0 0 5 829 aio 0 0 0 6 830 msc 0 0 0 7 831 aio 0 0 0 8 833 aio 0 0 0 9 834 aio 0 0 0 10 835 aio 0 0 0 11 836 aio 0 0 0 12 837 aio 0 0 0 13 838 aio 0 0 0 14 839 aio 0 0 0 15 840 aio 0 0 0 16 841 aio 0 0 0 17 842 aio 0 0 0 18 843 aio 0 0 0 19 844 aio 0 0 0 20 845 aio 0 0 0 21 846 aio 0 0 0 22 847 aio 0 0 0 23 848 aio 0 0 0
When a CPU VP does not have a thread to run, the process performs a 'busywait'. This is a loop of instructions designed to wait a short time. It does this rather than going into a sleep state for two reasons. First, a thread may become eligible to run very soon, sooner than any system call to wait could return, and second, there is no other more useful work to perform at the time. "Spins" are the number of loops in a busy wait.
The busy wait count is the number of times the VP was looking for a thread and found none. Spins is the number of iterations of the busy wait loop. If a thread becomes runnable before the busy wait loop count expires, then the VP exits the loop and runs the chosen thread.
If the busy wait loop is completed and there is still no work to do, the VP then waits using a semaphore operation. When a runnable thread becomes available, the VP be awakened to run it. In onstat -g sch, the semops column gives the number of times that the operation was completed and the thread was put to sleep awaiting awakening by a semaphore.
$ onstat -g con Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:12:47 -- 18464 Kbytes Conditions with waiters: cid addr name waiter waittime 679 c1be8a08 netnorm 70 6306 744 c1bf0d30 netnorm 76 7080
A condition is a mutex (latch). This command lists any conditions that have other threads waiting on those conditions. More detailed information can be found in the sysmaster table sysconlst.
This onstat command provides information about the CPU usage of virtual processors running in the instance. Typical systems will show the majority of the CPU usage in the CPU and AIO virtual processors.
The command gives various bits of information, including number of user sessions, number of threads, number of virtual processors, number of longspins, number of scheduler calls, thread switches, and yields. These last few pieces of information may be marginally interesting in viewing how the engine is handling thread switching, but there's not much you can do with the information. Thread switching and yielding is embedded deep into the engine and you have little or no control over it. It's interesting to watch, though.
The more interesting information is the summary and individual virtual processor information found at the end of the output.
$ onstat -g glo Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:57:03 -- 18464 Kbytes MT global info: sessions threads vps lngspins 1 29 23 0 sched calls thread switches yield 0 yield n yield forever total: 28403 21613 0 13655 1 per sec: 0 0 0 0 0
The above section deals with the activities of the threads and provides some interesting insights into how the IDS engine is accomplishing its multitasking features. When the engine is running virtual processors, each virtual processor can be assigned threads to run. Each thread is actually an area of shared memory that contains variables and stack space that is needed to run the thread. When one of the VPs reaches a point where it will be waiting for something, it politely gives up control of the processor by executing what is known as a "yield." There are three types of yields:
The thread relinquishes control and moves to the end of the ready queue. If no other thread is ready to execute, the yielding thread begins executing immediately.
The thread puts itself to sleep and will wake up in n seconds.
The thread puts itself to sleep and will only wake up when another thread specifically activates it
Virtual processor summary: class vps usercpu syscpu total cpu 1 1.05 1.37 2.42 aio 18 1.73 4.75 6.48 lio 1 0.11 0.29 0.40 pio 1 0.09 0.25 0.34 adm 1 0.44 1.00 1.44 msc 1 0.00 0.00 0.00 total 23 3.42 7.66 11.08 Individual virtual processors: vp pid class usercpu syscpu total 1 825 cpu 1.05 1.37 2.42 2 826 adm 0.44 1.00 1.44 3 827 lio 0.11 0.29 0.40 4 828 pio 0.09 0.25 0.34 5 829 aio 0.10 0.25 0.35 6 830 msc 0.00 0.00 0.00 7 831 aio 0.10 0.25 0.35 8 833 aio 0.10 0.27 0.37 9 834 aio 0.10 0.26 0.36 10 835 aio 0.09 0.26 0.35 11 836 aio 0.10 0.27 0.37 12 837 aio 0.11 0.27 0.38 13 838 aio 0.10 0.27 0.37 14 839 aio 0.09 0.28 0.37 15 840 aio 0.09 0.27 0.36 16 841 aio 0.11 0.27 0.38 17 842 aio 0.09 0.26 0.35 18 843 aio 0.10 0.26 0.36 19 844 aio 0.09 0.26 0.35 20 845 aio 0.09 0.25 0.34 21 846 aio 0.09 0.30 0.39 22 847 aio 0.09 0.24 0.33 23 848 aio 0.09 0.26 0.35 tot 3.42 7.66 11.08
This command allows the DBA to track VP usage. Run it during heavy load after an onstat -z (to clear the statistics in the internal tables), and time the amount of physical time elapsed during the run. Now look at the sum of the usercpu and syscpu columns for the first CPU-VP. If the accumulated time is near the elapsed time, the CPU-VP was running almost continuously and the CPU-VP count should be increased. CPU-VPs were the bottle-neck.
This same principle can be used to evaluate the overall performance of the engine over a known time. Simply take the totals time for usercpu and syscpu and compare that time with the elapsed time. This can give you a feeling for the amount of the overall processor that is being utilized by the database engine.