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 TABLE 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=Off RTO_SERVER_RESTART=Off
|
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 to
| 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ésar
--- 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=
..
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=
mory
fs)
> and put my unl there... this way I don' t get of I/O and the read is =
very
> fast.
> Except by Physical Log X dbspace concurrency transfer... what so far =
I
know
> work with big I/O...
> I stranger a lot this physical log usage... or I'm confusing somethin=
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 the=
> 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 opini=
ons
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 individua=
ls
> affiliated with any entity with which I am affiliated nor those of th=
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=
at
maybe
> > you do for execute this?
> >
> > I already executed a few tests with EXTERNALTABLE too (in my limite=
d
> > NetBook)
> > and get high usage of physical log and just a bit faster then HPL ,=
but
my
> > table have only 400 MB.
> > I don't know, maybe I need test over a bigger table to feel the gai=
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> escreveu:=
> >
> > 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 look
> > 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=
a
> > customer table. I
> > have included several of the load options
> >
> > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.custom=
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=
hem
> > > all, so keep them coming.
> > >
> > > Thanks,
> > > *****************************************************************=
**
> > > 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, and=
BEARS!
> > > "
> > > " Lets not forget - GO Pistons and Red Wings! "
> > > GSU
> > > *****************************************************************=
**
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behal=
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=
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=
ata?
> > >
> > > Since I can't use a backup and restore from different versions (I=
don't
> > > believe) and one or two databases do not have logging turned on, =
so
> > > replication is basically out.
> > >
> > > Would HPL, manual scripts (dbexport and dbimport), or a third par=
ty
> > > product do the trick.
> > >
> > > Please, your thoughts and suggestions are very helpful.
> > >
> > > Thanks,
> > >
> > > *****************************************************************=
**
> > >
> > > 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, and=
BEARS!
> > >
> > > "
> > >
> > > " Lets not forget - GO Pistons and Red Wings! "
> > >
> > > GSU
> > >
> > > *****************************************************************=
**
> > >
> > >
***********************************************************************=
*
> > > *******
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.
> > >
> > >
> > >
> >
> >
> >
> >
>
>
***********************************************************************=
********
> >
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.
> > >
> >
> >
> >
> >
>
>
***********************************************************************=
********
> > 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.=
> >
> >
>
> --001517478636b72094047e2dacbe
>
>
>
***********************************************************************=
********
> 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.=
>=
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com
