IDS 11.50/AIX 5.3 Slowness Issue?

OK I have sent the following to support but because it is a performance issue we are on a slow boat traveling in circles. I'm sure this is related to a configuration issue but I cannot figure which. I've got a feeling I'm overlooking something very easy. Any suggestions on what area I should focus on would be helpful. I have tried OS related and Informix Configuration changes to no good conclusion (I can list if anyone would like... 100 or so changes made and reverted). Masters of Informix guide me in my time of darkness. Problem: When running anything from within Informix we get no more than 5-7MB/sec (as seen by looking at the SAN). When selecting we get very flat activity until the process is done. My Laptop running VMware with Informix runs faster... Baseline Information: This occurs if the connection is made via Local Loopback (Apps connect here), Remotely (DW pulls) or Shared Memory (Admins). Server is 90% Idle when queries run. When accessing the commandline, data can be moved at a rate between 75-125MB/sec using dd. This includes to and from the informix chuncks. Configuration: IDS: 11.50 AIX: 5.3 TL9 4GL: 7.50 (Application is local to the DB Server) Vxfs: 5.0.3 (Used to provide replication - Planing to remove ASAP) IBM P520 (2 CPU - Dual Core, 8G, Dual 2G HBA) Compellent Model 30 SAN 8G Fiber connected via switch (Disabled Tiering for this server for testing Tier 1 FC Storage) AIX AIO Settings: (Have tried smaller and larger values with no difference) MIN: 200 MAX: 800 REQ: 16384 PRI: 39 State: Available Fast Path: enable Test Query Per support (Queries take 30 minutes to run, dd of the data is less then 3 minutes): time dbaccess product <<! UNLOAD TO /dev/null SELECT * FROM job_rules;! Test Query Per support: time dbaccess product <<! CREATE TEMP TABLE speed_test( dept_code char(3) not null , community char(2) not null , lot char(4) not null , unit_id char(2), optid char(7) not null , option_category char(3) not null , group_seq integer, gen_flag char(1), create_per char(10) default user, create_dt date default today, updat_per char(10) default user, updat_dt date default today, updat_time datetime hour to second default current hour to second ) FRAGMENT BY ROUND ROBIN IN tempdbs01, tempdbs02, tempdbs03, tempdbs04; INSERT INTO speed_test SELECT * FROM job_rules! Fiber Card Performance: FC SCSI Adapter Driver Information No DMA Resource Count: 0 No Adapter Elements Count: 0 No Command Resource Count: 0 IP over FC Traffic Statistics Input Requests: 0 Output Requests: 0 Control Requests: 0 Input Bytes: 0 Output Bytes: 0 FC SCSI Traffic Statistics Input Requests: 542306 Output Requests: 2000560 Control Requests: 64 Input Bytes: 9420192051 Output Bytes: 25358181400 HBA Configuration: FC Adapter fcs2 Description FC Adapter Status Available Location 0B-08 Maximum number of COMMANDS to queue to the adapter [2048] +# Maximum Transfer Size [0x1000000] + Preferred AL_PA [0x1] + INIT Link flags [pt2pt] + Long term DMA [0x8000000] + Disk Device Configuration has been adjust up and down but doesn't effect the runtime of Informix. dd does see beter response with a higher queue depth than the default 32.

Are the chunks using RAW device, COOKED device, or COOKED filesystem files? If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is the AIX IO Queue Depth set to (is the that "32" below)? Are the temp dbspaces on the same physical SAN structure as the data dbspaces for this table? What is the underlying structure of the physical array containing the data chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the SAN? How much data is in this table? Pagesize of the dbspace(s) containing the table's partition(s)? What are the IO service times on the chunks as reported by Informix (onstat -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache for this table's dbspace(s)? Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote: > OK I have sent the following to support but because it is a > performance issue we are on a slow boat traveling in circles. > > I'm sure this is related to a configuration issue but I cannot figure > which. I've got a feeling I'm overlooking something very easy. > > Any suggestions on what area I should focus on would be helpful. I > have tried OS related and Informix Configuration changes to no good > conclusion (I can list if anyone would like... 100 or so changes made > and reverted). Masters of Informix guide me in my time of darkness. > > Problem: > When running anything from within Informix we get no more than > 5-7MB/sec (as seen by looking at the SAN). When selecting we get very > flat activity until the process is done. My Laptop running VMware > with Informix runs faster... > > Baseline Information: > This occurs if the connection is made via Local Loopback (Apps > connect here), Remotely (DW pulls) or Shared Memory (Admins). > Server is 90% Idle when queries run. > When accessing the commandline, data can be moved at a rate between > 75-125MB/sec using dd. This includes to and from the informix > chuncks. > > Configuration: > IDS: 11.50 > AIX: 5.3 TL9 > 4GL: 7.50 (Application is local to the DB Server) > Vxfs: 5.0.3 (Used to provide replication - Planing to remove ASAP) > > IBM P520 (2 CPU - Dual Core, 8G, Dual 2G HBA) > Compellent Model 30 SAN 8G Fiber connected via switch (Disabled > Tiering for this server for testing Tier 1 FC Storage) > > AIX AIO Settings: (Have tried smaller and larger values with no difference) > MIN: 200 > MAX: 800 > REQ: 16384 > PRI: 39 > State: Available > Fast Path: enable > > Test Query Per support (Queries take 30 minutes to run, dd of the data > is less then 3 minutes): > time dbaccess product <<! > > UNLOAD TO /dev/null > SELECT * FROM job_rules;> ! > > Test Query Per support: > time dbaccess product <<! > CREATE TEMP TABLE speed_test(> > dept_code char(3) not null , > > community char(2) not null , > > lot char(4) not null , > > unit_id char(2), > > optid char(7) not null , > > option_category char(3) not null , > > group_seq integer, > > gen_flag char(1), > > create_per char(10) > > default user, > > create_dt date > > default today, > > updat_per char(10) > > default user, > > updat_dt date > > default today, > > updat_time datetime hour to second > > default current hour to second > > ) FRAGMENT BY ROUND ROBIN IN tempdbs01, tempdbs02, tempdbs03, tempdbs04; > > INSERT INTO speed_test > SELECT * FROM job_rules> ! > > Fiber Card Performance: > FC SCSI Adapter Driver Information > No DMA Resource Count: 0 > No Adapter Elements Count: 0 > No Command Resource Count: 0 > > IP over FC Traffic Statistics > Input Requests: 0 > Output Requests: 0 > Control Requests: 0 > Input Bytes: 0 > Output Bytes: 0 > > FC SCSI Traffic Statistics > Input Requests: 542306 > Output Requests: 2000560 > Control Requests: 64 > Input Bytes: 9420192051 > Output Bytes: 25358181400 > > HBA Configuration: > FC Adapter fcs2 > Description FC Adapter > Status Available > Location 0B-08 > Maximum number of COMMANDS to queue to the adapter [2048] +# > Maximum Transfer Size [0x1000000] + > Preferred AL_PA [0x1] + > INIT Link flags [pt2pt] + > Long term DMA [0x8000000] + > > Disk Device Configuration has been adjust up and down but doesn't > effect the runtime of Informix. dd does see beter response with a > higher queue depth than the default 32. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071ced8947de504a0a7370c

ART thank you for your questions. There was a second half to my post which was lost. There are the answers to your questions: RAW DIsks AIX IO Queue Depth: 32 (has been set to 256 with no difference in Informix) RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 Disk, 2M Block, 4G FC Drives 15K. The Temp DB are on difference disk on the same SAN. SAN cache 3.5GB per controller (2 Controllers) Table has about 5GB of data Currently 4K page (I have tried the same process with a 12K and 16K page, Margin of Error performance change 1-2%) IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 days 23:07:25 -- 1742592 KbytesSegment Summary: id key addr size ovhd class blkused blkfree 7340042 52574801 700000010000000 1681735680 20141248 R* 410576 4 7340044 52574802 700000080000000 98304000 1153600 V 16813 7187 7340043 52574803 700000090000000 4374528 52672 M 1067 1 Total: - - 1784414208 - - 428456 7192 ** I have increased and decreased the memory values with no difference. gfd pathname bytes read page reads bytes write page writes io/s 5 dms_vol01 2409951232 588367 0 0 157.5 37 dms_vol44 256000000 62500 0 0 75.3 116 dms_vol37 768000000 187500 0 0 121.7 186 dms_vol29 256000000 62500 0 0 82.6 partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio 0xb00002 0 0 0 0 170884 0 0 0 1 0 1 4294930196 * I think this ppf was after the query was completed. The odd thing to me is when testing under linux I see a performance difference when changing from 4K to 16K blocks. Other odd thing is how fast and many io/s I can do from the OS compared to within Informix. I expect over-head but not going from 75MB/s to 5MB/s. On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > Are the chunks using RAW device, COOKED device, or COOKED filesystem files? > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is the > AIX IO Queue Depth set to (is the that "32" below)? Are the temp dbspaces > on the same physical SAN structure as the data dbspaces for this table? > What is the underlying structure of the physical array containing the data > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the > SAN? How much data is in this table? Pagesize of the dbspace(s) > containing the table's partition(s)? > > What are the IO service times on the chunks as reported by Informix (onstat > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache for > this table's dbspace(s)? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote: >> >> OK I have sent the following to support but because it is a >> performance issue we are on a slow boat traveling in circles. >> >> I'm sure this is related to a configuration issue but I cannot figure >> which. I've got a feeling I'm overlooking something very easy. >> >> Any suggestions on what area I should focus on would be helpful. I >> have tried OS related and Informix Configuration changes to no good >> conclusion (I can list if anyone would like... 100 or so changes made >> and reverted). Masters of Informix guide me in my time of darkness. >> >> Problem: >> When running anything from within Informix we get no more than >> 5-7MB/sec (as seen by looking at the SAN). When selecting we get very >> flat activity until the process is done. My Laptop running VMware >> with Informix runs faster... >> >> Baseline Information: >> This occurs if the connection is made via Local Loopback (Apps >> connect here), Remotely (DW pulls) or Shared Memory (Admins). >> Server is 90% Idle when queries run. >> When accessing the commandline, data can be moved at a rate between >> 75-125MB/sec using dd. This includes to and from the informix >> chuncks. >> >> Configuration: >> IDS: 11.50 >> AIX: 5.3 TL9 >> 4GL: 7.50 (Application is local to the DB Server) >> Vxfs: 5.0.3 (Used to provide replication - Planing to remove ASAP) >> >> IBM P520 (2 CPU - Dual Core, 8G, Dual 2G HBA) >> Compellent Model 30 SAN 8G Fiber connected via switch (Disabled >> Tiering for this server for testing Tier 1 FC Storage) >> >> AIX AIO Settings: (Have tried smaller and larger values with no >> difference) >> MIN: 200 >> MAX: 800 >> REQ: 16384 >> PRI: 39 >> State: Available >> Fast Path: enable >> >> Test Query Per support (Queries take 30 minutes to run, dd of the data >> is less then 3 minutes): >> time dbaccess product <<! >> >> UNLOAD TO /dev/null >> SELECT * FROM job_rules;>> ! >> >> Test Query Per support: >> time dbaccess product <<! >> CREATE TEMP TABLE speed_test(>> >> dept_code char(3) not null , >> >> community char(2) not null , >> >> lot char(4) not null , >> >> unit_id char(2), >> >> optid char(7) not null , >> >> option_category char(3) not null , >> >> group_seq integer, >> >> gen_flag char(1), >> >> create_per char(10) >> >> default user, >> >> create_dt date >> >> default today, >> >> updat_per char(10) >> >> default user, >> >> updat_dt date >> >> default today, >> >> updat_time datetime hour to second >> >> default current hour to second >> >> ) FRAGMENT BY ROUND ROBIN IN tempdbs01, tempdbs02, tempdbs03, tempdbs04; >> >> INSERT INTO speed_test >> SELECT * FROM job_rules>> ! >> >> Fiber Card Performance: >> FC SCSI Adapter Driver Information >> No DMA Resource Count: 0 >> No Adapter Elements Count: 0 >> No Command Resource Count: 0 >> >> IP over FC Traffic Statistics >> Input Requests: 0 >> Output Requests: 0 >> Control Requests: 0 >> Input Bytes: 0 >> Output Bytes: 0 >> >> FC SCSI Traffic Statistics >> Input Requests: 542306 >> Output Requests: 2000560 >> Control Requests: 64 >> Input Bytes: 9420192051 >> Output Bytes: 25358181400 >> >> HBA Configuration: >> FC Adapter fcs2 >> Description FC Adapter >> Status Available >> Location 0B-08 >> Maximum number of COMMANDS to queue to the adapter [2048] +# >> Maximum Transfer Size [0x1000000] + >> Preferred AL_PA [0x1] + >> INIT Link flags [pt2pt] + >> Long term DMA [0x8000000] + >> >> Disk Device Configuration has been adjust up and down but doesn't >> effect the runtime of Informix. dd does see beter response with a >> higher queue depth than the default 32. >>@@

