Insert Performance on a Partitioned DB

[Jack]

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

[Mark A]

"Jack" <jb****@marketr on.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

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

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)?

[Fan Ruo Xin]

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:

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]

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 <fa*****@sbcglo bal.net> wrote in message news:<3F******* ********@sbcglo bal.net>...

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:

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

[YONETANI Tomokazu]

Hi.

jb****@marketro n.com (Jack) wrote in message news:<13******* *************** ****@posting.go ogle.com>...

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.

There's a statement in HTML document that came with DB2 V7.1:
Local bypass is enabled automatically whenever possible, but you can
increase its use by routing transactions to the partition containing
the data for that transactions.

Does this statement imply that there's another secret DB2 configuration
which is described in somewhere deep in the manual and only known to
someone who's read through each and every manual page :), or does it
simply mean that local bypass is only available when the target partition
happens to reside in the same node as the controlling partition?
Any ideas?

Thanks in advance.

[YONETANI Tomokazu]

y0******@yahoo. co.jp (YONETANI Tomokazu) wrote in message news:<b2******* *************** ****@posting.go ogle.com>...

Hi.

jb****@marketro n.com (Jack) wrote in message news:<13******* *************** ****@posting.go ogle.com>...

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.

There's a statement in HTML document that came with DB2 V7.1:
Local bypass is enabled automatically whenever possible, but you can
increase its use by routing transactions to the partition containing
the data for that transactions.

Does this statement imply that there's another secret DB2 configuration
which is described in somewhere deep in the manual and only known to
someone who's read through each and every manual page :), or does it
simply mean that local bypass is only available when the target partition
happens to reside in the same node as the controlling partition?
Any ideas?

Sorry, the previous message was unclear about what "statement" I was
referring to: my question was how "Local bypass is enabled automatically
whenever possible", in other words, how or whether it's possible to
exploit local bypass feature without intervention of user code(by calling
sqlugrpn() API to determine the appropriate node, for example).

[Paul Reddin]

Jack,

One thought, are you commiting each insert, or all as a single transaction?

- I'm thinking the commit overhead between partitions could
be causing the large difference?

Paul.

[Fan Ruo Xin]

YONETANI Tomokazu wrote:

y0******@yahoo. co.jp (YONETANI Tomokazu) wrote in message news:<b2******* *************** ****@posting.go ogle.com>...

Hi.

jb****@marketro n.com (Jack) wrote in message news:<13******* *************** ****@posting.go ogle.com>...

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.

There's a statement in HTML document that came with DB2 V7.1:
Local bypass is enabled automatically whenever possible, but you can
increase its use by routing transactions to the partition containing
the data for that transactions.

Does this statement imply that there's another secret DB2 configuration
which is described in somewhere deep in the manual and only known to
someone who's read through each and every manual page :), or does it
simply mean that local bypass is only available when the target partition
happens to reside in the same node as the controlling partition?
Any ideas?

Sorry, the previous message was unclear about what "statement" I was
referring to: my question was how "Local bypass is enabled automatically
whenever possible", in other words, how or whether it's possible to
exploit local bypass feature without intervention of user code(by calling
sqlugrpn() API to determine the appropriate node, for example).

Local Bypass is very helpful for those short transaction applications.
It is in user code, which should figure out which target partition the data should send to. When the data you need to
insert will be on the coordinator partition, then you don't need to do any configuration. If not, you can use db2 API
help you to find out.
For the OLTP system, another thing is also very important - don't make one db partition to become the workload
bottleneck.
Regards,
FRX

[stabbert]

This doesn't really surprise me in the least bit. Do a couple of
searches for inserts and partitions and read a couple of my posts. In
our env we went from 4000 rows/sec Insert in a non partitioned table to
400 rows/sec when partitioned.

The only way around this is utilize buffered inserts which was mentioned
or build your application smart enough to load directly to each
partition inside of your application.

Check this link out for more information on buffered inserts.

m.com/dmdd/library/techarticle/0204pooloth/0204pooloth.htm l?Open&ca=daw-
ad-dr

Spencer
--
Posted via http://dbforums.com