unloading a table more efficiently...
Hi Informix Users,
I need your advice again.
We are a financial institution in Belize ("Temptation Island").
We are running Solaris 2.7 with Informix IDS Workgroup Server 7.30.UC3.
I (SysAdmin/DBA) want to improve the loading of a GL history table.
Statistics on table:
Kbytes Mbytes
tabname rowsize nrows nrows*size nrows*size nexts
mch 494 1064897 513729.6 501.7 178
ndatapgs Pgs=>Mb npgsused Pgs=>Mb npgsalloc Pgs=>Mb
266225 1039.9 293311 1145.7 294784 1151.5
From dbschema in dbexport directory:
{ TABLE "opsi".mch row size = 494 number of columns = 68 index size = 838 }
{ unload file name = mch__00458.unl number of rows = 1064897 }
create table "opsi".mch
(
coentfincble decimal(5,0) not null ,
femovcble decimal(8,0) not null ,
cotiuniorgext char(1) not null ,
couniorgext decimal(5,0) not null ,
cocomcont char(3) not null ,
nrcomcble decimal(5,0) not null ,
nrsecmco decimal(5,0) not null ,
nrctacble char(15) not null ,
txdesmov char(50) not null ,
.
.
primary key
(coentfincble,femovcble,cotiuniorgext,couniorgext,cocomcont,nrco
mcble,nrsecmco)
) in testgldbs extent size 589568 next size 58956;
revoke all on "opsi".mch from "public";
create index "opsi".akmch on "opsi".mch (coentfincble,nrctacble,
cotiuniorgext1,couniorgext1) in testgldbs;
create index "opsi".akmch1 on "opsi".mch (coentfincble,cotiuniorgext1,
couniorgext1,nrtercble,nrctacble,femovcble,cotiuniorgext,
couniorgext,cocomcont,nrcomcble) in testgldbs;
create index "opsi".akmch2 on "opsi".mch (coentfincble,nrctacble,
cotiuniorgext1,couniorgext1,femovcble) in testgldbs;
create index "opsi".akmch3 on "opsi".mch (coentfincble,femovcble,
nrctacble,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
nrsecmco) in testgldbs;
create index "opsi".akmch4 on "opsi".mch (coentfincble,femovcble,
nrctacble,cotiuniorgext1,couniorgext1,comon,nrtercble,coprod,
cosbp,cotiuniorgext2,couniorgext2,cotiuniorgext3,couniorgext3,
nruserid,txvalsdocble1) in testgldbs;
create index "opsi".akmch5 on "opsi".mch (coentfincble,cotiuniorgext,
couniorgext,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
nrsecmco) in testgldbs;
create index "opsi".akmch6 on "opsi".mch (coentfincble,cotiuniorgext,
couniorgext,cocomcont,nrcomcble,nrsecmco) in testgldbs;
create index "opsi".akmch7 on "opsi".mch (coentfincble,cotiuniorgext,
couniorgext,nruserid,cocomcont,nrcomcble,nrsecmco) in testgldbs;
create index "opsi".akmch8 on "opsi".mch (coentfincble,cotiuniorgext,
couniorgext,nrtercble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
create index "opsi".akmch9 on "opsi".mch (coentfincble,cotiuniorgext,
couniorgext,nrctacble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
create index "opsi".akmch10 on "opsi".mch (coentfincble,femovcble,
cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,nrsecmco) in testgldbs;
create index "opsi".akmch11 on "opsi".mch (coentfincble,nrctacble,
femovcble,cotiuniorgext,couniorgext,cocomcont,nrcomcble,nrsecmco) in
testgldbs;
create index "opsi".akmch12 on "opsi".mch (coentfincble,cotiuniorgext1,
couniorgext1,femodmovcble) in testgldbs;
Using dbload this table takes 7.5 hours to load. I am currently setting up a
testing environment to evaluate other loading methods.
Notice that currently this table has 178 extents. I want to reduce the
number of extents to one during the dbimport by adding the "extent/next
size" clause.
The migration guide discusses other tools such as onunload/onload and the
HPL. However, it also says that the HPL (onpload) can't be used with the
Online Workgroup Server. We have IDS Workgroup Edition 7.30.UC3.
Please give me some advice about HPL and its use with Workgroup Edition?
I have the command line utility and I'd loke to try it; however, I have not
seen any documentation on the HPL (onunload) besides the syntax.
Is there a scripting language I should learn to be able to use the HPL
(onpload) effectively? Any examples would be appreciated.
I've used the onunload/onload once before to move a database between
different Sun machines, although it was not successfull. The integrity of
the data was questionable. I had to resort to a regular dbexport/dbimport.
Perhaps it was related to the fact that the machines had different number
representations (E3000 => Sparc5).
I'm willing to try it again though.
On a related note, I asked the question a few days ago about the optimum
number of extents for a table. Of course the answer is one. I read all the
worthy responses. One from Jack Parker caught my attention though.
He recommends selecting a smaller initial extent size (because index space
allocation is made according to the initial extent size) followed by a large
next extent size, loading the table and then altering the next extent size
downward.
I have tried in the past to calculate exactly how much space is used by
indexes directly, but I had to resort to calculations using the dbspace
size, free space and number of pages allocated. I was able to subtract the
datapages used and the free space which left me with the storage for
overhead and index.
What I found out is that the 12 detached indexes for this table takes up a
lot of storage in the dbspace. Exactly how much, I don't know. Any ideas
about calculating the index space usage and using index storage more
efficiently?
I know I'm asking for advice about several issues but I think this list is
the appropriate forum. Especially with experienced user/DBAs participating.
Thanks in advance,
Regards,
Denmark W.
What takes most of your time?
Loading table? or creating index?
If u spend most of your time in creating index,
check if your DS_TOTAL_MEMORY in onconfig is properly set,
and set PDQPRIORITY to 100.
Denmark B. Weatherburn <dweatherb@btl.net> wrote:
> Hi Informix Users,
> I need your advice again.
> We are a financial institution in Belize ("Temptation Island").
> We are running Solaris 2.7 with Informix IDS Workgroup Server 7.30.UC3.
> I (SysAdmin/DBA) want to improve the loading of a GL history table.
> Statistics on table:
> Kbytes Mbytes
> tabname rowsize nrows nrows*size nrows*size nexts
> mch 494 1064897 513729.6 501.7 178
> ndatapgs Pgs=>Mb npgsused Pgs=>Mb npgsalloc Pgs=>Mb
> 266225 1039.9 293311 1145.7 294784 1151.5
> From dbschema in dbexport directory:
> { TABLE "opsi".mch row size = 494 number of columns = 68 index size = 838 }
> { unload file name = mch__00458.unl number of rows = 1064897 }
"Denmark B. Weatherburn" wrote:
> I need your advice again.
> We are a financial institution in Belize ("Temptation Island").
> We are running Solaris 2.7 with Informix IDS Workgroup Server 7.30.UC3.
> I (SysAdmin/DBA) want to improve the loading of a GL history table.
> Statistics on table:
>[...snip...]
> create table "opsi".mch
> (
>[...snip...]
> primary key
> (coentfincble,femovcble,cotiuniorgext,couniorgext,cocomcont,nrco
> mcble,nrsecmco)
>
> ) in testgldbs extent size 589568 next size 58956;
> revoke all on "opsi".mch from "public";>
> create index "opsi".akmch on "opsi".mch (coentfincble,nrctacble,
> cotiuniorgext1,couniorgext1) in testgldbs;
> create index "opsi".akmch1 on "opsi".mch (coentfincble,cotiuniorgext1,
> couniorgext1,nrtercble,nrctacble,femovcble,cotiuniorgext,
> couniorgext,cocomcont,nrcomcble) in testgldbs;
A 10-part index? Are you sure this is used somewhere?
> create index "opsi".akmch2 on "opsi".mch (coentfincble,nrctacble,
> cotiuniorgext1,couniorgext1,femovcble) in testgldbs;
akmch2 seems to be a strict superset of akmch -- you only need akmch2.
Now, how many other indexes are redundant?
> create index "opsi".akmch3 on "opsi".mch (coentfincble,femovcble,
> nrctacble,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
> nrsecmco) in testgldbs;
> create index "opsi".akmch4 on "opsi".mch (coentfincble,femovcble,
> nrctacble,cotiuniorgext1,couniorgext1,comon,nrtercble,coprod,
> cosbp,cotiuniorgext2,couniorgext2,cotiuniorgext3,couniorgext3,
> nruserid,txvalsdocble1) in testgldbs;
A 15-part index? Are you sure this is used? Really?
> create index "opsi".akmch5 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
> nrsecmco) in testgldbs;
> create index "opsi".akmch6 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch7 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nruserid,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch8 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nrtercble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch9 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nrctacble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch10 on "opsi".mch (coentfincble,femovcble,
> cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch11 on "opsi".mch (coentfincble,nrctacble,
> femovcble,cotiuniorgext,couniorgext,cocomcont,nrcomcble,nrsecmco) in
> testgldbs;
> create index "opsi".akmch12 on "opsi".mch (coentfincble,cotiuniorgext1,
> couniorgext1,femodmovcble) in testgldbs;
The columns in akmch12 seem to be the same as the first four columns of
akmch10, albeit in a different order; is akmch12 really beneficial?
You're sure that pre-sorting data in index order is more efficient than
letting the server sort it on the fly, bearing in mind the cost of the
DB-Load and the runtime benefits?
Look very hard at your applications; are all these indexes really used?
Could any be dropped so that some application uses a different index,
but still performs adequately?
I'd probably ensure that the applications can all be made to turn SET
EXPLAIN on by configuration variable or environment variable, and I'd
try running everything with SET EXPLAIN on, and then check which indexes
are actually used. I'm sceptical that most of these indexes really are
used.
Also, given the naming scheme (couniorgext[123]. cotiuniorgext[123],
etc), I very much doubt whether the table is in BCNF or 3NF. Also, in
my experience, it is unusual to be using a DECIMAL as the lead-column of
any index, let alone every index. It's a DECIMAL(5,0); I wonder if
you'd be better off using an INTEGER field, or maybe a SMALLINT though I
suspect that range is too limited. DECIMAL(5,0) uses 4 bytes on disk,
the same as INTEGER. A number of the other columns also have
DECIMAL(n,0) types and might also be better converted to INTEGER, with a
CHECK constraint if desired.
Have you tried creating the indexes in a different dbspace from the
table's own dbspace?
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
modify the output for dbexport so that when you dbimport the indexes are not
defined. Loading only the data, the load should be very fast. Then create the
indexes. Overall this will be much faster. And probably easier than HPL.
Henry
"Denmark B. Weatherburn" wrote:
> Hi Informix Users,
>
> I need your advice again.
> We are a financial institution in Belize ("Temptation Island").
> We are running Solaris 2.7 with Informix IDS Workgroup Server 7.30.UC3.
> I (SysAdmin/DBA) want to improve the loading of a GL history table.
> Statistics on table:
>
> Kbytes Mbytes
> tabname rowsize nrows nrows*size nrows*size nexts
> mch 494 1064897 513729.6 501.7 178
>
> ndatapgs Pgs=>Mb npgsused Pgs=>Mb npgsalloc Pgs=>Mb
> 266225 1039.9 293311 1145.7 294784 1151.5
>
> From dbschema in dbexport directory:
>
> { TABLE "opsi".mch row size = 494 number of columns = 68 index size = 838 }
> { unload file name = mch__00458.unl number of rows = 1064897 }
>
> create table "opsi".mch
> (
> coentfincble decimal(5,0) not null ,
> femovcble decimal(8,0) not null ,
> cotiuniorgext char(1) not null ,
> couniorgext decimal(5,0) not null ,
> cocomcont char(3) not null ,
> nrcomcble decimal(5,0) not null ,
> nrsecmco decimal(5,0) not null ,
> nrctacble char(15) not null ,
> txdesmov char(50) not null ,
> .
> .
> primary key
> (coentfincble,femovcble,cotiuniorgext,couniorgext,cocomcont,nrco
> mcble,nrsecmco)
>
> ) in testgldbs extent size 589568 next size 58956;
> revoke all on "opsi".mch from "public";>
> create index "opsi".akmch on "opsi".mch (coentfincble,nrctacble,
> cotiuniorgext1,couniorgext1) in testgldbs;
> create index "opsi".akmch1 on "opsi".mch (coentfincble,cotiuniorgext1,
> couniorgext1,nrtercble,nrctacble,femovcble,cotiuniorgext,
> couniorgext,cocomcont,nrcomcble) in testgldbs;
> create index "opsi".akmch2 on "opsi".mch (coentfincble,nrctacble,
> cotiuniorgext1,couniorgext1,femovcble) in testgldbs;
> create index "opsi".akmch3 on "opsi".mch (coentfincble,femovcble,
> nrctacble,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
> nrsecmco) in testgldbs;
> create index "opsi".akmch4 on "opsi".mch (coentfincble,femovcble,
> nrctacble,cotiuniorgext1,couniorgext1,comon,nrtercble,coprod,
> cosbp,cotiuniorgext2,couniorgext2,cotiuniorgext3,couniorgext3,
> nruserid,txvalsdocble1) in testgldbs;
> create index "opsi".akmch5 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,
> nrsecmco) in testgldbs;
> create index "opsi".akmch6 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch7 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nruserid,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch8 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nrtercble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch9 on "opsi".mch (coentfincble,cotiuniorgext,
> couniorgext,nrctacble,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch10 on "opsi".mch (coentfincble,femovcble,
> cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,nrsecmco) in testgldbs;
> create index "opsi".akmch11 on "opsi".mch (coentfincble,nrctacble,
> femovcble,cotiuniorgext,couniorgext,cocomcont,nrcomcble,nrsecmco) in
> testgldbs;
> create index "opsi".akmch12 on "opsi".mch (coentfincble,cotiuniorgext1,
> couniorgext1,femodmovcble) in testgldbs;
>
> Using dbload this table takes 7.5 hours to load. I am currently setting up a
> testing environment to evaluate other loading methods.
> Notice that currently this table has 178 extents. I want to reduce the
> number of extents to one during the dbimport by adding the "extent/next
> size" clause.
> The migration guide discusses other tools such as onunload/onload and the
> HPL. However, it also says that the HPL (onpload) can't be used with the
> Online Workgroup Server. We have IDS Workgroup Edition 7.30.UC3.
> Please give me some advice about HPL and its use with Workgroup Edition?
> I have the command line utility and I'd loke to try it; however, I have not
> seen any documentation on the HPL (onunload) besides the syntax.
> Is there a scripting language I should learn to be able to use the HPL
> (onpload) effectively? Any examples would be appreciated.
> I've used the onunload/onload once before to move a database between
> different Sun machines, although it was not successfull. The integrity of
> the data was questionable. I had to resort to a regular dbexport/dbimport.
> Perhaps it was related to the fact that the machines had different number
> representations (E3000 => Sparc5).
> I'm willing to try it again though.
> On a related note, I asked the question a few days ago about the optimum
> number of extents for a table. Of course the answer is one. I read all the
> worthy responses. One from Jack Parker caught my attention though.
> He recommends selecting a smaller initial extent size (because index space
> allocation is made according to the initial extent size) followed by a large
> next extent size, loading the table and then altering the next extent size
> downward.
> I have tried in the past to calculate exactly how much space is used by
> indexes directly, but I had to resort to calculations using the dbspace
> size, free space and number of pages allocated. I was able to subtract the
> datapages used and the free space which left me with the storage for
> overhead and index.
> What I found out is that the 12 detached indexes for this table takes up a
> lot of storage in the dbspace. Exactly how much, I don't know. Any ideas
> about calculating the index space usage and using index storage more
> efficiently?
> I know I'm asking for advice about several issues but I think this list is
> the appropriate forum. Especially with experienced user/DBAs participating.
>
> Thanks in advance,
>
> Regards,
>
> Denmark W.