disk consumption analysis
Folks,
IDS12.10 FC4, RHEL.
I have Two tables ( see below for details).
I copied three set of information for each,
--schema definition ( ignored index part)
-- part of output oncheck -pt
-- part of output of oncheck -pd
The two tables consumed similar size of data pages, but one can hold
about 9 millions of rows, another can only hold about 4 millions of rows.
The row sizes are similar too ( close lengths, 704 vs. 744). page size is
2K.
Any comments?
Thanks
Frank
=========table ds_archive18
{ TABLE "informix".ds_archive18 row size = 704 number of columns = 11 index
size = 295 }
create table "informix".ds_archive18
(
dataset_name varchar(255,44) not null ,
archive_site char(10) not null ,
path_name varchar(250,50),
archive_dt datetime year to second,
update_dt datetime year to second,
hsm_fs char(10),
check_sum varchar(128),
tape_name char(16),
tape_block integer,
tape_offset integer,
checksum_dt datetime year to second
) in dbdw04 extent size 4096 next size 4096 lock mode row;
[informix@walter info]$ oncheck -pt noaa:ds_archive18
TBLspace Report for noaa:informix.ds_archive18
Physical Address 53:3373732
Creation date 11/06/2013 17:20:43
TBLspace Flags 902 Row Locking
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 704
Number of special columns 3
Number of keys 0
Number of extents 16
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 2048
Next extent size 131072
Number of pages allocated 1310720
Number of pages used 1292403
Number of data pages 1292081
Number of rows 9044549
Partition partnum 30408942
Partition lockid 30408942
[informix@walter info]$ oncheck -pd noaa:ds_archive18 | more
TBLspace data check for noaa:informix.ds_archive18
page_type rowid length fwd_ptr
HOME 101 197 0
0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
16: 34 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4N.S1106006.E110
32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
80: 31 c7 14 b 3 9 12 20 19 0 0 0 0 0 0 0 1G..... ........
96: 0 41 52 43 48 53 4d 30 32 20 20 20 64 63 39 31 .ARCHSM02 dc91
112: 35 36 31 31 34 63 61 36 63 36 38 63 65 31 39 37 56114ca6c68ce197
128: 65 63 38 30 64 63 39 64 39 64 63 66 30 30 36 39 ec80dc9d9dcf0069
144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c a8 43 ...(
160: 0 2 cf 80 c7 14 c c 15 17 2b 1f 0 0 20 0 ..O.G.....+... .
176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
192: d0 0 25 0 16 P...............
page_type rowid length fwd_ptr
HOME 102 197 0
0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
16: 34 53 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4S.S1106006.E110
32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
80: 32 c7 14 b 3 9 12 20 1e 0 0 0 0 0 0 0 2G..... ........
96: 0 41 52 43 48 53 4d 30 32 20 20 20 66 37 62 31 .ARCHSM02 f7b1
112: 62 31 38 30 63 34 34 36 64 37 35 39 35 38 66 35 b180c446d75958f5
128: 39 62 66 63 39 62 62 30 65 65 64 39 30 30 36 39 9bfc9bb0eed90069
144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c f4 43 ...t
160: 0 5 51 80 c7 14 c c 15 17 2b 20 0 0 20 0 ..Q.G.....+ .. .
176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
192: d0 0 25 0 16 P...............
page_type rowid length fwd_ptr
HOME 103 197 0
0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
16: 35 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 5N.S1106006.E110
32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
--More--
=========table archive_0006
{ TABLE "informix".archive_0006 row size = 744 number of columns = 15 index
size = 78 }
create table "informix".archive_0006
(
uuid char(36) not null ,
dataset_name varchar(255,12) not null ,
file_name varchar(255,49) not null ,
path_name char(9) not null ,
active char(1) not null ,
datatype_name char(10) not null ,
archive_dt datetime year to second not null ,
update_dt datetime year to second,
file_size_bytes bigint not null ,
md5_checksum char(32),
sha2_checksum char(96),
checksum_dt datetime year to second,
tape_name integer,
tape_position integer,
tape_offset bigint
) in dbdata09 extent size 4096 next size 4096 lock mode row;
[informix@walter ~]$ oncheck -pt noaa:archive_0006
TBLspace Report for noaa:informix.archive_0006
Physical Address 38:187415
Creation date 08/19/2014 14:53:32
TBLspace Flags 902 Row Locking
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 744
Number of special columns 2
Number of keys 0
Number of extents 46
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 2048
Next extent size 131072
Number of pages allocated 1310720
Number of pages used 1193119
Number of data pages 1192823
Number of rows 4389102
Partition partnum 23068967
Partition lockid 23068967
[informix@walter ~]$ oncheck -pd noaa:archive_0006 | more
TBLspace data check for noaa:informix.archive_0006
page_type rowid length fwd_ptr
HOME 101 355 0
0: 63 36 62 61 30 62 31 34 2d 38 66 35 39 2d 31 31 c6ba0b14-8f59-11
16: 65 33 2d 61 39 63 62 2d 35 63 66 33 66 63 62 38 e3-a9cb-5cf3fcb8
32: 64 38 37 38 2a 4e 53 53 2e 41 4d 41 58 2e 4e 4e d878*NSS.AMAX.NN
48: 2e 44 31 34 30 33 37 2e 53 31 33 34 39 2e 45 31 .D14037.S1349.E1
64: 35 34 34 2e 42 34 34 39 31 33 31 34 2e 47 43 4f 544.B4491314.GCO
80: 4e 53 53 2e 41 4d 41 58 2e 4e 4e 2e 44 31 34 30 NSS.AMAX.NN.D140
96: 33 37 2e 53 31 33 34 39 2e 45 31 35 34 34 2e 42 37.S1349.E1544.B
112: 34 34 39 31 33 31 34 2e 47 43 5f 63 36 62 61 30 4491314.GC_c6ba0
128: 62 31 34 2d 38 66 35 39 2d 31 31 65 33 2d 61 39 b14-8f59-11e3-a9
144: 63 62 2d 35 63 66 33 66 63 62 38 64 38 37 38 2f cb-5cf3fcb8d878/
160: 32 30 31 34 30 32 30 36 59 41 54 4f 56 5f 41 4d 20140206YATOV_AM
176: 53 55 41 c7 14 e 2 6 12 d 8 0 0 0 0 0 SUAG............
192: 0 0 0 0 0 0 0 0 21 a2 0 62 37 35 38 32 ........!".b7582
208: 63 30 66 38 62 35 65 30 61 65 38 63 63 38 66 65 c0f8b5e0ae8cc8fe
224: 32 31 62 33 30 37 32 32 63 39 35 37 65 30 39 34 21b30722c957e094
240: 32 61 62 38 38 32 31 37 65 38 36 32 65 34 39 39 2ab88217e862e499
256: 33 63 64 33 64 63 35 34 38 33 33 31 61 30 35 30 3cd3dc548331
You have two variable length columns in each table that can vary the row
sizes from 326 & 366 bytes if both are null to 704 & 744 bytes if both are
full. That's a 50% range. If one table's average actual row size is close
to max and the other is close to minimum then the difference in the number
of rows being stored in roughly the same disk space makes sense.
This will give you the averages for the second table:
select avg(length(file_name)), avg(length(dataset_name )) from archive_0006;
I'll leave the other table as an exercise. B^)
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
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 Fri, Apr 24, 2015 at 12:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Folks,
>
> IDS12.10 FC4, RHEL.
>
> I have Two tables ( see below for details).
>
> I copied three set of information for each,
> --schema definition ( ignored index part)
> -- part of output oncheck -pt
> -- part of output of oncheck -pd
>
> The two tables consumed similar size of data pages, but one can hold
> about 9 millions of rows, another can only hold about 4 millions of rows.
>
> The row sizes are similar too ( close lengths, 704 vs. 744). page size is
> 2K.
>
> Any comments?
>
> Thanks
> Frank
>
> =========table ds_archive18
>
> { TABLE "informix".ds_archive18 row size = 704 number of columns = 11 index
> size = 295 }
> create table "informix".ds_archive18
> (
>
> dataset_name varchar(255,44) not null ,
>
> archive_site char(10) not null ,
>
> path_name varchar(250,50),
>
> archive_dt datetime year to second,
>
> update_dt datetime year to second,
>
> hsm_fs char(10),
>
> check_sum varchar(128),
>
> tape_name char(16),
>
> tape_block integer,
>
> tape_offset integer,
>
> checksum_dt datetime year to second
> ) in dbdw04 extent size 4096 next size 4096 lock mode row;
>
> [informix@walter info]$ oncheck -pt noaa:ds_archive18
>
> TBLspace Report for noaa:informix.ds_archive18
>
> Physical Address 53:3373732
>
> Creation date 11/06/2013 17:20:43
>
> TBLspace Flags 902 Row Locking
>
> TBLspace contains VARCHARS
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 704
>
> Number of special columns 3
>
> Number of keys 0
>
> Number of extents 16
>
> Current serial value 1
>
> Current SERIAL8 value 1
>
> Current BIGSERIAL value 1
>
> Current REFID value 1
>
> Pagesize (k) 2
>
> First extent size 2048
>
> Next extent size 131072
>
> Number of pages allocated 1310720
>
> Number of pages used 1292403
>
> Number of data pages 1292081
>
> Number of rows 9044549
>
> Partition partnum 30408942
>
> Partition lockid 30408942
>
> [informix@walter info]$ oncheck -pd noaa:ds_archive18 | more
>
> TBLspace data check for noaa:informix.ds_archive18
> page_type rowid length fwd_ptr
> HOME 101 197 0
>
> 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
>
> 16: 34 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4N.S1106006.E110
>
> 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
>
> 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
>
> 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
>
> 80: 31 c7 14 b 3 9 12 20 19 0 0 0 0 0 0 0 1G..... ........
>
> 96: 0 41 52 43 48 53 4d 30 32 20 20 20 64 63 39 31 .ARCHSM02 dc91
> 112: 35 36 31 31 34 63 61 36 63 36 38 63 65 31 39 37 56114ca6c68ce197
> 128: 65 63 38 30 64 63 39 64 39 64 63 66 30 30 36 39 ec80dc9d9dcf0069
> 144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c a8 43 ...(
> 160: 0 2 cf 80 c7 14 c c 15 17 2b 1f 0 0 20 0 ..O.G.....+... .
> 176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
> 192: d0 0 25 0 16 P...............
> page_type rowid length fwd_ptr
> HOME 102 197 0
>
> 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
>
> 16: 34 53 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4S.S1106006.E110
>
> 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
>
> 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
>
> 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
>
> 80: 32 c7 14 b 3 9 12 20 1e 0 0 0 0 0 0 0 2G..... ........
>
> 96: 0 41 52 43 48 53 4d 30 32 20 20 20 66 37 62 31 .ARCHSM02 f7b1
> 112: 62 31 38 30 63 34 34 36 64 37 35 39 35 38 66 35 b180c446d75958f5
> 128: 39 62 66 63 39 62 62 30 65 65 64 39 30 30 36 39 9bfc9bb0eed90069
> 144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c f4 43 ...t
> 160: 0 5 51 80 c7 14 c c 15 17 2b 20 0 0 20 0 ..Q.G.....+ .. .
> 176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
> 192: d0 0 25 0 16 P...............
> page_type rowid length fwd_ptr
> HOME 103 197 0
>
> 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
>
> 16: 35 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 5N.S1106006.E110
>
> 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> --More--
>
> =========table archive_0006
> { TABLE "informix".archive_0006 row size = 744 number of columns = 15 index
> size = 78 }
> create table "informix".archive_0006
> (
>
> uuid char(36) not null ,
>
> dataset_name varchar(255,12) not null ,
>
> file_name varchar(255,49) not null ,
>
> path_name char(9) not null ,
>
> active char(1) not null ,
>
> datatype_name char(10) not null ,
>
> archive_dt datetime year to second not null ,
>
> update_dt datetime year to second,
>
> file_size_bytes bigint not null ,
>
> md5_checksum char(32),
>
> sha2_checksum char(96),
>
> checksum_dt datetime year to second,
>
> tape_name integer,
>
> tape_position integer,
>
> tape_offset bigint
> ) in dbdata09 extent size 4096 next size 4096 lock mode row;
>
> [informix@walter ~]$ oncheck -pt noaa:archive_0006
>
> TBLspace Report for noaa:informix.archive_0006
>
> Physical Address 38:187415
>
> Creation date 08/19/2014 14:53:32
>
> TBLspace Flags 902 Row Locking
>
> TBLspace contains VARCHARS
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 744
>
> Number of special columns 2
>
> Number of keys 0
>
> Number of extents 46
>
> Current serial value 1
>
> Current SERIAL8 value 1
>
> Current BIGSERIAL value 1
>
> Current REFID value 1
>
> Pagesize (k) 2
>
> First extent size 2048
>
> Next extent size 131072
>
> Number of pages allocated 1310720
>
> Number of pages used 1193119
>@@NL@
Thanks Art!
Yes, one table got actual data( of the two VARCHAR columns) lot longer
than another .
The following is its compression ratio, do you think it is worth or good to
be compressed? ( ratio not high, best >60% ? )
EXECUTE FUNCTION sysadmin:task("table estimate_compression",
"archive_0006", "noaa", "informix ")
(expression) est curr change partnum coloff table/index
----- ----- ------ ---------- -----
---------------------------
48.6% 0.0% +48.6 0x01600127 -1 noaa:informix.archive_0006
Succeeded: table estimate_compression
noaa:informix.archive_0006
Thanks
Frank
On Fri, Apr 24, 2015 at 12:59 PM, Art Kagel <art.kagel@gmail.com> wrote:
> You have two variable length columns in each table that can vary the row
> sizes from 326 & 366 bytes if both are null to 704 & 744 bytes if both are
> full. That's a 50% range. If one table's average actual row size is close
> to max and the other is close to minimum then the difference in the number
> of rows being stored in roughly the same disk space makes sense.
>
> This will give you the averages for the second table:
>
> select avg(length(file_name)), avg(length(dataset_name )) from
> archive_0006;
>
> I'll leave the other table as an exercise. B^)
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> 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 Fri, Apr 24, 2015 at 12:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Folks,
> >
> > IDS12.10 FC4, RHEL.
> >
> > I have Two tables ( see below for details).
> >
> > I copied three set of information for each,
> > --schema definition ( ignored index part)
> > -- part of output oncheck -pt
> > -- part of output of oncheck -pd
> >
> > The two tables consumed similar size of data pages, but one can hold
> > about 9 millions of rows, another can only hold about 4 millions of rows.
> >
> > The row sizes are similar too ( close lengths, 704 vs. 744). page size is
> > 2K.
> >
> > Any comments?
> >
> > Thanks
> > Frank
> >
> > =========table ds_archive18
> >
> > { TABLE "informix".ds_archive18 row size = 704 number of columns = 11
> index
> > size = 295 }
> > create table "informix".ds_archive18
> > (
> >
> > dataset_name varchar(255,44) not null ,
> >
> > archive_site char(10) not null ,
> >
> > path_name varchar(250,50),
> >
> > archive_dt datetime year to second,
> >
> > update_dt datetime year to second,
> >
> > hsm_fs char(10),
> >
> > check_sum varchar(128),
> >
> > tape_name char(16),
> >
> > tape_block integer,
> >
> > tape_offset integer,
> >
> > checksum_dt datetime year to second
> > ) in dbdw04 extent size 4096 next size 4096 lock mode row;
> >
> > [informix@walter info]$ oncheck -pt noaa:ds_archive18
> >
> > TBLspace Report for noaa:informix.ds_archive18
> >
> > Physical Address 53:3373732
> >
> > Creation date 11/06/2013 17:20:43
> >
> > TBLspace Flags 902 Row Locking
> >
> > TBLspace contains VARCHARS
> >
> > TBLspace use 4 bit bit-maps
> >
> > Maximum row size 704
> >
> > Number of special columns 3
> >
> > Number of keys 0
> >
> > Number of extents 16
> >
> > Current serial value 1
> >
> > Current SERIAL8 value 1
> >
> > Current BIGSERIAL value 1
> >
> > Current REFID value 1
> >
> > Pagesize (k) 2
> >
> > First extent size 2048
> >
> > Next extent size 131072
> >
> > Number of pages allocated 1310720
> >
> > Number of pages used 1292403
> >
> > Number of data pages 1292081
> >
> > Number of rows 9044549
> >
> > Partition partnum 30408942
> >
> > Partition lockid 30408942
> >
> > [informix@walter info]$ oncheck -pd noaa:ds_archive18 | more
> >
> > TBLspace data check for noaa:informix.ds_archive18
> > page_type rowid length fwd_ptr
> > HOME 101 197 0
> >
> > 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
> >
> > 16: 34 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4N.S1106006.E110
> >
> > 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> >
> > 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
> >
> > 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
> >
> > 80: 31 c7 14 b 3 9 12 20 19 0 0 0 0 0 0 0 1G..... ........
> >
> > 96: 0 41 52 43 48 53 4d 30 32 20 20 20 64 63 39 31 .ARCHSM02 dc91
> > 112: 35 36 31 31 34 63 61 36 63 36 38 63 65 31 39 37 56114ca6c68ce197
> > 128: 65 63 38 30 64 63 39 64 39 64 63 66 30 30 36 39 ec80dc9d9dcf0069
> > 144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c a8 43 ...(
> > 160: 0 2 cf 80 c7 14 c c 15 17 2b 1f 0 0 20 0 ..O.G.....+... .
> > 176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
> > 192: d0 0 25 0 16 P...............
> > page_type rowid length fwd_ptr
> > HOME 102 197 0
> >
> > 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
> >
> > 16: 34 53 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4S.S1106006.E110
> >
> > 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> >
> > 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
> >
> > 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
> >
> > 80: 32 c7 14 b 3 9 12 20 1e 0 0 0 0 0 0 0 2G..... ........
> >
> > 96: 0 41 52 43 48 53 4d 30 32 20 20 20 66 37 62 31 .ARCHSM02 f7b1
> > 112: 62 31 38 30 63 34 34 36 64 37 35 39 35 38 66 35 b180c446d75958f5
> > 128: 39 62 66 63 39 62 62 30 65 65 64 39 30 30 36 39 9bfc9bb0eed90069
> > 144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c f4 43 ...t
> > 160: 0 5 51 80 c7 14 c c 15 17 2b 20 0 0 20 0 ..Q.G.....+ .. .
> > 176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
> > 192: d0 0 25 0 16 P...............
> > page_type rowid length fwd_ptr
> > HOME 103 197 0
> >
> > 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
> >
> > 16: 35 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 5N.S1106006.E110
> >
> > 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> > --More--
> >
> > =========table archive_0006
> > { TABLE "informix".archive_0006 row size = 744 number of columns = 15
> index
> > size = 78 }
> > create table "informix".archive_0006
> > (
> >
> > uuid char(36) not null ,
> >
> > dataset_name varchar(255,12) not null ,
> >
> > file_name varchar(255,49) not null ,
> >
> > path_name char(9) not null ,
> >
> > active char(1) not null ,
> >
> > datatype_name char(10) not null ,
> >
> > archive_dt datetime year to second not nul
60% conmpression isn't great, no. I'd say this one is a wash between the
performance gained from storage and memory savings and the performance cost
of decompressing the data when it's needed.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
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 Fri, Apr 24, 2015 at 1:49 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks Art!
>
> Yes, one table got actual data( of the two VARCHAR columns) lot longer
> than another .
>
> The following is its compression ratio, do you think it is worth or good to
> be compressed? ( ratio not high, best >60% ? )
>
> EXECUTE FUNCTION sysadmin:task("table estimate_compression",
> "archive_0006", "noaa", "informix ")
> (expression) est curr change partnum coloff table/index>
> ----- ----- ------ ---------- -----
> ---------------------------
>
> 48.6% 0.0% +48.6 0x01600127 -1 noaa:informix.archive_0006
>
> Succeeded: table estimate_compression
> noaa:informix.archive_0006
>
> Thanks
> Frank
>
> On Fri, Apr 24, 2015 at 12:59 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > You have two variable length columns in each table that can vary the row
> > sizes from 326 & 366 bytes if both are null to 704 & 744 bytes if both
> are
> > full. That's a 50% range. If one table's average actual row size is close
> > to max and the other is close to minimum then the difference in the
> number
> > of rows being stored in roughly the same disk space makes sense.
> >
> > This will give you the averages for the second table:
> >
> > select avg(length(file_name)), avg(length(dataset_name )) from
> > archive_0006;
> >
> > I'll leave the other table as an exercise. B^)
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com
> >
> > 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 Fri, Apr 24, 2015 at 12:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Folks,
> > >
> > > IDS12.10 FC4, RHEL.
> > >
> > > I have Two tables ( see below for details).
> > >
> > > I copied three set of information for each,
> > > --schema definition ( ignored index part)
> > > -- part of output oncheck -pt
> > > -- part of output of oncheck -pd
> > >
> > > The two tables consumed similar size of data pages, but one can hold
> > > about 9 millions of rows, another can only hold about 4 millions of
> rows.
> > >
> > > The row sizes are similar too ( close lengths, 704 vs. 744). page size
> is
> > > 2K.
> > >
> > > Any comments?
> > >
> > > Thanks
> > > Frank
> > >
> > > =========table ds_archive18
> > >
> > > { TABLE "informix".ds_archive18 row size = 704 number of columns = 11
> > index
> > > size = 295 }
> > > create table "informix".ds_archive18
> > > (
> > >
> > > dataset_name varchar(255,44) not null ,
> > >
> > > archive_site char(10) not null ,
> > >
> > > path_name varchar(250,50),
> > >
> > > archive_dt datetime year to second,
> > >
> > > update_dt datetime year to second,
> > >
> > > hsm_fs char(10),
> > >
> > > check_sum varchar(128),
> > >
> > > tape_name char(16),
> > >
> > > tape_block integer,
> > >
> > > tape_offset integer,
> > >
> > > checksum_dt datetime year to second
> > > ) in dbdw04 extent size 4096 next size 4096 lock mode row;
> > >
> > > [informix@walter info]$ oncheck -pt noaa:ds_archive18
> > >
> > > TBLspace Report for noaa:informix.ds_archive18
> > >
> > > Physical Address 53:3373732
> > >
> > > Creation date 11/06/2013 17:20:43
> > >
> > > TBLspace Flags 902 Row Locking
> > >
> > > TBLspace contains VARCHARS
> > >
> > > TBLspace use 4 bit bit-maps
> > >
> > > Maximum row size 704
> > >
> > > Number of special columns 3
> > >
> > > Number of keys 0
> > >
> > > Number of extents 16
> > >
> > > Current serial value 1
> > >
> > > Current SERIAL8 value 1
> > >
> > > Current BIGSERIAL value 1
> > >
> > > Current REFID value 1
> > >
> > > Pagesize (k) 2
> > >
> > > First extent size 2048
> > >
> > > Next extent size 131072
> > >
> > > Number of pages allocated 1310720
> > >
> > > Number of pages used 1292403
> > >
> > > Number of data pages 1292081
> > >
> > > Number of rows 9044549
> > >
> > > Partition partnum 30408942
> > >
> > > Partition lockid 30408942
> > >
> > > [informix@walter info]$ oncheck -pd noaa:ds_archive18 | more
> > >
> > > TBLspace data check for noaa:informix.ds_archive18
> > > page_type rowid length fwd_ptr
> > > HOME 101 197 0
> > >
> > > 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
> > >
> > > 16: 34 4e 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4N.S1106006.E110
> > >
> > > 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> > >
> > > 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
> > >
> > > 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
> > >
> > > 80: 31 c7 14 b 3 9 12 20 19 0 0 0 0 0 0 0 1G..... ........
> > >
> > > 96: 0 41 52 43 48 53 4d 30 32 20 20 20 64 63 39 31 .ARCHSM02 dc91
> > > 112: 35 36 31 31 34 63 61 36 63 36 38 63 65 31 39 37 56114ca6c68ce197
> > > 128: 65 63 38 30 64 63 39 64 39 64 63 66 30 30 36 39 ec80dc9d9dcf0069
> > > 144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c a8 43 ...(
> > > 160: 0 2 cf 80 c7 14 c c 15 17 2b 1f 0 0 20 0 ..O.G.....+... .
> > > 176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
> > > 192: d0 0 25 0 16 P...............
> > > page_type rowid length fwd_ptr
> > > HOME 102 197 0
> > >
> > > 0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
> > >
> > > 16: 34 53 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4S.S1106006.E110
> > >
> > > 32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
> > >
> > > 48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73 class_dirs/archs
> > >
> > > 64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 30 m02/archive18/00
> > >
> > > 80: 32 c7 14 b 3 9 12 20 1e 0 0 0 0 0 0 0 2G..... ........
> > >
> > > 96: 0 41 52 43 48 53 4d 30 32 20 20 20 66 37 62 31 .ARCHSM02 f7b1
> > > 112: 62 31 38 30 63 34 34 36 64 37 35 39 35 38 66 3
If you are using vachars with fixed length data then
=0D<= DIV>things will not be that optimal for storage. varchars
=0Dassume random length data to be optimal in storage.=0D
<= /DIV>=0D
One think you should try is setting MAX=5FFILL=5FDATA=5FPAGES =
=0D
in the onconfig. This will attempt to be more spa= ce efficient.
=0D
=0D
=0D
See ever= yone at IIUG. -:)
=0D
=0D
=0D
=0D
=0D
John F. Miller III
=0D
STSM, Lead &nb= sp;Architect
=0D
[1]miller3@us.= ibm.com
=0D
503-747-1366
=0D
IBM Informix Dynamic Ser= ver (IDS) =0D
-----ids-bounce= s@iiug.org wrote: -----
>To: ids@iiug.org
>From: "FR= ANK"
>Sent by: ids-bounces@iiug.org
>Date: 04/24/2015 09:47AM<= BR>>Subject: disk consumption analysis
[35013]
>
>
>Fo= lks,
>
>IDS12.10 FC4, RHEL.
>
>I have Two tables = ( see below for details).
>
>I copied three set of information= for each,
>--schema definition ( ignored index part)
>-- par= t of output oncheck -pt
>-- part of output of oncheck -pd
>>The two tables consumed similar size of data pages, but one can
hold<= BR>>
>about 9 millions of rows, another can only hold about 4 mill= ions of
>rows.
>
>The row sizes are similar too ( close = lengths, 704 vs. 744). page
>size is
>2K.
>
>Any = comments?
>
>Thanks
>Frank
>
>=3D=3D=3D=3D= =3D=3D=3D=3D=3Dtable ds=5Farchive18
>
>{ TABLE "informix".ds= =5Farchive18 row size =3D 704 number of
columns =3D 11
>index
>= ;size =3D 295 }
>create table "informix".ds=5Farchive18
>( >
>dataset=5Fname varchar(255,44) not null ,
>
>arc= hive=5Fsite char(10) not null ,
>
>path=5Fname varchar(250,50)= ,
>
>archive=5Fdt datetime year to second,
>
>upd= ate=5Fdt datetime year to second,
>
>hsm=5Ffs char(10),
&g= t;
>check=5Fsum varchar(128),
>
>tape=5Fname char(16), <= BR>>
>tape=5Fblock integer,
>
>tape=5Foffset integer,=
>
>checksum=5Fdt datetime year to second
>) in dbdw04 = extent size 4096 next size 4096 lock mode row;
>
>[informix@wa= lter info]$ oncheck -pt noaa:ds=5Farchive18
>
>TBLspace Report= for noaa:informix.ds=5Farchive18
>
>Physical Address 53:33737= 32
>
>Creation date 11/06/2013 17:20:43
>
>TBLspa= ce Flags 902 Row Locking
>
>TBLspace contains VARCHARS
>= ;
>TBLspace use 4 bit bit-maps
>
>Maximum row size 704 <= BR>>
>Number of special columns 3
>
>Number of keys 0=
>
>Number of extents 16
>
>Current serial value = 1
>
>Current SERIAL8 value 1
>
>Current BIGSERIAL= value 1
>
>Current REFID value 1
>
>Pagesize (k)= 2
>
>First extent size 2048
>
>Next extent size = 131072
>
>Number of pages allocated 1310720
>
>Nu= mber of pages used 1292403
>
>Number of data pages 1292081
>
>Number of rows 9044549
>
>Partition partnum 30408= 942
>
>Partition lockid 30408942
>
>[informix@wal= ter info]$ oncheck -pd noaa:ds=5Farchive18 | more
>
>TBLspace = data check for noaa:informix.ds=5Farchive18
>page=5Ftype rowid lengt= h fwd=5Fptr
>HOME 101 197 0
>
>0: 23 50 52 44 2e 53 50 = 50 52 4f 44 2e 45 44 52 31 #PRD.SPPROD.EDR1
>
>16: 34 4e 2e 53= 31 31 30 36 30 30 36 2e 45 31 31 30 4N.S1106006.E110
>
>
&g= t;32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
><= BR>>48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 72 63 68 73
class=5Fdirs/arc= hs
>
>
>64: 6d 30 32 2f 61 72 63 68 69 76 65 31 38 2f 30 = 30
m02/archive18/00
>
>
>80: 31 c7 14 b 3 9 12 20 19 0 0 = 0 0 0 0 0 1G..... ........
>
>96: 0 41 52 43 48 53 4d 30 32 20= 20 20 64 63 39 31 .ARCHSM02 dc91
>112: 35 36 31 31 34 63 61 36 63 3= 6 38 63 65 31 39 37
56114ca6c68ce197
>
>128: 65 63 38 30 64 63 = 39 64 39 64 63 66 30 30 36 39
ec80dc9d9dcf0069
>
>144: 34 33 20= 20 20 20 20 20 20 20 20 20 0 7 c a8 43 ...(
>160: 0 2 cf 80 c7 14 c= c 15 17 2b 1f 0 0 20 0 ..O.G.....+... .
>176: 0 0 0 0 0 0 0 0 0 0 1= 0 0 0 0 0 ................
>192: d0 0 25 0 16 P...............
= >page=5Ftype rowid length fwd=5Fptr
>HOME 102 197 0
>
&= gt;0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 52 31
#PRD.SPPROD.EDR1
= >
>16: 34 53 2e 53 31 31 30 36 30 30 36 2e 45 31 31 30 4S.S1106006=
.E110
>
>
>32: 36 30 31 32 4e 47 44 43 20 20 20 20 20 20 = 22 2f 6012NGDC "/
>
>48: 63 6c 61 73 73 5f 64 69 72 73 2f 61 7= 2 63 68 73
class=5Fdirs/archs
>
>
>64: 6d 30 32 2f 61 72 = 63 68 69 76 65 31 38 2f 30 30
m02/archive18/00
>
>
>80: 3= 2 c7 14 b 3 9 12 20 1e 0 0 0 0 0 0 0 2G..... ........
>
>96: 0= 41 52 43 48 53 4d 30 32 20 20 20 66 37 62 31 .ARCHSM02 f7b1
>112: 6= 2 31 38 30 63 34 34 36 64 37 35 39 35 38 66 35
b180c446d75958f5
>
= >128: 39 62 66 63 39 62 62 30 65 65 64 39 30 30 36 39
9bfc9bb0eed90069>
>144: 34 33 20 20 20 20 20 20 20 20 20 20 0 7 c f4 43 ...t
>160: 0 5 51 80 c7 14 c c 15 17 2b 20 0 0 20 0 ..Q.G.....+ .. .
>= ;176: 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 ................
>192: d0 0 25= 0 16 P...............
>page=5Ftype rowid length fwd=5Fptr
>H= OME 103 197 0
>
>0: 23 50 52 44 2e 53 50 50 52 4f 44 2e 45 44 = 52 31 #PRD.SPPROD.EDR1
>
>16: 35 4e 2e 53 31 31 30 36 30 30 36= 2e 45 31 31 30 5N.S1106006.E110
>
>
>32: 36 30 31 32 4e = 47 44 43 20 20 20 20 20 20 22 2f 6012NGDC "/
>--More--
>
&= gt;=3D=3D=3D=3D=3D=3D=3D=3D=3Dtable archive=5F0006
>{ TABLE "informi= x".archive=5F0006 row size =3D 744 number of
columns =3D 15
>index >size =3D 78 }
>create table "informix".archive=5F0006
>= (
>
>uuid char(36) not null ,
>
>dataset=5Fname v= archar(255,12) not null ,
>
>file=5Fname varchar(255,49) not n= ull ,
>
>path=5Fname char(9) not null ,
>
>active= char(1) not null ,
>
>datatype=5Fname char(10) not null ,
@