RE: Questionable IDS 9.21 performance on HP-UX v11.11

Jason, I think You are having a pure disk bottleneck. For some reason, your server is having HUGE database read and write activity more then 50 disk operations/sec average (I think, including calm night hours) Most of your writes are LRU writes; that means, there is contention between database server reads and writes You even have FG writes!!! This means, that disk performance is REALLY poor. Most probably, your application is poorly designed Try to analyze using SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGREADS DESC; SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGWRITES DESC;what tables/indexes are consuming most of the server's I/O resources - and, then, try to identify application modules that are accessing these objects for read and write It is also interesting to see how much LOG activity your system is having. Are you using BUFFERED or UNBUFFERED logging? ------------------------------------------ Alexey Sonkin > > |rp3440# onstat -g iov > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > AIO I/O vps: > class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup > errors > kio 0 i 15.8 11892971 9676523 2216448 0 20290947 0.6 > 0 > kio 1 i 29.0 21847003 19754567 2092436 0 40141578 0.5 > 0 > kio 2 i 8.9 6727200 5239536 1487664 0 11472467 0.6 > 0 > kio 3 i 3.3 2449829 1475915 973914 0 3648252 0.7 > 0 > msc 0 i 0.4 315655 0 0 0 260726 1.2 > 0 > aio 0 i 0.0 6468 1 6438 0 453 14.3 > 0 > aio 1 i 0.0 7390 1 7364 0 537 13.8 > 0 > aio 2 i 0.0 5166 14 5142 0 523 9.9 > 0 > pio 0 i 0.0 0 0 0 0 1 0.0 > 0 > lio 0 i 0.0 0 0 0 0 1 0.0 > 0 > > |rp3440# onstat -D > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > Dbspaces > address number flags fchunk nchunks flags owner name > ab5477e0 1 0x1 1 1 N informix > rootdbs > abdf6e28 2 0x1 2 18 N informix > superdbs > abdf7018 3 0x2001 11 3 N T informix > tempdbs > abdf7160 5 0x1 21 2 N informix > llogdbs > abdf72a8 6 0x1 22 1 N informix > plogdbs > 5 active, 2047 maximum > > Chunks > address chk/dbs offset page Rd page Wr pathname > ab547928 1 1 0 900505 93033 /DBSCHUNKS/smdbroot > ab547b90 2 2 0 1904828 948878 /DBSCHUNKS/smdbcnk1 > ab547cf8 3 2 0 2002130 288585 /DBSCHUNKS/smdbcnk2 > ab547e60 4 2 0 593918 574384 /DBSCHUNKS/smdbcnk3 > abdf5018 5 2 0 436718 18924 /DBSCHUNKS/smdbcnk4 > abdf5180 6 2 0 2058921 137814 /DBSCHUNKS/smdbcnk5 > abdf52e8 7 2 0 1917362 250573 /DBSCHUNKS/smdbcnk6 > abdf5450 8 2 0 857691 226353 /DBSCHUNKS/smdbcnk7 > abdf55b8 9 2 0 579518 226330 /DBSCHUNKS/smdbcnk8 > abdf5720 10 2 0 151589 128230 /DBSCHUNKS/smdbcnk9 > abdf5888 11 3 0 817768 1588643 /DBSCHUNKS/smdbtmp1 > abdf59f0 12 2 0 1875342 124678 /DBSCHUNKS/smdbcnk10 > abdf5b58 13 2 0 805369 9619 /DBSCHUNKS/smdbcnk11 > abdf5cc0 14 2 0 1338308 152518 /DBSCHUNKS/smdbcnk12 > abdf5e28 15 2 0 826294 42800 /DBSCHUNKS/smdbcnk13 > abdf6018 16 3 0 1 1 /DBSCHUNKS/smdbtmp2 > abdf6180 17 3 0 1 1 /DBSCHUNKS/smdbtmp3 > abdf62e8 18 2 0 776963 179341 /DBSCHUNKS/smdbcnk14 > abdf6450 19 2 0 1935275 174721 /DBSCHUNKS/smdbcnk15 > abdf65b8 20 2 0 1333378 613908 /DBSCHUNKS/smdbcnk16 > abdf6720 21 5 0 1031342 1016023 /DBSCHUNKS/smdbllog > abdf6888 22 6 0 55 1407385 /DBSCHUNKS/smdbplog > abdf69f0 23 2 0 620538 686632 /DBSCHUNKS/smdbcnk17 > abdf6b58 24 2 0 203975 445984 /DBSCHUNKS/smdbcnk18 > abdf6cc0 25 5 0 914521 875145 /DBSCHUNKS/smdbllog2 > 25 active, 2047 maximum > > |rp3440# onstat -F > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:18 -- 2097152 Kbytes > > > Fg Writes LRU Writes Chunk Writes > 25 3146257 2975943 > > > |rp3440# onstat -g glo > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > days 17:02:19 -- 2097152 Kbytes > > MT global info: > sessions threads vps lngspins > 142 194 12 1 > > sched calls thread switches yield 0 yield n yield > forever > total: 1609299772 518881646 1174760484 3461476 > 110493152 > per sec: 15 10 0 1 1 > > Virtual processor summary: > class vps usercpu syscpu total > cpu 4 104830.79 17448.02 122278.81 > aio 3 8.46 21.78 30.24 > shm 1 607.77 154.40 762.17 > lio 1 2.72 6.73 9.45 > pio 1 2.79 7.10 9.89 > adm 1 15.10 44.46 59.56 > msc 1 67.57 37.20 104.77 > total 12 105535.20 17719.69 123254.89 > > Individual virtual processors: > vp pid class usercpu syscpu total > 1 3168 cpu 60665.43 9730.08 70395.51 > 2 3170 adm 15.10 44.46 59.56 > 3 3171 cpu 37111.27 7361.13 44472.40 > 4 3172 cpu 6056.48 267.17 6323.65 > 5 3173 cpu 997.61 89.64 1087.25 > 6 3174 lio 2.72 6.73 9.45 > 7 3175 pio 2.79 7.10 9.89 > 8 3176 aio 2.82 7.02 9.84 > 9 3177 msc 67.57 37.20 104.77 > 10 3187 aio 2.83 7.54 10.37 > 11 3206 aio 2.81 7.22 10.03 > 12 3273 shm 607.77 154.40 762.17 > tot 105535.20 17719.69 123254.89 > > |rp3440# onstat -P | tail > 3150242 3316 7 3309 0 0 32 > 3150243 9 5 4 0 0 0 > > Totals: 800000 203335 588260 8405 0 2227 > > Percentages: > Data 73.53 > Btree 25.42 > Other 1.05 > > sending to informix-list

