Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2013
    Posts
    26

    Unanswered: Tablespace layout

    Hello, From physical design perspective, is it a good idea to have tablespaces on different mount points? Plan to have the data and transaction log filesystems on different luns.

    Thanks

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    Depending on your usage patterns , there may be a performance gain by separating the tablespaces onto different mount points. Although if the mount points are not associated to separate IO channels - then the gain may be limited.
    An example of a separate IO channel - may include different adapter and separate data stores
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    Really depends on the type of storage you are using. In the old days separate mount ponts was really important because you would get hot disks etc. With the current storage systems it really does not matter. I have 10 TB+ db's that I stripe all of the data/iindex tablespaces using automatic storage across multiple mount points. I still keep temp's separate just so a run a query does not fill my data file systems. The best answer I can give is it depends :-) It is still beneficial to separate your larger tables to their own tablespaces and balance your data across multiple tablespaces. This helps when running backups and other utilities.

  4. #4
    Join Date
    Nov 2013
    Posts
    26
    Thanks Guys. This is on 10.5 BLU. We will be using virtual LUNS, emc storage. The db would be 10+ TB. My understaning is, this raw data would go down to 1-2 TB because of compression. I read that the extent size of the tablespace and stripe size on storage should match. I was thinking of different mount points to prevent temp filling up and causing issues (as you mentioned). Also, ease of maintenance.

    Suggestions on extent size and lun size? 200GB ??? Any other tips appreciated.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The recommended tablespace parameters for column-organized tables are 32K page size, 4 page extents, so if you want to match the stripe size it should be 128Kib.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Nov 2013
    Posts
    26
    Thanks everyone.

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    One tip when using network storage is to make sure that multiple LUNs does not share physical disks. Sometimes, DBA will put index on one set of LUNs, data on another set of LUNs, logs on another set of LUNs and Network storage administrator creates different LUNs using same set of physical disks negating the DBA actions.

    We had a situation when large tablespace was put on 6 LUNs for performance reasons only to find out later that storage administrator created those 6 LUNs from same set of physical disks.

    ....Satya

  8. #8
    Join Date
    Nov 2013
    Posts
    26
    Satya, Thanks for the tip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •