IDS Forum

Re: What is the best tool to unload records fr....

Posted By: Richard Kofler
Date: Friday, 16 April 2010, at 1:43 p.m.

In Response To: Re: What is the best tool to unload records fr.... (John Miller iii)

Thank you, John - this is great news!

dic_k

John Miller iii schrieb:
> Just to let you know external tables can both read and write records
> in parallel. You will need to ensure a few things are setup, some are
> the same as HPL.
>
> 1. The table you are unloading is fragmented
>
> 2. When executing the insert into select from statement make sure PDQ is
> on. This
>
> is required for both the insert side and read side parallelism.
>
> 3. When creating the external table make sure you specific multiple output
> devices.
>
> CREATE EXTERNAL TABLE unload_cust SAMEAS customer
>
> USING
>
> (
>
> DATAFILES
>
> ('DISK:/tmp/unload_file1.unl','DISK:/tmp/unload_file2.unl','DISK:/tmp/unload_file3.unl'),
>
> other options.....
>
> );
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 04/14/2010 12:23:25 PM:
>
>> [image removed]
>>
>> Re: What is the best tool to unload records fr.... [19691]
>>
>> Richard Kofler
>>
>> to:
>>
>> ids
>>
>> 04/14/2010 12:24 PM
>>
>> Sent by:
>>
>> ids-bounces@iiug.org
>>
>> Please respond to ids
>>
>> Hi John,
>>
>> thank you for mentioning PDQPRIORITY.
>>
>> But my English is rusty and I was not precise enough:
>> What I was not able to archive is to emulate HPLs ability to
>> read AND write in parallel, when input table is fragmented and
>> when using a device array having an appropriate numer of devices.
>>
>> >From IDS V11.50.xC4 docs, HPL Users Guide, pg 6-1:
>> When the HPL unloads data, it assigns records to the devices of
>> a device array in a round-robin fashion.
>>
>> And exacly this behaviour ends up as a defacto parallel rading
>> AND parallel writing, speedwise - if buffers are largish
>> (using -a <iobufsize> & -b <bufsize> on the command line or configure
>> AIOBUFERS, AIOBUFSIZE & STRMBUFERS, STRMBUFFSIZE with appropriate values)
>
>> and if using raw devices
>> anf if your CPUs are fast.
>>
>> BTW, in doing HPL unloads the difference of raw vs. every other type
>> of non raw disks shows the biggest difference in read speed during
>> my testing in the last decade. It is also a VERY GOOD test when
>> evaluating I/O subsystems how much seq read speed they will give.
>> Especially, when the cache of the subsystem is emptied in a few seconds
>> and the bitter truth is visible ;) Or when you read a table fragged
> 64-way.
>> But this is another subject.
>>
>> Testing external tables as an unload target, I was only able to get
>> near of what I wanted to see when I fragment avoidance on the input table
> and
>> invoked paralel unloads for every fragment. But failed to fine tune and
>> archive what I can do using -a, -b on onpload on the command line.
>> Which in fact such unload faster, if pagesize is large and not 2 KB, by
>> reducing number of start I/Os to 50% on the ouput side when table
>> page size is
>> 4 KB instead of 2 KB, and it scales further as expected.
>>
>> [ Now why so much details? When using SSDs, and this is what I do
> nowadays,
>> the number of start-I/Os for writes is a thing to watch! ]
>>
>> dic_k
>>
>> John Miller iii schrieb:
>>> You have to make sure you enable PDQ to get parallelism with
>>> the reading of the fragment non-external tables and have the
>>> associated onconfig setup (which usually are by default).
>>>
>>> In addition, with both HPL and external tables you can export to
>>> an informix native format which saves you converting to
>>> and from ascii when moving data between IDS systems.
>>>
>>> John F. Miller III
>>> STSM, Support Architect
>>> miller3@us.ibm.com
>>> 503-578-5645
>>> IBM Informix Dynamic Server (IDS)
>>>
>>> ids-bounces@iiug.org wrote on 04/14/2010 11:35:33 AM:
>>>
>>>> [image removed]
>>>>
>>>> Re: What is the best tool to unload records fr.... [19689]
>>>>
>>>> Richard Kofler
>>>>
>>>> to:
>>>>
>>>> ids
>>>>
>>>> 04/14/2010 11:36 AM
>>>>
>>>> Sent by:
>>>>
>>>> ids-bounces@iiug.org
>>>>
>>>> Please respond to ids
>>>>
>>>> Hi,
>>>>
>>>> tested external tables, but was not able to twist it
>>>> so, that a fragmented non-external table was read
>>>> using frag parallelism, which is no problem when
>>>> using the HPL. Having real fast disks, I therefore
>>>> stay with HPL & make use of multiple devices in device
>>>> array.
>>>> To give you an idea of what I consider go(o)d speed,
>>>> using 2 RAID10 setups (one for reading, 1 for output)
>>>> on 2 controllers sitting on 2 different PCI 2.0 x16
>>>> connectors: If unload speed is > than 30 GB per min
>>>> I stop the tweaking.
>>>>
>>>> cu, dic_k
>>>>
>>>> Art Kagel schrieb:
>>>>> HPLoader is faster. My ul.ec utility (in the utils2_ak package)
> exports
>>> and
>>>>> imports data in binary format which tends to be more compact and so a
>
>>> bit
>>>>> faster than text and it can work remotely unloading to a target
>>>> machine over
>>>>> the net which can save to time to write the output then read it back
> to
>>>>> transfer it. Jonathan Leffler's sqlcmd's sqlunload utility is also a
>>> bit
>>>>> faster and also can be used remotely.
>>>>>
>>>>> If you are using IDS 11.50.xC6 (or later I guess) you can use an
>>> external
>>>>> table to export the data even faster than HPLoader can and using an
> NFS
>>>>> target file you can unload to a remote machine also.
>>>>>
>>>>> Art
>>>>>
>>>>> Art S. Kagel
>>>>> Advanced DataTools (www.advancedatatools.com)
>>>>> IIUG Board of Directors (art@iiug.org)
>>>>>
>>>>> See you at the 2010 IIUG Informix Conference
>>>>> April 25-28, 2010
>>>>> Overland Park (Kansas City), KS
>>>>> www.iiug.org/conf
>>>>>
>>>>> 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 Wed, Apr 14, 2010 at 6:05 AM, JACK PAPA <informix2009@gmail.com>
>>> wrote:
>>>>>> Hi All,
>>>>>>
>>>>>> I'll be performing unloading records from a table. What is the
> fastest
>>> way
>>>>>> to
>>>>>> perform the unloading records?
>>>>>>
>>>>>> My game plan is :
>>>>>>
>>>>>> 1. set the database in No Logging Mode
>>>>>>
>>>>>> 2. Execute "unload to tablename.unl select * from tablename"
>>>>>>
>>>>>> Is there any way is I can do it faster?
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>
>
> *******************************************************************************
>
>>>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>>>
>>>>>>
>>>>> --0016e6d2634cf2c0580484322d4b
>>>>>
>>>>>
>>>>>
>>>
>
> *******************************************************************************
>
>>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>>
>>>>>
>>>> --
>>>> Richard Kofler
>>>> SOLID STATE EDV
>>>> Dienstleistungen GmbH
>>>> Vienna/Austria/Europe
>>>>
>>>>
>>>>
>>>
>
> *******************************************************************************
>
>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>
>>>
>>>
>
> *******************************************************************************
>
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>> --
>> Richard Kofler
>> SOLID STATE EDV
>> Dienstleistungen GmbH
>> Vienna/Austria/Europe
>>
>>
>>
>
> *******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Messages In This Thread

