RE: Understanding Terracotta caching

  • From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
  • To: <sbecker6925@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 21 Jan 2010 12:50:03 -0500

Sandy,
 
    First things first, no amount of caching is going to fix a badly
designed application or make that application more scalable.  All your
going to end up doing is moving the problems from the database server
into the cache and then your going to make them worse with the constant
refreshing.  Yes, caching is just dandy for tables that are used in
lookup mode, namely 90% reads.  It becomes a real pain when you have
writes because most caching algorithms use the dump and reload method.
So, what should you do,?  Go back to the application and re-work those
portions that are causing problems.  To get scalability the app has to
be fast because it's just going to be asked to do the same thing it is
today, just more often and with fewer cpu ticks to do it.
 

Dick Goulet 
Senior Oracle DBA/NA Team Lead 
PAREXEL International 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandra Becker
Sent: Thursday, January 21, 2010 12:19 PM
To: oracle-l
Subject: Understanding Terracotta caching


Platform:  IBM Series z
OS:         SUSE 10
Oracle:    EE 10gR2
 
Current situation:
 
Concern that the application is not scalable.  Some large tables are
have heavy loads, a mix of read/write, heavier on the reads.  Some
inefficient code on top of a poor design have been causing performance
issues.  We both write and host the application for our customers.
 
Tables:

1.      
        c - used by a small portion of the transactions,  a mix of
read/write, code/indexes fairly efficient, have never seen this table as
a bottleneck
2.      
        d - critical table used in approximately 60% of all code, 33M+
rows, lots of DML and reads, inefficient code, poor design, frequently a
bottleneck
3.      
        i - important table, 7.5M+ rows, inefficient code, lots of DML
and reads, sometimes it and its children are a bottleneck
4.      
        m - critical table, 7.4M+ rows, inefficient code, combination of
columns in tables d and i because "joins are bad", all inserts to d and
i requie inserts to this table as well, updates on several columns in d
and i require updates on the same columns in this table, the state
column can have any one of 13 different values, frequently a bottleneck
5.      
        p - heavily used, 1.1M+ rows, predominately reads a little DML,
has been a bottleneck at times in the past 6 months
6.      
        u - very heavily used, 151K+ rows, predominately reads some DML,
some inefficient code but not generally a bottleneck

Proposal:
Use Terracotta caching to make the application more scalable as we add
more customers and users.
 
The database architect is proposing that table c be fully cached because
he thinks it is important.  He also proposes only one state of the 13
for table m be cached.  The working theory is that this single state
accounts for most of the reads against the table and the other 12 states
are frequently updated/inserted.  We both agree that the u table should
be cached.  He is planning to refresh the cache every 5 to 10 minutes.
When I asked him what were we expecting the end user to see, he said
there shouldn't be any change in performance but this would make the
application scalable.  I also asked what was the expected benefit if he
was freshing the cache that often.  I didn't get an answer.
 
I suggested that the p table be cached rather than the c table since it
is used more often in the application and doesn't have as much DML.  I
also suggested that we do some research to determine if the state on the
m table he wants to cache is really the most often requested state for
reads.  He nixed that idea saying he knew it was.  By reviewing the sql
being executed today for the m table, I see that it shows it is not the
most frequently requested state for reads but I can't get him to
consider he's wrong.  I also went to the Terracotta website to get more
information about their EX caching and see if they had any
recommendations, which they did.
 
Questions:

1.      
        My understanding of caching in general and Terracotta in
particular is you should cache heavily used tables that don't experience
a lot of DML.  Is this an incorrect understanding?  Terracotta says less
than 50% of transacations against the table should be DML so that's
partly what I base my opinion on.
2.      
        My theory is we should focus on those parts of the application
that we KNOW are the bottlenecks rather than just ASSUMING we know where
they are.  If we don't know, then we should research.  I had some
success researching/tuning other parts of inefficient code so I'm
certain we can effect some positive changes in this area.  Am I totally
off base here thinking we should do more research/testing?
3.      
        I suggested we spend more time tuning the parts of the code we
know are inefficient along with looking at the caching.  That was
rejected as taking too much time even though the same code must be
reviewed to use the caching.  I'm really lost on this one as to why you
wouldn't look at both options at the same time.  Is that unreasonable?
4.      
        Is refreshing the cache every 5 to 10 minutes a reasonable time
period?  Is it too often, not often enough, etc.  Since the refresh has
to read the table, what should I be looking at to see if the benefits
are really there?
5.      
        Terracotta says to look at the cache/hit ratio for the table to
determine if it really is a good candidate.  How do I look at cache/hit
ratios for tables?

Thanks in advance for any clarifcation you can give me.
 
Sandy
Transzap, Inc.
 

Other related posts: