Virtual Processors and CPU-Load

Hi all, we are a little bit confused concerning the CPU-Load of our Informix-Database-Server. (9.4 UC3, RH9 2.4.20) We use a 2-Xeon (3.0 GHZ)-Dell-Server and both CPU's are mostly at 99.9%! All tables and indexes (DBSpaces, Extents, ...) are optimized to the best of our knowledge. The Query-Tree of the most used SQL-Queries make sense and seems to be ok. We have around 200 concurrent User-connections. The read-caching is at > 98% The most load refers to the VP-Class cpu. The Informix-Performance-Guide recommends #CPU's -1 for VPN. We experimented setting the number of VP-CPU'S without any success. We can't find the bottleneck. Does anyone has an idea? Here is the output of onstat -g glo and onstat -p $ onstat -g glo IBM Informix Dynamic Server Version 9.40.UC3 -- On-Line -- Up 13:20:16 -- 1633048 Kbytes MT global info: sessions threads vps lngspins 165 201 15 155 sched calls thread switches yield 0 yield n yield forever total: 475242278 126695115 350020159 235545 57287028 per sec: 0 0 0 0 0 Virtual processor summary: class vps usercpu syscpu total cpu 2 8537.87 176.72 8714.59 aio 4 282.68 1645.61 1928.29 lio 1 0.21 2.10 2.31 pio 1 0.07 0.53 0.60 adm 1 0.51 8.22 8.73 soc 5 24.45 156.17 180.62 msc 1 0.58 57.45 58.03 total 15 8846.37 2046.80 10893.17 Individual virtual processors: vp pid class usercpu syscpu total 1 1819 cpu 4225.41 87.09 4312.50 2 1820 adm 0.51 8.22 8.73 3 1821 cpu 4312.46 89.63 4402.09 4 1822 lio 0.21 2.10 2.31 5 1823 pio 0.07 0.53 0.60 6 1824 aio 211.03 1121.48 1332.51 7 1825 msc 0.58 57.45 58.03 8 1828 aio 51.99 359.31 411.30 9 1829 aio 14.75 124.10 138.85 10 1830 aio 4.91 40.72 45.63 11 1831 soc 5.12 32.54 37.66 12 1832 soc 4.85 35.50 40.35 13 1833 soc 5.30 35.15 40.45 14 1834 soc 4.68 27.75 32.43 15 1835 soc 4.50 25.23 29.73 tot 8846.37 2046.80 10893.17 onstat -p IBM Informix Dynamic Server Version 9.40.UC3 -- On-Line -- Up 13:53:33 -- 1633048 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 38320518 42660887 2500565924 98.47 216063 1077852 1843027 88.28 isamtot open start read write rewrite delete commit rollbk 1483190124 4135120 52920296 1267196089 495295 54257 38656 13731 4 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 9944.30 2310.04 161 494 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 482918 6 43719539 0 0 82 35753 600231 ixda-RA idx-RA da-RA RA-pgsused lchwaits 541136 1269654 3133087 4939574 2197358 Thanks in advance! Bye Markus

On Thu, 18 Mar 2004 05:13:05 -0500, Markus Bschorer wrote: DO NOT POST HTML/MIME! I'll answer but often folk here will ignore such posting altogether! Sorry I cannot include details of your post, it's gibberish in a non-HTML mailer! One major problem I see is that you have 5 NET VPS configured for a 2CPU box running only 2 CPU VPs. Cut that down to 1 or 2, there's no way 2 CPU VPs could keep up with the load that 5 NET VPs could handle if they were truly needed anyway. Another problem onstat -p shows 4million pages of readahead were read in and only 2 million were actually accessed. I'd cut the RA_PAGES in half and make RA_THRESHOLD very much closer to RA_PAGES. I like 16 & 4 for a fast disk farm and 16 & 8 for singleton disks. Art S. Kagel

Your onstat -p output suggest that application tuning is required... 1. read v/s write/rewrite. Reads are 1.2 billion while writes & rewrites total under 0.5 million. Unless this is a DSS system, that's way out of whack. 2. seqscans. Again, in the perspective of the writes & rewrites which, arguably, represent the actual "work" done, the values of seqscans is very high. 3. pagwrites. High, in comparison to writes/rewrites. 4. Cache ratios. Low Your focus has to be on determining what part of your application is generating all those reads. Home in on your active sessions (onstat -g act) with onstat -g sql <sid> or equivalent. You will, I'm sure, very quickly find a pattern of abuse :) Also examine sysmaster:sysptprof to figure out which tables are the highest hit. Chances are that there will be a few of them that "consume" most of the reads. Focus on tuning access to them. All the best Rudy