dbexport slow exporting to ramdrive

Hey there, I'm doing an excercise running dbexport using RAMDRIVE as an output folder: dbexport -q -o $DIR $DB -ss $DIR is RAMDRIVE (/dev/ram0 5.7G 3.4G 2.4G 59% /tmp/dbexport) $DB is sei_copy_deal in this case, 3GB database) When running dbexport (which goes directly from engine to the /tmp/dbexport) I see the following: 1. dbexport CPU usage 10-40% 2. oninit process (only one of them) 92-98% (consider it as 100%), the rest oninit process stay calm 3. no big read work - max 5-7MB/s, % of the device's queue used also stays low so it's not a disk reading issue here (checking using # iostat -xm /dev/sdc 1) on /dev/sdc (dbspaces) so assume this is all from informix buffers 4. the test before the dbexport shows: # dd if=/dev/zero of=/tmp/dbexport/file1 bs=2048 count=1000000 1000000+0 records in 1000000+0 records out 2048000000 bytes (2.0 GB) copied, 2.98008 seconds, 687 MB/s 5. load average in 'top' doesn't grow above 2.20 (4 CPUs in the box). Normal (to normal raid disk) dbexport takes 5min15sec. This one (with ramdrive) takes ~3min45sec. I'd need someone's opinion on that. I think that's only one oninit process is serving data from engine to dbexport. So it looks like IDS's problem rather than dbexport. It hits 100% of cpu and cannot go more (understandable). Why IDS doesn't use other CPU VPs to provide data needed by dbexport? - I might be wrong here obviously. Thanks for any suggestions Waldek

You should always post your engine version and platform information so the answers you receive can be most specific. Dbexport is single threaded itself. The session it starts in the server will not use parallelism unless you set PDQPRIORITY > 1 AND your tables are fragmented since the queries that it uses are simple ones against a single table at a time. If you want more parallelism, try using my dbexport replacement utility, myexport, which you can download from the IIUG Software Repository. It has an option, -p, which will attempt to download tables in parallel. If you are using IDS 11.50 or later, you can use the -E option to export using external tables. This will tend to be at least as fast as exporting using the HPLoader, so it should be significantly faster than using dbexport even without -p. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Tue, Dec 21, 2010 at 5:23 AM, WALDEMAR ZNOINSKI <waldek@znoinski.pl>wrote: > Hey there, > > I'm doing an excercise running dbexport using RAMDRIVE as an output folder: > dbexport -q -o $DIR $DB -ss> > $DIR is RAMDRIVE (/dev/ram0 5.7G 3.4G 2.4G 59% /tmp/dbexport) > $DB is sei_copy_deal in this case, 3GB database) > > When running dbexport (which goes directly from engine to the > /tmp/dbexport) I > see the following: > 1. dbexport CPU usage 10-40% > 2. oninit process (only one of them) 92-98% (consider it as 100%), the rest > oninit process stay calm > 3. no big read work - max 5-7MB/s, % of the device's queue used also stays > low > so it's not a disk reading issue here > (checking using # iostat -xm /dev/sdc 1) on /dev/sdc (dbspaces) so assume > this > is all from informix buffers > > 4. the test before the dbexport shows: > # dd if=/dev/zero of=/tmp/dbexport/file1 bs=2048 count=1000000 > 1000000+0 records in > 1000000+0 records out > 2048000000 bytes (2.0 GB) copied, 2.98008 seconds, 687 MB/s > > 5. load average in 'top' doesn't grow above 2.20 (4 CPUs in the box). > > Normal (to normal raid disk) dbexport takes 5min15sec. This one (with > ramdrive) takes ~3min45sec. > > I'd need someone's opinion on that. > I think that's only one oninit process is serving data from engine to > dbexport. So it looks like IDS's problem rather than dbexport. It hits 100% > of > cpu and cannot go more (understandable). Why IDS doesn't use other CPU VPs > to > provide data needed by dbexport? - I might be wrong here obviously. > > Thanks for any suggestions > Waldek > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5c1eb6c62c50497ea061f

Thanks for reply Art. I'm running 11.50FC7 GE on SLES 10.3. My license doesn't cover PDQ unfortunately. In previous versions (10.x) engine didn't force disabling PDQ at the engine start so if you had it enabled in onconfig then you made use of it even in Workgroup edition - we didn't use it obviously! ;-) I'll be probably trying to test your myexport as suggested a while ago by our Informix support.Last news I had about using external tables with your myexport was that it's a work in progress. Can we consider this feature as stable one now? Will that cope with SBLOB tables (both ? - I will give it a try anyway and come back with some conclusions.

Export needs the latest myschema to do external takes, but it works when I test it. Have not tried with SLOBS. Would probably have to modify the export script that myschema builds to define the Target files for the SLOB data though. I will look at that. Art. On Dec 21, 2010 11:17 AM, "WALDEMAR ZNOINSKI" <waldek@znoinski.pl> wrote: > Thanks for reply Art. > I'm running 11.50FC7 GE on SLES 10.3. My license doesn't cover PDQ > unfortunately. In previous versions (10.x) engine didn't force disabling PDQ > at the engine start so if you had it enabled in onconfig then you made use of > it even in Workgroup edition - we didn't use it obviously! ;-) > I'll be probably trying to test your myexport as suggested a while ago by our > Informix support.Last news I had about using external tables with your > myexport was that it's a work in progress. Can we consider this feature as > stable one now? Will that cope with SBLOB tables (both ? - I will give it a > try anyway and come back with some conclusions. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --001636c5c241f65fc30497ef87f5