Weird one

<Cross posted to CDI & IDS Forum> OK, an oddity for you. I'm doing some performance and tuning testing using multiple copies of dbload. I'll tell you what I'm dealing with. Anyone with any idea as to why the engine is behaving as it is please pipe in. Platform: Linux 2.6.9-67.ELsmp, x86-32 with 2 single core 3.4GHZ processors, single disk spindle for IDS another for the OS IDS Vers: 10.00.UC8 OK, so I'm getting reasonable timings running various numbers of load clients against the engine with 1, 2, 4, 6, & 8 CPU VPs truncating the table between runs. I notice in TOP that at some point in the run IO Wait goes way up and CPU consumption way down for 10-30 seconds then back to 'normal'. I figure, "Oh! Extends are being added." So after completing the set of timing runs I change the script to drop and recreate the table with an extent size sufficient for the loaded data in the initial load instead of the truncate and run the tests again expecting to see that the runtimes have decrease by 10-20 seconds per run. NO! the runtimes INCREASED by 16 to 64 seconds! Yes, I checked that there was indeed still a single extent the size of the initial extent size allocation after each load. Any ideas as to why this should be? -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Art: Are you loading into a logging database? If so do you make sure a checkpoint completes after dropping the table, but before you start loading the data? This will make sure that your inserts to the end? of the table do not need to physically log any data. John F. Miller III STSM, Support Architect miller3@us.ibm.com IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/24/2008 05:51:59 PM: > <Cross posted to CDI & IDS Forum> > > OK, an oddity for you. I'm doing some performance and tuning testing using > multiple copies of dbload. > > I'll tell you what I'm dealing with. Anyone with any idea as to why the > engine is behaving as it is please pipe in. > > Platform: Linux 2.6.9-67.ELsmp, x86-32 with 2 single core 3.4GHZ processors, > single disk spindle for IDS another for the OS > IDS Vers: 10.00.UC8 > > OK, so I'm getting reasonable timings running various numbers of load > clients against the engine with 1, 2, 4, 6, & 8 CPU VPs truncating the table > between runs. > > I notice in TOP that at some point in the run IO Wait goes way up and CPU > consumption way down for 10-30 seconds then back to 'normal'. I figure, > "Oh! Extends are being added." So after completing the set of timing runs > I change the script to drop and recreate the table with an extent size > sufficient for the loaded data in the initial load instead of the truncate > and run the tests again expecting to see that the runtimes have decrease by > 10-20 seconds per run. NO! the runtimes INCREASED by 16 to 64 seconds! > > Yes, I checked that there was indeed still a single extent the size of the > initial extent size allocation after each load. > > Any ideas as to why this should be? > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Unlogged database, forgot to mention that. Art On Thu, Sep 25, 2008 at 12:53 AM, John Miller iii <miller3@us.ibm.com>wrote: > Art: > > Are you loading into a logging database? If so do you > make sure a checkpoint completes after dropping > the table, but before you start loading the data? > > This will make sure that your inserts to the end? > of the table do not need to physically log any data. > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 09/24/2008 05:51:59 PM: > > > <Cross posted to CDI & IDS Forum> > > > > OK, an oddity for you. I'm doing some performance and tuning testing > using > > multiple copies of dbload. > > > > I'll tell you what I'm dealing with. Anyone with any idea as to why the > > engine is behaving as it is please pipe in. > > > > Platform: Linux 2.6.9-67.ELsmp, x86-32 with 2 single core 3.4GHZ > processors, > > single disk spindle for IDS another for the OS > > IDS Vers: 10.00.UC8 > > > > OK, so I'm getting reasonable timings running various numbers of load > > clients against the engine with 1, 2, 4, 6, & 8 CPU VPs truncating the > table > > between runs. > > > > I notice in TOP that at some point in the run IO Wait goes way up and CPU > > > consumption way down for 10-30 seconds then back to 'normal'. I figure, > > "Oh! Extends are being added." So after completing the set of timing runs > > > I change the script to drop and recreate the table with an extent size > > sufficient for the loaded data in the initial load instead of the > truncate > > and run the tests again expecting to see that the runtimes have decrease > by > > 10-20 seconds per run. NO! the runtimes INCREASED by 16 to 64 seconds! > > > > Yes, I checked that there was indeed still a single extent the size of > the > > initial extent size allocation after each load. > > > > Any ideas as to why this should be? > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > those > > opinions reflect those of other individuals affiliated with any entity > with > > which I am affiliated nor those of the entities themselves. > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Sounds like it gets worse as you increase VP threads. Are there any threads waiting to run during this "timeout"? As the thread saturation goes up you have to wonder about the pcode running on the CPU(s). In a multi-cpu enviornment OS's may handle CPU instruction look-ahead in various ways. Perhaps your experiencing a rollback at the instruction code level.

