Meaning of direct_writes in tablespace snapshot

[Jean-Marc Blaise]

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot
on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode)
* No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

[Mark A]

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot
on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode) * No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

Is this a massive insert job? For starters, if you have multiple CPU's,
increase number of pages cleaners and I/O servers. How large is your buffer
pool?

[Jean-Marc Blaise]

"Mark A" <no****@nowhere.com> a écrit dans le message de
news:Qk*****************@news.uswest.net...

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode)

* No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

Is this a massive insert job? For starters, if you have multiple CPU's,
increase number of pages cleaners and I/O servers. How large is your

buffer pool?

Yes, this is a massive insert job, about 100 million rows. We have about 8
page cleaners and I/O servers per partition (SMS has 4 containers per
partition), and a ps -ef |grep -e db2clnr -e db2pfchr shows that some
processes have not consumed CPU. Buffer pool is about 20000 16K pages and
chgnpgs_threshold is set to 20%.

Do you mean that shortage of resources could lead DB2 to use direct writes ?

Regards,

JM

[Mark A]

> > Is this a massive insert job? For starters, if you have multiple CPU's,

increase number of pages cleaners and I/O servers. How large is your buffer

pool?

Yes, this is a massive insert job, about 100 million rows. We have about 8
page cleaners and I/O servers per partition (SMS has 4 containers per
partition), and a ps -ef |grep -e db2clnr -e db2pfchr shows that some
processes have not consumed CPU. Buffer pool is about 20000 16K pages and
chgnpgs_threshold is set to 20%.

Do you mean that shortage of resources could lead DB2 to use direct writes

?
Regards,

JM

Take a look at this article about insert performance:
http://www-106.ibm.com/developerwork...m-0403wilkins/

[Fan Ruo Xin]

Insert into a NLI table supposed to bypass the logging phase, of course it
would be the direct write.

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot
on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode) * No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

[Jean-Marc Blaise]

"Mark A" <no****@nowhere.com> a écrit dans le message de
news:CD*****************@news.uswest.net...

Is this a massive insert job? For starters, if you have multiple CPU's, increase number of pages cleaners and I/O servers. How large is your buffer

pool?

Yes, this is a massive insert job, about 100 million rows. We have about 8 page cleaners and I/O servers per partition (SMS has 4 containers per
partition), and a ps -ef |grep -e db2clnr -e db2pfchr shows that some
processes have not consumed CPU. Buffer pool is about 20000 16K pages and chgnpgs_threshold is set to 20%.

Do you mean that shortage of resources could lead DB2 to use direct

writes ?

Regards,

JM

Take a look at this article about insert performance:
http://www-106.ibm.com/developerwork...m-0403wilkins/

I have already read this very interesting article, and we pretty fit its
recommandations ...

To come back to my original question, why do I see "direct writes" and what
is the meaning ? I was thinking of SMS extent allocation, but it seems
changing the extent size (a SMS tablespace with extentsize 16 to a SMS
tablespace with extensize 32) does not multiply by 2 the "direct writes" /
"direct writes requests" ratio.

The DB2 documentation is incomplete on this matter.

Thanks,

JM

[Jean-Marc Blaise]

Hi Fan,

I don't agree with that (maybe I'm wrong). NLI table bypasses the logging
phase, but still uses bufferpools. So in that respect, all mechanisms with
bufferpools should apply, especially asynchronous page cleaning.

Anyway, if it were the case (maybe I'm still wrong), the "direct writes"
data element is not specifiying this NLI in its list. I'm still interested
to know the truth :-)

Best regards,

Jean-Marc

"Fan Ruo Xin" <fa*****@sbcglobal.net> a écrit dans le message de
news:dL***************@newssvr32.news.prodigy.com. ..

Insert into a NLI table supposed to bypass the logging phase, of course it
would be the direct write.

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially

mode)

* No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

[Mark A]

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot
on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode) * No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

I am not sure what "direct_writes" means, but I found this information,
which may, or may not, be relevant:

"Direct I/O (DIO) is an alternative caching policy that reduces CPU
utilization for reads and writes by eliminating the copy from file cache to
user buffer. A read/write against a file opened with the O_DIRECT flag
causes data to be transferred directly between the user buffer and the disk.

When using the file system caching policy, which is the default policy for
DB2 UDB, all I/O operations are performed in buffered mode. While this
caching policy is extremely effective when the cache hit ratio is high, it
has an overhead of making an extra copy of the buffer from the disk to file
cache (in the case of read) or from file cache to disk (in the case of
write). Since the buffer is already cached in the DB2 buffer pool layer,
this dual level of caching proves to be unnecessary in situations where the
file system cache hit ratio is low and many I/O operations are performed."

So it appears that DIO bypasses the operating system file cache, since it is
usually redundant to the use of bufferpools to cache data. Again, I am not
sure if this is the same thing as direct_writes in the snapshot information.

[Jean-Marc Blaise]

