RE: onunload
Larry asked how long onunload of a 50-60GB database should take on a 4-CPU Solaris 10 box running IDS 11.50.FC7 (his was taking 10+ hours), and what faster alternatives exist for copying a 50-75GB database between instances on the same server. Lester said 10+ hours is far too slow (rule of thumb a few minutes per GB), suggested benchmarking raw disk write speed with dd and looking at KAIO, AIOVP, CLEANERS, BUFFERPOOL and LRU settings. Art suggested skipping unload files entirely: recreate the target empty (using myschema for extents, tables separate from indexes/constraints), put it in separate chunks, set tables to RAW during load, and copy directly with his dbcopy/dbmove utilities run in parallel with WHERE-clause splits; he also posted an awk script for rewriting dbspace clauses. No confirmation of the final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC7 - Solaris 10
I know there is no way to exactly answer this question without knowing more
about the hardware, but I was wondering if anyone had a ballpark range for how
long it should take a 4 CPU server to onunload a 50GB database to disk. (Sun
server)
Also, what things might speed it up?
What are some alternatives for copying a 50-75GB database from a
multi-database instance to another instance on the same server a couple a
times per month.
Thank you in advance.
Larry
Larry, here's one option: Drop the target database and recreate it empty
with the expected extent sizing (myschema can help with that) then copy the
data directly from the source database to the target database using my
dbcopy and/or dbmove utilities. Make sure that the target database is
located in a separate set of chunks to minimize disk contention and break
up the copies of larger tables into multiple parallel dbcopy/dbmove runs
using distinct WHERE clauses. I can give you an awk script to automate
modifying a schema file's IN <dbspace> clauses so you can take the schema
from the source database and modify it.
Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a single
table or CLOB or BLOB columns and because of a CSDK bug cannot handle
LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
issues and is still faster than SELECT FROM ... INSERT INTO without the
risk of long transaction rollbacks.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> IDS 11.50.FC7 - Solaris 10
>
> I know there is no way to exactly answer this question without knowing more
> about the hardware, but I was wondering if anyone had a ballpark range for
> how
> long it should take a 4 CPU server to onunload a 50GB database to disk.
> (Sun
> server)
>
> Also, what things might speed it up?
>
> What are some alternatives for copying a 50-75GB database from a
> multi-database instance to another instance on the same server a couple a
> times per month.
>
> Thank you in advance.
>
> Larry
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7beba13af65aa904f9e99312
Ok. So, I will need to get your programs from the IUG website, compile them on
the Solaris server.
In the meantime, Does 10+ hours seem normal for an onunload of a 50-60 GB
database?
Also, your AWK script would be helpful.
Thank you.
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: onunload [33071]
> Date: Wed, 21 May 2014 10:11:58 -0400
>
> Larry, here's one option: Drop the target database and recreate it empty
> with the expected extent sizing (myschema can help with that) then copy the
> data directly from the source database to the target database using my
> dbcopy and/or dbmove utilities. Make sure that the target database is
> located in a separate set of chunks to minimize disk contention and break
> up the copies of larger tables into multiple parallel dbcopy/dbmove runs
> using distinct WHERE clauses. I can give you an awk script to automate
> modifying a schema file's IN <dbspace> clauses so you can take the schema
> from the source database and modify it.
>
> Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a single
> table or CLOB or BLOB columns and because of a CSDK bug cannot handle
> LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
> issues and is still faster than SELECT FROM ... INSERT INTO without the
> risk of long transaction rollbacks.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > IDS 11.50.FC7 - Solaris 10
> >
> > I know there is no way to exactly answer this question without knowing more
> > about the hardware, but I was wondering if anyone had a ballpark range for
> > how
> > long it should take a 4 CPU server to onunload a 50GB database to disk.
> > (Sun
> > server)
> >
> > Also, what things might speed it up?
> >
> > What are some alternatives for copying a 50-75GB database from a
> > multi-database instance to another instance on the same server a couple a
> > times per month.
> >
> > Thank you in advance.
> >
> > Larry
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --047d7beba13af65aa904f9e99312
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Larry,
This seems very long. On my Mac's I can do this in about a minute per GB. My
old rule of thumb for a Sun server (from the 90's) was 5 minutes per 2 GB for
writing to disk.
Regards - Lester
On 5/21/14 10:55 AM, LARRY SORENSEN wrote:
> Ok. So, I will need to get your programs from the IUG website, compile them
on
> the Solaris server.
>
> In the meantime, Does 10+ hours seem normal for an onunload of a 50-60 GB
> database?
>
> Also, your AWK script would be helpful.
>
> Thank you.
>
> Larry
>
>> To: ids@iiug.org
>> From: art.kagel@gmail.com
>> Subject: Re: onunload [33071]
>> Date: Wed, 21 May 2014 10:11:58 -0400
>>
>> Larry, here's one option: Drop the target database and recreate it empty
>> with the expected extent sizing (myschema can help with that) then copy the
>> data directly from the source database to the target database using my
>> dbcopy and/or dbmove utilities. Make sure that the target database is
>> located in a separate set of chunks to minimize disk contention and break
>> up the copies of larger tables into multiple parallel dbcopy/dbmove runs
>> using distinct WHERE clauses. I can give you an awk script to automate
>> modifying a schema file's IN <dbspace> clauses so you can take the schema
>> from the source database and modify it.
>>
>> Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a single
>> table or CLOB or BLOB columns and because of a CSDK bug cannot handle
>> LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
>> issues and is still faster than SELECT FROM ... INSERT INTO without the
>> risk of long transaction rollbacks.
>>
>> Art
>>
>> Art S. Kagel, Principal Consultant
>> ASK Database Management
>>
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on the IIUG, nor any other organization with which I am
>> associated either explicitly, implicitly, or by inference. Neither do
>> those opinions reflect those of other individuals affiliated with any
>> entity with which I am affiliated nor those of the entities themselves.
>>
>> On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>>
>>> IDS 11.50.FC7 - Solaris 10
>>>
>>> I know there is no way to exactly answer this question without knowing
> more
>>> about the hardware, but I was wondering if anyone had a ballpark range for
>>> how
>>> long it should take a 4 CPU server to onunload a 50GB database to disk.
>>> (Sun
>>> server)
>>>
>>> Also, what things might speed it up?
>>>
>>> What are some alternatives for copying a 50-75GB database from a
>>> multi-database instance to another instance on the same server a couple a
>>> times per month.
>>>
>>> Thank you in advance.
>>>
>>> Larry
>>>
>>>
>>>
>>>
>>
>
*******************************************************************************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>>
>> --047d7beba13af65aa904f9e99312
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
______________________________________________________________________
Lester Knutsen lester@advancedatatools.com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
______________________________________________________________________
Are there some configuration parameters that are specifically related to this
that I might look at?
> To: ids@iiug.org
> From: lester@advancedatatools.com
> Subject: Re: onunload [33073]
> Date: Wed, 21 May 2014 11:09:53 -0400
>
> Larry,
>
> This seems very long. On my Mac's I can do this in about a minute per GB. My
> old rule of thumb for a Sun server (from the 90's) was 5 minutes per 2 GB for
> writing to disk.
>
> Regards - Lester
>
> On 5/21/14 10:55 AM, LARRY SORENSEN wrote:
> > Ok. So, I will need to get your programs from the IUG website, compile them
> on
> > the Solaris server.
> >
> > In the meantime, Does 10+ hours seem normal for an onunload of a 50-60 GB
> > database?
> >
> > Also, your AWK script would be helpful.
> >
> > Thank you.
> >
> > Larry
> >
> >> To: ids@iiug.org
> >> From: art.kagel@gmail.com
> >> Subject: Re: onunload [33071]
> >> Date: Wed, 21 May 2014 10:11:58 -0400
> >>
> >> Larry, here's one option: Drop the target database and recreate it empty
> >> with the expected extent sizing (myschema can help with that) then copy
the
> >> data directly from the source database to the target database using my
> >> dbcopy and/or dbmove utilities. Make sure that the target database is
> >> located in a separate set of chunks to minimize disk contention and break
> >> up the copies of larger tables into multiple parallel dbcopy/dbmove runs
> >> using distinct WHERE clauses. I can give you an awk script to automate
> >> modifying a schema file's IN <dbspace> clauses so you can take the schema
> >> from the source database and modify it.
> >>
> >> Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a single
> >> table or CLOB or BLOB columns and because of a CSDK bug cannot handle
> >> LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
> >> issues and is still faster than SELECT FROM ... INSERT INTO without the
> >> risk of long transaction rollbacks.
> >>
> >> Art
> >>
> >> Art S. Kagel, Principal Consultant
> >> ASK Database Management
> >>
> >> Blog: http://informix-myview.blogspot.com/
> >>
> >> Disclaimer: Please keep in mind that my own opinions are my own opinions
> >> and do not reflect on the IIUG, nor any other organization with which I am
> >> associated either explicitly, implicitly, or by inference. Neither do
> >> those opinions reflect those of other individuals affiliated with any
> >> entity with which I am affiliated nor those of the entities themselves.
> >>
> >> On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >>
> >>> IDS 11.50.FC7 - Solaris 10
> >>>
> >>> I know there is no way to exactly answer this question without knowing
> > more
> >>> about the hardware, but I was wondering if anyone had a ballpark range
for
> >>> how
> >>> long it should take a 4 CPU server to onunload a 50GB database to disk.
> >>> (Sun
> >>> server)
> >>>
> >>> Also, what things might speed it up?
> >>>
> >>> What are some alternatives for copying a 50-75GB database from a
> >>> multi-database instance to another instance on the same server a couple a
> >>> times per month.
> >>>
> >>> Thank you in advance.
> >>>
> >>> Larry
> >>>
> >>>
> >>>
> >>>
> >>
> >
>
*******************************************************************************
> >>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>>
> >>>
> >>
> >> --047d7beba13af65aa904f9e99312
> >>
> >>
> >>
> >
>
*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> ______________________________________________________________________
> Lester Knutsen lester@advancedatatools.com
> Advanced DataTools Corporation Voice: 703-256-0267 x102
> Visit our Web page: http://www.advancedatatools.com
> ______________________________________________________________________
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Larry,
KAIO, AIOVP, CLEANERS, BUFFERPOOL, LRU, will all have an effect. Without
knowing more about your system it is hard to make recommendations.
To set a baseline I would copy or dd a 2GB file to the disk you are writing
to. That will give you a time per GB that your disk can handle. Onunload is
going to be writing data our in the same page size as the dbspace it is coming
from (default for Sun is 2K).
Regards - Lester
On 5/21/14 11:24 AM, LARRY SORENSEN wrote:
> Are there some configuration parameters that are specifically related to this
> that I might look at?
>
>> To: ids@iiug.org
>> From: lester@advancedatatools.com
>> Subject: Re: onunload [33073]
>> Date: Wed, 21 May 2014 11:09:53 -0400
>>
>> Larry,
>>
>> This seems very long. On my Mac's I can do this in about a minute per GB. My
>> old rule of thumb for a Sun server (from the 90's) was 5 minutes per 2 GB
> for
>> writing to disk.
>>
>> Regards - Lester
>>
>> On 5/21/14 10:55 AM, LARRY SORENSEN wrote:
>>> Ok. So, I will need to get your programs from the IUG website, compile
> them
>> on
>>> the Solaris server.
>>>
>>> In the meantime, Does 10+ hours seem normal for an onunload of a 50-60 GB
>>> database?
>>>
>>> Also, your AWK script would be helpful.
>>>
>>> Thank you.
>>>
>>> Larry
>>>
>>>> To: ids@iiug.org
>>>> From: art.kagel@gmail.com
>>>> Subject: Re: onunload [33071]
>>>> Date: Wed, 21 May 2014 10:11:58 -0400
>>>>
>>>> Larry, here's one option: Drop the target database and recreate it empty
>>>> with the expected extent sizing (myschema can help with that) then copy
> the
>>>> data directly from the source database to the target database using my
>>>> dbcopy and/or dbmove utilities. Make sure that the target database is
>>>> located in a separate set of chunks to minimize disk contention and break
>>>> up the copies of larger tables into multiple parallel dbcopy/dbmove runs
>>>> using distinct WHERE clauses. I can give you an awk script to automate
>>>> modifying a schema file's IN <dbspace> clauses so you can take the schema
>>>> from the source database and modify it.
>>>>
>>>> Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a
> single
>>>> table or CLOB or BLOB columns and because of a CSDK bug cannot handle
>>>> LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
>>>> issues and is still faster than SELECT FROM ... INSERT INTO without the
>>>> risk of long transaction rollbacks.
>>>>
>>>> Art
>>>>
>>>> Art S. Kagel, Principal Consultant
>>>> ASK Database Management
>>>>
>>>> Blog: http://informix-myview.blogspot.com/
>>>>
>>>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>>>> and do not reflect on the IIUG, nor any other organization with which I
> am
>>>> associated either explicitly, implicitly, or by inference. Neither do
>>>> those opinions reflect those of other individuals affiliated with any
>>>> entity with which I am affiliated nor those of the entities themselves.
>>>>
>>>> On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com>
>> wrote:
>>>>
>>>>> IDS 11.50.FC7 - Solaris 10
>>>>>
>>>>> I know there is no way to exactly answer this question without knowing
>>> more
>>>>> about the hardware, but I was wondering if anyone had a ballpark range
> for
>>>>> how
>>>>> long it should take a 4 CPU server to onunload a 50GB database to disk.
>>>>> (Sun
>>>>> server)
>>>>>
>>>>> Also, what things might speed it up?
>>>>>
>>>>> What are some alternatives for copying a 50-75GB database from a
>>>>> multi-database instance to another instance on the same server a couple
> a
>>>>> times per month.
>>>>>
>>>>> Thank you in advance.
>>>>>
>>>>> Larry
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
*******************************************************************************
>>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>>
>>>>>
>>>>
>>>> --047d7beba13af65aa904f9e99312
>>>>
>>>>
>>>>
>>>
>>
>
*******************************************************************************
>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>
>>>
>>>
>>>
>>
>
*******************************************************************************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>>
>> --
>> ______________________________________________________________________
>> Lester Knutsen lester@advancedatatools.com
>> Advanced DataTools Corporation Voice: 703-256-0267 x102
>> Visit our Web page: http://www.advancedatatools.com
>> ______________________________________________________________________
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
______________________________________________________________________
Lester Knutsen lester@advancedatatools.com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
______________________________________________________________________
I would have estimated more like 4-6 hours. BTW, take advantage of
myschema's ability to separate the create table statements from indexes,
constraints, and privileges to separate files so you can load the tables
without indexes. You might also use dbscript to output scripts to shift
all of the target tables to RAW mode before the copy then back to STANDARD
mode after for fastest load performance.
Script attached. Run without arguments to see usage.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, May 21, 2014 at 10:55 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote:
> Ok. So, I will need to get your programs from the IUG website, compile
> them on
> the Solaris server.
>
> In the meantime, Does 10+ hours seem normal for an onunload of a 50-60 GB
> database?
>
> Also, your AWK script would be helpful.
>
> Thank you.
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: onunload [33071]
> > Date: Wed, 21 May 2014 10:11:58 -0400
> >
> > Larry, here's one option: Drop the target database and recreate it empty
> > with the expected extent sizing (myschema can help with that) then copy
> the
> > data directly from the source database to the target database using my
> > dbcopy and/or dbmove utilities. Make sure that the target database is
> > located in a separate set of chunks to minimize disk contention and break
> > up the copies of larger tables into multiple parallel dbcopy/dbmove runs
> > using distinct WHERE clauses. I can give you an awk script to automate
> > modifying a schema file's IN <dbspace> clauses so you can take the schema
> > from the source database and modify it.
> >
> > Dbcopy is fastest but cannot handle multiple BYTE/TEXT columns in a
> single
> > table or CLOB or BLOB columns and because of a CSDK bug cannot handle
> > LVARCHAR columns for CSDK versions 3.50 or later. Dbmove avoids those
> > issues and is still faster than SELECT FROM ... INSERT INTO without the
> > risk of long transaction rollbacks.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Wed, May 21, 2014 at 9:34 AM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > IDS 11.50.FC7 - Solaris 10
> > >
> > > I know there is no way to exactly answer this question without knowing
> more
> > > about the hardware, but I was wondering if anyone had a ballpark range
> for
> > > how
> > > long it should take a 4 CPU server to onunload a 50GB database to disk.
> > > (Sun
> > > server)
> > >
> > > Also, what things might speed it up?
> > >
> > > What are some alternatives for copying a 50-75GB database from a
> > > multi-database instance to another instance on the same server a
> couple a
> > > times per month.
> > >
> > > Thank you in advance.
> > >
> > > Larry
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --047d7beba13af65aa904f9e99312
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9cfcabcc6984e04f9eaf6c4