[Building Sakai] Experience with Oracle RAC

Mon Oct 12 19:16:01 PDT 2009

I am not trying to make I am not a DBA, but we have a number of them,
and we rallied the troops pretty well.

On Sat, Oct 10, 2009 at 6:17 AM, Job Miller <jobmiller at yahoo.com> wrote:
> your comments below are a misinterpretation of how RAC works.

I only represented what we experienced, what other people we talked to
experienced and what consultants we hired told us.

I don't pretend to represent what RAC *is* or intends to be.

I am not trying to make RAC look bad, it just did.

>
> RAC doesn't manage "segmentation".  RAC doesn't split data/indexes up, RAC doesn't change the DB structure at all.  It just allows multiple instances to access the data at the same time.  The linear scalability of RAC is very well proven (assuming the application scales on an SMP box) and is in use by many large and small Universities for their ERP applications.

ASSM (auto segment space management)... are we mincing words?

it consistently got it wrong, and we could not turn it off. We could
not turn it off on RAC, without RAC, we can.

>
> I didn't  read through all of your confluence postings, but if you want some help understanding how RAC works and how RAC for sakai can work, let me know.  If you already own RAC from a licensing perspective and are interested in using it for Sakai you should spend the time to understand how it works and troubleshoot the outcome you experienced with someone that understands oracle diagnostics.

These are good recommendations. That is also what *we* did.

We had some pretty lofty experts (FMP) looking at it... we get pretty
good response from Oracle when we need help ... we as Campus drop
considerable sums $$ into their coffers each year

>
> If the App can't handle concurrency, because it serializes access on a single row and makes everyone wait for one person to update a single row before anyone else can do something, that will cause even more problems for RAC because instead of just lining up and waiting, now oracle is lining up and waiting and passing a block around between nodes.
>
> Small mostly read lookup tables aren't a problem for RAC.  Small single tables (or more specifically blocks/rows within a table) that are updated concurrently by many different users thousands of times a minute can cause problems.  But that problem exists for SMP as well as for RAC.

As I said, this was not identified as a problem for us even though we
suspected it... if there is a lag here in the local RAC network, then
this would a geometrically with the number of nodes

>
> In regards to segment space management,  RAC environments and really all db environments should use ASSM (auto segment space management).  It was introduced in 9i and it became the DEFAULT in 10gR2.

Maybe one should use it, but it seemed to be killing us

We found a tripartite correlation over time among the level of
fragmentation of tablespaces, the cost calculations, the deterioration
in performance.

>
> In this release (10gR2), Automatic Segment Space Management (ASSM) is enabled by default. The tablespace attribute, Segment Space Management, now has the default value of AUTO.
>
> All new tablespaces created using default attributes benefit from the manageability and performance advantages of ASSM over Freelist-based space management.
>
> This has nothing to do with RAC though.

Except that with RAC, AFAIK, you can't turn it off.

>
> Also, changing CBO related init.ora parameters such as optimizer_index_cost_adj is also a very bad idea unless you really know why you are changing them.
>
> Changing an init.ora parameter like that will change every calculation the Cost Based Optimizer does when determining which of the thousands of access paths it should take to ensure the fastest response time.  By shifting those parameters you are telling Oracle that an index lookup is either very efficient or not so efficient.  You really shouldn't change those defaults.    Index use doesn't mean fast.

I suspect the DBA's knew why they were changing them

Thanks for the suggestions, too late for us, I only respond here to
answer the 'misrepresentation' and to allay the notion that we didn't
throw enough expertise at the problem.

Also, I think it would be a disservice to leave out the horror stories.

RAC is very complicated and can be an intricate thing to get
tuned....we have a number of nicely functioning production RAC
installations on Campus, two of those teams are less than 15 feet from
our Sakai team.... but, it proved not worth the effort of everyone in
this case.

Hopefully you can guide others to a good end....