"Mark A" <no****@nowhere.com> a écrit dans le message de
news:l0****************@news.uswest.net...

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode)

* No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

I am not sure what "direct_writes" means, but I found this information,
which may, or may not, be relevant:

"Direct I/O (DIO) is an alternative caching policy that reduces CPU
utilization for reads and writes by eliminating the copy from file cache

to user buffer. A read/write against a file opened with the O_DIRECT flag
causes data to be transferred directly between the user buffer and the disk.
When using the file system caching policy, which is the default policy for
DB2 UDB, all I/O operations are performed in buffered mode. While this
caching policy is extremely effective when the cache hit ratio is high, it
has an overhead of making an extra copy of the buffer from the disk to file cache (in the case of read) or from file cache to disk (in the case of
write). Since the buffer is already cached in the DB2 buffer pool layer,
this dual level of caching proves to be unnecessary in situations where the file system cache hit ratio is low and many I/O operations are performed."

So it appears that DIO bypasses the operating system file cache, since it is usually redundant to the use of bufferpools to cache data. Again, I am not
sure if this is the same thing as direct_writes in the snapshot information.

Hi Mark, the problem is DIO is supported from V8 on AIX, and I am on V7 :-((

When DB2 is using temp tables, the disks are fully utilized (Tempspace 16K
SMS, 4 containers) and no direct writes. When my SMS tablespace is used,
direct writes are used, and ratio keeps being 256*512, that is 128K.

Something related to db2empfa ? How can I raise this ratio to obtain 256K or
512K (the disks are about 25% usage ...) ?

Regards,

JM

[Josh Tiefenbach]

Jean-Marc Blaise <no****@nowhere.com> wrote:

Hi there, I would like to know the meaning of direct_writes in a tablespace snapshot
on a tablespace that is: * SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode)
* No BLOB, long varchar ...in the table
* No utility running Can someone help understand why we see a high number of direct_writes ?

MPFA uses direct writes to append new pages to the object.

jsoh

[Jean-Marc Blaise]

"Josh Tiefenbach" <jt******@nowhere.in.ibm.com> a écrit dans le message de
news:cc**********@hanover.torolab.ibm.com...

Jean-Marc Blaise <no****@nowhere.com> wrote:

Hi there,

I would like to know the meaning of direct_writes in a tablespace snapshot on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially mode) * No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?

MPFA uses direct writes to append new pages to the object.

jsoh

Thanks Josh for this confirmation.

Is there any system or DB2 parameter that can be set to do direct writes
other than 128K (I mean 256K, 512K) or is this a fix rate ?

Best regards,

Jean-Marc

[Fan Ruo Xin]

You are right. I was thinking of flush dirty pages to disk synchrously.
Anyway the most possible reason is you disable the MMAP_READ/WRITE. And when
you insert into an NLI table, db2 will try to write the dirty pages
synchronously. In this case you will see the DIRECT WRITES keep increasing
if you captured the snapshot a couple of times in sequential.

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader5.wanadoo.fr...

Hi Fan,

I don't agree with that (maybe I'm wrong). NLI table bypasses the logging
phase, but still uses bufferpools. So in that respect, all mechanisms with
bufferpools should apply, especially asynchronous page cleaning.

Anyway, if it were the case (maybe I'm still wrong), the "direct writes"
data element is not specifiying this NLI in its list. I'm still interested
to know the truth :-)

Best regards,

Jean-Marc

"Fan Ruo Xin" <fa*****@sbcglobal.net> a écrit dans le message de
news:dL***************@newssvr32.news.prodigy.com. ..

Insert into a NLI table supposed to bypass the logging phase, of course it

would be the direct write.

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cc**********@news-reader2.wanadoo.fr...

Hi there,

I would like to know the meaning of direct_writes in a tablespace

snapshot on a tablespace that is:

* SMS (db2empfa ON)
* Only 1 table is accessed in INSERT - Z locked (Not logged Initially

mode)

* No BLOB, long varchar ...in the table
* No utility running

Can someone help understand why we see a high number of direct_writes ?
Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4

Thanks for your help,

Jean-Marc

[Josh Tiefenbach]

Jean-Marc Blaise <no****@nowhere.com> wrote:

Is there any system or DB2 parameter that can be set to do direct writes
other than 128K (I mean 256K, 512K) or is this a fix rate ?

No. Its capped at 128k - it may be less if extent size * page size is less
than 128k, but no higher.

jsoh

[Jean-Marc Blaise]

Thanks for this clear answer.

Regards,

JM

"Josh Tiefenbach" <jt******@nowhere.in.ibm.com> a écrit dans le message de
news:cc**********@hanover.torolab.ibm.com...

Jean-Marc Blaise <no****@nowhere.com> wrote:

Is there any system or DB2 parameter that can be set to do direct writes
other than 128K (I mean 256K, 512K) or is this a fix rate ?

No. Its capped at 128k - it may be less if extent size * page size is less
than 128k, but no higher.

jsoh