Question about prefetchsize, containers and extensize

[Michel Esber]

Hello,

DB2 V8 FP12 LUW.

My system is under heavy wait-io times.

I have read the following article: http://tinyurl.com/p844z and it says
that prefetchsize should be the product of extentsize and the number of
containers.

My tablespace does not follow this:

CREATE REGULAR TABLESPACE PSDATWIN IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE '/db/rtmdb/data2/psdatawin1'2800000)
EXTENTSIZE 32
PREFETCHSIZE 128

What is the exact performance impact I should be expecting from this
configuration (1 container only) ? In order to reduce my wait-io times,
is it better to add 3 more containers or reduce my prefetchsize to 32 ?

Thanks in advance,

Michel

[Mark A]

"Michel Esber" <mi****@us.automatos.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hello,

DB2 V8 FP12 LUW.

My system is under heavy wait-io times.

I have read the following article: http://tinyurl.com/p844z and it says
that prefetchsize should be the product of extentsize and the number of
containers.

My tablespace does not follow this:

CREATE REGULAR TABLESPACE PSDATWIN IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE '/db/rtmdb/data2/psdatawin1'2800000)
EXTENTSIZE 32
PREFETCHSIZE 128

What is the exact performance impact I should be expecting from this
configuration (1 container only) ? In order to reduce my wait-io times,
is it better to add 3 more containers or reduce my prefetchsize to 32 ?

Thanks in advance,

Michel

Prefetch is important when you are doing table scans on large tables, and
usually not when accessing small amounts of data each time via an index.
Based on your tablespace size, your application may be doing such table
scans, and that is where you might see some performance enhancements.

If you can put each container on a separate physical disk or disk array,
then definitely create multiple containers.

If you are using SAN or NAS, and you use multiple containers, be sure to
issue the following command:
db2set DB2_PARALLEL_IO=*

[tsmithnj]

Another track you should consider investigating is the throughput
capability of your I/O subsystem. Take a look at alll the volumes that
comprise your containers. Have your disk admin (or the vendor) tell
you what kind of sequential throughput you should expect based on the
configuration. Then, do a DD or comparable test to see what kind of
throughput you are actually seeing. If it's not the vendor's number,
it's the vendors problem. If DD matches the vendors advertised
performance, run a tablescan with the bufferpools set to 100 pages and
see what kind of throughput you generate. If it is less than the DD
number, *then* it's time to look at prefetch/extent/etc.

If it's an OLTP system then replace "sequentioal scan" with IOPS. (and
if so, prefetchsize won't make a lick of difference....)

t


Mark A wrote:

"Michel Esber" <mi****@us.automatos.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Hello,

DB2 V8 FP12 LUW.

My system is under heavy wait-io times.

I have read the following article: http://tinyurl.com/p844z and it says
that prefetchsize should be the product of extentsize and the number of
containers.

My tablespace does not follow this:

CREATE REGULAR TABLESPACE PSDATWIN IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE
USING (FILE '/db/rtmdb/data2/psdatawin1'2800000)
EXTENTSIZE 32
PREFETCHSIZE 128

What is the exact performance impact I should be expecting from this
configuration (1 container only) ? In order to reduce my wait-io times,
is it better to add 3 more containers or reduce my prefetchsize to 32 ?

Thanks in advance,

Michel

Prefetch is important when you are doing table scans on large tables, and
usually not when accessing small amounts of data each time via an index.
Based on your tablespace size, your application may be doing such table
scans, and that is where you might see some performance enhancements.

If you can put each container on a separate physical disk or disk array,
then definitely create multiple containers.

If you are using SAN or NAS, and you use multiple containers, be sure to
issue the following command:
db2set DB2_PARALLEL_IO=*

[Raj]

What is DD? IOPS??(index acess??)

[e-mail] wrote:

Another track you should consider investigating is the throughput
capability of your I/O subsystem. Take a look at alll the volumes that
comprise your containers. Have your disk admin (or the vendor) tell
you what kind of sequential throughput you should expect based on the
configuration. Then, do a DD or comparable test to see what kind of
throughput you are actually seeing. If it's not the vendor's number,
it's the vendors problem. If DD matches the vendors advertised
performance, run a tablescan with the bufferpools set to 100 pages and
see what kind of throughput you generate. If it is less than the DD
number, *then* it's time to look at prefetch/extent/etc.

If it's an OLTP system then replace "sequentioal scan" with IOPS. (and
if so, prefetchsize won't make a lick of difference....)

t

[tsmithnj]

DD is a file copy utility in unix. It is more granular than "cp" in
that you can specify input and output block sizes, so you can more
closely emulate db2 pages.

IOPS is I/Os per second.


Raj wrote:

What is DD? IOPS??(index acess??)

[e-mail] wrote:

Another track you should consider investigating is the throughput
capability of your I/O subsystem. Take a look at alll the volumes that
comprise your containers. Have your disk admin (or the vendor) tell
you what kind of sequential throughput you should expect based on the
configuration. Then, do a DD or comparable test to see what kind of
throughput you are actually seeing. If it's not the vendor's number,
it's the vendors problem. If DD matches the vendors advertised
performance, run a tablescan with the bufferpools set to 100 pages and
see what kind of throughput you generate. If it is less than the DD
number, *then* it's time to look at prefetch/extent/etc.

If it's an OLTP system then replace "sequentioal scan" with IOPS. (and
if so, prefetchsize won't make a lick of difference....)

t