• What is the best tool to unload records from a tab
  JACK PAPA -- Wednesday, 14 April 2010, at 6:05 a.m.
  • Re: What is the best tool to unload records from a
    Obnoxio The Clown -- Wednesday, 14 April 2010, at 6:08 a.m.
    • Re: What is the best tool to unload records from a
      JACK PAPA -- Wednesday, 14 April 2010, at 6:26 a.m.
      • Re: What is the best tool to unload records from a
        Obnoxio The Clown -- Wednesday, 14 April 2010, at 6:31 a.m.
        • Re: What is the best tool to unload records from a
          JACK PAPA -- Wednesday, 14 April 2010, at 10:28 p.m.
          • AW: What is the best tool to unload records from a
            Andreas.KUTSCHE@spar.at -- Thursday, 15 April 2010, at 3:23 a.m.
  • Re: What is the best tool to unload records from a
    Art Kagel -- Wednesday, 14 April 2010, at 9:16 a.m.
    • Re: What is the best tool to unload records fr....
      Richard Kofler -- Wednesday, 14 April 2010, at 2:35 p.m.
      • Re: What is the best tool to unload records fr....
        John Miller iii -- Wednesday, 14 April 2010, at 2:46 p.m.
        • Re: What is the best tool to unload records fr....
          Richard Kofler -- Wednesday, 14 April 2010, at 3:23 p.m.
          • Re: What is the best tool to unload records fr....
            John Miller iii -- Friday, 16 April 2010, at 1:00 p.m.
            • Re: What is the best tool to unload records fr....
              Richard Kofler -- Friday, 16 April 2010, at 1:43 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.