Oracle Woe's - Pam Issue?

[alex at crackpot.org]

Quoting Mike Garfias <mike at garfias.org>:

> You're missing the point.  You can scale beyond a single box without
> getting into some complex clustering setup AND without getting into
> expensive hardware.  It just requires work in the dev cycle, rather
> than SA work after the app is built.

I second this.  Here's a few ideas from my experiences.

- Whatever code you use to make a database connection should know how  
to select from one of many servers.  The logic you use to make the  
decision isn't important yet, since you're starting with just 1  
server.  Just leave yourself a hook so that if you grow to multiple  
boxes, you won't have a major refactoring to do.  Lots of web apps  
(especially ones that started as small open-source projects) are built  
around the assumption that the database is a single box, and breaking  
that assumption can be a huge pain once its in place.

- Notice chunks of your code that do read-only queries.  These could  
be offloaded to read-only database slaves.  At least with MySQL,  
that's a very cheap and easy way to scale read traffic.  MySQL  
replication is efficient and reliable.

- Scaling write traffic is a lot harder.  The 'sharding' approach  
mentioned earlier in this thread is the one that makes the most sense  
to me.  Again, make sure your code is written so that, when the time  
comes, it is easy to select from one of many servers.

- Don't use the database if you don't have to.  Make use of a caching  
layer to save as much database traffic as possible.  The MySQL query  
cache is a very easy way to do this, but something like memcache is  
even better since you can move traffic completely away from the DB  
server and towards other boxes.  The memcache architecture is  
distributed by design, so it's easy to build a very large cache out of  
lots of cheap boxes.  http://www.danga.com/memcached/

- Write good SQL.  No amount of server-side tweaking will save you  
from badly written queries.

- Monitor your performance from the beginning.  Figure out ahead of  
time what the trouble signs might be that you're outgrowing your  
current resources.  You don't want the first sign of trouble to be the  
fact that your app is crashing/offline.  Ganglia is one way to do  
this, and I think works pretty well.     
http://ganglia.sourceforge.net/  If you try this out and want some  
mysql or apache specific ideas about what to monitor, let me know.

I hope that helps.

alex