Ralph, I think that you may have missed the point. The runs take longer for the same client session count when there is a single pre-allocated extent large enough to hold all of the data than when multiple smaller extents have to be allocated. Note also (forgot to mention) that this test is being performed with several 36byte indexes in place, so any extent allocation in the table itself also means that there are an nearly equal number of extent allocations being performed for each index and that they are unlikely to be contiguous. So, I expected that the preallocation of the extents would have made a major improvement in load times. (Yes, I know the load will be faster without the indexes, but that's another, separate, part of the testing suite.) Art On Thu, Sep 25, 2008 at 7:41 AM, RALPH GENTRY <gentrym@staples.com> wrote: > Sounds like it gets worse as you increase VP threads. Are there any threads > waiting to run during this "timeout"? As the thread saturation goes up you > have to wonder about the pcode running on the CPU(s). In a multi-cpu > enviornment OS's may handle CPU instruction look-ahead in various ways. > Perhaps your experiencing a rollback at the instruction code level. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Art, 2 questions about your load tests... 1) Are you using AIO or KAIO for this test? 2) You state that your using various numbers of load clients and that you have several indexes in place. Are all these dbloads adding data concurrently to the same table? If you have multiple dbloads adding data to a single table, how consistent is the sequencing of record additions to the table from test to test? The sequencing of record additions relative to the index keys could have a significant impact to the work required to maintain the indexes.

See below: Art On Thu, Sep 25, 2008 at 2:09 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > Art, > 2 questions about your load tests... > 1) Are you using AIO or KAIO for this test? AIO VPs (48 for this particular test. After a 12 client test each VP exhibited 1.6-2.9 io/wup and 6.0-14.0 io/sec on onstat -g iov). Direct IO is not linked into the kernel on this machine and the chunks are cooked files all on a single spindle. > > 2) You state that your using various numbers of load clients and that you > have > several indexes in place. Are all these dbloads adding data concurrently to > the same table? If you have multiple dbloads adding data to a single table, > how consistent is the sequencing of record additions to the table from test > to > test? The sequencing of record additions relative to the index keys could > have > a significant impact to the work required to maintain the indexes. Yes, I am running from 2 to 12 copies of dbload all loading the same delimited input file with 58,318 rows into a table with a rowsize approximately 4K in a 4K dbspace. In the initial tests 4, 8, 10, and 12 client runs would require the table to allocate several additional extents to the table and to each index as the extent and next sizes were initially set at 9000 & 1000 respectively and the TRUNCATE preceding each run would deallocate all of the previously allocated pages. The last set of runs which are confusing me I replaced the TRUNCATE with DROP TABLE and CREATE TABLE.... EXTENT SIZE 4000000 NEXT SIZE 1000000. For this run none of the test runs needed additional extents added to the base table and I have verify that the indexes each fit into the initially calculated extent. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

In AIO environments, overallocating AIOVPs and/or CPUVPs can lead to IO Wait problems and substantial performance degradation. The AIOVPs end up waitting on each other for physical disk access. Since the AIOVPs occupy cpu resources to get to the disk, it backs up that physical resource too. Once you hit the physical resource limits, addition of more VPs simply adds to the cpu cycles required to juggle them all and compounds the problem. High io/wup can be an indicator of this problem as aiovps wait on disk and more work is piled up in their queue to manage. This bottleneck can result in variable levels of performance degradation. On a 2 processor box, I'd suggest running a test with say 2 CPUVPs, 10 AIOVPs and several more tests with CPUVP/AIOVP alotments ranging up to your current settings. In addition to monitoring io waits, keep an eye on your onstat -R to see if your routinely exceeding LRUMAXDIRTY. And yes, I realize I'm ignoring your correlation of reduced(eliminated) extent allocations to decreased performance. Simply put, it is illogical that an elimination of work would cause a performance degradation. Therefore it is more likely a coincidence and the performance variance is due to other factors.

