Temporary Tablespace type to encrease performance

[zebi]

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

[Mark A]

"zebi" <ze**@titi.org> wrote in message news:bm**********@news.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

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.

[zebi]

Thanks

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

"Mark A" <[e-mail]> a écrit dans le message de news:
9S*****************@news.uswest.net...

"zebi" <ze**@titi.org> wrote in message news:bm**********@news.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

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 A]

"zebi" <ze**@titi.org> wrote in message news:bm**********@news.tiscali.fr...

Thanks

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

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.

[Blair Adamache]

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/li..._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/...60proc_FDR.pdf

and

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

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

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 [IBM]]

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" <ze**@titi.org> wrote in message news:bm**********@news.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

[Mark A]

> "Bob [IBM]" <No**********@ibm.com> wrote in

message news:bm**********@news.btv.ibm.com...
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

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 document (the
IBM page number, not the PDF page number):

http://www.tpc.org/results/FDR/tpch/...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

[Joseph,,,]

"Mark A" <[e-mail]> writes:

"zebi" <ze**@titi.org> wrote in message news:bm**********@news.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

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.

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

[Bob [IBM]]

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" <[e-mail]> wrote in message news:_i*****************@news.uswest.net...

"Bob [IBM]" <No**********@ibm.com> wrote in
message news:bm**********@news.btv.ibm.com...
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

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 document (the
IBM page number, not the PDF page number):

http://www.tpc.org/results/FDR/tpch/...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 A]

> "Bob [IBM]" <No**********@ibm.com

wrote in message news:bm**********@news.btv.ibm.com...
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!

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 [IBM]]

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" <[e-mail]> wrote in message news:tM****************@news.uswest.net...

"Bob [IBM]" <No**********@ibm.com
wrote in message news:bm**********@news.btv.ibm.com...
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.

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 A]

> "Bob [IBM]" <No**********@ibm.com> wrote in message

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

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.

[Fan Ruo Xin]

Mark A wrote:

"Bob [IBM]" <No**********@ibm.com> wrote in message
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

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.

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

[Bob [IBM]]

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" <[e-mail]> wrote in message news:C3****************@news.uswest.net...

"Bob [IBM]" <No**********@ibm.com> wrote in message
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

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 A]

"Bob [IBM]" <No**********@ibm.com> wrote in message

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.

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.

[Blair Adamache]

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:

"Bob [IBM]" <No**********@ibm.com> wrote in message

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.

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 A]

"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:bn**********@hanover.torolab.ibm.com...

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).

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).

[Joseph,,,]

"Mark A" <[e-mail]> writes:

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.

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

[Mark A]

> "Mark A" <[e-mail]> writes:

DBA's do get fewer complaints with SMS because it is less likely to run outof 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.
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

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?