Re: tough choices

From: Serge Rielau <srielau_at_ca.eye-be-em.com>
Date: Sat, 19 Jun 2004 19:45:59 -0400
Message-ID: <cb2j8b$p72$1@hanover.torolab.ibm.com>

Daniel Morgan wrote:

> Mark A wrote:
> 
>>> Please correct me if you think I am incorrect. But losing a single node
>>> with RAC can not deprive users of access to data. The system continues
>>> to run with no effect other than the loss of a few CPU's and their
>>> associated RAM.
>>>
>>> With DB2 I could lose a node and either lose access to some of the data
>>> or, worst case, lose the entire database application.

Daniel, you are still measures with two metrics :-) When an Oracle RAC node goes down it has information that is needed by the other nodes. All the remaining nodes are affected by that during this timeframe where RAC gets its balance back. I take your word, that this is in the second ballpark. Now in a DB2 + DPF scenario, if DB Partition goes down all clients connected to that partition get kicked.
All other clients will not get kicked and they may or may not feel that a partition went down, depending on whether the downed partition is needed or not. When you fire the gardner it will not slow down the kitchenwork. The likely hood to get food is not affected by the number of gardners you have.
This is a core differentiator between Oracle RAC and DB2 + DPF. (not better, not worse, just different)

>> Do you mean loose the database permanently or just until a fallover
>> can be
>> accomplished or the hardware repaired? I don't know of a situation where
>> data would be lost permanently unless there was a multiple disk failure
>> affecting both the data and logs.

> I meant only until it is brought back on-line. DB2 is far more robust to > become ashes ... just toast. ;-)
Right, so now the question is the race against time to get the down partition up again. On the same hardware, different hardware, doesn't matter.

>>>>> Correct me if I am wrong ... but it seems DPF
>>>>> is an extra cost add-in only available on ESE systems with that some
>>>>> functionality only works on AIX. One question ... is it supported by the
>>>>> optimizer?

>> Of course it is supported by the optimizer. The use of multiple
>> partitions
>> enables parallel database access (inter-partition parallelism).
>> Intra-partition parallelism is available even on a single partition
>> system
>> in certain circumstances.

> So the optimizer knows that a query not requiring information from all > but one partition can and will query only the single partition? Absolutely.
Let's stick with a store example.
The schema involves a table with store-date (pk store id), an inventory table (pk: store id, product id), a product table (pk: product id). The partitioning would ne by store_id
(DB2 Stinger's design advisor would propose it). The product table and store table is pretty fixed and small. It would be replicated (another word for a synchronized cache). If you want to query the inventory of Coffee in Starbucks at Time square and how much that is worth.
The SQL will be a join across all those tables to look up the surrogate keys for the store, retrieve all the product ids for coffee and then sum up the price (stored in the inventory table) times inventory. So what does the plan look like?
1. Get the store id (local to the coordinator partition) 2. Send the rest of the request to the db partition holding the that

    store id data
    Do a local join between the inventory and product table doing the     local aggregation
3. Pass the result back to the coordinator

At most two nodes were involved.
If the software associating the clients to db partitions has any brains it uses the same mapping as DB2 and the coordinator is the right partition to begin with.
Let's assume the query comes in with the store id rather than the store name (more realistic actually). In this case a feature called "local bypass" kicks in. The coordinator would simply pass the query on to the right node.
Now does this require a bit of DB Schema design work? Absolutely. But in a BI environment which is where DB2 + DPF plays you have that knowledge. In return for their efforts the user gets near linear scalability once the homework has been done.

DB2 + DPF is not sold as an HA solution it is sold for scale out.

>>>> To the best of my knowledge DPF is offered on all supported 
>>>> platforms of
>>>> DB2 for LUW.
>>>
>>>
>>> Not according to:
>>> http://www.developer.ibm.com/tech/faq/individual?oid=2:82779

>> I don't know exactly what statement you are reading, but DPF
>> (partitioning)
>> is available on DB2 ESE for Linux, UNIX, and Windows. These are the
>> platforms known as LUW.

> Thanks. But still limited to ESE and still an additional license cost. > Is that correct?
Yes. It's a scale out solution.
If anyone tries to sell DB2 + DPF to a mom&pop shop with 100GB for HA (s)he's missing the boat.

If you just want a simple HA solution with DB2 Stinger you will use the HDR feature.
For currently available releases HA is driven with general solutions. I'm no experts in this.. Too far away from my area. For HA you need to compare: DB2 + HA solutions or soon DB2 + HDR. To learn about HDR best ask in the informix newsgroup for HADR. HDR is stage one of that port.

Just to wrap up:
The point being made is:
1. DB2 + DPF for near unlimited scale out

    (DB2 supports 999 DB Partitions,
     there >100 partition installation out there) 2. DB2 + DPF _supports_ HA solutions if needed 3. DB2 + DPF is not an HA feature and never was meant to be one.

My personal toughts on RAC are:
Oracle RAC is an HA feature with neat limited scale out ability Oracle RAC has yet to proof how far it can scale out.

I don't believe that near linear scale out can be achieved without a divide and conquer strategy of sorts. That strategy requires schema/app changes.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Sat Jun 19 2004 - 18:45:59 CDT