 |
IDS Forum
Re: Data migration Brainstorm.
Posted By: John Miller iii
Date: Thursday, 28 January 2010, at 11:10 p.m.
In Response To: Re: Data migration Brainstorm. (Cesar Inacio Martins)
It is actually a data recovery issue.If there has been no dropped tabl=
es
since
the last checkpoint, then IDS does not need to protect the before image=
s
(i.e. no
physical logging).
If there has been a dropped table since the last checkpoint then the be=
fore
image MUST
be saved. If you crash then IDS must put back the original images and
create a consistent
look at the checkpoint. Without these before images then there is no wa=
y to
put back the
dropped table. I guess you can call it an optimistic drop table.
Doing the checkpoint before doing a large load is not a new optimizatio=
n in
IDS, but it was first
introduced in version 6.0.
I would like to point out that if you are moving data from one IDS inst=
ance
to another IDS instance
there is actually a format called "informix" that will keep the data in=
native informix format reducing
the conversion to and from acsii. This should actually improve
performance 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 01/28/2010 05:28:43 PM:
> Hi John,
>
> The problem is the missing checkpoint... I can't figure out why this,=
for
me
> this appear be a bug.
>
> But you said this is an expected behave, can you explain? please...
>
> BTW, with checkpoint, HPL run in 33sec , EXTERNAL TABLE in 22 seconds=
,
very
> good... not 3x time faster like the White paper on IIUG site, but I'm=
very
> happy with this results! :)
>
> HPL and EXTERNAL have similar configuration (express, load 3 files in=
> parallel)
>
> Here is the answers for your questions ...
>
> ----------------
>
> 1. I run in express mode
>
> 2. I forgot to force the checkpoint and this is the reason to use of =
the
> physical log, what for me isn't a expected behave.
>
> 3. Default value, 128KB
>
> ----------------
>
> If you want to reproduce, check the steps bellow.
>
> Steps what I do:
>
> (running on Linux)
>
> ----------------
>
> A. Create a file to load my table:
>
> |$ find / -printf "%h|%f|%p|%l|%m|%M|%F|%y|%Y|%u|%g|%U|" \
>
> | -printf "%G|%s|%i|%AY-%Am-%Ad %AT|%CY-%Cm-%Cd %CT|" \
>
> | -printf "%TY-%Tm-%Td %TT|%D|\n" 2>/dev/null | \
>
> | sed -e "s,\.[0-9]\{10\},\.0,g" > /tmp/dados.unl
>
> |
>
> my /tmp is a tmpfs
>
> split this file in 3 files (no reason, just for fun):
>
> |$ split -l 117000 dados.unl
>
> ----------------
>
> B. Create a database with no log
>
> ----------------
>
> C. Create the scripts (2 files: fs_full.sql , ext.sql)
>
> | $ cat fs_full.sql
>
> | drop table fs_full;
>
> | CREATE RAW TABLE fs_full
>
> | (
>
> | diretorio NCHAR(300),
>
> | nome_arquivo NCHAR(100) not null ,
>
> | path_nome_arquivo NCHAR(400),
>
> | link_destino NCHAR(400),
>
> | permissao_octal SMALLINT,
>
> | permissao_str NCHAR(10),
>
> | filesystem_armazenado NCHAR(10),
>
> | tipo1 NCHAR(1),
>
> | tipo2 NCHAR(1),
>
> | owner_user NCHAR(15) not null ,
>
> | owner_group NCHAR(15) not null ,
>
> | owner_uid INTEGER not null ,
>
> | owner_gid INTEGER not null ,
>
> | tamanho_bytes BIGINT,
>
> | inode BIGINT,
>
> | ultimo_acesso DATETIME YEAR TO FRACTION(3),
>
> | ultima_mod_status DATETIME YEAR TO FRACTION(3),
>
> | ultima_mod_dados DATETIME YEAR TO FRACTION(3),
>
> | device_number INTEGER
>
> | )
>
> |extent size 160000 next size 10240 LOCK MODE ROW ;
>
> |$ cat ext.sql
>
> |drop table ex_fs_full;
>
> |
>
> |create external table ex_fs_full SAMEAS fs_full
>
> |USING (
>
> | DATAFILES ( 'DISK:/tmp/xaa',
>
> | 'DISK:/tmp/xab',
>
> | 'DISK:/tmp/xac'),
>
> | FORMAT 'DELIMITED',
>
> | REJECTFILE '/tmp/dados.rej',
>
> | MAXERRORS 10,
>
> | EXPRESS
>
> |)
>
> |
>
> ----------------
>
> D. Execute the scripts:
>
> $ cat fs_full.sql ext.sql | dbaccess myfs_db
>
> ----------------
>
> E. Run the load, drop tables, create tables, run the load again:
>
> | $ time { echo "set explain on; insert into fs_full select * from
> ex_fs_full"
> | dbaccess myfs_db; }
>
> | ....
>
> | $ cat fs_full.sql ext.sql | dbaccess myfs_db
>
> | ....
>
> | $ time { echo "set explain on; insert into fs_full select * from
> ex_fs_full"
> | dbaccess myfs_db; }
>
> | Database selected.
>
> | Explain set.
>
> | 335799 row(s) inserted.
>
> | Database closed.
>
> | real 0m37.669s <<<<<<<<<<<<<<<<<<<<<<<<<
>
> | user 0m0.015s
>
> | sys 0m0.018s
>
> Sorry, I'm confuse my self with the times... this way the EXTERNAL TA=
BLE
is
> slower than HPL (37s x 33s)
>
> Monitoring my physical log , I got this picture :
>
> | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up
> 00:26:23 --
> 353916 Kbytes
>
> | Physical Logging
>
> | Buffer bufused bufsize numpages numwrits pages/io
>
> | P-1 0 64 114176 3576 31.93
>
> | phybegin physize phypos phyused %used
>
> | 3:53 70000 27891 35295 50.42
>
> |
>
> ----------------
>
> F. If run with a checkpoint after the drop/create table, works fine:
>
> | $ cat fs_full.sql ext.sql | dbaccess myfs_db
>
> | ....
>
> | $ onmode -c
>
> | $ time { echo "set explain on; insert into fs_full select * from
> ex_fs_full"
> | dbaccess myfs_db; }
>
> | Database selected.
>
> | Explain set.
>
> | 335799 row(s) inserted.
>
> | Database closed.
>
> |
> real 0m22.533s
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>
> | user 0m0.019s
>
> | sys 0m0.030s
>
> | $ onmode -c
>
> I don't detect any Physical log usage .
>
> ----------------
>
> G. Look the last 4 checkpoint stats (total pages):
>
> | $ onstat -g ckp
>
> | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up
> 00:31:59 --
> 353916 Kbytes
>
> | AUTO_CKPTS=3DOff RTO_SERVER_RESTART=3DOff
>
> |
> Critical Sections Physical Log Logical Log
>
> |
> Clock Total Flush Block # Ckpt
> Wait Long # Dirty Dskflu Total Avg Total Avg
>
> |
> Interval Time Trigger LSN Time Time Time
> Waits Time Time Time Buffers /Sec Pages /Sec Pages /Sec
>
> |
> 43720 22:27:56 *User 652:0x5cc018 0.0 0.0 0.0
> 1 0.0 0.0 0.0 11 11 138 1 53 0
>
> |
> 43721 22:29:55 *Pload 652:0x5da018 0.1 0.0 0.0
> 1 0.0 0.1 0.1 8 8 76 0 14 0
>
> |
> 43722 22:30:21 *Pload 652:0x5dd4d4 0.0 0.0 0.0
> 0 0.0 0.0 0.0 1 1 63 2 3 0
>
> |
> 43723 22:30:37 *Pload 652:0x5f1018 0.1 0.0 0.0
> 1 0.0 0.1 0.1 8 8 76 4 20 1
>
> |
> 43724 22:32:12 *Pload 652:0x61f018 0.2 0.0 0.0
> 1 0.0 0.2 0.2 12 12 135 1 46 0
>
> |
> 43725 22:32:39 *Pload 652:0x6224d4 0.0 0.0 0.0
> 0 0.0 0.0 0.0 1 1 63 2 3 0
>
> |
> 43726 22:37:00 *User 652:0x642018 0.0 0.0 0.0
> 1 0.0 0.0 0.0 9 9 120 0 32 0
>
> |
> 43727 22:38:25 Plog 652:0x659018 0.2 0.1 0.0
> 1 0.0 0.1 0.1 51 51 52500 617 23 0
>
> |
> 43728 22:39:15 Plog 652:0x672018 0.4 0.3 0.0
> 1 0.0 0.0 0.0 70 70 52500 1050 25 0
>
> |
> 43729 22:41:04 Plog 652:0x696018 0.5 0.5 0.0
> 1 0.0 0.0 0.0 96 96 52500 481 36 0
>
> |
> 43730 22:46:19 CKPTINTVL 652:0x69a018 0.5 0.2 0.0
> 0 0.0 0.0 0.0 32 32 648 2 4 0
>
> |*43731
> 22:50:58 Plog 652:0x6b1018 0.3 0.1 0.0 1 0.0
> 0.1 0.1 51 51 *52500 188 23 0
>
> |*43732
> 22:52:01 Plog 652:0x6ca018 0.4 0.3 0.0 1 0.0
> 0.0 0.0 70 70 *52500 833 25 0
>
> |*43733
> 22:53:43 *User 652:0x6e3018 0.3 0.0 0.0 1 0.0
> 0.3 0.3 8 8 *533 5 25 0
>
> |*43734
> 22:59:09 CKPTINTVL 652:0x6e7018 0.5 0.1 0.0 0 0.0
> 0.0 0.0 32 32 *64 0 4 0
>
> |
>
> | Max Plog Max Llog Max Dskflush Avg Dskflush Avg Dirty Blocked
>
> | pages/sec pages/sec Time pages/sec pages/sec Time
>
> | 4008 200 0 27 0 0
>
> |
>
> | Based on the current workload, the physical log might be too small
>
> | to accommodate the time it takes to flush the buffer pool during
>
> | checkpoint processing. The server might block transactions during
> checkpoints.
>
> | If the server blocks transactions, increase the physical log size t=
o
>
> | at least 280560 KB.
>
> Just to know, The AUTO_CKPTS is active, this output is wrong.
>
> | $ onstat -c |grep ^AUTO_CKPT
>
> | AUTO_CKPTS 1
>
> Regards
>
> C=E9sar
>
> --- Em qui, 28/1/10, John Miller iii <miller3@us.ibm.com> escreveu:
>
> De: John Miller iii <miller3@us.ibm.com>
> Assunto: Re: Data migration Brainstorm. [18828]
> Para: ids@iiug.org
> Data: Quinta-feira, 28 de Janeiro de 2010, 15:51
>
> There is a few things to try.
>
> 1. Which mode where you running the load/unload in. Dexule or Express=
=3D
> ...
>
> Express is much faster, but does have a few limitations, HPL has
>
> both of these modes also.
>
> 2. Please make sure you do a checkpoint before doing you loads.
>
> If you have done a drop table in the dbspace to where you
>
> are load data, this will cause a lot more physical logging.
>
> 3. Please check the size of your physical log buffer, onstat -l will
>
> show it average usage.
>
> 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 01/28/2010 03:18:51 AM:
>
> > Hi Art,
> >
> > No, I' m very careful with this kind of issues , I create a tmpfs (=
me=3D
> mory
> fs)
> > and put my unl there... this way I don' t get of I/O and the read i=
s =3D
> very
>
> > fast.
> > Except by Physical Log X dbspace concurrency transfer... what so fa=
r =3D
> I
> know
> > work with big I/O...
> > I stranger a lot this physical log usage... or I'm confusing someth=
in=3D
> g...
>
> >
> > Regards
> > Cesar
> >
> > --- Em qua, 27/1/10, Art Kagel <art.kagel@gmail.com> escreveu:
> >
> > De: Art Kagel <art.kagel@gmail.com>
> > Assunto: Re: Data migration Brainstorm. [18818]
> > Para: ids@iiug.org
> > Data: Quarta-feira, 27 de Janeiro de 2010, 21:22
> >
> > The problem on your NETBOOK may be that the database's chunks and t=
he=3D
>
> > external table's file are all residing on your single disk drive.
> >
> > 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 opi=
ni=3D
> ons
> and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor an=
y
> other
> > organization with which I am associated either explicitly, implicit=
ly=3D
> , or
> by
> > inference. Neither do those opinions reflect those of other individ=
ua=3D
> ls
> > affiliated with any entity with which I am affiliated nor those of =
th=3D
> e
> > entities themselves.
> >
> > On Wed, Jan 27, 2010 at 6:19 PM, Cesar Inacio Martins <
> > cesar_inacio_martins@yahoo.com.br> wrote:
> >
> > > Hi John,
> > >
> > > Can you explain here details about some configuration (ONCONFIG) =
wh=3D
> at
> maybe
> > > you do for execute this?
> > >
> > > I already executed a few tests with EXTERNALTABLE too (in my limi=
te=3D
> d
> > > NetBook)
> > > and get high usage of physical log and just a bit faster then HPL=
,=3D
> but
> my
> > > table have only 400 MB.
> > > I don't know, maybe I need test over a bigger table to feel the g=
ai=3D
> n of
>
> > > performance...
> > >
> > > Any tips of the configuration to use the EXTERNAL TABLE?
> > >
> > > Regards
> > > Cesar
> > >
> > > --- Em qua, 27/1/10, John Miller iii <miller3@us.ibm.com> escreve=
u:=3D
>
> > >
> > > De: John Miller iii <miller3@us.ibm.com>
> > > Assunto: RE: Data migration Brainstorm. [18805]
> > > Para: ids@iiug.org
> > > Data: Quarta-feira, 27 de Janeiro de 2010, 16:39
> > >
> > > If you are looking at upgrading to a version of 11.50 I would loo=
k
> > > at using external tables. My tests show that they are much faster=
> > > than HPL and I think simpler.
> > >
> > > Here are the three steps for a simple load from two unload files =
of=3D
> a
> > > customer table. I
> > > have included several of the load options
> > >
> > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.cust=
om=3D
> er
> > > USING
> > > (
> > > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'),
> > > FORMAT 'DELIMITED',
> > > DELIMITER '|',
> > > RECORDEND '',
> > > Deluxe,
> > > NUMROWS 50,
> > > MAXERRORS 50,
> > > REJECTFILE ''
> > > );
> > >
> > > INSERT INTO informix.customer SELECT * FROM EXTcustomer;
> > > DROP TABLE EXTcustomer;
> > >
> > > 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 01/27/2010 09:16:57 AM:
> > >
> > > > [image removed]
> > > >
> > > > RE: Data migration Brainstorm. [18803]:
> > > >
> > > > Knox, Ernest
> > > >
> > > > to:
> > > >
> > > > ids
> > > >
> > > > 01/27/2010 09:17 AM
> > > >
> > > > Sent by:
> > > >
> > > > ids-bounces@iiug.org
> > > >
> > > > Please respond to ids
> > > >
> > > > I'm getting some good suggestions. I appreciate and will review=
t=3D
> hem
> > > > all, so keep them coming.
> > > >
> > > > Thanks,
> > > > ***************************************************************=
**=3D
> **
> > > > Ernie Knox
> > > > IT Database Administrator Specialist
> > > > Sears Holdings
> > > > 3333 Beverly Rd., B4-266A
> > > > Hoffman Estates, IL. 60179
> > > > Office: (847) 286-5735
> > > > Email: Ernest.Knox@searshc.com
> > > > Blackberry: 2244650553@messaging.sprintpcs.com
> > > > Page via Skytel: 2244650553@sprint.skytel.com
> > > > Informix or MySQL Primary: 9110210@skytel.com
> > > > Informix or MySQL Secondary: 7276872@skytel.com
> > > >
> > > > " Yes we can make a Change! "
> > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, a=
nd=3D
>
> BEARS!
> > > > "
> > > > " Lets not forget - GO Pistons and Red Wings! "
> > > > GSU
> > > > ***************************************************************=
**=3D
> **
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Beh=
al=3D
> f Of
>
> > > > Knox, Ernest
> > > > Sent: Wednesday, January 27, 2010 10:46 AM
> > > > To: ids@iiug.org
> > > > Subject: Data migration Brainstorm. [18798]
> > > >
> > > > We are upgrading our Servers, OS and version of Informix from 7=
.3=3D
> 0,
> > > > 7.31, 9.21, 9.30, and 9.40 to version 11.50.
> > > >
> > > > My brainstorming question(s) to the group is:
> > > >
> > > > What is the quickest way to migrate between 1 gig to 300 gig of=
d=3D
> ata?
>
> > > >
> > > > Since I can't use a backup and restore from different versions =
(I=3D
>
> don't
> > > > believe) and one or two databases do not have logging turned on=
, =3D
> so
> > > > replication is basically out.
> > > >
> > > > Would HPL, manual scripts (dbexport and dbimport), or a third p=
ar=3D
> ty
> > > > product do the trick.
> > > >
> > > > Please, your thoughts and suggestions are very helpful.
> > > >
> > > > Thanks,
> > > >
> > > > ***************************************************************=
**=3D
> **
> > > >
> > > > Ernie Knox
> > > >
> > > > IT Database Administrator Specialist
> > > >
> > > > Sears Holdings
> > > >
> > > > 3333 Beverly Rd., B4-266A
> > > >
> > > > Hoffman Estates, IL. 60179
> > > >
> > > > Office: (847) 286-5735
> > > >
> > > > Email: Ernest.Knox@searshc.com
> > > >
> > > > Blackberry: 2244650553@messaging.sprintpcs.com
> > > > <mailto:2244650553@messaging.sprintpcs.com>
> > > >
> > > > Page via Skytel: 2244650553@sprint.skytel.com
> > > > <mailto:2244650553@sprint.skytel.com>
> > > >
> > > > Informix or MySQL Primary: 9110210@skytel.com
> > > > <mailto:9110210@skytel.com>
> > > >
> > > > Informix or MySQL Secondary: 7276872@skytel.com
> > > > <mailto:7276872@skytel.com>
> > > >
> > > > " Yes we can make a Change! "
> > > >
> > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, a=
nd=3D
>
> BEARS!
> > > >
> > > > "
> > > >
> > > > " Lets not forget - GO Pistons and Red Wings! "
> > > >
> > > > GSU
> > > >
> > > > ***************************************************************=
**=3D
> **
> > > >
> > > >
> *********************************************************************=
**=3D
> *
> > > > *******
> > > > Forum Note: Use "Reply" to post a response in the discussion fo=
ru=3D
> m.
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
> >
> *********************************************************************=
**=3D
> ********
>
> > >
> > > > Forum Note: Use "Reply" to post a response in the discussion fo=
ru=3D
> m.
> > > >
> > >
> > >
> > >
> > >
> >
> >
> *********************************************************************=
**=3D
> ********
>
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.=3D
>
> > >
> > >
> > >
> > >
> >
> >
> _____________________________________________________________________=
__=3D
> _____________
>
> > > Veja quais s=3DE3o os assuntos do momento no Yahoo! +Buscados
> > > http://br.maisbuscados.yahoo.com
> > >
> > >
> > >
> > >
> >
> >
> *********************************************************************=
**=3D
> ********
>
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.=3D
>
> > >
> > >
> >
> > --001517478636b72094047e2dacbe
> >
> >
> >
> *********************************************************************=
**=3D
> ********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
> >
> _____________________________________________________________________=
__=3D
> _____________
>
> > Veja quais s=3DE3o os assuntos do momento no Yahoo! +Buscados
> > http://br.maisbuscados.yahoo.com
> >
> >
> >
> *********************************************************************=
**=3D
> ********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
=3D
>
> >=3D
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
_______________________________________________________________________=
_____________
> Veja quais s=E3o os assuntos do momento no Yahoo! +Buscados
> http://br.maisbuscados.yahoo.com
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
Messages In This Thread
- Data migration Brainstorm.
Knox, Ernest -- Wednesday, 27 January 2010, at 11:46 a.m.
- Re: Data migration Brainstorm.
Obnoxio The Clown -- Wednesday, 27 January 2010, at 11:57 a.m.
- RE: Data migration Brainstorm.
Knox, Ernest -- Wednesday, 27 January 2010, at 12:16 p.m.
- Re: Data migration Brainstorm.
Art Kagel -- Wednesday, 27 January 2010, at 5:39 p.m.
- AW: Data migration Brainstorm.
Andreas.KUTSCHE@spar.at -- Thursday, 28 January 2010, at 3:32 a.m.
- Re: Data migration Brainstorm.
Keith Simmons -- Thursday, 28 January 2010, at 3:53 a.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
