Professional Web Applications Themes

Index on Partitioning Key UDB EEE 7.2 - IBM DB2

We are utilizing UDB EEE 7.2 partitioned across 8 nodes. Interestingly we had a case where an index was mistakenly not applied to our partitioning key which was the first column in our table and an integer. As the volume of data grew in the table insert performance decreased dramatically from about 400 rows/sec to less then 100 rows/sec with 7 Million rows in the table. After applying the index our insert performance went back to the 400 rows/sec. Why would this be? What happens with partitioning in EEE that would mandate an index on the partitioning key to get ...

  1. #1

    Default Index on Partitioning Key UDB EEE 7.2


    We are utilizing UDB EEE 7.2 partitioned across 8 nodes. Interestingly
    we had a case where an index was mistakenly not applied to our
    partitioning key which was the first column in our table and an integer.
    As the volume of data grew in the table insert performance decreased
    dramatically from about 400 rows/sec to less then 100 rows/sec with 7
    Million rows in the table. After applying the index our insert
    performance went back to the 400 rows/sec. Why would this be? What
    happens with partitioning in EEE that would mandate an index on the
    partitioning key to get maximum performance?

    Spencer


    --
    Posted via http://dbforums.com
    stabbert Guest

  2. #2

    Default Re: Index on Partitioning Key UDB EEE 7.2

    "stabbert" <com> wrote in message
    news:com... 
    Please tell us what other indexes were on the table and whether any are
    defined as clustering.


    Mark Guest

  3. #3

    Default Re: Index on Partitioning Key UDB EEE 7.2


    We had two other non-unique indexes on integers which were in place when
    the partitioning index was missing and after it was applied. None of
    the indexes were ever clustered.


    --
    Posted via http://dbforums.com
    stabbert Guest

  4. #4

    Default Re: Index on Partitioning Key UDB EEE 7.2



    stabbert wrote:
     

    EEE will not ask for an index on the partitioning key in order to improve
    insert performance. Either it is a EE db or EEE db, either the partitioning
    key is part of one of the indexes columns or not. Index maintain will hurt
    insert performance.
    I did some testing in order to find the cost of index maintainance on EEE. I
    did the same thing as you did. The whole insert performance is a little
    slower than after I created an index.

    Is your insert stmt like "insert ... select from ..." or just "insert ...;
    commit; insert ...; commit; ..."
    My testing is based the second case.

    Regards,
    FRX

    Fan Guest

  5. #5

    Default Re: Index on Partitioning Key UDB EEE 7.2


    I know it seems bizarre and I am going to retest my findings but I
    believe them to be accurate. I am not sure if the lack of index caused
    a hotspot on each node or something like that. That is all I can think
    of. The insert statements were single insert statements with values
    supplied not insert select froms.



    Spence


    --
    Posted via http://dbforums.com
    stabbert Guest

  6. #6

    Default Re: Index on Partitioning Key UDB EEE 7.2

    "stabbert" <com> wrote in message
    news:com... 

    Were the inserts all submitted from the same program?
    What is the committ interval?
    Did you use buffered inserts?


    Mark Guest

  7. #7

    Default Re: Index on Partitioning Key UDB EEE 7.2


    Were the inserts all submitted from the same program? Yes



    What is the committ interval? 10,000



    Did you use buffered inserts? No


    --
    Posted via http://dbforums.com
    stabbert Guest

  8. #8

    Default Re: Index on Partitioning Key UDB EEE 7.2

    >>>>> "Spence" == stabbert <com> writes:

    Spence> I know it seems bizarre and I am going to retest my
    Spence> findings but I believe them to be accurate. I am not sure
    Spence> if the lack of index caused a hotspot on each node or
    Spence> something like that. That is all I can think of. The
    Spence> insert statements were single insert statements with
    Spence> values supplied not insert select froms.

    Do you have any triggers on your statements ? Or perhaps any foreign
    keys ? Essentially I'm looking for something that causes a read
    operation.

    --
    Pip-pip
    Sailesh
    http://www.cs.berkeley.edu/~sailesh

    Sailesh Guest

  9. #9

    Default Re: Index on Partitioning Key UDB EEE 7.2


    No triggers no fk's no constraints.


    --
    Posted via http://dbforums.com
    stabbert Guest

  10. #10

    Default Re: Index on Partitioning Key UDB EEE 7.2

    "stabbert" <com> wrote in message
    news:com... 
    Commit interval of 10,000 is too high IMO. You were probably getting some
    log buffer overflow or wait time when the log buffer is written to the
    active log.

    Not sure why you aren't using buffered inserts though.


    Mark Guest

Similar Threads

  1. space partitioning
    By DANGH_b in forum Macromedia Director 3D
    Replies: 2
    Last Post: March 7th, 09:58 PM
  2. Partitioning Postgresql
    By phil campaigne in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 26th, 11:24 PM
  3. Partitioning Prob
    By Mark in forum Linux / Unix Administration
    Replies: 7
    Last Post: October 10th, 12:40 AM
  4. Partitioning HDD
    By Spinner in forum Windows XP/2000/ME
    Replies: 0
    Last Post: June 30th, 04:45 AM
  5. debunking partitioning
    By Mikito Harakiri in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 06:53 PM

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