IDS Forum

Re: disk consumption analysis

Posted By: FRANK
Date: Friday, 24 April 2015, at 1:49 p.m.

In Response To: Re: disk consumption analysis (Art Kagel)

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

Messages In This Thread

• disk consumption analysis
  FRANK -- Friday, 24 April 2015, at 12:45 p.m.
  • Re: disk consumption analysis
    Art Kagel -- Friday, 24 April 2015, at 12:59 p.m.
    • Re: disk consumption analysis
      FRANK -- Friday, 24 April 2015, at 1:49 p.m.
      • Re: disk consumption analysis
        Art Kagel -- Friday, 24 April 2015, at 1:56 p.m.
  • Re: disk consumption analysis
    John Miller iii -- Friday, 24 April 2015, at 8:08 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.