Hi Ashok,
I would agree with Richard that you might as well combine your nodes
1 and 0 (catalog and non-partitioned tables, as long as the total
size of the non partitioned tables is not too huge). You mention data
spread across nodes 1-7 and also 2-7. I vote for using all but the
catalog node. All coordination activity has to interact with the
catalog, so if your coordinator node is not the catalog, all
connections will have to perform some cross-partition trafic just
during the compilation phase. I'm a big advocate of connecting to
your catalog node, setting your bufferpools to cache the catalog so
that other data flowing to your apps wont flush catalog pages.
Richard, just my two cents worth: do you ever join your small
partitioned tables to the large partitioned tables? if so they will
be non colocated and the performance will most likely suffer to some
degree. Is there a reason for separating the small partitioned tables
from the large ones ? Even if these tables don't join often or, when
they are joined, its still not a colocated join, partitions 1-7,
having both large and small partitions, will have more work to do,
creating non-uniformity in resource requirements across your
partitions. There are four "resources" one should consider, Memory,
CPU, Disk and "Network Communication" If you are on an SMP, the cpus
are shared, so the unballanced nodes will pretty much not run into
CPU shortages any sooner than the other partitions. Memory usage
could be impacted somewhat on partitions 1-7 with more tables hitting
the bufferpools, pages in their bufferpools may be flushed sooner
than the other paritions (8-14). Network communications between
partitions will be increased in small-large table joins... Disk
allocation should be consideredd as well.
On the plus, when only the small parititioned tables are joined
together using only 7 partitions, there will be less network comm
between partitions.
In general, I would value colocation very highly, over reducing
interpartion communication.
At 11:07 AM 5/23/2007, Richard wrote:
>I was told the right hand column is the one that counts- it should be in
>numeric order starting with 0 for each physical host
>1 qa-devdb2 0 << flipped from 1 to 0
>0 qa-devdb2 1 << flipped from 0 to 1
>
>your coordinator node is 1. You can use the db2advis utility from command
>line to look at recent sql or specific sql statements- it will recommend
>MQTs which will be repartitioned partitioned tables. I used the following
>paterns for 8 and 16 nodes
>
>0 catalog + non partitioned application tables
>1-7 partitioned application tables
>
>0 catalog + small non partitioned application tables
>1-14 large partitioned application tables
>990 large non partitioned application tables.
>NOTICE TO RECIPIENT: If you are not the intended recipient of this
>e-mail, you are prohibited from sharing, copying, or otherwise using or
>disclosing its contents. If you have received this e-mail in error,
>please notify the sender immediately by reply e-mail and permanently
>delete this e-mail and any attachments without reading, forwarding or
>saving them. Thank you.
>
>
>
>
>"Ashok Rathi " <db2udbdba-ezmlmshield-x74852639.[Email address protected]
>05/23/2007 12:28 AM
>
>To
>"LazyDBA Discussion" <[Email address protected]
>cc
>
>Subject
>Node Flipping in Partitioned Environment - Anyone?
>
>
>
>
>
>
>
>We experienced some performance issues with Partitioned DB. I want to get
>opinion from the experts. Let me explain the situation. We created 8
>logical partitions (on the same machine) on Linux AMD machine. We are
>running DB2 8.1/FP-14. Here is the physical layout of partitions:
>
>Partition 0: All catalog tables (catalog node)
>Partition 1: All Non-partitioned tables, and One partitioned table (same
>table as below)
>Partition 2-7: One Partitioned Table
>
>Essentially we have only one table that is partitioned across nodes 1 thru
>7. When we queried from remote machine, we had some performance issue with
>the query that involved Partitioned table. We tweaked FCM Buffer size,
>COMM rate etc., but made no difference. Interestingly when we set
>"DB2NODE=1", we got much better performance. But since we use Type-4 JDBC
>driver (Thin driver) with our application, DB2NODE variable is not
>available. Then IBM suggested that we try flipping node-0 and node-1 in
>db2nodes.cfg, and we did as follows:
>
>0 qa-devdb2 1 << flipped from 0 to 1
>1 qa-devdb2 0 << flipped from 1 to 0
>2 qa-devdb2 2
>3 qa-devdb2 3
>4 qa-devdb2 4
>5 qa-devdb2 5
>6 qa-devdb2 6
>7 qa-devdb2 7
>
>It definitely improved query performance. Now when we load SQL store
>procedures, we have to specifically set DB2NODE=0 for Catalog Node since
>default connection is always to Node=1. Questions are:
>
>#1) Has anyone experienced this kind of issue?
>
>#2) Could node-flipping above be a problem in any respect?
>
>#3) Any other ideas or thoughts around this issue? Could we partition
>differently?
>
>Regards,
>
>Ashok Rathi | Director, Database Engineering Group
>DemandTec, Inc. | 1 Circle Star Way, Suite 200 | San Carlos, CA 94070
>p | 650.226.4666 f | 650.556.1190
>
>
>
>**************************************************************************
>
>DemandTec Email Notice
>
>This email and any attachments may contain confidential and/or proprietary
>information and is intended solely for the use of the addressee. If you
>are not the intended recipient we request that you notify us via email or
>telephone and delete all copies of the message from your systems.
>Additionally, although DemandTec has taken reasonable precautions to
>ensure the security of this email and any attachments, we encourage you to
>take similar precautions and accept no liability for any loss or damage
>resulting from its use.
>DemandTec, 1 Circle Star Way, Suite 200, San Carlos, CA 94070,
>650-226-4600
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page