>
>
> --- On Wed, 10/7/09, Jon Gorrono <jpgorrono at ucdavis.edu> wrote:
>
>> From: Jon Gorrono <jpgorrono at ucdavis.edu>
>> Subject: Re: [Building Sakai] Experience with Oracle RAC
>> To: mame-awa.diop at hec.ca
>> Cc: "sakai-dev" <sakai-dev at collab.sakaiproject.org>
>> Date: Wednesday, October 7, 2009, 7:30 PM
>> Did you say Oracle RAC? Slowly I
>> turned... step by step....
>> (http://www.youtube.com/watch?v=9pQii1L8fGk)
>>
>> We have some cool headed doc's about our experiences with
>> rac in out
>> confluence, which I can dig out if you would like the
>> details.
>>
>> In contrast I can give you a sense of our experience with
>> RAC: it was
>> not good. There were to issues that bubbled to the top of
>> the heap and
>> lingered long enough for us to decide to give ip on the
>> RAC
>>
>> 1). a) the RAC insists on managing segment fragmentation
>> and b) it sux at it
>>  - we would setup our tables views and mviews and a bunch
>> of nicely
>> performing indexes and the RAC would split up the table and
>> index
>> segments until the performance tanked. And we coul dnot
>> turn it off
>> even though it appeared that we had declaratively
>> 2) There was a lot of speculation that the number of small
>> tables used
>> in sakai for simple low data lookups would harm performance
>> in a RAC
>> environment due to the added overhead for synch'ing those
>> tables over
>> the RAC private network... but we never tested this: #1
>> killed the
>> project.
>>
>> On Tue, Oct 6, 2009 at 11:12 AM, Mame-Awa Diop <mame-awa.diop at hec.ca>
>> wrote:
>> > Hello everybody,
>> >
>> > I was wondering if you guys using Oracle as a database
>> for Sakai and using a
>> > clustering tool like Oracle RAC has had any special
>> behavior with the
>> > clustering tool ? Is there something worth monitoring
>> with the clustering
>> > tool ?
>> > If you use another clustering tool, your input would
>> be appreciated also.
>> >
>> >
>> > Cheers,
>> >
>> > --
>> >
>> > Mame Awa Diop
>> > Analyste programmeur
>> > Service de gestion des technologies et de
>> l'information
>> > HEC Montréal
>> > 3000, chemin de la côte-Sainte-Catherine
>> > Montréal  (Québec)   H3T 2A7
>> >
>> > GTI: 514.340.6000 #2029 - Bureau 3.755
>> > Projet OpenSyllabus: 514.340.6776 - Bureau 4.521
>> > 5255, avenue Decelles
>> > Montréal (Québec) Canada
>> > H3T 2B
>> >
>> > Télécopieur: (514)340-5637
>> >
>> > _______________________________________________
>> > sakai-dev mailing list
>> > sakai-dev at collab.sakaiproject.org
>> > http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>> >
>> > TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org
>> > with a subject of "unsubscribe"
>> >
>>
>>
>>
>> --
>> Jon Gorrono
>> PGP Key: 0x5434509D -
>> http{pgp.mit.edu:11371/pks/lookup?search=0x5434509D&op=index}
>> Thawte Notary -
>> https{www.thawte.com/cgi/personal/wot/directory.exe?node=312}
>> GSWoT Introducer - {GSWoT:US75 5434509D Jon P. Gorrono
>> <jpgorrono - gswot.org>}
>> http{ats.ucdavis.edu}
>>
>> Sent from Davis, CA, United States
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org
>> with a subject of "unsubscribe"
>>
>
>
>
>

-- 
Jon Gorrono
PGP Key: 0x5434509D -
http{pgp.mit.edu:11371/pks/lookup?search=0x5434509D&op=index}
Thawte Notary - https{www.thawte.com/cgi/personal/wot/directory.exe?node=312}
GSWoT Introducer - {GSWoT:US75 5434509D Jon P. Gorrono <jpgorrono - gswot.org>}
http{ats.ucdavis.edu}

Sent from Davis, CA, United States