I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential.
So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel.
Regards, Dave
[EMAIL PROTECTED] wrote:
Hi Hans/Vikas,
I tend to agree that the old draconian rule that "thou shalt always separate indexes from tables" may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!!
The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS).
Even then, you can always add more drives to the
existing volume and restripe, i.e., adding 5 more
drives to 10 drives increases the I/O capacity by 50%.
At the end of the day, the I/O sub-system does not
care, whether it is servicing a data segment, index
segment or undo segment.
But, in certain environments, that I have dealt with,
there has been a need to separate heavily and
concurrently accessed objects (does not matter whether
these objects are all indexes or tables or both). This
may be true only for certain objects and certain
queries. So, please don't apply this in a blanket
fashion.
Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created.
I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots. But the implementation of SAME and how many volumes you need in your enviroment, is a function of your custom needs based on your system and application demands. When you over-simplify something, you lose the flexibility. The art factor here (which requires some planning) is in achieving a balance between simplicity, flexibility, performance, manageability and availability.
Hope that helps,
Gaja --- Hans de Git <[EMAIL PROTECTED]> wrote:
Vikas,
Spend an hour on reading this usenet thread:
http://groups.google.nl/groups?hl=nl&lr=&ie=UTF-8&oe=UTF-8&threadm=brjz8.15%24707.245%40news.oracle.com&rnum=1&prev=/groups%3Fhl%3Dnl%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex
It will open your eyes about separating data/index.
Still not sure about the redolog stream...Because of
the sequential nature of redologfiles. I've read tests that 'prove' it
doesn't matter much whether you separate your redolog from 'ordinary'
datafiles or not. It does simplify things when you pure SAME.
Regards, Hans
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 08 Oct 2003 09:54:30 -0800
Thanks Gaja ! Does it also make sense from a performance perspective (I/O issues due to concurrent access of index and data ) to separate them or is that point moot once you apply the SAME methodology ?
-----Original Message----- Gaja Krishna Vaidyanatha Sent: Wednesday, October 08, 2003 9:24 AM To: Multiple recipients of list ORACLE-L
Vikas,
The answer is an enthusiastic yes. This is purely from an administrative and manageability standpoint. For example, if you have INDEX and DATA segments separated in 2 different tablespaces, the backup of these tablespaces can be done INDEPENDENTLY. This is relevant, as if you were to rebuild your indexes using the NOLOGGING option between 2 backup jobs. If that were the case, then all you will need to do after the rebuild is complete, is to backup only the INDX tablespace.
This is a best practice (if not a requirement) in most production shops, unless you think you can re-re-build your indexes in the event of media failure and you lose your INDX tablespace.
Hope that helps,
Gaja --- vikas kawatra <[EMAIL PROTECTED]> wrote: > Guys, > > Does it make sense to separate data and index > segments into separate > tablespaces if you create a single logical volume > and all files are > striped using the SAME methodology ? > > Thanks > > vikas > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: vikas kawatra > INET: [EMAIL PROTECTED] >
===== Gaja Krishna Vaidyanatha Principal Technical Product Manager, Application Performance Management, Veritas Corporation E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180 Website: http://www.veritas.com
__________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: vikas kawatra INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
=====
Gaja Krishna Vaidyanatha
Principal Technical Product Manager, Application Performance Management, Veritas Corporation
E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180
Website: http://www.veritas.com
__________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Hau INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).