Oracle-L: RE: Sizing - RAC, storage subsystem EMC

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 22 May 2003 08:56:44 -0800
Message-ID: <F001.005A01A0.20030522085644@fatcity.com>

> I've yet to see anyone give a benchmark result which
> showed that table and index separation gave any
> noticeable benefit (san, cache, or otherwise).

I totally agree. However, from an historical perspective, by accident some of my work may be the reason people think they have to put their indexes and data on different devices. In the OFA Standard document I wrote in the early 1990s, I wrote that there are three requirements that drive how one should separate one's segments into different tablespaces (OFA Standard, pp13-14):

fragmentation character
I/O distribution
administrative needs

I recommended separating data and indexes into separate *tablespaces* (which is not to say different devices). Index segments should be put into separate tablespaces from data segments predominantly for reasons 1 and 3. Reason 2 ranks a distant third (indexes are dropped and rebuilt much more often than tables, and indexes should probably have different storage parameters and backup schedules than tables). The tablespace separation recommendation allows you to distribute table vs. index I/O, but I/O distribution was *not* the prime reason that I recommended separating indexes and data into different tablespaces.

I'd encourage you guys to believe Connor, for sure, but don't mistake his point to mean that it's a good idea all of a sudden to start mixing index segments and data segments in the same tablespace.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details...

-----Original Message-----
McDonald
Sent: Wednesday, May 21, 2003 11:12 AM
To: Multiple recipients of list ORACLE-L

I've yet to see anyone give a benchmark result which showed that table and index separation gave any noticeable benefit (san, cache, or otherwise).

Conversely, I have seen systems where judicious separation of certain segments helped enormously, probably the most common in my (limited) experience being partitions setup in different stripes to maximise large scale parallel scan performance. (The improvement was in comparison to SAME btw)

Sensible I/O separation to maximise throughput is a good thing to have - if that ends up being data and indexes separate then so be it, but I would contend its probably very unlikely to be the norm.

Similarly, having a big cache will help with some things, but not all...eg if you're backing a database disk-to-disk, then when it comes down the crunch, it pure disk performance thats going to matter.

Going back to the original post on raid 0+1 versus raid 5, its not raid-5 during normal operation that bothers me (cache and other smarts can hide the overhead quite well)...Its what happen when you lose a disk...Just when you're most vulnerable, your I/O performance takes a bullet to the head.

hth
connor

"Koivu, Lisa" <Lisa.Koivu_at_Fairfieldresorts.com> wrote: > Hi Yeichel,
>
> If the database is writing to the cache memory (and
> then to disk behind the scenes), then does the rule
> of separating tables and indexes on different
> devices still apply? Last implementation I did on a
> SAN I followed that rule anyway, figuring it
> couldn't hurt. What do you think?
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
> Office: 954-935-4117
> Fax: 954-935-3639
> Cell: 954-683-4459
> -----Original Message-----
> Sent: Wednesday, May 21, 2003 8:55 AM
> To: Multiple recipients of list ORACLE-L
>
>
> With EMC, or any other SAN, you do not write to the
> disks. You write into a cache memory on the
> controller and the controller then writes the data
> to the disks at his own time. If you have big enough
> write cache on the controller the raid-5 write speed
> does not concern you.
>
> Raid-5 might be a little slow but it save almost 1/2
> the disk space needed to ensure the correctness of
> the data since it can use one parity disk for 10-20
> disks.
>
> Yechiel Adar
> Mehish
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> <mailto:ORACLE-L_at_fatcity.com>
> Sent: Tuesday, May 20, 2003 9:06 AM
>
> Hi all, hope you can give some input ideas.
>
> I am in the process of designing a system for a
> client of ours for a proposal
>
> The sizing information I have been given is as
> follows.
>
> 58.1 million tickets/day at 351 bytes per record.
> The record was complete populated (all columns
> filled to max) in a table and then analyzed. Average
> row size 351 bytes.
> =~ 19 GB/day. Raw data. Plus overhead (indexes, temp
> space, rollback, some other data etc) here and there
> I have requested 5 TB.
>
> We need to keep records for a month. Table design I
> am looking at is a date partition with a second
> level hash partition. This is so that I can move
> data in the oldest week/table space off line and
> write them to optical storage for possible retrieval
> at a later date (requirement).
>
> Of course this will be on locally managed table
> spaces with auto storage management for segments.
>
> Hardware:
> The database will be a Oracle RAC 9.2.0.4 on Sun
> cluster 3 build on 2 x Sun StarFire V880, 4 CPU's, 4
> GB RAM each,
> Connected to an EMC SAN via Fiber Channel
>
> I do not have more information about the EMC array
> at the moment. Hitachi has been mentioned. (excuse
> the spelling)
>
> Question I have.
>
> I have been asked how many writes the Database will
> be doing to the SAN per second.
> I have determined that I should expect about 2000
> tickets/second.
> The table in question will have 2 indexes.
>
> Now following rough guessing I said I should expect
> at least 16 000 writes/second
>
> This was done by say/assuming
>
> 2 writes for the redo log files (2 members)
> 2 writes for the control files (2 control files)
> 2 writes to index blocks
> 1 write to undo table space block
> 1 write to table block for data
> total 8 blocks written to per ticket.
>
> Now I know the above is a real rough. And probably
> very wrong, if someone can shed some more light on
> it and give me a more accurate method/guess I would
> appreciate it.
>
> Another question.
> The hardware SAN engineers are telling me they want
> to configure the SAN in a RAID 5 configuration. I
> have requested Raid 0 + 1. They say this is going to
> be to expensive and the new technology allows them
> to give me the performance I want using RAID 5.
>
> I would prefer to err on the side of caution and
> follow Oracle industry wide recommendation and
> follow the SAME methodology.
> Comment.
>
> Thx.
>
> George
> ________________________________________________
> George Leonard
>
> You Have The Obligation to Inform One Honestly of
> the risk, And As a Person
> You Are Committed to Educate Yourself to the Total
> Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every
> Fool Has the Right to Kill or Injure Themselves as
> They See Fit!
>
>
>
> This email and all contents are subject to the
> following disclaimer:
>
> "http://www.didata.com/disclaimer.asp"
>
>

Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

It's Samaritans' Week. Help Samaritans help others. Call 08709 000032 to give or donate online now at http://www.samaritans.org/support/donations.shtm

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 22 2003 - 11:56:44 CDT