IO performance issues

I'm opening this thread because of some IO performance issues that I have now and need to be sure of what I must do. This is my db profile counts:Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 22906487 87848814 34363980245 99.93 332057743 398328761 1082072410 69.31 isamtot open start read write rewrite delete commit rollbk 48406998458 7423608018 9188986012 10875359326 1488886 347373130 682316 347179506 1 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 1487097310 353965214 343076548 4397605 4723657 2 18018239 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 457810.60 98673.63 3364 7580 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 541933 6627626 13923560081 0 0 29422 1170160 807177 ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits 70449 9643446 760355 1294 18020622 137838512 Onconfig: NETTYPE soctcp,1,200,NET LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NUMFDSERVERS 4 NS_CACHE host=900,service=900,user=900,group=900 MULTIPROCESSOR 1 VPCLASS cpu,num=12,noage VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 CLEANERS 8 DIRECT_IO 1 LOCKS 20000 DEF_TABLE_LOCKMODE page RESIDENT 0 SHMBASE 0x44000000L SHMVIRTSIZE 10485760 SHMADD 262144 EXTSHMADD 51200 SHMTOTAL 62914560 SHMVIRT_ALLOCSEG 0,3 SHMNOACCESS MAX_PDQPRIORITY 100 DS_MAX_QUERIES DS_TOTAL_MEMORY DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 256 DATASKIP BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50,lru_ max_dirty=60.5 BUFFERPOOL size=2K BUFFERPOOL size=8K onstat -PPercentages: Data 55.67 Btree 8.09 Other 36.24 onstat -F Fg Writes LRU Writes Chunk Writes 2 611239 14997845 address flusher state data # LRU Chunk Wakeups Idle Tim 4c3e48e8 0 I 0 603 10296 1021956 1010204.637 4c3e51a8 1 I 0 707 9448 1021391 1009724.104 4c3e5a68 2 I 0 627 7475 1019552 1010959.875 4c3e6328 3 I 0 873 4565 1017161 1011482.790 4c3e6be8 4 I 0 742 7209 1019094 1010744.211 4c3e74a8 5 I 0 727 6145 1018085 1010709.286 4c3e7d68 6 I 0 858 7217 1019100 1010542.952 4c3e8628 7 I 0 773 6640 1018665 1010826.101 states: Exit Idle Chunk Lru The machine on which DB runs has 16 cpus and 64 GB of RAM. These values are not configured by me. Any ideeas ? Thanks.

onstat -g mgm IBM Informix Dynamic Server Version 12.10.FC6 -- On-Line -- Up 11 days 18:00:30 -- 10595476 Kbytes Memory Grant Manager (MGM) -------------------------- MAX_PDQPRIORITY: 100 DS_MAX_QUERIES: 6144 DS_MAX_SCANS: 1048576 DS_NONPDQ_QUERY_MEM: 256 KB DS_TOTAL_MEMORY: 62856488 KB Queries: Active Ready Maximum 0 0 6144 Memory: Total Free Quantum (KB) 62856488 62856488 10224 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 0.0 +- 0.0 7857061 Scans 0.0 +- 0.0 1048576 Queries Average # Maximum # Total # -------------- --------------- --------- ------- Active 0.0 +- 0.0 0 0 Ready 0.0 +- 0.0 0 0 Resource/Lock Cycle Prevention count: 0

Michael: Are these really your BUFFERPOOL settings? BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50,lru_ max_dirty=60.5 BUFFERPOOL size=2K BUFFERPOOL size=8K IB that this implies that you have 10,000 2K and 10,000 8K buffers. The write cache % of 69% indicates that you likely need many more buffers. However, there's not enough information here to determine how many nor to which buffer pool they need to be added if not both. Also, I see no indication that there is an IO problem, but you didn't post the onstat output we would need to determine that, nor for that matter those we'd need to determine what else (besides not enough buffer cache) is your problem. I think what you mean to say is that you have a performance problem that MIGHT be IO related, but you don't know. Honestly, and I'm not just angling for a paid gig here, you need someone like me or Lester or one of the other excellent consultants who specialize in performance tuning to do a full on server health check for you. That said, zero your stats (onstat -z) at the end of today's normal processing cycle and then at the end of the next day capture and post the following output (and include the onstat header lines please - there's info there that we need) and the number of hours between zeroing the stats and taking those readings for us and we'll see if anyone in the community can spot the problem: onstat -p (again) onstat -g buf onstat -g iov onstat -g iof onstat -D onstat -g seg onstat -g glo onstat -g rea onstat -g ckp (during normal processing hours)' onstat -g dic onstat -g dsc onstat -g prc Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Dec 8, 2016 at 5:47 AM, R MICHAEL <mboncalo@gmail.com> wrote: > I'm opening this thread because of some IO performance issues that I have > now > and need to be sure of what I must do. > > This is my db profile counts:Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 22906487 87848814 34363980245 99.93 332057743 398328761 1082072410 69.31 > > isamtot open start read write rewrite delete commit rollbk > 48406998458 7423608018 9188986012 10875359326 1488886 347373130 682316 > 347179506 1 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 1487097310 353965214 343076548 4397605 4723657 2 18018239 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 457810.60 98673.63 3364 7580 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 541933 6627626 13923560081 0 0 29422 1170160 807177 > > ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits > 70449 9643446 760355 1294 18020622 137838512 > > Onconfig: > NETTYPE soctcp,1,200,NET > LISTEN_TIMEOUT 60 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NUMFDSERVERS 4 > NS_CACHE host=900,service=900,user=900,group=900 > > MULTIPROCESSOR 1 > VPCLASS cpu,num=12,noage > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 0 > CLEANERS 8 > DIRECT_IO 1 > LOCKS 20000 > DEF_TABLE_LOCKMODE page > RESIDENT 0 > SHMBASE 0x44000000L > SHMVIRTSIZE 10485760 > SHMADD 262144 > EXTSHMADD 51200 > SHMTOTAL 62914560 > SHMVIRT_ALLOCSEG 0,3 > SHMNOACCESS > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES > DS_TOTAL_MEMORY > DS_MAX_SCANS 1048576 > DS_NONPDQ_QUERY_MEM 256 > DATASKIP > BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50,lru_ > max_dirty=60.5 > BUFFERPOOL size=2K > BUFFERPOOL size=8K> > onstat -P> Percentages: > Data 55.67 > Btree 8.09 > Other 36.24 > > onstat -F> > Fg Writes LRU Writes Chunk Writes > 2 611239 14997845 > > address flusher state data # LRU Chunk Wakeups Idle Tim > 4c3e48e8 0 I 0 603 10296 1021956 1010204.637 > 4c3e51a8 1 I 0 707 9448 1021391 1009724.104 > 4c3e5a68 2 I 0 627 7475 1019552 1010959.875 > 4c3e6328 3 I 0 873 4565 1017161 1011482.790 > 4c3e6be8 4 I 0 742 7209 1019094 1010744.211 > 4c3e74a8 5 I 0 727 6145 1018085 1010709.286 > 4c3e7d68 6 I 0 858 7217 1019100 1010542.952 > 4c3e8628 7 I 0 773 6640 1018665 1010826.101 > states: Exit Idle Chunk Lru > > The machine on which DB runs has 16 cpus and 64 GB of RAM. > > These values are not configured by me. > > Any ideeas ? > > Thanks. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1148d7be93048d0543242905

Michael: FWIW, your other post (with onstat -g mgm) doesn't tell us anything useful except that the server's been online for 11 days and 18 hours or 282 hours. Assuming you have not zero'd your stats since startup, I can calculate a rough Buffer Turnover Rate (BTR) across the two buffer caches (2K & 8K combined) which is 207.4 turns per hour. An ideal BTR would be something less than 10. However, this appears to be an insert heavy server so the BTR may be inflated by all those inserts (approximately 1/6 of your queries is an update and 1/6 are inserts). I'd need access to your server to get some lower level data to get a more accurate BTR3 calculation performed, but the BTR does tell me that my initial guess was correct. You need MANY MANY more buffers. Do you need them mostly in the 2K cache or the 8K cache? I don't know. The additional onstat output I asked for may tell us. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Dec 8, 2016 at 5:47 AM, R MICHAEL <mboncalo@gmail.com> wrote: > I'm opening this thread because of some IO performance issues that I have > now > and need to be sure of what I must do. > > This is my db profile counts:Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 22906487 87848814 34363980245 99.93 332057743 398328761 1082072410 69.31 > > isamtot open start read write rewrite delete commit rollbk > 48406998458 7423608018 9188986012 10875359326 1488886 347373130 682316 > 347179506 1 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 1487097310 353965214 343076548 4397605 4723657 2 18018239 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 457810.60 98673.63 3364 7580 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 541933 6627626 13923560081 0 0 29422 1170160 807177 > > ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits > 70449 9643446 760355 1294 18020622 137838512 > > Onconfig: > NETTYPE soctcp,1,200,NET > LISTEN_TIMEOUT 60 > MAX_INCOMPLETE_CONNECTIONS 1024 > FASTPOLL 1 > NUMFDSERVERS 4 > NS_CACHE host=900,service=900,user=900,group=900 > > MULTIPROCESSOR 1 > VPCLASS cpu,num=12,noage > VP_MEMORY_CACHE_KB 0 > SINGLE_CPU_VP 0 > CLEANERS 8 > DIRECT_IO 1 > LOCKS 20000 > DEF_TABLE_LOCKMODE page > RESIDENT 0 > SHMBASE 0x44000000L > SHMVIRTSIZE 10485760 > SHMADD 262144 > EXTSHMADD 51200 > SHMTOTAL 62914560 > SHMVIRT_ALLOCSEG 0,3 > SHMNOACCESS > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES > DS_TOTAL_MEMORY > DS_MAX_SCANS 1048576 > DS_NONPDQ_QUERY_MEM 256 > DATASKIP > BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50,lru_ > max_dirty=60.5 > BUFFERPOOL size=2K > BUFFERPOOL size=8K> > onstat -P> Percentages: > Data 55.67 > Btree 8.09 > Other 36.24 > > onstat -F> > Fg Writes LRU Writes Chunk Writes > 2 611239 14997845 > > address flusher state data # LRU Chunk Wakeups Idle Tim > 4c3e48e8 0 I 0 603 10296 1021956 1010204.637 > 4c3e51a8 1 I 0 707 9448 1021391 1009724.104 > 4c3e5a68 2 I 0 627 7475 1019552 1010959.875 > 4c3e6328 3 I 0 873 4565 1017161 1011482.790 > 4c3e6be8 4 I 0 742 7209 1019094 1010744.211 > 4c3e74a8 5 I 0 727 6145 1018085 1010709.286 > 4c3e7d68 6 I 0 858 7217 1019100 1010542.952 > 4c3e8628 7 I 0 773 6640 1018665 1010826.101 > states: Exit Idle Chunk Lru > > The machine on which DB runs has 16 cpus and 64 GB of RAM. > > These values are not configured by me. > > Any ideeas ? > > Thanks. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b71b8c8eabe0543244fb1

Here are the commands outputs: http://s000.tinyupload.com/?file_id=11579693612376769642 It seems I need to increase 2k buffer size

Hi, some thoughts: 1) check where the sequential scans come from. 68000 within one day is a lot .. It might be empty or almost empty tables, when the optimizer decides to read the 0-3 records instead of searching in an index, but in case you are reading through a huge number of records ... (and your dbs layout with so many chunks indicates you have a lot of data (but the size is not stated)) You should check with "set explain on" or dynamic explain (onmode -Y) what number of records are read. But the read ratio of your statistics indicates you should not have a real problem here. 2) %cached for the writes in 2k pages is very low (on our systems, normally near 90%) -> yes, your 2k buffers may need adjustment, but on the other side your bufwaits are not very high Generally, for a 10GB heap system, your buffers are very low from my point of view Your shared memory seems to be used 3) Dictionary cache seems to be mostly unused, Why is that ? Do you have such a small amount of tables ? Or is it because the database was not really used since start ? 4) there are LRU writes in the 2k area, but none in the 8k area. There is nothing really wrong about that, it only means that the system is flushing stuff to disk between the checkpoints because the LRUs have reached their max. Maybe it would be better to increase the limits (lru_max_dirty/min_dirty) and write all data in a real checkpoint (with chunk writes). But you should monitor the checkpoints (the excerpt is not very useful because it gives only some checkpoints from very early in the morning, where I would expect not such a huge activity). When checkpoints are taking too long, even blocking transactions, then lowering the limits for lru is a way to spread the write operations. 5) phydb1 is active, but not used (0 writes). physical log is in phydb, I suspect, so phydb1 is not really used ? 6) On some chunks, you seem to have a huge write activity in comparison to the number of reads It seems there was a relatively huge data load. Hints for your setup cannot be done when the usage of the system is unknown to us ... Do you normally have LRU writes also in the 8K area ? I suspect there has not been a lot of activity on the system since starttime, so your statistics in general are not telling us the "real" usage. You should wait for a relatively "normal" day and grap the statistics after that period. Marcus Haarmann Von: "R MICHAEL" <mboncalo@gmail.com> An: "ids" <ids@iiug.org> Gesendet: Mittwoch, 21. Dezember 2016 22:14:48 Betreff: Re: IO performance issues [38356] Here are the commands outputs: http://s000.tinyupload.com/?file_id=11579693612376769642 It seems I need to increase 2k buffer size ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.