RE: Understanding Terracotta caching
Date: Thu, 21 Jan 2010 12:50:03 -0500
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.
Senior Oracle DBA/NA Team Lead
[mailto:oracle-l-bounce_at_freelists.org] 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
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.
c - used by a small portion of the transactions, a mix of
read/write, code/indexes fairly efficient, have never seen this table as
d - critical table used in approximately 60% of all code, 33M+
rows, lots of DML and reads, inefficient code, poor design, frequently a
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
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.
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.
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?
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.