Professional Web Applications Themes

Insert Performance on a Partitioned DB - IBM DB2

I have a test database that I have built in a 3 partition (and 3 node) environment. I have defined all the tables so they have the same partition key. The tables (7 of them) form a hierarchical arrangement. The data is all bogus, so I using the generate_unique function to come up with a partitioning key for each record. This is all done through a stored proc with will insert about 90 records in the heirarchy. The behavior that I am seeing is when the proc generates a partitioning key that will put (all) the data on the local ...

  1. #1

    Default Insert Performance on a Partitioned DB

    I have a test database that I have built in a 3 partition (and 3 node)
    environment. I have defined all the tables so they have the same
    partition key. The tables (7 of them) form a hierarchical
    arrangement. The data is all bogus, so I using the generate_unique
    function to come up with a partitioning key for each record. This is
    all done through a stored proc with will insert about 90 records in
    the heirarchy.

    The behavior that I am seeing is when the proc generates a
    partitioning key that will put (all) the data on the local partition
    it runs quickly, (.2 second or less), when the partitioning key
    resolves to another partition it takes 2 to 4 seconds. This is true
    no matter which node I actually run the stored procedure on.

    I know that there will be internodal communication and inserting the
    data on one of those nodes will be slower than on the local partition,
    but this seems excessive. Has anyone dealt with this kind of problem?
    I have exhausted my ideas of where to look to address this
    communication issue.

    Any ideas?

    thanks

    Jack
    Jack Guest

  2. #2

    Default Re: Insert Performance on a Partitioned DB

    "Jack" <com> wrote in message
    news:google.com... 

    Can you explain exactly how many inserts are being done and what the syntax
    is?

    Can you explain your network configuration (the hardware used to link the
    nodes together)?


    Mark Guest

  3. #3

    Default Re: Insert Performance on a Partitioned DB

    Insert into a partitioned db will run slower than a single-partition db.
    But not so slow like your testing result. I will not suggest define the
    partitioning key on the function.
    BTW, if you only want to improve INSERT performance, you can check with
    Buffered Insert and local bypass feature.

    Jack wrote:
     

    Fan Guest

  4. #4

    Default Re: Insert Performance on a Partitioned DB

    I've looked at ISERT BUF, it appears to have no impact on my tests.
    Local Bypass is really not an option because the partition key is
    created at run time in the Stored Procedure.

    The database is seven tables in a hiearchical arrangement. All tables
    are partitioned on the key of the parent table (T1). The structure
    looks something like this:

    T1
    --------------
    T2 T3
    -----------
    T4 T5
    -----
    T6 T7


    The SP just does a series of inserts with do loops

    The keys for each table is generated in the SP via a Generate_Unique
    function.

    The 3 nodes are dual IA32 boxes running RH AS 2.1. They are connected
    via a public 100 mb network and a private 1gb network. The private
    network is defined as the switching network for DB2. This private
    network is only for DB2 cluster. If have verified by FTP tests that
    the networks are running at expected speeds.

    I believe the issue tied to the performance of communication between
    the nodes.


    thanks

    Jack

    Fan Ruo Xin <net> wrote in message news:<net>... [/ref]
    Jack Guest

Similar Threads

  1. db2move and partitioned db on v8.1
    By Prince in forum IBM DB2
    Replies: 7
    Last Post: October 14th, 05:21 PM
  2. Replies: 5
    Last Post: October 6th, 11:43 PM
  3. Partitioned views
    By Amrit in forum IBM DB2
    Replies: 2
    Last Post: August 25th, 07:06 PM
  4. Replies: 22
    Last Post: July 8th, 01:59 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