Professional Web Applications Themes

Temporary Tablespace type to encrease performance - IBM DB2

"zebi" <org> wrote in message news:bmpleq$47v$tiscali.fr...  Best is DMS, but make absolutely sure it is plenty large enough. Several times the size of the largest table is not unreasonable. Try to create multiple containers, each on different disk drives or different disk arrays....

  1. #1

    Default Re: Temporary Tablespace type to encrease performance

    "zebi" <org> wrote in message news:bmpleq$47v$tiscali.fr... 
    Best is DMS, but make absolutely sure it is plenty large enough. Several
    times the size of the largest table is not unreasonable. Try to create
    multiple containers, each on different disk drives or different disk arrays.


    Mark Guest

  2. #2

    Default Temporary Tablespace type to encrease performance

    hello,

    What's your opinion :
    The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 /
    multiprocessor ) is DMS ou SMS ?

    Thanks

    ZEB


    zebi Guest

  3. #3

    Default Re: Temporary Tablespace type to encrease performance

    Thanks

    Do you know he impact of the type of temporary tablespaces (only the
    tempspace) for the performance ?

    "Mark A" <net> a écrit dans le message de news:
    9SYjb.223$uswest.net... 
    news:bmpleq$47v$tiscali.fr... 
    > Best is DMS, but make absolutely sure it is plenty large enough. Several
    > times the size of the largest table is not unreasonable. Try to create
    > multiple containers, each on different disk drives or different disk[/ref]
    arrays. 


    zebi Guest

  4. #4

    Default Re: Temporary Tablespace type to encrease performance

    "zebi" <org> wrote in message news:bmpo0i$b6k$tiscali.fr... 
    If you do it right (with containers on separate drives/arrays) then you get
    striping of disk, and better disk subsystem throughput. Multiple containers
    on separate disk/arrays also enables intra-partition parallelism (if certain
    parms are set in DB2). This works especially well if you have multiple
    CPU's.


    Mark Guest

  5. #5

    Default Re: Temporary Tablespace type to encrease performance

    Depends on what you mean by "best" - for DB2 performance benchmarks
    (TPC-C and TPC-H) DMS is often used. For administration and optimal use
    of space, SMS has advantage for temps. The reasons are explained here:

    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html

    If you want to see the type of tablespaces used in IBM DB2 AIX
    benchmarks, see:

    http://www.tpc.org/results/FDR/tpch/p690_10000GB_160proc_FDR.pdf

    and

    http://www.tpc.org/results/FDR/TPCC/IBMp690fdr_06302003.pdf

    (Look in the appendixes for tablespace set up - I think it's usually
    Appendix B).


    zebi wrote:
     

    Blair Guest

  6. #6

    Default Re: Temporary Tablespace type to encrease performance

    We never recommend that temp table spaces be anything but SMS. There may be additional overhead for TEMP table spaces on DMS and the rule-of-thumb is always to use SMS.

    --

    Bob
    Consulting I/T Specialist
    IBM Toronto Lab
    IBM Software Services for Data Management
    "zebi" <org> wrote in message news:bmpleq$47v$tiscali.fr...
    hello,

    What's your opinion :
    The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 /
    multiprocessor ) is DMS ou SMS ?

    Thanks

    ZEB


    Bob Guest

  7. #7

    Default Re: Temporary Tablespace type to encrease performance

    > "Bob [IBM]" <com> wrote in 

    In the TPC-H benchmark submitted by IBM in July 2003 using a 100GB database
    (which now owns the performance record for this size database), a DMS
    temporary tablespace was used. See page 77 of the following doent (the
    IBM page number, not the PDF page number):

    http://www.tpc.org/results/FDR/tpch/ibm.e325.100gb.030810.fdr.pdf

    The system was as follows:

    IBM eServer 325 8-node cluster
    Database Manager: IBM DB2 UDB 8.1 ESE with DPF
    Operating System: SuSE Linux 8.1


    Mark Guest

  8. #8

    Default Re: Temporary Tablespace type to encrease performance

    "Mark A" <net> writes:
     
    >Best is DMS, but make absolutely sure it is plenty large enough. Several
    >times the size of the largest table is not unreasonable. Try to create
    >multiple containers, each on different disk drives or different disk arrays.[/ref]

    Without knowing about the application, it is not possible to say which
    is best, actually. The advantage of SMS tablespaces are that the
    OS allocates disk space to them as needed, so you won't have to
    administer the tablespace much as long as you don't run out of
    disk space on the server. But Windows and Unix use indexed allocation
    of disk blocks to files, which scatters the disk blocks of a file
    all over the disk. In a database, this leads to more time to service
    disk I/O requests because of more seek time-- this is especially
    true for tables where sequential scans and index scans with clustered
    indexes are running. When scanning sequentially, you get better performance
    if the disk blocks where the tuples are stored are contiguous on the
    disk. Since the operating system allocation of disk blocks to files
    is not continguous with Unix or Windows, it is customary for a relational
    DBMS to create tablespaces with large chunks of contiguous storage,
    and manage the allocation of disk space out of these predefined
    containers. Better designed systems support extents to the space
    so that the system can find space when the original allocation
    is fully utilized. But these types of tablespaces require more
    administration.

    So, the short answer is that DMS tablespaces perform better but
    require more sysadmin work to keep them working and to keep them
    organized for performance. SMS tablespaces require less administrative
    work, but don't perform as well. If your application will perform
    adequately with SMS temporary tablespaces and you don't have
    gobs of free disk space just to make the temporary space huge,
    then SMS is a fine choice. If performance is an issue, you'll want
    DMS, and if you have lots of disk space, you can, as Mark noted,
    make them quite large to minimize administrative overhead.

    Cheers,

    Joseph
    Joseph,,, Guest

  9. #9

    Default Re: Temporary Tablespace type to encrease performance

    Mark - I am fully aware of the benchmark .. but thanks as always for your insight. I still never suggest anything but SMS for the reason of never knowing if you will have enough space and also the additional overhead of using DMS table spaces. Of course, your mileage may vary. Have a great weekend!

    --

    Bob
    Consulting I/T Specialist
    IBM Toronto Lab
    IBM Software Services for Data Management
    "Mark A" <net> wrote in message news:_i1kb.943$uswest.net... 

    In the TPC-H benchmark submitted by IBM in July 2003 using a 100GB database
    (which now owns the performance record for this size database), a DMS
    temporary tablespace was used. See page 77 of the following doent (the
    IBM page number, not the PDF page number):

    http://www.tpc.org/results/FDR/tpch/ibm.e325.100gb.030810.fdr.pdf

    The system was as follows:

    IBM eServer 325 8-node cluster
    Database Manager: IBM DB2 UDB 8.1 ESE with DPF
    Operating System: SuSE Linux 8.1


    Bob Guest

  10. #10

    Default Re: Temporary Tablespace type to encrease performance

    > "Bob [IBM]" <com 

    I am not sure what you mean by overhead, but I believe that the overall
    performance is better on DMS, if set up in an optimal manner (each container
    on its own drive or array).

    DBA's do get fewer complaints with SMS because it is less likely to run out
    of space. However, if one creates the temporary tablespaces with a
    sufficient size (several times the largest table if possible) that is not
    likely to happen. Disk space is cheap.


    Mark Guest

  11. #11

    Default Re: Temporary Tablespace type to encrease performance

    There is overhead for DMS incurred by going through the SMP and EMP ... and if I am not mistaken some synchronous writes occur during the creation of the temporary table if using DMS. Of course regardless of type, we all remember that it is not advantageous to have > 1 temporary table space of the same page size ...

    Bob
    Consulting I/T Specialist
    IBM Toronto Lab
    IBM Software Services for Data Management
    [My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]

    "Mark A" <net> wrote in message news:tMlkb.41$uswest.net... 

    I am not sure what you mean by overhead, but I believe that the overall performance is better on DMS, if set up in an optimal manner (each container
    on its own drive or array).

    DBA's do get fewer complaints with SMS because it is less likely to run out of space. However, if one creates the temporary tablespaces with a
    sufficient size (several times the largest table if possible) that is not likely to happen. Disk space is cheap.


    Bob Guest

  12. #12

    Default Re: Temporary Tablespace type to encrease performance

    > "Bob [IBM]" <com> wrote in message 

    There is overhead on both DMS and SMS, but they may be in different areas.
    You only mentioned the overhead attributable to DMS. Overall, DMS performs
    better if set up properly with enough space to ensure that one does not run
    out. I presume that is why the IBM benchmark team used DMS.


    Mark Guest

  13. #13

    Default Re: Temporary Tablespace type to encrease performance



    Mark A wrote:
     
    >
    > There is overhead on both DMS and SMS, but they may be in different areas.
    > You only mentioned the overhead attributable to DMS. Overall, DMS performs
    > better if set up properly with enough space to ensure that one does not run
    > out. I presume that is why the IBM benchmark team used DMS.[/ref]

    AFAIK, only on SUN and Linux suggest using DMS as system temporary tablespace.
    On Sun, you'd better use DMS raw device.


    Fan Guest

  14. #14

    Default Re: Temporary Tablespace type to encrease performance

    Can you elaborate on some of the overhead you see in SMS? I cannot state any specifically so would be interested in your experiences in the field in this area please.

    --

    Bob
    Consulting I/T Specialist
    IBM Toronto Lab
    IBM Software Services for Data Management
    [My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
    "Mark A" <net> wrote in message news:C3nkb.46$uswest.net... 

    There is overhead on both DMS and SMS, but they may be in different areas.
    You only mentioned the overhead attributable to DMS. Overall, DMS performs
    better if set up properly with enough space to ensure that one does not run
    out. I presume that is why the IBM benchmark team used DMS.


    Bob Guest

  15. #15

    Default Re: Temporary Tablespace type to encrease performance

    "Bob [IBM]" <com> wrote in message 

    I am not an expert in this area, although I have heard some discussion of
    this topic, not all of which I remember in detail off-hand. I think it might
    be beneficial to ask the DB2 Linux benchmark team about why they chose DMS
    and whether they benchmarked different DMS vs SMS tablespace scenarios.

    Someone earlier suggested that the DMS vs. SMS decision is operating system
    dependent, so that would be something to follow up on also.



    Mark Guest

  16. #16

    Default Re: Temporary Tablespace type to encrease performance

    Benchmarks usually get done in a few weeks, so the advantages of
    performance (which DMS sometimes delivers for temp tablespaces) over SMS
    can be pronounced. The advantages of SMS (ease of administration,
    ability to grow and shrink quickly) matter less in a benchmark, where
    the workload is well understood and is completed quickly, versus a
    database you will maintain for years. Finally, writing a benchmark kit
    (i.e. a TPC-H kit or TPC-C kit) is a lot of work, so sometimes
    performance benchmarks might stick with a choice they inherit if it
    works okay - i.e. if I/O performed well on the original TPC-H (or TPC-D)
    kit with DMS temps, they might leave that as is, and focus on other
    query tuning and techniques (like MQT's) when running such a benchmark
    on Linux.

    There were once large performance advantages for raw over SMS for
    relational databases - these advantages are shrinking, disappearing, or
    even becoming advantages for SMS as disk caching techniques at the OS
    and disk subsystem levels improve (and disk technologies like EMC and
    Shark aim more at the needs of high end databases like DB2).

    Mark A wrote:
     
    >
    >
    > I am not an expert in this area, although I have heard some discussion of
    > this topic, not all of which I remember in detail off-hand. I think it might
    > be beneficial to ask the DB2 Linux benchmark team about why they chose DMS
    > and whether they benchmarked different DMS vs SMS tablespace scenarios.
    >
    > Someone earlier suggested that the DMS vs. SMS decision is operating system
    > dependent, so that would be something to follow up on also.
    >
    >
    >[/ref]

    Blair Guest

  17. #17

    Default Re: Temporary Tablespace type to encrease performance

    "Blair Adamache" <> wrote in message
    news:bn1bd9$2s3$torolab.ibm.com... 

    I agree about the administrative advantages of SMS over DMS. I think that
    DMS is sometimes overused, especially for transaction systems with large
    bufferpools.

    However, tables created by DB2 in temporary tablespace are frequently
    accessed with tablespace scans, which can be quite large in ad-hoc query or
    data warehouse environments . I would suspect that there are advantages for
    DMS in this type of environment, especially if one places the containers in
    an optimal configuration.

    To a large degree, the overhead associated with DBA maintenance activities
    relating to DMS for temporary tablespaces can be minimized with a generous
    allocation of space, especially since it is a shared space and disk is
    cheap. Because of this, I suspect there is a better case to be made for use
    of DMS for temporary tablespaces than regular tablespaces (which may require
    more require frequent administration).


    Mark Guest

  18. #18

    Default Re: Temporary Tablespace type to encrease performance

    "Mark A" <net> writes:
     

    True, but you may have to do regular re-orgs to keep things contiguous if you
    want to keep the performance advantage.

    My policy is SMS is the standard for everything unless performance
    constraints require better performance, but our installation is
    not performance bound.

    Joseph
    Joseph,,, Guest

  19. #19

    Default Re: Temporary Tablespace type to encrease performance

    > "Mark A" <net> writes: [/ref]
    out 
    >
    > True, but you may have to do regular re-orgs to keep things contiguous if[/ref]
    you 

    Temporary DB2 tables are created and dropped by DB2 during the SQL unit of
    work. There is nothing to reorganize, or am I missing something?


    Mark Guest

Similar Threads

  1. How to move temporary tablespace in DB2
    By BernardDark in forum IBM DB2
    Replies: 0
    Last Post: August 2nd, 08:01 PM
  2. Index, Tablespace and performance
    By alexandre::aldeia digital in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 10:43 PM
  3. Offline Temporary Tablespace
    By Rick Austin in forum IBM DB2
    Replies: 6
    Last Post: July 11th, 01:55 PM
  4. Replies: 4
    Last Post: January 11th, 09:37 AM
  5. alter table move into temporary tablespace.
    By Charles J. Fisher in forum Oracle Server
    Replies: 3
    Last Post: December 16th, 04:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139