Re: Logical Files (Views) - Impact on Performance (I/O) -- MIDRANGE-L

Agree that the larger the key the less efficient I/O is...

What I was saying is that the poor disk I/O is not caused by the space
taken by LFs (or any other object for that matter)

You'd see the basically same disk I/O when your disk space is at 10% used
or 40% used.

At least until you get to the 90-98% threshold. :)

And granted, when dealing with spiny disks, it's not 100% technically
true. As you can see better performance with 10% used vs. 70% used.

Charles

On Fri, May 20, 2016 at 10:15 AM, DrFranken <midrange@xxxxxxxxxxxx> wrote:

Remember that LFs (Access Paths) show up on listings such as DSPLIB and
the disk tools reports. They do have real size based on the size of the key
and the necessary parts to point back to the correct rows. I have seen
this myself at multiple customers where they did in fact exceed the size of
the data!

As to their size having no impact on I/O that I would disagree with.
ANYTHING that is bigger requires more I/O to process. If I have some 4
character field as a key a single block of input from that access path may
pull in hundreds of key values while a 400 byte key (yep, seen that!) will
pull in only a few. So that will certainly have an effect.

Of course the fact that so many of these exists could have a huge impact
on overall I/O due to all the maintenance going on.

Spot on though with build sequence. In fact in reality you only need LF3
in your example if developers are willing to accept that. (Some, oddly, are
not!)

- Larry "DrFranken" Bolhuis

www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.

On 5/20/2016 9:05 AM, Charles Wilt wrote:

How did you calculate the the LF's were taking 57% of the space?

The only thing that takes up space in a logical is the key storage.

--For a logical, --
Using DSPFD
Index size . . . . . . . . . . . . . . : 1597440
Total of member sizes . . . . . . . . . . : 1597440

Using DSPOBJD
Storage information:
Size . . . . . . . . . . . . . . . . : 1658880
Offline size . . . . . . . . . . . . : 1630208

--For the physical--
Using DSPFD
Total of member sizes . . . . . . . . . . : 28897280
Storage information:
Size . . . . . . . . . . . . . . . . : 28987392
Offline size . . . . . . . . . . . . : 28938240

Additionally, the amount of space used by a LF has absolutely no effect on
Disk I/O...

The only negative effect a LF has on disk I/O is additional overhead when
one of the key field changes. This can be minimized by sharing access
paths. For example lets say you have the following LFs
LF (keys)
LF1 (FLD1)
LF2 (FLD1, FLD2)
LF3 (FLD1, FLD2, FLD3)

If the LFs were created in order, (LF1, LF2, LF3) then they each take up
space and if FLD1 happens to change, each on has to be maintained.

If however they are created LF3, LF2, LF1; then only LF3 takes any real
space and it's the only object that needs maintained when FLD1 changes.

Charles

On Fri, May 20, 2016 at 7:36 AM, <Kok.Gregory@xxxxxxxxxxxxxx> wrote:

Hello Midrangers.

We recently implemented a new system on our IBM i720 (v6r1m0).
Performance
is since negatively impacted and the bottle-neck is massively I/O
related.
Disks are not keeping up.

Looking at the size of the systems database, it is 53% Logical Files
(Views).
All these views (LF's) were created (CRTLF) with the attribute FILETYPE =
DATA. (Most LF's reference a single Physical File only, not multiple
PF's)
These LF's are occupying physical space on disk (53%... more than half
the
actual database) and I'm concerned there is an I/O impact because of this
?

Can someone elaborate on what the FILETYPE = DATA setting does ? Is it
duplicating physical file data and possibly the source of severe I/O
activity ? (Whats the difference between data records and source records
?) Is there any best practice / recommendations re the use of LF's ?

CRTLF (Command - Create Logical File)

Attribute : File type (FILETYPE)
Specifies whether each member of the logical file being created contains
data records, or contains source records for a program or another file.
*DATA
The logical file contains data records.
*SRC
The logical file contains source records. This value cannot be specified
for join logical files.

Thanks,
G

-------------------------------------------------------------------------------------
This e-mail is subject to the Columbus Stainless [Pty] Ltd Email Legal
Notices available at: http://www.columbus.co.za/EmailLegalNotice.htm.

-------------------------------------------------------------------------------------

This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal

-------------------------------------------------------------------------------------
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.