Could you post the $ONCONFIG and "onstat -g ioa"? Also setting WSTATS in $ONCONFIG before running the tests and onstat -g wst (before closing the sessions) could help to understand why the queries take time. Something that will definitively increase the performance in "UNLOAD .... SELECT *" (without conditions) is:- Make sure your tables are not using too many extents - increase FET_BUF_SIZE env variable Comparing query performance with DD is not fair. There are reasons why we use relational databases instead of simple files. Direct throughput it not one of them. By this I'm not saying that what you see is normal... Regards. On Mon, Apr 11, 2011 at 6:07 PM, Eric Rowell <erowell@gmail.com> wrote: > ART thank you for your questions. There was a second half to my post > which was lost. There are the answers to your questions: > > RAW DIsks > > AIX IO Queue Depth: 32 (has been set to 256 with no difference in Informix) > > RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 > Disk, 2M Block, 4G FC Drives 15K. > > The Temp DB are on difference disk on the same SAN. > > SAN cache 3.5GB per controller (2 Controllers) > > Table has about 5GB of data > > Currently 4K page (I have tried the same process with a 12K and 16K > page, Margin of Error performance change 1-2%) > > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 > days 23:07:25 -- 1742592 Kbytes> Segment Summary: > id key addr size ovhd class > blkused blkfree > 7340042 52574801 700000010000000 1681735680 20141248 R* > 410576 4 > 7340044 52574802 700000080000000 98304000 1153600 V > 16813 7187 > 7340043 52574803 700000090000000 4374528 52672 M > 1067 1 > Total: - - 1784414208 - - > 428456 7192 > > ** I have increased and decreased the memory values with no difference. > > gfd pathname bytes read page reads bytes write page writes io/s > 5 dms_vol01 2409951232 588367 0 0 > > 157.5 > 37 dms_vol44 256000000 62500 0 0 > > 75.3 > 116 dms_vol37 768000000 187500 0 0 > > 121.7 > 186 dms_vol29 256000000 62500 0 0 > > 82.6 > > partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd > bfwrt seqsc rhitratio > 0xb00002 0 0 0 0 170884 0 0 0 1 > 0 1 4294930196 > > * I think this ppf was after the query was completed. > > The odd thing to me is when testing under linux I see a performance > difference when changing from 4K to 16K blocks. Other odd thing is > how fast and many io/s I can do from the OS compared to within > Informix. I expect over-head but not going from 75MB/s to 5MB/s. > > On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > > Are the chunks using RAW device, COOKED device, or COOKED filesystem > files? > > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable > > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is > the > > AIX IO Queue Depth set to (is the that "32" below)? Are the temp dbspaces > > on the same physical SAN structure as the data dbspaces for this table? > > What is the underlying structure of the physical array containing the > data > > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the > > SAN? How much data is in this table? Pagesize of the dbspace(s) > > containing the table's partition(s)? > > > > What are the IO service times on the chunks as reported by Informix > (onstat > > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache for > > this table's dbspace(s)? > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote: > >> > >> OK I have sent the following to support but because it is a > >> performance issue we are on a slow boat traveling in circles. > >> > >> I'm sure this is related to a configuration issue but I cannot figure > >> which. I've got a feeling I'm overlooking something very easy. > >> > >> Any suggestions on what area I should focus on would be helpful. I > >> have tried OS related and Informix Configuration changes to no good > >> conclusion (I can list if anyone would like... 100 or so changes made > >> and reverted). Masters of Informix guide me in my time of darkness. > >> > >> Problem: > >> When running anything from within Informix we get no more than > >> 5-7MB/sec (as seen by looking at the SAN). When selecting we get very > >> flat activity until the process is done. My Laptop running VMware > >> with Informix runs faster... > >> > >> Baseline Information: > >> This occurs if the connection is made via Local Loopback (Apps > >> connect here), Remotely (DW pulls) or Shared Memory (Admins). > >> Server is 90% Idle when queries run. > >> When accessing the commandline, data can be moved at a rate between > >> 75-125MB/sec using dd. This includes to and from the informix > >> chuncks. > >> > >> Configuration: > >> IDS: 11.50 > >> AIX: 5.3 TL9 > >> 4GL: 7.50 (Application is local to the DB Server) > >> Vxfs: 5.0.3 (Used to provide replication - Planing to remove ASAP) > >> > >> IBM P520 (2 CPU - Dual Core, 8G, Dual 2G HBA) > >> Compellent Model 30 SAN 8G Fiber connected via switch (Disabled > >> Tiering for this server for testing Tier 1 FC Storage) > >> > >> AIX AIO Settings: (Have tried smaller and larger values with no > >> difference) > >> MIN: 200 > >> MAX: 800 > >> REQ: 16384 > >> PRI: 39 > >> State: Available > >> Fast Path: enable > >> > >> Test Query Per support (Queries take 30 minutes to run, dd of the data > >> is less then 3 minutes): > >> time dbaccess product <<! > >> > >> UNLOAD TO /dev/null > >> SELECT * FROM job_rules;> >> ! > >> > >> Test Query Per support: > >> time dbaccess product <<! > >> CREATE TEMP TABLE speed_test(> >> > >> dept_code char(3) not null , > >> > >> community char(2) not null , > >> > >> lot char(4) not null , > >> > >> unit_id char(2), > >> > >> optid char(7) not null , > >> > >> option_category char(3) not null , > >> > >> group_seq integer, > >> > >> gen_flag char(1), > >> > >> create_per char(10) > >> > >> default user, > >> > >> create_dt date > >> > >> default today, > >> > >> updat_per char(10) > >> > >> default user, > >> > >> updat_dt date > >> > >> default today, > >> > >> updat_time datetime hour to second > >> > >> default current hour to second > >> > >> ) FRAGMENT BY ROUND ROBIN IN tempdbs01, tempdbs02, tempdbs03, t

Eric, You may or may not have done this already, if not then I would say at this point you have to start looking to see what this machine is doing during the unloads. 4GL: 7.50 (Application is local to the DB Server) One thing to be cognizant of when the apps are local is os paging. Try running topas during your test unloads and see if the server starts to page heavily (look under PAGING and not PAGING SPACE , since that will only show % used , not a good guage in my opinion if you configured a large swap space). Even though you may have RESIDENT set, doesn't help much if the machine itself becomes preoccupied swapping other stuff. Also keep an eye on the cpu's to see if they start to burn in user or sys. Mark

Good question. This is a test system and no 4GL applications are running during the test. Sorry I wasn't clear on that aspect. Eric B. Rowell On Mon, Apr 11, 2011 at 3:41 PM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net> wrote: > Eric, > > You may or may not have done this already, if not then I would say at this > point you have to start looking to see what this machine is doing during the > unloads. > > 4GL: 7.50 (Application is local to the DB Server) > > One thing to be cognizant of when the apps are local is os paging. > Try running topas during your test unloads and see if the server starts to > page heavily (look under PAGING and not PAGING SPACE , since that will only > show % used , not a good guage in my opinion if you configured a large swap > space). Even though you may have RESIDENT set, doesn't help much if the > machine itself becomes preoccupied swapping other stuff. > > Also keep an eye on the cpu's to see if they start to burn in user or sys. > > Mark > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Eric B. Rowell

The only obvious problem that I see is the 2MB stripe block size on the disk array. Unlike filesystems, Informix performs best with small stripe blocks ideally in the 32K to 64K range. That is because Informix only ever either reads or writes a single page (4K on AIX as you know) or what's known as a Big Read which is eight pages (so 32K). That may explain at least some of the reduction in IO rates when using Informix versus dd in either of two apparently opposite ways: 1) Informix is pulling in 2MB every time it asks for 4K that is causing a 500x hit on bandwidth reading unnecessary data. - or - 2) When Informix asks for 4K and 4MB is read, any following sequential reads are satisfied from the controller cache not from the SAN itself so the SAN statistics are misleading. HUH? What I'm saying is that either you are wasting lots of cache pages and available IO bandwidth on 492 - 499 unread Informix pages with each IO, or your actual data rate as experienced by Informix is much higher than the SAN is seeing. The final arbitrator of which is truth is the query performance which you are saying is running 10x slower than raw IO on the devices using dd. No database will every reach dd's simplicity and speed, but this isn't even close. I'm going to go for the wasted bandwidth interpretation here and say the there is a real slowdown, not a phantom caused by looking at the wrong level of the system. The remedy is to rebuild the disk array using a 32K or 64K block size. Also look at reducing your RA_ parameters in the ONCONFIG file. I have not seen the output from onstat -p or onstat -g iov but I suspect you are also experiencing excessive read-ahead within Informix, but that's probably not the main problem. Oh, and stick with increasing the AIX IO queue depth to 64 or more. On the system I was having a problem on it was set to only 8, but 64 seemed to work very well in combination with other changes (most related to tuning AIX properly for running Informix on the P7 processors - so that doesn't apply to you). Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 1:07 PM, Eric Rowell <erowell@gmail.com> wrote: > ART thank you for your questions. There was a second half to my post > which was lost. There are the answers to your questions: > > RAW DIsks > > AIX IO Queue Depth: 32 (has been set to 256 with no difference in > Informix) > > RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 > Disk, 2M Block, 4G FC Drives 15K. > > The Temp DB are on difference disk on the same SAN. > > SAN cache 3.5GB per controller (2 Controllers) > > Table has about 5GB of data > > Currently 4K page (I have tried the same process with a 12K and 16K > page, Margin of Error performance change 1-2%) > > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 > days 23:07:25 -- 1742592 Kbytes> Segment Summary: > id key addr size ovhd class > blkused blkfree > 7340042 52574801 700000010000000 1681735680 20141248 R* > 410576 4 > 7340044 52574802 700000080000000 98304000 1153600 V > 16813 7187 > 7340043 52574803 700000090000000 4374528 52672 M > 1067 1 > Total: - - 1784414208 - - > 428456 7192 > > ** I have increased and decreased the memory values with no difference. > > gfd pathname bytes read page reads bytes write page writes > io/s > 5 dms_vol01 2409951232 588367 0 0 > 157.5 > 37 dms_vol44 256000000 62500 0 0 > 75.3 > 116 dms_vol37 768000000 187500 0 0 > 121.7 > 186 dms_vol29 256000000 62500 0 0 > 82.6 > > partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd > bfwrt seqsc rhitratio > 0xb00002 0 0 0 0 170884 0 0 0 1 > 0 1 4294930196 > * I think this ppf was after the query was completed. > > The odd thing to me is when testing under linux I see a performance > difference when changing from 4K to 16K blocks. Other odd thing is > how fast and many io/s I can do from the OS compared to within > Informix. I expect over-head but not going from 75MB/s to 5MB/s. > > > > On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > > Are the chunks using RAW device, COOKED device, or COOKED filesystem > files? > > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable > > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is > the > > AIX IO Queue Depth set to (is the that "32" below)? Are the temp > dbspaces > > on the same physical SAN structure as the data dbspaces for this table? > > What is the underlying structure of the physical array containing the > data > > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the > > SAN? How much data is in this table? Pagesize of the dbspace(s) > > containing the table's partition(s)? > > > > What are the IO service times on the chunks as reported by Informix > (onstat > > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache > for > > this table's dbspace(s)? > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote: > >> > >> OK I have sent the following to support but because it is a > >> performance issue we are on a slow boat traveling in circles. > >> > >> I'm sure this is related to a configuration issue but I cannot figure > >> which. I've got a feeling I'm overlooking something very easy. > >> > >> Any suggestions on what area I should focus on would be helpful. I > >> have tried OS related and Informix Configuration changes to no good > >> conclusion (I can list if anyone would like... 100 or so changes made > >> and reverted). Masters of Informix guide me in my time of darkness. > >> > >> Problem: > >> When running anything from within Informix we get no more than > >> 5-7MB/sec (as seen by looking at the SAN). When selecting we get very > >> flat activity until the process is done. My Laptop running VMware > >> with Informix runs faster... > >> > >> Baseline Information: > >> This occurs if the connection is made via Local Loopback (Apps > >> connect here), Remotely (DW pulls) or Shared Memory (Admins). > >> Server is 90% Idl

How many LUNs are you hitting? How many dbspaces / chunks are on those LUNs? Does Compellent have a utility to show queue depth on the SAN? You may have too many threads hitting a lot of chunks on one big LUN and backing up those queues? Bob ----- Original Message ----- From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Sent: Monday, April 11, 2011 4:06:01 PM Subject: Re: IDS 11.50/AIX 5.3 Slowness Issue? [23383] The only obvious problem that I see is the 2MB stripe block size on the disk array. Unlike filesystems, Informix performs best with small stripe blocks ideally in the 32K to 64K range. That is because Informix only ever either reads or writes a single page (4K on AIX as you know) or what's known as a Big Read which is eight pages (so 32K). That may explain at least some of the reduction in IO rates when using Informix versus dd in either of two apparently opposite ways: 1) Informix is pulling in 2MB every time it asks for 4K that is causing a 500x hit on bandwidth reading unnecessary data. - or - 2) When Informix asks for 4K and 4MB is read, any following sequential reads are satisfied from the controller cache not from the SAN itself so the SAN statistics are misleading. HUH? What I'm saying is that either you are wasting lots of cache pages and available IO bandwidth on 492 - 499 unread Informix pages with each IO, or your actual data rate as experienced by Informix is much higher than the SAN is seeing. The final arbitrator of which is truth is the query performance which you are saying is running 10x slower than raw IO on the devices using dd. No database will every reach dd's simplicity and speed, but this isn't even close. I'm going to go for the wasted bandwidth interpretation here and say the there is a real slowdown, not a phantom caused by looking at the wrong level of the system. The remedy is to rebuild the disk array using a 32K or 64K block size. Also look at reducing your RA_ parameters in the ONCONFIG file. I have not seen the output from onstat -p or onstat -g iov but I suspect you are also experiencing excessive read-ahead within Informix, but that's probably not the main problem. Oh, and stick with increasing the AIX IO queue depth to 64 or more. On the system I was having a problem on it was set to only 8, but 64 seemed to work very well in combination with other changes (most related to tuning AIX properly for running Informix on the P7 processors - so that doesn't apply to you). Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 1:07 PM, Eric Rowell <erowell@gmail.com> wrote: > ART thank you for your questions. There was a second half to my post > which was lost. There are the answers to your questions: > > RAW DIsks > > AIX IO Queue Depth: 32 (has been set to 256 with no difference in > Informix) > > RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 > Disk, 2M Block, 4G FC Drives 15K. > > The Temp DB are on difference disk on the same SAN. > > SAN cache 3.5GB per controller (2 Controllers) > > Table has about 5GB of data > > Currently 4K page (I have tried the same process with a 12K and 16K > page, Margin of Error performance change 1-2%) > > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 > days 23:07:25 -- 1742592 Kbytes> Segment Summary: > id key addr size ovhd class > blkused blkfree > 7340042 52574801 700000010000000 1681735680 20141248 R* > 410576 4 > 7340044 52574802 700000080000000 98304000 1153600 V > 16813 7187 > 7340043 52574803 700000090000000 4374528 52672 M > 1067 1 > Total: - - 1784414208 - - > 428456 7192 > > ** I have increased and decreased the memory values with no difference. > > gfd pathname bytes read page reads bytes write page writes > io/s > 5 dms_vol01 2409951232 588367 0 0 > 157.5 > 37 dms_vol44 256000000 62500 0 0 > 75.3 > 116 dms_vol37 768000000 187500 0 0 > 121.7 > 186 dms_vol29 256000000 62500 0 0 > 82.6 > > partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd > bfwrt seqsc rhitratio > 0xb00002 0 0 0 0 170884 0 0 0 1 > 0 1 4294930196 > * I think this ppf was after the query was completed. > > The odd thing to me is when testing under linux I see a performance > difference when changing from 4K to 16K blocks. Other odd thing is > how fast and many io/s I can do from the OS compared to within > Informix. I expect over-head but not going from 75MB/s to 5MB/s. > > > > On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > > Are the chunks using RAW device, COOKED device, or COOKED filesystem > files? > > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable > > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is > the > > AIX IO Queue Depth set to (is the that "32" below)? Are the temp > dbspaces > > on the same physical SAN structure as the data dbspaces for this table? > > What is the underlying structure of the physical array containing the > data > > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on the > > SAN? How much data is in this table? Pagesize of the dbspace(s) > > containing the table's partition(s)? > > > > What are the IO service times on the chunks as reported by Informix > (onstat > > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache > for > > this table's dbspace(s)? > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> wrote: > >> > >> OK I have sent the following to support but because it is a > >> performance issue we are on a slow boat traveling in circles. > >> > >> I'm sure this is related to a configuration issue but I cannot figure > >> which. I've got a feeling I'm overlooking something very easy. > >> > >> Any suggestions on what area I should focus on would be helpful. I > >> have tried OS related and Informix Configuration changes to no good > >> conclusion (I can list if anyone would like... 100 or so changes made > >> and reverted). Masters of Informix guide me in my time of darkness. > >>@

dcucsapp01prd_informix(16) #onstat -g wst IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 01:43:47 -- 1742592 Kbytes name tid state n avg(us) max(us) lio vp 0 2 yield 0 1 29 29 lio vp 0 2 ready 2 17 34 lio vp 0 2 run 1 15 15 pio vp 0 3 yield 0 1 6 6 pio vp 0 3 ready 2 5 10 pio vp 0 3 run 1 17 17 aio vp 0 4 yield 0 1 6 6 aio vp 0 4 yield time 2 681486 999655 aio vp 0 4 other mutex 1 420 420 aio vp 0 4 ready 778 8 298 aio vp 0 4 run 777 3030 10205 aio vp 0 4 IO Idle 773 14551 3.0s msc vp 0 5 yield 0 1 6 6 msc vp 0 5 ready 7 29 97 msc vp 0 5 run 6 5282 17635 msc vp 0 5 IO Idle 5 26.7s 128.8s fifo vp 6 yield 0 1 5 5 fifo vp 6 ready 2 5 11 fifo vp 6 run 1 20 20 aio vp 1 7 yield 0 1 5 5 aio vp 1 7 yield time 1 363361 363361 aio vp 1 7 other mutex 1 154 154 aio vp 1 7 ready 6 170 606 aio vp 1 7 run 5 83 390 aio vp 1 7 IO Idle 2 5.8s 6.6s main_loo 8 IO Wait 1182 5611 1.4s main_loo 8 yield 0 2 54 74 main_loo 8 yield time 6225 997688 1.0s main_loo 8 yield forever 10 63819 100626 main_loo 8 ready 7422 30 2454 main_loo 8 run 7407 65 24008 sm_poll 9 yield 0 1 30 30 sm_poll 9 yield time 1 883891 883891 sm_poll 9 ready 3 93 242 sm_poll 9 run 1 64 64 soctcppo 10 yield 0 1 19 19 soctcppo 10 other cond 1 571082 571082 soctcppo 10 ready 25092 0 25 soctcppo 10 run 1 175 175 soctcppo 11 yield 0 1 25 25 soctcppo 11 other cond 1 476546 476546 soctcppo 11 ready 25084 0 31 soctcppo 11 run 1 146 146 soctcppo 12 yield 0 1 22 22 soctcppo 12 other cond 1 129.2s 129.2s soctcppo 12 ready 24366 0 53 soctcppo 12 run 1 156 156 soctcppo 13 yield 0 1 23 23 soctcppo 13 other cond 1 130.1s 130.1s soctcppo 13 ready 284313 0 47 soctcppo 13 run 1 167 167 soctcppo 14 yield 0 1 18 18 soctcppo 14 ready 2 12 24 soctcppo 14 run 1 139 139 soctcppo 15 yield 0 1 19 19 soctcppo 15 ready 2 12 25 soctcppo 15 run 1 176 176 sm_liste 16 IO Wait 13 1757 10065 sm_liste 16 ready 14 6 8 sm_liste 16 run 13 698 8690 sm_disco 17 yield time 6218 999950 1.0s sm_disco 17 ready 6219 128 2566 sm_disco 17 run 6218 6 1328 soctcpls 18 IO Wait 5 6362 22774 soctcpls 18 yield forever 6 21.6s 128.8s soctcpls 18 ready 15 922 13627 soctcpls 18 run 10 2286 12847 soctcpls 19 IO Wait 5 6910 24008 soctcpls 19 yield 0 1 7 7 soctcpls 19 yield forever 1 250834 250834 soctcpls 19 ready 8 1733 7404 soctcpls 19 run 6 937 5192 flush_su 20 IO Wait 7 1235 2534 flush_su 20 yield time 6222 999308 1.0s flush_su 20 ready 6230 129 2605 flush_su 20 run 6229 3 199 flush_su 21 IO Wait 3 894 1108 flush_su 21 yield time 6220 999591 1.0s flush_su 21 ready 6225 127 2561 flush_su 21 run 6224 3 251 flush_su 22 IO Wait 1 592 592 flush_su 22 yield time 6218 999913 1.0s flush_su 22 ready 6221 127 2756 flush_su 22 run 6220 3 112 flush_su 23 IO Wait 1 375 375 flush_su 23 yield time 6218 999912 1.0s flush_su 23 ready 6221 128 2731 flush_su 23 run 6220 3 1240 flush_su 24 IO Wait 1 710 710 flush_su 24 yield time 6218 999912 1.0s flush_su 24 ready 6221 128 3058 flush_su 24 run 6220 3 1274 flush_su 25 IO Wait 1 411 411 flush_su 25 yield time 6218 999913 1.0s flush_su 25 ready 6221 127 2706 flush_su 25 run 6220 3 1224 flush_su 26 IO Wait 1 741 741 flush_su 26 yield time 6218 999913 1.0s flush_su 26 ready 6221 129 2695 flush_su 26 run 6220 3 698 flush_su 27 IO Wait 1 442 442 flush_su 27 yield time 6219 999788 1.0s flush_su 27 ready 6221 130 2621 flush_su 27 run 6220 3 1229 flush_su 28 yield time 6218 999949 1.0s flush_su 28 ready 6219 128 2551 flush_su 28 run 6218 3 265 flush_su 29 yield time 6217 1.0s 1.0s flush_su 29 ready 6219 129 2829 flush_su 29 run 6218 3 1224 flush_su 30 yield time 6217 1.0s 1.0s flush_su 30 ready 6219 128 2643 flush_su 30 run 6218 3 1322 flush_su 31 yield time 6217 1.0s 1.0s flush_su 31 ready 6219 128 2734 flush_su 31 run 6218 3 1225 flush_su 32 yield time 6217 1.0s 1.0s flush_su 32 ready 6219 127 2559 flush_su 32 run 6218 3 1223 flush_su 33 yield time 6217 1.0s 1.0s flush_su 33 ready 6219 129 2701 flush_su 33 run 6218 3 1246 flush_su 34 yield time 6217 1.0s 1.0s flush_su 34 ready 6219 127 2601 flush_su 34 run 6218 3 26 flush_su 35 yield time 6217 1.0s 1.0s flush_su 35 ready 6219 128 2590 flush_su 35 run 6218 3 1223 flush_su 36 yield time 6217 1.0s 1.0s flush_su 36 ready 6219 127 2707 flush_su 36 run 6218 3 1225 flush_su 37 yield time 6217 1.0s 1.0s flush_su 37 ready 6219 129 2892 flush_su 37 run 6218 4 1351 flush_su 38 yield time 6217 1.0s 1.0s flush_su 38 ready 6219 127 2610 flush_su 38 run 6218 3 1230 flush_su 39 yield time 6217 1.0s 1.0s flush_su 39 ready 6219 128 2582 flush_su 39 run 6218 3 1225 flush_su 40 yield time 6217 1.0s 1.0s flush_su 40 ready 6219 128 2722 flush_su 40 run 6218 3 263 flush_su 41 yield time 6217 1.0s 1.0s flush_su 41 ready 6219 128 2650 flush_su 41 run 6218 3 1233 flush_su 42 yield time 6217 1.0s 1.0s flush_su 42 ready 6219 128 2659 flush_su 42 run 6218 4 1885 flush_su 43 yield time 6217 1.0s 1.0s flush_su 43 ready 6219 129 2673 flush_su 43 run 6218 3 214 flush_su 44 yield time 6217 1.0s 1.0s flush_su 44 ready 6219 130 2649 flush_su 44 run 6218 3 1230 flush_su 45 yield time 6217 1.0s 1.0s flush_su 45 ready 6219 127 2692 flush_su 45 run 6218 3 292 flush_su 46 yield time 6217 1.0s 1.0s flush_su 46 ready 6219 128 2802 flush_su 46 run 6218 3 174 flush_su 47 yield time 6217 1.0s 1.0s flush_su 47 ready 6219 127 2637 flush_su 47 run 6218 3 1337 flush_su 48 yield time 6217 1.0s 1.0s flush_su 48 ready 6219 129 2918 flush_su 48 run 6218 3 706 flush_su 49 yield time 6217 1.0s 1.0s flush_su 49 ready 6219 128 2698 flush_su 49 run 6218 3 1233 flush_su 50 yield time 6217 1.0s 1.0s flush_su 50 ready 6219 127 2678 flush_su 50 run 6218 3 1252 flush_su 51 yield time 6217 1.0s 1.0s flush_su 51 ready 6219 129 2623 flush_su 51 run 6218 3 1230 flush_su 52 yield time 6217 1.0s 1.0s flush_su 52 ready 6219 131 2888 flush_su 52 run 6218 3 209 flush_su 53 yield time 6217 1.0s 1.0s flush_su 53 ready 6219 128 2815 flush_su 53 run 6218 3 1224 flush_su 54 yield time 6217 1.0s 1.0s flush_su 54 ready 6219 128 2747 flush_su 54 run 6218 3 13 flush_su 55 yield time 6217 1.0s 1.0s flush_su 55 ready 6219 129 2556 flush_su 55 run 6218 3 19 flush_su 56 yield time 6217 1.0s 1.0s flush_su 56 ready 6219 127 2839 flush_su 56 run 6218 3 273 flush_su 57 yield time 6217 1.0s 1.0s flush_su 57 ready 6219 128 2887 flush_su 57 run 6218 3 1224 flush_su 58 yield time 6217 1.0s 1.0s flush_su 58 ready 6219 129 2634 flush_su 58 run 6218 3 15 flush_su 59 yield time 6217 1.0s 1.0s flush_su 59 ready 6219 130 2791 flush_su 59 run 6218 3 1224 flush_su 60 yield time 6217 1.0s 1.0s flush_su 60 ready 6

onstat -g ioa IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 01:46:53 -- 1742592 Kbytes AIO global info: 9 aio classes 236 open files 256 max global files AIO I/O queues: q name/id len maxlen totalops dskread dskwrite dskcopy fifo 0 0 0 0 0 0 0 drda_dbg 0 0 0 0 0 0 0 sqli_dbg 0 0 0 0 0 0 0 kio 0 0 16 2920 2903 17 0 kio 1 0 16 2718 2640 78 0 kio 2 0 16 122 56 66 0 kio 3 0 1 3 1 2 0 kio 4 0 1 2 1 1 0 kio 5 0 1 1 1 0 0 kio 6 0 16 57 0 57 0 adt 0 0 0 0 0 0 0 msc 0 0 1 7 0 0 0 aio 0 0 6 769 12 2 0 pio 0 0 0 0 0 0 0 lio 0 0 0 0 0 0 0 gfd 3 0 0 0 0 0 0 gfd 4 0 8 10 2 8 0 gfd 5 0 8 10 2 8 0 gfd 6 0 8 10 2 8 0 gfd 7 0 8 10 2 8 0 gfd 8 0 8 10 2 8 0 gfd 9 0 8 10 2 8 0 gfd 10 0 8 10 2 8 0 gfd 11 0 8 10 2 8 0 gfd 12 0 0 0 0 0 0 gfd 13 0 0 0 0 0 0 gfd 14 0 0 0 0 0 0 gfd 15 0 0 0 0 0 0 gfd 16 0 0 0 0 0 0 gfd 17 0 0 0 0 0 0 gfd 18 0 0 0 0 0 0 gfd 19 0 0 0 0 0 0 gfd 20 0 0 0 0 0 0 gfd 21 0 0 0 0 0 0 gfd 22 0 0 0 0 0 0 gfd 23 0 0 0 0 0 0 gfd 24 0 0 0 0 0 0 gfd 25 0 0 0 0 0 0 gfd 26 0 0 0 0 0 0 gfd 27 0 0 0 0 0 0 gfd 28 0 0 0 0 0 0 gfd 29 0 0 0 0 0 0 gfd 30 0 0 0 0 0 0 gfd 31 0 0 0 0 0 0 gfd 32 0 0 0 0 0 0 gfd 33 0 0 0 0 0 0 gfd 34 0 0 0 0 0 0 gfd 35 0 0 0 0 0 0 gfd 36 0 0 0 0 0 0 gfd 37 0 0 0 0 0 0 gfd 38 0 0 0 0 0 0 gfd 39 0 0 0 0 0 0 gfd 40 0 0 0 0 0 0 gfd 41 0 0 0 0 0 0 gfd 42 0 0 0 0 0 0 gfd 43 0 0 0 0 0 0 gfd 44 0 0 0 0 0 0 gfd 45 0 0 0 0 0 0 gfd 46 0 0 0 0 0 0 gfd 47 0 0 0 0 0 0 gfd 48 0 0 0 0 0 0 gfd 49 0 0 0 0 0 0 gfd 50 0 0 0 0 0 0 gfd 51 0 0 0 0 0 0 gfd 52 0 0 0 0 0 0 gfd 53 0 0 0 0 0 0 gfd 54 0 0 0 0 0 0 gfd 55 0 0 0 0 0 0 gfd 56 0 0 0 0 0 0 gfd 57 0 0 0 0 0 0 gfd 58 0 0 0 0 0 0 gfd 59 0 0 0 0 0 0 gfd 60 0 0 0 0 0 0 gfd 61 0 0 0 0 0 0 gfd 62 0 0 0 0 0 0 gfd 63 0 0 0 0 0 0 gfd 64 0 0 0 0 0 0 gfd 65 0 0 0 0 0 0 gfd 66 0 0 0 0 0 0 gfd 67 0 0 0 0 0 0 gfd 68 0 0 0 0 0 0 gfd 69 0 0 0 0 0 0 gfd 70 0 0 0 0 0 0 gfd 71 0 0 0 0 0 0 gfd 72 0 0 0 0 0 0 gfd 73 0 0 0 0 0 0 gfd 74 0 0 0 0 0 0 gfd 75 0 0 0 0 0 0 gfd 76 0 0 0 0 0 0 gfd 77 0 0 0 0 0 0 gfd 78 0 0 0 0 0 0 gfd 79 0 0 0 0 0 0 gfd 80 0 0 0 0 0 0 gfd 81 0 0 0 0 0 0 gfd 82 0 0 0 0 0 0 gfd 83 0 0 0 0 0 0 gfd 84 0 0 0 0 0 0 gfd 85 0 0 0 0 0 0 gfd 86 0 0 0 0 0 0 gfd 87 0 0 0 0 0 0 gfd 88 0 0 0 0 0 0 gfd 89 0 0 0 0 0 0 gfd 90 0 0 0 0 0 0 gfd 91 0 0 0 0 0 0 gfd 92 0 0 0 0 0 0 gfd 93 0 0 0 0 0 0 gfd 94 0 0 0 0 0 0 gfd 95 0 0 0 0 0 0 gfd 96 0 0 0 0 0 0 gfd 97 0 0 0 0 0 0 gfd 98 0 0 0 0 0 0 gfd 99 0 0 0 0 0 0 gfd 100 0 0 0 0 0 0 gfd 101 0 0 0 0 0 0 gfd 102 0 0 0 0 0 0 gfd 103 0 0 0 0 0 0 gfd 104 0 0 0 0 0 0 gfd 105 0 0 0 0 0 0 gfd 106 0 0 0 0 0 0 gfd 107 0 0 0 0 0 0 gfd 108 0 0 0 0 0 0 gfd 109 0 0 0 0 0 0 gfd 110 0 0 0 0 0 0 gfd 111 0 0 0 0 0 0 gfd 112 0 0 0 0 0 0 gfd 113 0 0 0 0 0 0 gfd 114 0 0 0 0 0 0 gfd 115 0 0 0 0 0 0 gfd 116 0 0 0 0 0 0 gfd 117 0 0 0 0 0 0 gfd 118 0 0 0 0 0 0 gfd 119 0 0 0 0 0 0 gfd 120 0 0 0 0 0 0 gfd 121 0 0 0 0 0 0 gfd 122 0 0 0 0 0 0 gfd 123 0 0 0 0 0 0 gfd 124 0 0 0 0 0 0 gfd 125 0 0 0 0 0 0 gfd 126 0 0 0 0 0 0 gfd 127 0 0 0 0 0 0 gfd 128 0 0 0 0 0 0 gfd 129 0 0 0 0 0 0 gfd 130 0 0 0 0 0 0 gfd 131 0 0 0 0 0 0 gfd 132 0 0 0 0 0 0 gfd 133 0 0 0 0 0 0 gfd 134 0 0 0 0 0 0 gfd 135 0 0 0 0 0 0 gfd 136 0 0 0 0 0 0 gfd 137 0 0 0 0 0 0 gfd 138 0 0 0 0 0 0 gfd 139 0 0 0 0 0 0 gfd 140 0 0 0 0 0 0 gfd 141 0 0 0 0 0 0 gfd 142 0 0 0 0 0 0 gfd 143 0 0 0 0 0 0 gfd 144 0 0 0 0 0 0 gfd 145 0 0 0 0 0 0 gfd 146 0 0 0 0 0 0 gfd 147 0 0 0 0 0 0 gfd 148 0 0 0 0 0 0 gfd 149 0 0 0 0 0 0 gfd 150 0 0 0 0 0 0 gfd 151 0 0 0 0 0 0 gfd 152 0 0 0 0 0 0 gfd 153 0 0 0 0 0 0 gfd 154 0 0 0 0 0 0 gfd 155 0 0 0 0 0 0 gfd 156 0 0 0 0 0 0 gfd 157 0 0 0 0 0 0 gfd 158 0 0 0 0 0 0 gfd 159 0 0 0 0 0 0 gfd 160 0 0 0 0 0 0 gfd 161 0 0 0 0 0 0 gfd 162 0 0 0 0 0 0 gfd 163 0 0 0 0 0 0 gfd 164 0 0 0 0 0 0 gfd 165 0 0 0 0 0 0 gfd 166 0 0 0 0 0 0 gfd 167 0 0 0 0 0 0 gfd 168 0 0 0 0 0 0 gfd 169 0 0 0 0 0 0 gfd 170 0 0 0 0 0 0 gfd 171 0 0 0 0 0 0 gfd 172 0 0 0 0 0 0 gfd 173 0 0 0 0 0 0 gfd 174 0 0 0 0 0 0 gfd 175 0 0 0 0 0 0 gfd 176 0 0 0 0 0 0 gfd 177 0 0 0 0 0 0 gfd 178 0 0 0 0 0 0 gfd 179 0 0 0 0 0 0 gfd 180 0 0 0 0 0 0 gfd 181 0 0 0 0 0 0 gfd 182 0 0 0 0 0 0 gfd 183 0 0 0 0 0 0 gfd 184 0 0 0 0 0 0 gfd 185 0 0 0 0 0 0 gfd 186 0 0 0 0 0 0 gfd 187 0 0 0 0 0 0 gfd 188 0 0 0 0 0 0 gfd 189 0 0 0 0 0 0 gfd 190 0 0 0 0 0 0 gfd 191 0 0 0 0 0 0 gfd 192 0 0 0 0 0 0 gfd 193 0 0 0 0 0 0 gfd 194 0 0 0 0 0 0 gfd 195 0 0 0 0 0 0 gfd 196 0 0 0 0 0 0 gfd 197 0 0 0 0 0 0 gfd 198 0 0 0 0 0 0 gfd 199 0 0 0 0 0 0 gfd 200 0 0 0 0 0 0 gfd 201 0 0 0 0 0 0 gfd 202 0 0 0 0 0 0 gfd 203 0 0 0 0 0 0 gfd 204 0 0 0 0 0 0 gfd 205 0 0 0 0 0 0 gfd 206 0 0 0 0 0 0 gfd 207 0 0 0 0 0 0 gfd 208 0 0 0 0 0 0 gfd 209 0 0 0 0 0 0 gfd 210 0 0 0 0 0 0 gfd 211 0 0 0 0 0 0 gfd 212 0 0 0 0 0 0 gfd 213 0 0 0 0 0 0 gfd 214 0 0 0 0 0 0 gfd 215 0 0 0 0 0 0 gfd 216 0 0 0 0 0 0 gfd 217 0 0 0 0 0 0 gfd 218 0 0 0 0 0 0 gfd 219 0 0 0 0 0 0 gfd 220 0 0 0 0 0 0 gfd 221 0 0 0 0 0 0 gfd 222 0 0 0 0 0 0 gfd 223 0 0 0 0 0 0 gfd 224 0 0 0 0 0 0 gfd 225 0 0 0 0 0 0 gfd 226 0 0 0 0 0 0 gfd 227 0 0 0 0 0 0 gfd 228 0 0 0 0 0 0 gfd 229 0 0 0 0 0 0 gfd 230 0 0 0 0 0 0 gfd 231 0 0 0 0 0 0 gfd 232 0 0 0 0 0 0 gfd 233 0 0 0 0 0 0 gfd 234 0 0 0 0 0 0 gfd 235 0 0 0 0 0 0 AIO I/O vps: class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops fifo 16 0 i 0.0 0 0 0 0 1 0.0 0 0 kio -1 0 i 0.5 2893 2876 17 0 6284 0.5 0 0 kio -1 1 i 0.4 2681 2603 78 0 5856 0.5 0 0 kio -1 2 i 0.0 101 42 59 0 173 0.6 0 0 kio -1 3 i 0.0 3 1 2 0 6 0.5 0 0 kio -1 4 i 0.0 2 1 1 0 4 0.5 0 0 kio -1 5 i 0.0 1 1 0 0 2 0.5 0 0 kio -1 6 i 0.0 42 0 42 0 28 1.5 0 0 msc 15 0 i 0.0 7 0 0 0 6 1.2 0 7 aio 14 0 i 0.1 792 20 22 0 774 1.0 0 0 aio 17 1 i 0.0 1 0 0 0 3 0.3 0 0 aio 25 2 i 0.0 2 0 0 0 2 1.0 0 0 aio 26 3 i 0.0 1 0 0 0 2 0.5 0 0 aio 27 4 i 0.0 1 0 0 0 2 0.5 0 0 aio 28 5 i 0.0 0 0 0 0 2 0.0 0 0 aio 29 6 i 0.0 0 0 0 0 2 0.0 0 0 aio 30 7 i 0.0 0 0 0 0 2 0.0 0 0 pio 13 0 i 0.0 0 0 0 0 1 0.0 0 0 lio 12 0 i 0.0 0 0 0 0 1 0.0 0 0 --20cf3071cbd686fcb204a0be1a6a

IO big buffer usage summary: class reads writes pages ops pgs/op holes hl-ops hls/op pages ops pgs/op fifo 0 0 0.00 0 0 0.00 0 0 0.00 drda_dbg 0 0 0.00 0 0 0.00 0 0 0.00 sqli_dbg 0 0 0.00 0 0 0.00 0 0 0.00 kio 1163589 5524 210.64 0 0 0.00 310 199 1.56 adt 0 0 0.00 0 0 0.00 0 0 0.00 msc 0 0 0.00 0 0 0.00 0 0 0.00 aio 8 8 1.00 0 0 0.00 32 20 1.60 pio 0 0 0.00 0 0 0.00 0 0 0.00 lio 0 0 0.00 0 0 0.00 0 0 0.00 --20cf3071cbd647d1eb04a0be1ff0

I agree that dd is a very bad comparison when it comes to Databases but I haven't ever see it be so different when compared. I did try to share a few of the onstat commands but they are so long they don't make it out to the world. I will see if I can get some of this info out to share. This is a test system so we don't have much going on. I'm going to follow along the lines of overhead due to size of the SAN block. Compellent have very few supported block sizes because of it's tiering. Odd thing is I went back and looked we had the same performance when dealing with IBM FastT 900 with a 32K block SAN... I do like the queue depth set higher and will do so for performance in other areas. I hear there are some performance challenges with the P7 but other wise it is very nice. IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 1 days 01:50:09 -- 1742592 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 11058 1169820 1158351 99.44 9195 12449 93517 90.17 isamtot open start read write rewrite delete commit rollbk 2587165 13356 36898 2334115 21445 4942 2753 12556 0 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 192.24 44.15 120 90 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 291 0 226331 0 0 30 4132 7614 ixda-RA idx-RA da-RA RA-pgsused lchwaits 291 0 1681 1935 2386 On Mon, Apr 11, 2011 at 4:05 PM, Art Kagel <art.kagel@gmail.com> wrote: > The only obvious problem that I see is the 2MB stripe block size on the > disk array. Unlike filesystems, Informix performs best with small stripe > blocks ideally in the 32K to 64K range. That is because Informix only ever > either reads or writes a single page (4K on AIX as you know) or what's known > as a Big Read which is eight pages (so 32K). That may explain at least some > of the reduction in IO rates when using Informix versus dd in either of two > apparently opposite ways: 1) Informix is pulling in 2MB every time it asks > for 4K that is causing a 500x hit on bandwidth reading unnecessary data. - > or - 2) When Informix asks for 4K and 4MB is read, any following sequential > reads are satisfied from the controller cache not from the SAN itself so the > SAN statistics are misleading. > > HUH? What I'm saying is that either you are wasting lots of cache pages > and available IO bandwidth on 492 - 499 unread Informix pages with each IO, > or your actual data rate as experienced by Informix is much higher than the > SAN is seeing. The final arbitrator of which is truth is the query > performance which you are saying is running 10x slower than raw IO on the > devices using dd. No database will every reach dd's simplicity and speed, > but this isn't even close. I'm going to go for the wasted bandwidth > interpretation here and say the there is a real slowdown, not a phantom > caused by looking at the wrong level of the system. > > The remedy is to rebuild the disk array using a 32K or 64K block size. > Also look at reducing your RA_ parameters in the ONCONFIG file. I have not > seen the output from onstat -p or onstat -g iov but I suspect you are also > experiencing excessive read-ahead within Informix, but that's probably not > the main problem. > > Oh, and stick with increasing the AIX IO queue depth to 64 or more. On the > system I was having a problem on it was set to only 8, but 64 seemed to work > very well in combination with other changes (most related to tuning AIX > properly for running Informix on the P7 processors - so that doesn't apply > to you). > > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 1:07 PM, Eric Rowell <erowell@gmail.com> wrote: > >> ART thank you for your questions. There was a second half to my post >> which was lost. There are the answers to your questions: >> >> RAW DIsks >> >> AIX IO Queue Depth: 32 (has been set to 256 with no difference in >> Informix) >> >> RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 >> Disk, 2M Block, 4G FC Drives 15K. >> >> The Temp DB are on difference disk on the same SAN. >> >> SAN cache 3.5GB per controller (2 Controllers) >> >> Table has about 5GB of data >> >> Currently 4K page (I have tried the same process with a 12K and 16K >> page, Margin of Error performance change 1-2%) >> >> IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 >> days 23:07:25 -- 1742592 Kbytes>> Segment Summary: >> id key addr size ovhd class >> blkused blkfree >> 7340042 52574801 700000010000000 1681735680 20141248 R* >> 410576 4 >> 7340044 52574802 700000080000000 98304000 1153600 V >> 16813 7187 >> 7340043 52574803 700000090000000 4374528 52672 M >> 1067 1 >> Total: - - 1784414208 - - >> 428456 7192 >> >> ** I have increased and decreased the memory values with no difference. >> >> gfd pathname bytes read page reads bytes write page writes >> io/s >> 5 dms_vol01 2409951232 588367 0 0 >> 157.5 >> 37 dms_vol44 256000000 62500 0 0 >> 75.3 >> 116 dms_vol37 768000000 187500 0 0 >> 121.7 >> 186 dms_vol29 256000000 62500 0 0 >> 82.6 >> >> partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd >> bfwrt seqsc rhitratio >> 0xb00002 0 0 0 0 170884 0 0 0 1 >> 0 1 4294930196 >> * I think this ppf was after the query was completed. >> >> The odd thing to me is when testing under linux I see a performance >> difference when changing from 4K to 16K blocks. Other odd thing is >> how fast and many io/s I can do from the OS compared to within >> Informix. I expect over-head but not going from 75MB/s to 5MB/s. >> >> >> >> On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: >> > Are the chunks using RAW device, COOKED device, or COOKED filesystem >> files? >> > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable >> > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is >> the >> > AIX IO Queue Depth set to (is the that "32" below)? Are the temp >> dbspaces >> > on the same physical SAN structure as the data dbspaces for this table? >> > What is the underlying structure of the physical array containing the >> data >> > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on >> the >> > SAN? How much data is in this table? Pagesize of the dbspace(s) >> > containing the table's partition(s)? >> > >> > What are the IO service times on the chunks as reported by Informix >> (onstat >> > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache >> for >> > thi

Bob, On Mon, Apr 11, 2011 at 4:36 PM, rroussey@comcast.net <rroussey@comcast.net>wrote: > How many LUNs are you hitting? How many dbspaces / chunks are on those > LUNs? > Does Compellent have a utility to show queue depth on the SAN? You may have > too many threads hitting a lot of chunks on one big LUN and backing up > those > queues? > > Bob > > ----- Original Message ----- > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Sent: Monday, April 11, 2011 4:06:01 PM > Subject: Re: IDS 11.50/AIX 5.3 Slowness Issue? [23383] > > The only obvious problem that I see is the 2MB stripe block size on the > disk > array. Unlike filesystems, Informix performs best with small stripe blocks > ideally in the 32K to 64K range. That is because Informix only ever either > reads or writes a single page (4K on AIX as you know) or what's known as a > Big Read which is eight pages (so 32K). That may explain at least some of > the reduction in IO rates when using Informix versus dd in either of two > apparently opposite ways: 1) Informix is pulling in 2MB every time it asks > for 4K that is causing a 500x hit on bandwidth reading unnecessary data. - > or - 2) When Informix asks for 4K and 4MB is read, any following sequential > reads are satisfied from the controller cache not from the SAN itself so > the > SAN statistics are misleading. > > HUH? What I'm saying is that either you are wasting lots of cache pages and > available IO bandwidth on 492 - 499 unread Informix pages with each IO, or > your actual data rate as experienced by Informix is much higher than the > SAN > is seeing. The final arbitrator of which is truth is the query performance > which you are saying is running 10x slower than raw IO on the devices using > dd. No database will every reach dd's simplicity and speed, but this isn't > even close. I'm going to go for the wasted bandwidth interpretation here > and say the there is a real slowdown, not a phantom caused by looking at > the > wrong level of the system. > > The remedy is to rebuild the disk array using a 32K or 64K block size. Also > look at reducing your RA_ parameters in the ONCONFIG file. I have not seen > the output from onstat -p or onstat -g iov but I suspect you are also > experiencing excessive read-ahead within Informix, but that's probably not > the main problem. > > Oh, and stick with increasing the AIX IO queue depth to 64 or more. On the > system I was having a problem on it was set to only 8, but 64 seemed to > work > very well in combination with other changes (most related to tuning AIX > properly for running Informix on the P7 processors - so that doesn't apply > to you). > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 1:07 PM, Eric Rowell <erowell@gmail.com> wrote: > > > ART thank you for your questions. There was a second half to my post > > which was lost. There are the answers to your questions: > > > > RAW DIsks > > > > AIX IO Queue Depth: 32 (has been set to 256 with no difference in > > Informix) > > > > RAID 10 (Compellent will Tier Data but this is set to a fixed Tier), 9 > > Disk, 2M Block, 4G FC Drives 15K. > > > > The Temp DB are on difference disk on the same SAN. > > > > SAN cache 3.5GB per controller (2 Controllers) > > > > Table has about 5GB of data > > > > Currently 4K page (I have tried the same process with a 12K and 16K > > page, Margin of Error performance change 1-2%) > > > > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 > > days 23:07:25 -- 1742592 Kbytes> > Segment Summary: > > id key addr size ovhd class > > blkused blkfree > > 7340042 52574801 700000010000000 1681735680 20141248 R* > > 410576 4 > > 7340044 52574802 700000080000000 98304000 1153600 V > > 16813 7187 > > 7340043 52574803 700000090000000 4374528 52672 M > > 1067 1 > > Total: - - 1784414208 - - > > 428456 7192 > > > > ** I have increased and decreased the memory values with no difference. > > > > gfd pathname bytes read page reads bytes write page writes > > io/s > > 5 dms_vol01 2409951232 588367 0 0 > > 157.5 > > 37 dms_vol44 256000000 62500 0 0 > > 75.3 > > 116 dms_vol37 768000000 187500 0 0 > > 121.7 > > 186 dms_vol29 256000000 62500 0 0 > > 82.6 > > > > partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd > > bfwrt seqsc rhitratio > > 0xb00002 0 0 0 0 170884 0 0 0 1 > > 0 1 4294930196 > > * I think this ppf was after the query was completed. > > > > The odd thing to me is when testing under linux I see a performance > > difference when changing from 4K to 16K blocks. Other odd thing is > > how fast and many io/s I can do from the OS compared to within > > Informix. I expect over-head but not going from 75MB/s to 5MB/s. > > > > > > > > On Mon, Apr 11, 2011 at 12:38 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Are the chunks using RAW device, COOKED device, or COOKED filesystem > > files? > > > If COOKED FILES, do you have O_DIRECT enabled and set to "2" to enable > > > CONCURRENT_IO? If COOKED FILES, what is the filesystem type and what is > > the > > > AIX IO Queue Depth set to (is the that "32" below)? Are the temp > > dbspaces > > > on the same physical SAN structure as the data dbspaces for this table? > > > What is the underlying structure of the physical array containing the > > data > > > chunk(s) (ie RAID level, stripe block size, etc.)? How much cache on > the > > > SAN? How much data is in this table? Pagesize of the dbspace(s) > > > containing the table's partition(s)? > > > > > > What are the IO service times on the chunks as reported by Informix > > (onstat > > > -g iof)? On the tables (onstat -g ppf)? How large is the buffer cache > > for > > > this table's dbspace(s)? > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Apr 11, 2011 at 12:20 PM, Eric Rowell <erowell@gmail.com> > wrote: > > >> > > >> OK I have sent the following to support but because it is a > > >> performance issue we are on a slo

Bob, I will look into the SAN side of the queue depth... When looking from the OS side we don't over run the local queue depth. I have run tests off hours as well during the day with no difference it number of requests or amount of data returned. On Mon, Apr 11, 2011 at 4:36 PM, rroussey@comcast.net <rroussey@comcast.net>wrote: > How many LUNs are you hitting? How many dbspaces / chunks are on those > LUNs? > Does Compellent have a utility to show queue depth on the SAN? You may have > too many threads hitting a lot of chunks on one big LUN and backing up > those > queues? > > Bob > > --20cf307f32ba7853d604a0be68dd

It appears the onstat -g ioa is cut off. My favorite and I feel the most useful part of the onstat -g ioa (is the sub option onstat -g iof). It will tell you the average service time of the device. It does not matter how the device is broken up under the covers, if your average service time is lightening fast you are generally OK. onstat -g iof AIO global files: gfd pathname bytes read page reads bytes write page writes io/s 3 rootdbs 1001472 489 126976 62 7502.7 op type count avg. time seeks 0 N/A reads 427 0.0000 writes 41 0.0015 kaio_reads 0 N/A kaio_writes 0 N/A John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/12/2011 12:57:52 PM: > [image removed] > > Re: IDS 11.50/AIX 5.3 Slowness Issue? [23389] > > Eric Rowell > > to: > > ids > > 04/12/2011 12:59 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > onstat -g ioa> > IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up > 01:46:53 -- 1742592 Kbytes> > AIO global info: > 9 aio classes > 236 open files > 256 max global files > > AIO I/O queues: > q name/id len maxlen totalops dskread dskwrite dskcopy > fifo 0 0 0 0 0 0 0 > drda_dbg 0 0 0 0 0 0 0 > sqli_dbg 0 0 0 0 0 0 0 > kio 0 0 16 2920 2903 17 0 > kio 1 0 16 2718 2640 78 0 > kio 2 0 16 122 56 66 0 > kio 3 0 1 3 1 2 0 > kio 4 0 1 2 1 1 0 > kio 5 0 1 1 1 0 0 > kio 6 0 16 57 0 57 0 > adt 0 0 0 0 0 0 0 > msc 0 0 1 7 0 0 0 > aio 0 0 6 769 12 2 0 > pio 0 0 0 0 0 0 0 > lio 0 0 0 0 0 0 0 > gfd 3 0 0 0 0 0 0 > gfd 4 0 8 10 2 8 0 > gfd 5 0 8 10 2 8 0 > gfd 6 0 8 10 2 8 0 > gfd 7 0 8 10 2 8 0 > gfd 8 0 8 10 2 8 0 > gfd 9 0 8 10 2 8 0 > gfd 10 0 8 10 2 8 0 > gfd 11 0 8 10 2 8 0 > gfd 12 0 0 0 0 0 0 > gfd 13 0 0 0 0 0 0 > gfd 14 0 0 0 0 0 0 > gfd 15 0 0 0 0 0 0 > gfd 16 0 0 0 0 0 0 > gfd 17 0 0 0 0 0 0 > gfd 18 0 0 0 0 0 0 > gfd 19 0 0 0 0 0 0 > gfd 20 0 0 0 0 0 0 > gfd 21 0 0 0 0 0 0 > gfd 22 0 0 0 0 0 0 > gfd 23 0 0 0 0 0 0 > gfd 24 0 0 0 0 0 0 > gfd 25 0 0 0 0 0 0 > gfd 26 0 0 0 0 0 0 > gfd 27 0 0 0 0 0 0 > gfd 28 0 0 0 0 0 0 > gfd 29 0 0 0 0 0 0 > gfd 30 0 0 0 0 0 0 > gfd 31 0 0 0 0 0 0 > gfd 32 0 0 0 0 0 0 > gfd 33 0 0 0 0 0 0 > gfd 34 0 0 0 0 0 0 > gfd 35 0 0 0 0 0 0 > gfd 36 0 0 0 0 0 0 > gfd 37 0 0 0 0 0 0 > gfd 38 0 0 0 0 0 0 > gfd 39 0 0 0 0 0 0 > gfd 40 0 0 0 0 0 0 > gfd 41 0 0 0 0 0 0 > gfd 42 0 0 0 0 0 0 > gfd 43 0 0 0 0 0 0 > gfd 44 0 0 0 0 0 0 > gfd 45 0 0 0 0 0 0 > gfd 46 0 0 0 0 0 0 > gfd 47 0 0 0 0 0 0 > gfd 48 0 0 0 0 0 0 > gfd 49 0 0 0 0 0 0 > gfd 50 0 0 0 0 0 0 > gfd 51 0 0 0 0 0 0 > gfd 52 0 0 0 0 0 0 > gfd 53 0 0 0 0 0 0 > gfd 54 0 0 0 0 0 0 > gfd 55 0 0 0 0 0 0 > gfd 56 0 0 0 0 0 0 > gfd 57 0 0 0 0 0 0 > gfd 58 0 0 0 0 0 0 > gfd 59 0 0 0 0 0 0 > gfd 60 0 0 0 0 0 0 > gfd 61 0 0 0 0 0 0 > gfd 62 0 0 0 0 0 0 > gfd 63 0 0 0 0 0 0 > gfd 64 0 0 0 0 0 0 > gfd 65 0 0 0 0 0 0 > gfd 66 0 0 0 0 0 0 > gfd 67 0 0 0 0 0 0 > gfd 68 0 0 0 0 0 0 > gfd 69 0 0 0 0 0 0 > gfd 70 0 0 0 0 0 0 > gfd 71 0 0 0 0 0 0 > gfd 72 0 0 0 0 0 0 > gfd 73 0 0 0 0 0 0 > gfd 74 0 0 0 0 0 0 > gfd 75 0 0 0 0 0 0 > gfd 76 0 0 0 0 0 0 > gfd 77 0 0 0 0 0 0 > gfd 78 0 0 0 0 0 0 > gfd 79 0 0 0 0 0 0 > gfd 80 0 0 0 0 0 0 > gfd 81 0 0 0 0 0 0 > gfd 82 0 0 0 0 0 0 > gfd 83 0 0 0 0 0 0 > gfd 84 0 0 0 0 0 0 > gfd 85 0 0 0 0 0 0 > gfd 86 0 0 0 0 0 0 > gfd 87 0 0 0 0 0 0 > gfd 88 0 0 0 0 0 0 > gfd 89 0 0 0 0 0 0 > gfd 90 0 0 0 0 0 0 > gfd 91 0 0 0 0 0 0 > gfd 92 0 0 0 0 0 0 > gfd 93 0 0 0 0 0 0 > gfd 94 0 0 0 0 0 0 > gfd 95 0 0 0 0 0 0 > gfd 96 0 0 0 0 0 0 > gfd 97 0 0 0 0 0 0 > gfd 98 0 0 0 0 0 0 > gfd 99 0 0 0 0 0 0 > gfd 100 0 0 0 0 0 0 > gfd 101 0 0 0 0 0 0 > gfd 102 0 0 0 0 0 0 > gfd 103 0 0 0 0 0 0 > gfd 104 0 0 0 0 0 0 > gfd 105 0 0 0 0 0 0 > gfd 106 0 0 0 0 0 0 > gfd 107 0 0 0 0 0 0 > gfd 108 0 0 0 0 0 0 > gfd 109 0 0 0 0 0 0 > gfd 110 0 0 0 0 0 0 > gfd 111 0 0 0 0 0 0 > gfd 112 0 0 0 0 0 0 > gfd 113 0 0 0 0 0 0 > gfd 114 0 0 0 0 0 0 > gfd 115 0 0 0 0 0 0 > gfd 116 0 0 0 0 0 0 > gfd 117 0 0 0 0 0 0 > gfd 118 0 0 0 0 0 0 > gfd 119 0 0 0 0 0 0 > gfd 120 0 0 0 0 0 0 > gfd 121 0 0 0 0 0 0 > gfd 122 0 0 0 0 0 0 > gfd 123 0 0 0 0 0 0 > gfd 124 0 0 0 0 0 0 > gfd 125 0 0 0 0 0 0 > gfd 126 0 0 0 0 0 0 > gfd 127 0 0 0 0 0 0 > gfd 128 0 0 0 0 0 0 > gfd 129 0 0 0 0 0 0 > gfd 130 0 0 0 0 0 0 > gfd 131 0 0 0 0 0 0 > gfd 132 0 0 0 0 0 0 > gfd 133 0 0 0 0 0 0 > gfd 134 0 0 0 0 0 0 > gfd 135 0 0 0 0 0 0 > gfd 136 0 0 0 0 0 0 > gfd 137 0 0 0 0 0 0 > gfd 138 0 0 0 0 0 0 > gfd 139 0 0 0 0 0 0 > gfd 140 0 0 0 0 0 0 > gfd 141 0 0 0 0 0 0 > gfd 142 0 0 0 0 0 0 > gfd 143 0 0 0 0 0 0 > gfd 144 0 0 0 0 0 0 > gfd 145 0 0 0 0 0 0 > gfd 146 0 0 0 0 0 0 > gfd 147 0 0 0 0 0 0 > gfd 148 0 0 0 0 0 0 > gfd 149 0 0 0 0 0 0 > gfd 150 0 0 0 0 0 0 > gfd 151 0 0 0 0 0 0 > gfd 152 0 0 0 0 0 0 > gfd 153 0 0 0 0 0 0 > gfd 154 0 0 0 0 0 0 > gfd 155 0 0 0 0 0 0 > gfd 156 0 0 0 0 0 0 > gfd 157 0 0 0 0 0 0 > gfd 158 0 0 0 0 0 0 > gfd 159 0 0 0 0 0 0 > gfd 160 0 0 0 0 0 0 > gfd 161 0 0 0 0 0 0 > gfd 162 0 0 0 0 0 0 > gfd 163 0 0 0 0 0 0 > gfd 164 0 0 0 0 0 0 > gfd 165 0 0 0 0 0 0 > gfd 166 0 0 0 0 0 0 > gfd 167 0 0 0 0 0 0 > gfd 168 0 0 0 0 0 0 > gfd 169 0 0 0 0 0 0 > gfd 170 0 0 0 0 0 0 > gfd 171 0 0 0 0 0 0 > gfd 172 0 0 0 0 0 0 > gfd 173 0 0 0 0 0 0 > gfd 174 0 0 0 0 0 0 > gfd 175 0 0 0 0 0 0 > gfd 176 0 0 0 0 0 0 > gfd 177 0 0 0 0 0 0 > gfd 178 0 0 0 0 0 0 > gfd 179 0 0 0 0 0 0 > gfd 180 0 0 0 0 0 0 > gfd 181 0 0 0 0 0 0 > gfd 182 0 0 0 0 0 0 > gfd 183 0 0 0 0 0 0 > gfd 184 0 0 0 0 0 0 > gfd 185 0 0 0 0 0 0 > gfd 186 0 0 0 0 0 0 > gfd 187 0 0 0 0 0 0 > gfd 188 0 0 0 0 0 0 > gfd 189 0 0 0 0 0 0 > gfd 190 0 0 0 0 0 0 > gfd 191 0 0 0 0 0 0 > gfd 192 0 0 0 0 0 0 > gfd 193 0 0 0 0 0 0 > gfd 194 0 0 0 0 0 0 > gfd 195 0 0 0 0 0 0 > gfd 196 0 0 0 0 0 0 > gfd 197 0 0 0 0 0 0 > gfd 198 0 0 0 0 0 0 > gfd 199 0 0 0 0 0 0 > gfd 200 0 0 0 0 0 0 > gfd 201 0 0 0 0 0 0 > gfd 202 0 0 0 0 0 0 > gfd 203 0 0 0 0 0 0 > gfd 204 0 0 0 0 0 0 > gfd 205 0 0 0 0 0 0 > gfd 206 0 0 0 0 0 0 > gfd 207 0 0 0 0 0 0 > gfd 208 0 0 0 0 0 0 > gfd 209 0 0 0 0 0 0 > gfd 210 0 0 0 0 0 0 > gfd 211 0 0 0 0 0 0 > gfd 212 0 0 0 0 0 0 > gfd 213 0 0 0 0 0 0 > gfd 214 0 0 0 0 0 0 > gfd 215 0 0 0 0 0 0 > gfd 216 0 0 0 0 0 0 > gfd 217 0 0 0 0 0 0 > gfd 218 0 0 0 0 0 0 > gfd 219 0 0 0 0 0 0 > gfd 220 0 0 0 0 0 0 > gfd 221

Long time no reply... Well here is where I'm at... After looking at a few more things I was able to see that it isn't an issue with the SAN access of the database. Which was the only major change of late so was the first thing to be reviewed. Thank you everyone for the ideas and areas to look at... In the simplest form there is a large spike of disk activity when the session starts pulling data but the user session never sees that at the application side returning data. When a second session is started the SAN activity doubles and for each session after it increase about 5-10%. The server and the application continue to only ever see about 2.5M/sec each no matter the speed of the disk, available memory, or CPU. As for increasing to 16K pages the SAN returned data to the Database engine faster but the user didn't see any gain at the application. So now off to start looking at communications, but I feel like I'm missing something important and simple. The query takes the same amount of time if using shared memory or TCP/IP on local loopback (application is local). Eric B. Rowell --20cf300257b8eb463904a3a6d80e