Or maybe the processing blockage during the extent allocations give the IO subsystems time they need to catch up reducing contention. Could be that. The idea occurred to me this late AM and I'm testing around it. Thanks. BTW, I started with just 1 CPU VP and 18 AIO VPs initial conditions then started testing 2 CPU VPs w/24 AIO VPs and have worked my way up to 8 CPU VPs with 48 AIO VPs. Art On Thu, Sep 25, 2008 at 4:14 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > In AIO environments, overallocating AIOVPs and/or CPUVPs can lead to IO > Wait > problems and substantial performance degradation. The AIOVPs end up > waitting > on each other for physical disk access. Since the AIOVPs occupy cpu > resources > to get to the disk, it backs up that physical resource too. Once you hit > the > physical resource limits, addition of more VPs simply adds to the cpu > cycles > required to juggle them all and compounds the problem. High io/wup can be > an > indicator of this problem as aiovps wait on disk and more work is piled up > in > their queue to manage. This bottleneck can result in variable levels of > performance degradation. On a 2 processor box, I'd suggest running a test > with > say 2 CPUVPs, 10 AIOVPs and several more tests with CPUVP/AIOVP alotments > ranging up to your current settings. In addition to monitoring io waits, > keep > an eye on your onstat -R to see if your routinely exceeding LRUMAXDIRTY. > > And yes, I realize I'm ignoring your correlation of reduced(eliminated) > extent > allocations to decreased performance. Simply put, it is illogical that an > elimination of work would cause a performance degradation. Therefore it is > more likely a coincidence and the performance variance is due to other > factors. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Art Kagel schrieb: > <Cross posted to CDI & IDS Forum> > > OK, an oddity for you. I'm doing some performance and tuning testing using > multiple copies of dbload. > > I'll tell you what I'm dealing with. Anyone with any idea as to why the > engine is behaving as it is please pipe in. > > Platform: Linux 2.6.9-67.ELsmp, x86-32 with 2 single core 3.4GHZ processors, > single disk spindle for IDS another for the OS > IDS Vers: 10.00.UC8 > > OK, so I'm getting reasonable timings running various numbers of load > clients against the engine with 1, 2, 4, 6, & 8 CPU VPs truncating the table > between runs. > > I notice in TOP that at some point in the run IO Wait goes way up and CPU > consumption way down for 10-30 seconds then back to 'normal'. I figure, > "Oh! Extends are being added." So after completing the set of timing runs > I change the script to drop and recreate the table with an extent size > sufficient for the loaded data in the initial load instead of the truncate > and run the tests again expecting to see that the runtimes have decrease by > 10-20 seconds per run. NO! the runtimes INCREASED by 16 to 64 seconds! > > Yes, I checked that there was indeed still a single extent the size of the > initial extent size allocation after each load. > > Any ideas as to why this should be? > Hi Art, sry for joining in so late. I'd suggest to install package sysstat, which gives you sar and iostat. You also need to search the net for 'linux kernel I/O scheduler settings' or some such as there are 4 different I/O schedulers and the default is definitely _not_ what you want when on cooked files. Do you use lvm? Is your mount having options to do write behind? What is your disk-HW like? SATA II or SAS, and how fast is it in the tech specs/data sheet? What is the file system type? My findings are, that it is best to avoid logging file systems => ext2, the dinosaur, is your friend :) There is always bonnie++ to do a bit of testing. Maybe one of the *top utilities is also able to provide I/O details. And there is always sync (IB default is 120 secs, but not sure) and this beast is always good for surprises, especially if memory is huge. Remember those days in SOL9 when I used to do df in a loop, just to see when I/O cools down whe rmming say >2000 files? It used to return instantly, but then was causing heavy I/O for a loooong time ..... HTH dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Thanks Richard, There is only one hard wired SCSI-3 drive and likely not a fast one at that. No LVM just a std partition table. The filesystems are all EXT3 which is not TOO bad. Std scheduler. This is a minimal resources system and I'm tuning that within those limits. New experience that, I'm used to tuning massive systems, so this is fun. Just didn't expect the servers to act counter to common sense and logic. ;-( Art On Thu, Sep 25, 2008 at 7:06 PM, Richard Kofler <richard.kofler@chello.at>wrote: > Art Kagel schrieb: > > <Cross posted to CDI & IDS Forum> > > > > OK, an oddity for you. I'm doing some performance and tuning testing > using > > multiple copies of dbload. > > > > I'll tell you what I'm dealing with. Anyone with any idea as to why the > > engine is behaving as it is please pipe in. > > > > Platform: Linux 2.6.9-67.ELsmp, x86-32 with 2 single core 3.4GHZ > processors, > > single disk spindle for IDS another for the OS > > IDS Vers: 10.00.UC8 > > > > OK, so I'm getting reasonable timings running various numbers of load > > clients against the engine with 1, 2, 4, 6, & 8 CPU VPs truncating the > table > > between runs. > > > > I notice in TOP that at some point in the run IO Wait goes way up and CPU > > consumption way down for 10-30 seconds then back to 'normal'. I figure, > > "Oh! Extends are being added." So after completing the set of timing runs > > I change the script to drop and recreate the table with an extent size > > sufficient for the loaded data in the initial load instead of the > truncate > > and run the tests again expecting to see that the runtimes have decrease > by > > 10-20 seconds per run. NO! the runtimes INCREASED by 16 to 64 seconds! > > > > Yes, I checked that there was indeed still a single extent the size of > the > > initial extent size allocation after each load. > > > > Any ideas as to why this should be? > > > Hi Art, > sry for joining in so late. > I'd suggest to install package sysstat, which gives you sar and iostat. > You also need to search the net for 'linux kernel I/O scheduler settings' > or some such as there are 4 different I/O schedulers and the default is > definitely _not_ what you want when on cooked files. > Do you use lvm? Is your mount having options to do write behind? > What is your disk-HW like? SATA II or SAS, and how fast is it in the > tech specs/data sheet? > What is the file system type? My findings are, that it is best to avoid > logging file systems => ext2, the dinosaur, is your friend :) > There is always bonnie++ to do a bit of testing. > Maybe one of the *top utilities is also able to provide I/O details. > And there is always sync (IB default is 120 secs, but not sure) and > this beast is always good for surprises, especially if memory is huge. > Remember those days in SOL9 when I used to do df in a loop, just to see > when I/O cools down whe rmming say >2000 files? It used to return > instantly, > but > then was causing heavy I/O for a loooong time ..... > > HTH > dic_k > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.