Monday, February 27, 2012

Setting aside a node for maintenance on Exadata

Actually, this isn't exadata specific, but it becomes even more important on a multi-node cluster.

First the background.

  I have a data warehouse application in which we are loading up lots of data.  At the same time, we have users reporting off the data.  I am finding that we actually have 2 needs, and they are opposed

USERS -- Their needs
  • Lots of concurrency
  • Small amounts of data
  • Small PGA
  • small temp
  • Large SGA
If the users need more than this something probably went wrong with their query..

 DBA/ODI jobs

  • Very little concurrency (except for some parallelization)
  • Large amounts of data
  • HUGE Temp
  • HUGE Undo segments
  • Small SGA

The Temp issue is easy enough to fix with a separate temp  for each user, and by setting up a temporary tablespace group for users.

But what about my the other things data load jobs need ??  The only answer seems to be to set aside 1 (or more) nodes out of my cluster for maintenance/loading.  This node (or nodes) will have a different configuration.  This node, let's say node 8, has the following characteristics.

  • The only service  running on this node is my ODI (data load) service, and a service the DBA's to use for index rebuilds
  • PGA Automatic memory management is not enabled
  • work_area_size_policy is manual
  • sort_area_size=60g
  • hash_area_size=60g
  • undo tablespace size is set to 1tb, much, much larger than the other nodes.  Undo_retention is set to a very large number.
The only work done on Node 8 will be loading of large tables, and  rebuild/creation of indexes.

I was even thinking about getting the memory expansion kit for just this node, to bring it up to 144g from 96g.

Anyone else do this ? set aside a node specifically for "heavy lifting" with a different configuration ?

UPDATE ---  After writing this, and looking at my load process, I noticed that most of my load time is going to temp.. Both reads, and writes since I am doing huge hashes.  I am considering Dedicating SSD luns to the temp area for my ODI jobs only.  I might even try dedicating SSD to the i$ (intermediate staging tables) that ODI uses.

No comments:

Post a Comment