Alexey Sonkin <alexeis@grandvirtual.com> wrote in message news:<cgo86n$vdn$1@news.xmission.com>... Alexey, Thanks for your observations. Reading the Informix Handbook, it says: "Foreground Writes. These occur when IDS needs a buffer and must interrupt processing to flush buffers to disk to free a buffer. These are the least desirable types of writes. If a foreground writes occurs, you should seriously consider tuning your instance. The goal is to have zero FG writes." This leads me to a couple of questions: - Why am I getting FG writes? With 1.6Gb BUFFERS + a SAN disk array I find it hard to understand why I'm getting FG writes and poor disk performance as you say. Do I have the 'right' number of LRU queues for 800,000 buffers? - How do I get rid of the FG writes? Is it possible just by tuning, or is it more of an application-level change? As for which db objects are being hit hardest, I have tried the queries you suggest and it does tend to be a few large tables that are constantly hit by multiple users and app modules. How can I increase parallelisation of access to these few objects? Fragmentation? Re: Logical log activity, how do you measure it? We use BUFFERED logging. As you can probably tell I'm a reluctant DBA who's really a sys admin, and so I have limited experience in this area of database design. However, you've come to the same conclusion as I have ... the application is poorly designed! Thanks, Jason > Jason, > > I think You are having a pure disk bottleneck. > > For some reason, your server is having HUGE database > read and write activity more then 50 disk operations/sec > average (I think, including calm night hours) > > Most of your writes are LRU writes; that means, there is > contention between database server reads and writes > > You even have FG writes!!! > This means, that disk performance is REALLY poor. > > Most probably, your application is poorly designed > > Try to analyze using > SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGREADS DESC; > SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGWRITES DESC;> what tables/indexes are consuming most of the server's I/O > resources - and, then, try to identify application modules > that are accessing these objects for read and write > > It is also interesting to see how much LOG activity > your system is having. Are you using BUFFERED or UNBUFFERED > logging? > > > ------------------------------------------ > Alexey Sonkin > > > > > > |rp3440# onstat -g iov > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > days 17:02:18 -- 2097152 Kbytes > > > > AIO I/O vps: > > class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup > > errors > > kio 0 i 15.8 11892971 9676523 2216448 0 20290947 0.6 > > 0 > > kio 1 i 29.0 21847003 19754567 2092436 0 40141578 0.5 > > 0 > > kio 2 i 8.9 6727200 5239536 1487664 0 11472467 0.6 > > 0 > > kio 3 i 3.3 2449829 1475915 973914 0 3648252 0.7 > > 0 > > msc 0 i 0.4 315655 0 0 0 260726 1.2 > > 0 > > aio 0 i 0.0 6468 1 6438 0 453 14.3 > > 0 > > aio 1 i 0.0 7390 1 7364 0 537 13.8 > > 0 > > aio 2 i 0.0 5166 14 5142 0 523 9.9 > > 0 > > pio 0 i 0.0 0 0 0 0 1 0.0 > > 0 > > lio 0 i 0.0 0 0 0 0 1 0.0 > > 0 > > > > |rp3440# onstat -D > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > days 17:02:18 -- 2097152 Kbytes > > > > Dbspaces > > address number flags fchunk nchunks flags owner name > > ab5477e0 1 0x1 1 1 N informix > > rootdbs > > abdf6e28 2 0x1 2 18 N informix > > superdbs > > abdf7018 3 0x2001 11 3 N T informix > > tempdbs > > abdf7160 5 0x1 21 2 N informix > > llogdbs > > abdf72a8 6 0x1 22 1 N informix > > plogdbs > > 5 active, 2047 maximum > > > > Chunks > > address chk/dbs offset page Rd page Wr pathname > > ab547928 1 1 0 900505 93033 /DBSCHUNKS/smdbroot > > ab547b90 2 2 0 1904828 948878 /DBSCHUNKS/smdbcnk1 > > ab547cf8 3 2 0 2002130 288585 /DBSCHUNKS/smdbcnk2 > > ab547e60 4 2 0 593918 574384 /DBSCHUNKS/smdbcnk3 > > abdf5018 5 2 0 436718 18924 /DBSCHUNKS/smdbcnk4 > > abdf5180 6 2 0 2058921 137814 /DBSCHUNKS/smdbcnk5 > > abdf52e8 7 2 0 1917362 250573 /DBSCHUNKS/smdbcnk6 > > abdf5450 8 2 0 857691 226353 /DBSCHUNKS/smdbcnk7 > > abdf55b8 9 2 0 579518 226330 /DBSCHUNKS/smdbcnk8 > > abdf5720 10 2 0 151589 128230 /DBSCHUNKS/smdbcnk9 > > abdf5888 11 3 0 817768 1588643 /DBSCHUNKS/smdbtmp1 > > abdf59f0 12 2 0 1875342 124678 /DBSCHUNKS/smdbcnk10 > > abdf5b58 13 2 0 805369 9619 /DBSCHUNKS/smdbcnk11 > > abdf5cc0 14 2 0 1338308 152518 /DBSCHUNKS/smdbcnk12 > > abdf5e28 15 2 0 826294 42800 /DBSCHUNKS/smdbcnk13 > > abdf6018 16 3 0 1 1 /DBSCHUNKS/smdbtmp2 > > abdf6180 17 3 0 1 1 /DBSCHUNKS/smdbtmp3 > > abdf62e8 18 2 0 776963 179341 /DBSCHUNKS/smdbcnk14 > > abdf6450 19 2 0 1935275 174721 /DBSCHUNKS/smdbcnk15 > > abdf65b8 20 2 0 1333378 613908 /DBSCHUNKS/smdbcnk16 > > abdf6720 21 5 0 1031342 1016023 /DBSCHUNKS/smdbllog > > abdf6888 22 6 0 55 1407385 /DBSCHUNKS/smdbplog > > abdf69f0 23 2 0 620538 686632 /DBSCHUNKS/smdbcnk17 > > abdf6b58 24 2 0 203975 445984 /DBSCHUNKS/smdbcnk18 > > abdf6cc0 25 5 0 914521 875145 /DBSCHUNKS/smdbllog2 > > 25 active, 2047 maximum > > > > |rp3440# onstat -F > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > days 17:02:18 -- 2097152 Kbytes > > > > > > Fg Writes LRU Writes Chunk Writes > > 25 3146257 2975943 > > > > > > |rp3440# onstat -g glo > > > > Informix Dynamic Server 2000 Version 9.21.HC2 -- On-Line -- Up 8 > > days 17:02:19 -- 2097152 Kbytes > > > > MT global info: > > sessions threads vps lngspins > > 142 194 12 1 > > > > sched calls thread switches yield 0 yield n yield > > forever > > total: 1609299772 518881646 1174760484 3461476 > > 110493152 > > per sec: 15 10 0 1 1 > > > > Virtual processor summary: > > class vps usercpu syscpu total > > cpu 4 104830.79 17448.02 122278.81 > > aio 3 8.46 21.78 30.24 > > shm 1 607.77 154.40 762.17 > > lio 1