Professional Web Applications Themes

Average MB/s read from snapshot - IBM DB2

All, I've been monitoring the tablespace snapshot information and been calculating how much MB/s the database reads / tablespace (asynch and synch). Looking at this I see that the performance / tablespace is different for both synchronous reads and asynchronous reads. Our tablespaces are all divided over 10 filesystems and all containers for each tablespace is the same size. Our disks are in a SAN (EMC) and located on a meta. A meta is a 4-way stripe of disks using Raid 0+1 and the stripe size is 1MB. Now since all our tablespaces are spread over the same amount of ...

  1. #1

    Default Average MB/s read from snapshot

    All,

    I've been monitoring the tablespace snapshot information and been
    calculating how much MB/s the database reads / tablespace (asynch and
    synch).

    Looking at this I see that the performance / tablespace is different for
    both synchronous reads and asynchronous reads.
    Our tablespaces are all divided over 10 filesystems and all containers for
    each tablespace is the same size.
    Our disks are in a SAN (EMC) and located on a meta. A meta is a 4-way stripe
    of disks using Raid 0+1 and the stripe size is 1MB.

    Now since all our tablespaces are spread over the same amount of
    filesystems/containers (we have 1 container / tablespace) and are all
    sitting on the same filesystems I would have expected that the MB/s would be
    equal between them. Now I can understand that the asynchronous reads might
    be different (our prefetch size is equal to extent size) since our
    tablespaces have different extent sizes thus 1 read operation would be
    different between the tablespaces. But for a synchronous read which thus
    only reads 1 page at a time I would have expected them to be close to each
    other. All our tablespaces are 4KB pagesize.

    Below is a example of the data. This is a delta (what I do is I take the
    difference between last hour and this hour for # of pages reads and elapsed
    time to come to the delta). The database total is coming from the database
    snapshot.
    Can anyone tell me if I'm doing something wrong here or explain why this is?

    Thanks.

    Tablespace / Total Average Total Asynch Asynch Total
    Synch Synch Total Direct Direct
    Database (MB) (MB/s) (MB) (MB/s) (MB)
    (MB/s) (MB) (MB/s)

    ReadDTR SYSCATSPACE 12.1 0.66 4.2 4.30 7.7
    0.47 0.1 0.15
    ReadDTR TEMPSPACE1 125.0 1.24 117.1 1.56 7.9
    0.31 0.0 0.00
    ReadDTR ZSAPREPORT1D 0.8 2.36 0.0 0.00 0.8
    2.36 0.0 0.00
    ReadDTR PSAPBTABD 5605.4 1.19 524.3 2.18 5081.2
    1.13 0.0 0.00
    ReadDTR PSAPBTABI 396.0 1.05 351.0 0.00 45.0
    0.12 0.0 0.00
    ReadDTR PSAPCLUD 1.5 1.93 0.0 0.00 1.5
    1.93 0.0 0.00
    ReadDTR PSAPCLUI 0.8 2.06 0.0 0.00 0.8
    2.06 0.0 0.00
    ReadDTR PSAPDDICD 0.5 0.77 0.0 0.00 0.4
    0.95 0.0 0.19
    ReadDTR PSAPDDICI 0.4 0.80 0.0 0.00 0.4
    0.80 0.0 0.00
    ReadDTR PSAPDOCUD 0.7 2.27 0.0 0.00 0.7
    2.27 0.0 0.00
    ReadDTR PSAPDOCUI 0.7 2.47 0.0 0.00 0.7
    2.47 0.0 0.00
    ReadDTR PSAPEL46CD 0.4 1.82 0.0 0.00 0.4
    1.82 0.0 0.00
    ReadDTR PSAPEL46CI 0.3 1.62 0.0 0.00 0.3
    1.62 0.0 0.00
    ReadDTR PSAPES46CD 0.4 1.21 0.0 0.00 0.4
    1.21 0.0 0.00
    ReadDTR PSAPES46CI 0.4 1.14 0.0 0.00 0.4
    1.14 0.0 0.00
    ReadDTR PSAPLOADD 1.2 2.31 0.0 0.00 1.2
    2.31 0.0 0.00
    ReadDTR PSAPLOADI 0.6 2.64 0.0 0.00 0.6
    2.64 0.0 0.00
    ReadDTR PSAPPOOLD 0.5 0.59 0.0 0.00 0.5
    0.59 0.0 0.00
    ReadDTR PSAPPOOLI 0.5 0.45 0.0 0.00 0.5
    0.45 0.0 0.00
    ReadDTR PSAPPROTD 5.2 1.22 3.0 5.64 2.2
    0.58 0.0 0.00
    ReadDTR PSAPPROTI 1.7 0.43 0.7 0.00 1.0
    0.26 0.0 0.00
    ReadDTR PSAPSOURCED 1.2 1.80 0.0 0.00 1.2
    1.80 0.0 0.00
    ReadDTR PSAPSOURCEI 0.6 1.30 0.0 0.00 0.6
    1.30 0.0 0.00
    ReadDTR PSAPSTABD 4219.1 1.09 631.1 1.39 3588.0
    1.05 0.0 0.00
    ReadDTR PSAPSTABI 750.2 0.47 32.5 0.00 717.7
    0.45 0.0 0.00
    ReadDTR PSAPUSER1D 0.3 0.90 0.0 0.00 0.3
    0.90 0.0 0.00
    ReadDTR PSAPUSER1I 0.2 1.03 0.0 0.00 0.2
    1.03 0.0 0.00
    ReadDTR ZSAPREPORT1I 0.6 1.79 0.0 0.00 0.6
    1.79 0.0 0.00
    ReadDTR ZSAPBTABD 654.4 6.57 653.0 6.79 1.4
    0.39 0.0 0.00
    ReadDTR ZSAPFISPLD 0.6 1.63 0.0 0.00 0.6
    1.63 0.0 0.00
    ReadDTR ZSAPBTABI 204.4 0.58 64.4 0.00 140.0
    0.40 0.0 0.00
    ReadDTR ZSAPFISPLI 0.6 1.82 0.0 0.00 0.6
    1.82 0.0 0.00
    ------------------
    ReadDTR Database 11987.3 1.07 2381.3 2.75 9605.9
    0.93 0.2 0.15


    Erik Hendrix Guest

  2. #2

    Default Re: Average MB/s read from snapshot

    Numbers you see is what you've used over an hour.
    Unless you can push queries that reads the same amount of data on
    all tablespaces, those numbers won't be equal.

    If you read at 10 MB/s for 1 min, you'll read 600 MB. (10 * 60)
    (then, there is no activity)
    Over an hour, this will be 600 MB / (60 min * 60 sec) = 0.16 MB/s.


    PM

    "Erik Hendrix" <hendrix_erikhotmail.com> a écrit dans le message de news:
    [email]d318210a9239a2af17b4e499c1a478befree.teranews.com[/email]...
    > All,
    >
    > I've been monitoring the tablespace snapshot information and been
    > calculating how much MB/s the database reads / tablespace (asynch and
    > synch).
    >
    > Looking at this I see that the performance / tablespace is different for
    > both synchronous reads and asynchronous reads.
    > Our tablespaces are all divided over 10 filesystems and all containers for
    > each tablespace is the same size.
    > Our disks are in a SAN (EMC) and located on a meta. A meta is a 4-way
    stripe
    > of disks using Raid 0+1 and the stripe size is 1MB.
    >
    > Now since all our tablespaces are spread over the same amount of
    > filesystems/containers (we have 1 container / tablespace) and are all
    > sitting on the same filesystems I would have expected that the MB/s would
    be
    > equal between them. Now I can understand that the asynchronous reads might
    > be different (our prefetch size is equal to extent size) since our
    > tablespaces have different extent sizes thus 1 read operation would be
    > different between the tablespaces. But for a synchronous read which thus
    > only reads 1 page at a time I would have expected them to be close to each
    > other. All our tablespaces are 4KB pagesize.
    >
    > Below is a example of the data. This is a delta (what I do is I take the
    > difference between last hour and this hour for # of pages reads and
    elapsed
    > time to come to the delta). The database total is coming from the database
    > snapshot.
    > Can anyone tell me if I'm doing something wrong here or explain why this
    is?
    >
    > Thanks.
    >
    > Tablespace / Total Average Total Asynch Asynch Total
    > Synch Synch Total Direct Direct
    > Database (MB) (MB/s) (MB) (MB/s) (MB)
    > (MB/s) (MB) (MB/s)
    >
    > ReadDTR SYSCATSPACE 12.1 0.66 4.2 4.30 7.7
    > 0.47 0.1 0.15
    > ReadDTR TEMPSPACE1 125.0 1.24 117.1 1.56 7.9
    > 0.31 0.0 0.00
    > ReadDTR ZSAPREPORT1D 0.8 2.36 0.0 0.00 0.8
    > 2.36 0.0 0.00
    > ReadDTR PSAPBTABD 5605.4 1.19 524.3 2.18 5081.2
    > 1.13 0.0 0.00
    > ReadDTR PSAPBTABI 396.0 1.05 351.0 0.00 45.0
    > 0.12 0.0 0.00
    > ReadDTR PSAPCLUD 1.5 1.93 0.0 0.00 1.5
    > 1.93 0.0 0.00
    > ReadDTR PSAPCLUI 0.8 2.06 0.0 0.00 0.8
    > 2.06 0.0 0.00
    > ReadDTR PSAPDDICD 0.5 0.77 0.0 0.00 0.4
    > 0.95 0.0 0.19
    > ReadDTR PSAPDDICI 0.4 0.80 0.0 0.00 0.4
    > 0.80 0.0 0.00
    > ReadDTR PSAPDOCUD 0.7 2.27 0.0 0.00 0.7
    > 2.27 0.0 0.00
    > ReadDTR PSAPDOCUI 0.7 2.47 0.0 0.00 0.7
    > 2.47 0.0 0.00
    > ReadDTR PSAPEL46CD 0.4 1.82 0.0 0.00 0.4
    > 1.82 0.0 0.00
    > ReadDTR PSAPEL46CI 0.3 1.62 0.0 0.00 0.3
    > 1.62 0.0 0.00
    > ReadDTR PSAPES46CD 0.4 1.21 0.0 0.00 0.4
    > 1.21 0.0 0.00
    > ReadDTR PSAPES46CI 0.4 1.14 0.0 0.00 0.4
    > 1.14 0.0 0.00
    > ReadDTR PSAPLOADD 1.2 2.31 0.0 0.00 1.2
    > 2.31 0.0 0.00
    > ReadDTR PSAPLOADI 0.6 2.64 0.0 0.00 0.6
    > 2.64 0.0 0.00
    > ReadDTR PSAPPOOLD 0.5 0.59 0.0 0.00 0.5
    > 0.59 0.0 0.00
    > ReadDTR PSAPPOOLI 0.5 0.45 0.0 0.00 0.5
    > 0.45 0.0 0.00
    > ReadDTR PSAPPROTD 5.2 1.22 3.0 5.64 2.2
    > 0.58 0.0 0.00
    > ReadDTR PSAPPROTI 1.7 0.43 0.7 0.00 1.0
    > 0.26 0.0 0.00
    > ReadDTR PSAPSOURCED 1.2 1.80 0.0 0.00 1.2
    > 1.80 0.0 0.00
    > ReadDTR PSAPSOURCEI 0.6 1.30 0.0 0.00 0.6
    > 1.30 0.0 0.00
    > ReadDTR PSAPSTABD 4219.1 1.09 631.1 1.39 3588.0
    > 1.05 0.0 0.00
    > ReadDTR PSAPSTABI 750.2 0.47 32.5 0.00 717.7
    > 0.45 0.0 0.00
    > ReadDTR PSAPUSER1D 0.3 0.90 0.0 0.00 0.3
    > 0.90 0.0 0.00
    > ReadDTR PSAPUSER1I 0.2 1.03 0.0 0.00 0.2
    > 1.03 0.0 0.00
    > ReadDTR ZSAPREPORT1I 0.6 1.79 0.0 0.00 0.6
    > 1.79 0.0 0.00
    > ReadDTR ZSAPBTABD 654.4 6.57 653.0 6.79 1.4
    > 0.39 0.0 0.00
    > ReadDTR ZSAPFISPLD 0.6 1.63 0.0 0.00 0.6
    > 1.63 0.0 0.00
    > ReadDTR ZSAPBTABI 204.4 0.58 64.4 0.00 140.0
    > 0.40 0.0 0.00
    > ReadDTR ZSAPFISPLI 0.6 1.82 0.0 0.00 0.6
    > 1.82 0.0 0.00
    > ------------------
    > ReadDTR Database 11987.3 1.07 2381.3 2.75 9605.9
    > 0.93 0.2 0.15
    >
    >

    PM \(pm3iinc-nospam\) Guest

  3. #3

    Default Re: Average MB/s read from snapshot

    I understand that, but the time spent reading is coming from the tablespace
    snapshot as well. So I do not use the 1 hour in my calculation.

    Here is a example of the tablespace snapshot for 2 tablespaces:

    Tablespace name = SYSCATSPACE
    Buffer pool data logical reads = 1431861
    Buffer pool data physical reads = 56539
    Asynchronous pool data page reads = 42031
    Buffer pool data writes = 664
    Asynchronous pool data page writes = 144
    Buffer pool index logical reads = 1185749
    Buffer pool index physical reads = 6921
    Asynchronous pool index page reads = 1688
    Buffer pool index writes = 144
    Asynchronous pool index page writes = 134
    Total buffer pool read time (ms) = 154946
    Total buffer pool write time (ms) = 902
    Total elapsed asynchronous read time = 21749
    Total elapsed asynchronous write time = 1
    Asynchronous read requests = 1433
    Direct reads = 2485056
    Direct writes = 1800
    Direct read requests = 87596
    Direct write requests = 308
    Direct reads elapsed time (ms) = 624831
    Direct write elapsed time (ms) = 291
    Number of files closed = 0
    Data pages copied to extended storage = 0
    Index pages copied to extended storage = 0
    Data pages copied from extended storage = 0
    Index pages copied from extended storage = 0

    So what I'm doing is the following:

    "Asynchronous pool data page reads" * "Page Size" / 1024 / (Total elapsed
    asynchronous read time / 1000)

    So for SYSCATSPACE:
    42031 * 4 / 1024 / (21749 / 1000) = 7.5 MB/s

    Thus it would not matter how long I wait since I take the read time from the
    snapshot.

    One thing I just noticed though, I have a system with filesystem caching
    disabled. And altough the numbers between certain tablespaces are different,
    it looks like the ones with the same extent size (and prefetch size is set =
    to extent size) that the MB/s is rather equal. So maybe the filesystem is
    "ing" up my results for the systems with filesystem caching enabled.

    Thanks.



    "PM (pm3iinc-nospam)" <Pm3iinc.NoSpamsympatico.ca> wrote in message
    news:OOzOa.9377$Tx.409116news20.bellglobal.com...
    > Numbers you see is what you've used over an hour.
    > Unless you can push queries that reads the same amount of data on
    > all tablespaces, those numbers won't be equal.
    >
    > If you read at 10 MB/s for 1 min, you'll read 600 MB. (10 * 60)
    > (then, there is no activity)
    > Over an hour, this will be 600 MB / (60 min * 60 sec) = 0.16 MB/s.
    >
    >
    > PM
    >
    > "Erik Hendrix" <hendrix_erikhotmail.com> a écrit dans le message de news:
    > [email]d318210a9239a2af17b4e499c1a478befree.teranews.com[/email]...
    > > All,
    > >
    > > I've been monitoring the tablespace snapshot information and been
    > > calculating how much MB/s the database reads / tablespace (asynch and
    > > synch).
    > >
    > > Looking at this I see that the performance / tablespace is different for
    > > both synchronous reads and asynchronous reads.
    > > Our tablespaces are all divided over 10 filesystems and all containers
    for
    > > each tablespace is the same size.
    > > Our disks are in a SAN (EMC) and located on a meta. A meta is a 4-way
    > stripe
    > > of disks using Raid 0+1 and the stripe size is 1MB.
    > >
    > > Now since all our tablespaces are spread over the same amount of
    > > filesystems/containers (we have 1 container / tablespace) and are all
    > > sitting on the same filesystems I would have expected that the MB/s
    would
    > be
    > > equal between them. Now I can understand that the asynchronous reads
    might
    > > be different (our prefetch size is equal to extent size) since our
    > > tablespaces have different extent sizes thus 1 read operation would be
    > > different between the tablespaces. But for a synchronous read which thus
    > > only reads 1 page at a time I would have expected them to be close to
    each
    > > other. All our tablespaces are 4KB pagesize.
    > >
    > > Below is a example of the data. This is a delta (what I do is I take the
    > > difference between last hour and this hour for # of pages reads and
    > elapsed
    > > time to come to the delta). The database total is coming from the
    database
    > > snapshot.
    > > Can anyone tell me if I'm doing something wrong here or explain why this
    > is?
    > >
    > > Thanks.
    > >
    > > Tablespace / Total Average Total Asynch Asynch Total
    > > Synch Synch Total Direct Direct
    > > Database (MB) (MB/s) (MB) (MB/s)
    (MB)
    > > (MB/s) (MB) (MB/s)
    > >
    > > ReadDTR SYSCATSPACE 12.1 0.66 4.2 4.30
    7.7
    > > 0.47 0.1 0.15
    > > ReadDTR TEMPSPACE1 125.0 1.24 117.1 1.56
    7.9
    > > 0.31 0.0 0.00
    > > ReadDTR ZSAPREPORT1D 0.8 2.36 0.0 0.00
    0.8
    > > 2.36 0.0 0.00
    > > ReadDTR PSAPBTABD 5605.4 1.19 524.3 2.18
    5081.2
    > > 1.13 0.0 0.00
    > > ReadDTR PSAPBTABI 396.0 1.05 351.0 0.00
    45.0
    > > 0.12 0.0 0.00
    > > ReadDTR PSAPCLUD 1.5 1.93 0.0 0.00
    1.5
    > > 1.93 0.0 0.00
    > > ReadDTR PSAPCLUI 0.8 2.06 0.0 0.00
    0.8
    > > 2.06 0.0 0.00
    > > ReadDTR PSAPDDICD 0.5 0.77 0.0 0.00
    0.4
    > > 0.95 0.0 0.19
    > > ReadDTR PSAPDDICI 0.4 0.80 0.0 0.00
    0.4
    > > 0.80 0.0 0.00
    > > ReadDTR PSAPDOCUD 0.7 2.27 0.0 0.00
    0.7
    > > 2.27 0.0 0.00
    > > ReadDTR PSAPDOCUI 0.7 2.47 0.0 0.00
    0.7
    > > 2.47 0.0 0.00
    > > ReadDTR PSAPEL46CD 0.4 1.82 0.0 0.00
    0.4
    > > 1.82 0.0 0.00
    > > ReadDTR PSAPEL46CI 0.3 1.62 0.0 0.00
    0.3
    > > 1.62 0.0 0.00
    > > ReadDTR PSAPES46CD 0.4 1.21 0.0 0.00
    0.4
    > > 1.21 0.0 0.00
    > > ReadDTR PSAPES46CI 0.4 1.14 0.0 0.00
    0.4
    > > 1.14 0.0 0.00
    > > ReadDTR PSAPLOADD 1.2 2.31 0.0 0.00
    1.2
    > > 2.31 0.0 0.00
    > > ReadDTR PSAPLOADI 0.6 2.64 0.0 0.00
    0.6
    > > 2.64 0.0 0.00
    > > ReadDTR PSAPPOOLD 0.5 0.59 0.0 0.00
    0.5
    > > 0.59 0.0 0.00
    > > ReadDTR PSAPPOOLI 0.5 0.45 0.0 0.00
    0.5
    > > 0.45 0.0 0.00
    > > ReadDTR PSAPPROTD 5.2 1.22 3.0 5.64
    2.2
    > > 0.58 0.0 0.00
    > > ReadDTR PSAPPROTI 1.7 0.43 0.7 0.00
    1.0
    > > 0.26 0.0 0.00
    > > ReadDTR PSAPSOURCED 1.2 1.80 0.0 0.00
    1.2
    > > 1.80 0.0 0.00
    > > ReadDTR PSAPSOURCEI 0.6 1.30 0.0 0.00
    0.6
    > > 1.30 0.0 0.00
    > > ReadDTR PSAPSTABD 4219.1 1.09 631.1 1.39
    3588.0
    > > 1.05 0.0 0.00
    > > ReadDTR PSAPSTABI 750.2 0.47 32.5 0.00
    717.7
    > > 0.45 0.0 0.00
    > > ReadDTR PSAPUSER1D 0.3 0.90 0.0 0.00
    0.3
    > > 0.90 0.0 0.00
    > > ReadDTR PSAPUSER1I 0.2 1.03 0.0 0.00
    0.2
    > > 1.03 0.0 0.00
    > > ReadDTR ZSAPREPORT1I 0.6 1.79 0.0 0.00
    0.6
    > > 1.79 0.0 0.00
    > > ReadDTR ZSAPBTABD 654.4 6.57 653.0 6.79
    1.4
    > > 0.39 0.0 0.00
    > > ReadDTR ZSAPFISPLD 0.6 1.63 0.0 0.00
    0.6
    > > 1.63 0.0 0.00
    > > ReadDTR ZSAPBTABI 204.4 0.58 64.4 0.00
    140.0
    > > 0.40 0.0 0.00
    > > ReadDTR ZSAPFISPLI 0.6 1.82 0.0 0.00
    0.6
    > > 1.82 0.0 0.00
    > > ------------------
    > > ReadDTR Database 11987.3 1.07 2381.3 2.75
    9605.9
    > > 0.93 0.2 0.15
    > >
    > >
    >
    >
    >

    Erik Hendrix Guest

Similar Threads

  1. average days
    By JoeyTMann in forum Macromedia ColdFusion
    Replies: 4
    Last Post: April 27th, 02:40 PM
  2. average using lingo
    By bright_lightsuk in forum Macromedia Director Lingo
    Replies: 0
    Last Post: November 11th, 11:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139