Re: Oracle RAC & Fusion I/O Cards
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: troach@xxxxxxxxx, vnulllists@xxxxxxxxxxxx
- Date: Sun, 31 Jan 2010 21:02:12 -0800 (PST)
I have a similar scenario in play for my marts- BUT- I'm not on RAC and my
RAC experience is limited, (yes, I'm one of those DBA's that prefers dataguard
and feels failure rarely grants me failover when it comes to RAC, go ahead, sue
me over it... :))
I did feel that it would be rude of me not to put in my history with the rest
of the technical specs below.
I do have 5 fusion I/O cards in each of my two mart servers, four databases
total. Our marts are built as a duplicate recovery process and then builds the
main schema with datapump from the previous production mart each week, (one
week rotation, 6-10TB, depending on feed requirements, oh what fun!)
One of my big challenges was parallel tuning and the size of the objects they
were creating in parallel, reading from in parallel. the waits on temp read
and write due to hashing, but mostly sorting were consistently large and after
tuning, I talked my manager into letting me create temporary tablespace groups
striped across the fusion I/O cards. I am using three temporary tablespaces,
48GB each, four files each, ensuring they are all the same size, (striping gets
very bizarre otherwise) across four of the drives, reversing and exchanging the
order of the files on the drives to ensure that no two exact files that would
be written to are on the same drive.
This works exceptionally well, but as these were added drives to the
environment, the consideration for memory allocated to each card was a factor
and more memory added to each server, (each fusion I/O card claims between
4-6GB of memory, which can be quite a hit on many servers...) We also found
out that I can stripe my files and extend my I/O too much on a server, hitting
the limit on what the bus could take.
Last challenge was how the CBO will react. I was quite surprised how
"different" the database grid looked in OEM alone when viewing the consistent
processing for the marts. The CBO took this change in stride, but with I/O
looking less expensive for the database after updating my system stats, yes, a
few execution plans changed, so this DBA was scrambling quickly to put a few
hints in to compensate.
The one challenge I see for you, is that you want to assign users to this temp
tablespace group. If they are executing packages owned by another user, this
could hinder you, as the system allocates temp tablespace and may disregard all
your careful work. I wrote up a piece on invokers rights for this very purpose
that might interest you, hopefully it helps!
http://dbakevlar.blogspot.com/2009/11/utilizing-separate-temp-tablespace.html
Good luck, sorry I can't help with the RAC part... :)
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Sun, 1/31/10, Jakub Wartak <vnulllists@xxxxxxxxxxxx> wrote:
From: Jakub Wartak <vnulllists@xxxxxxxxxxxx>
Subject: Re: Oracle RAC & Fusion I/O Cards
To: troach@xxxxxxxxx
Cc: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
Date: Sunday, January 31, 2010, 5:36 AM
Dnia sobota, 30 stycznia 2010 02:07, Thomas Roach napisał:
> I have a RAC (10.2.0.4 on RHEL 5.3 Linux x86-64) cluster and we do a large
> amounts of sorts and things in Parallel. I was looking at Fusion I/O cards
> which are kept in the server. What I was thinking was, if I used a
> Temporary Tablespace Groups and assigned users to this, would it be
> possible to store 1 temporary tablespace locally on each node (4 total), or
> does this have to be on shared storage as well? If I can store them local,
> then I can use the Fusion I/O device.
Thomas,
There is silence on this topic, so perhaps I'll try...
If you have single schema (and tablespace assigment is done on per-schema
level) you would need to create users schemaN (where N=RAC_node_id). This
would require proper GRANTs on schemaN to access orginal "schema"... Then you
would need to perform temp. tablespaces assigment to schemaN. AFAIK temporary
tablespace in 10.2 was not registered in controlfiles.
.. but first I would start from measuring I/O performance of current
tempfiles. There are many ways:
1) gv$filestat
2) assigning dedidcated shared storage location (CFS/LUN) and placing only
tempfiles there to monitor their real I/O metrics via iostat -x
3) systemtap (might be difficult on RHEL5.3)
I would be very worried about several things:
1) RAC access to tempfile from other RAC node especially during things like
TAF SELECT failovers (not sure how TAF does this interally, by re-executing
whole query from start and starting returning rows from last sent row?)
2) Load balancing... i mean: ensuring that only schemaN on nodeN can access
tempfileN but no tempfileM (where N,M = instance IDs), what might be the
problem is that during instanceN crash, clients would start to failover to
instanceM (using schemaN which would have been configured to use tempfileN).
So taking into account "2", if you have 3 nodes each FusionI/O filesystem
would need to have same tempfiles configured (?)
For me it is RISKY, i would just stick to tuning PGA/adding more
memory*/putting more NVRAM into storage :)
* = if don't have some large ammounts of it like 128GB.
--
Jakub Wartak
http://jakub.wartak.pl/blog
--
//www.freelists.org/webpage/oracle-l
Other related posts:
