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 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 3cd3dc548331a050
> > > 272: 62 37 35 62 39 39 35 34 62 37 33 38 31 63 64 66 b75b9954b7381cdf
> > > 288: 64 62 36 62 62 64 33 38 32 38 33 63 64 31 34 31 db6bbd38283cd141
> > > 304: 65 31 33 64 62 31 36 38 63 65 64 65 66 32 66 61 e13db168cedef2fa
> > > 320: 61 38 37 65 65 36 32 33 34 34 37 c7 14 e c 10 a87ee623447G....
> > > 336: 6 3b e 0 89 f1 48 0 0 1 a7 0 0 0 0 48 .;...qH...'....H
> > > 352: 1c 65 40 .e@.............
> > > page_type rowid length fwd_ptr
> > > HOME 102 355 0
> > >
> > > 0: 63 36 66 34 37 65 30 32 2d 38 66 35 39 2d 31 31 c6f47e02-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 35 33 38 2e 45 31 .D14037.S1538.E1
> > >
> > > 64: 37 31 36 2e 42 34 34 39 31 34 31 35 2e 47 43 4f 716.B4491415.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 35 33 38 2e 45 31 37 31 36 2e 42 37.S1538.E1716.B
> > > 112: 34 34 39 31 34 31 35 2e 47 43 5f 63 36 66 34 37 4491415.GC_c6f47
> > > 128: 65 30 32 2d 38 66 35 39 2d 31 31 65 33 2d 61 39 e02-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 1c a2 0 31 61 32 61 64 .........".1a2ad
> > > 208: 37 39 62 30 37 34 64 34 37 65 64 63 32 39 38 31 79b074d47edc2981
> > > 224: 37 33 62 63 65 39 33 63 33 66 31 66 39 30 62 34 73bce93c3f1f90b4
> > > 240: 35 63 35 30 35 37 32 37 39 65 30 36 37 39 34 65 5c5057279e06794e
> > > 256: 39 66 66 62 30 61 63 34 36 32 66 64 34 38 39 66 9ffb0ac462fd489f
> > > 272: 62 39 30 64 61 62 38 37 33 35 32 63 39 65 35 33 b90dab87352c9e53
> > > 288: 38 30 66 33 35 37 36 34 32 66 62 30 66 34 62 35 80f357642fb0f4b5
> > > 304: 31 30 35 34 64 38 33 37 63 35 31 31 65 39 38 63 1054d837c511e98c
> > > 320: 66 38 31 31 30 62 34 33 65 38 62 c7 14 e c 10 f8110b43e8bG....
> > > 336: 6 3b d 0 89 f1 48 0 0 1 a7 0 0 0 0 6e .;...qH...'....n
> > > 352: 52 91 0 R...............
> > > page_type rowid length fwd_ptr
> > > HOME 103 355 0
> > >
> > > 0: 30 38 65 34 39 63 39 61 2d 38 66 34 39 2d 31 31 08e49c9a-8f49-11
> > >
> > > 16: 65 33 2d 62 61 31 38 2d 35 63 66 33 66 63 62 38 e3-ba18-5cf3fcb8
> > >
> > > 32: 64 38 37 38 2a 4e 53 53 2e 41 4d 41 58 2e 4e 4c d878*NSS.AMAX.NL
> > >
> > > 48: 2e 44 31 34 30 33 37 2e 53 30 38 35 36 2e 45 31 .D14037.S0856.E1
> > >
> > > --001a1133bfb4f6ffb705147b1f25
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1132f21454a29805147b52d0
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1147d51833628505147c03a9
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113f3d1ca8c67e05147c1ecc
