Meaning of direct_writes in tablespace snapshot

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

re: Meaning of direct_writes in tablespace snapshot

"Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=blue]
> 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[/color]
mode)[color=blue]
> * 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
>[/color]
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?

re: Meaning of direct_writes in tablespace snapshot

"Mark A" <nobody@nowhere.com> a écrit dans le message de
news:QkFGc.646$NK4.31933@news.uswest.net...[color=blue]
> "Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
> news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=green]
> > Hi there,
> >
> > I would like to know the meaning of direct_writes in a tablespace[/color][/color]
snapshot[color=blue][color=green]
> > on a tablespace that is:
> >
> > * SMS (db2empfa ON)
> > * Only 1 table is accessed in INSERT - Z locked (Not logged Initially[/color]
> mode)[color=green]
> > * 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
> >[/color]
> 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[/color]
buffer[color=blue]
> pool?
>[/color]

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

re: Meaning of direct_writes in tablespace snapshot

> > Is this a massive insert job? For starters, if you have multiple CPU's,[color=blue][color=green]
> > increase number of pages cleaners and I/O servers. How large is your[/color]
> buffer[color=green]
> > pool?
> >[/color]
>
> 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[/color]
?[color=blue]
>
> Regards,
>
> JM
>[/color]
Take a look at this article about insert performance:
http://www-106.ibm.com/developerwork...m-0403wilkins/

re: Meaning of direct_writes in tablespace snapshot

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

"Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=blue]
> 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[/color]
mode)[color=blue]
> * 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
>
>[/color]

re: Meaning of direct_writes in tablespace snapshot

"Mark A" <nobody@nowhere.com> a écrit dans le message de
news:CDGGc.651$NK4.34655@news.uswest.net...[color=blue][color=green][color=darkred]
> > > Is this a massive insert job? For starters, if you have multiple[/color][/color][/color]
CPU's,[color=blue][color=green][color=darkred]
> > > increase number of pages cleaners and I/O servers. How large is your[/color]
> > buffer[color=darkred]
> > > pool?
> > >[/color]
> >
> > Yes, this is a massive insert job, about 100 million rows. We have about[/color][/color]
8[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > chgnpgs_threshold is set to 20%.
> >
> > Do you mean that shortage of resources could lead DB2 to use direct[/color][/color]
writes[color=blue]
> ?[color=green]
> >
> > Regards,
> >
> > JM
> >[/color]
> Take a look at this article about insert performance:
> http://www-106.ibm.com/developerwork...m-0403wilkins/
>[/color]

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

re: Meaning of direct_writes in tablespace snapshot

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" <fanruox@sbcglobal.net> a écrit dans le message de
news:dLJGc.850$WB4.306@newssvr32.news.prodigy.com. ..[color=blue]
> Insert into a NLI table supposed to bypass the logging phase, of course it
> would be the direct write.
>
> "Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
> news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=green]
> > Hi there,
> >
> > I would like to know the meaning of direct_writes in a tablespace[/color][/color]
snapshot[color=blue][color=green]
> > on a tablespace that is:
> >
> > * SMS (db2empfa ON)
> > * Only 1 table is accessed in INSERT - Z locked (Not logged Initially[/color]
> mode)[color=green]
> > * 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
> >
> >[/color]
>
>[/color]

re: Meaning of direct_writes in tablespace snapshot

"Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=blue]
> 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[/color]
mode)[color=blue]
> * 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
>[/color]
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.

re: Meaning of direct_writes in tablespace snapshot

"Mark A" <nobody@nowhere.com> a écrit dans le message de
news:l0UGc.12$%p4.23644@news.uswest.net...[color=blue]
> "Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
> news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=green]
> > Hi there,
> >
> > I would like to know the meaning of direct_writes in a tablespace[/color][/color]
snapshot[color=blue][color=green]
> > on a tablespace that is:
> >
> > * SMS (db2empfa ON)
> > * Only 1 table is accessed in INSERT - Z locked (Not logged Initially[/color]
> mode)[color=green]
> > * 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
> >[/color]
> 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[/color]
to[color=blue]
> 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[/color]
disk.[color=blue]
>
> 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[/color]
file[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
is[color=blue]
> 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[/color]
information.[color=blue]
>[/color]

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

re: Meaning of direct_writes in tablespace snapshot

Jean-Marc Blaise <nobody@nowhere.com> wrote:[color=blue]
> Hi there,[/color]
[color=blue]
> I would like to know the meaning of direct_writes in a tablespace snapshot
> on a tablespace that is:[/color]
[color=blue]
> * 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[/color]
[color=blue]
> Can someone help understand why we see a high number of direct_writes ?[/color]

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

jsoh

re: Meaning of direct_writes in tablespace snapshot

"Josh Tiefenbach" <jtiefenb@nowhere.in.ibm.com> a écrit dans le message de
news:cchdgt$30g$1@hanover.torolab.ibm.com...[color=blue]
> Jean-Marc Blaise <nobody@nowhere.com> wrote:[color=green]
> > Hi there,[/color]
>[color=green]
> > I would like to know the meaning of direct_writes in a tablespace[/color][/color]
snapshot[color=blue][color=green]
> > on a tablespace that is:[/color]
>[color=green]
> > * SMS (db2empfa ON)
> > * Only 1 table is accessed in INSERT - Z locked (Not logged Initially[/color][/color]
mode)[color=blue][color=green]
> > * No BLOB, long varchar ...in the table
> > * No utility running[/color]
>[color=green]
> > Can someone help understand why we see a high number of direct_writes ?[/color]
>
> MPFA uses direct writes to append new pages to the object.
>
> jsoh[/color]

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

re: Meaning of direct_writes in tablespace snapshot

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" <nobody@nowhere.com> wrote in message
news:ccg733$ld6$1@news-reader5.wanadoo.fr...[color=blue]
> 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" <fanruox@sbcglobal.net> a écrit dans le message de
> news:dLJGc.850$WB4.306@newssvr32.news.prodigy.com. ..[color=green]
> > Insert into a NLI table supposed to bypass the logging phase, of course[/color][/color]
it[color=blue][color=green]
> > would be the direct write.
> >
> > "Jean-Marc Blaise" <nobody@nowhere.com> wrote in message
> > news:ccf79d$ol4$1@news-reader2.wanadoo.fr...[color=darkred]
> > > Hi there,
> > >
> > > I would like to know the meaning of direct_writes in a tablespace[/color][/color]
> snapshot[color=green][color=darkred]
> > > on a tablespace that is:
> > >
> > > * SMS (db2empfa ON)
> > > * Only 1 table is accessed in INSERT - Z locked (Not logged Initially[/color]
> > mode)[color=darkred]
> > > * No BLOB, long varchar ...in the table
> > > * No utility running
> > >
> > > Can someone help understand why we see a high number of direct_writes[/color][/color][/color]
?[color=blue][color=green][color=darkred]
> > >
> > > Version: DB2 UDB V7 EEE FP11 / Aix 5.1 ML4
> > >
> > > Thanks for your help,
> > >
> > > Jean-Marc
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Meaning of direct_writes in tablespace snapshot

Jean-Marc Blaise <nobody@nowhere.com> wrote:[color=blue]
> 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 ?[/color]

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

jsoh

re: Meaning of direct_writes in tablespace snapshot

Thanks for this clear answer.

Regards,

JM

"Josh Tiefenbach" <jtiefenb@nowhere.in.ibm.com> a écrit dans le message de
news:cck2ca$dpn$1@hanover.torolab.ibm.com...[color=blue]
> Jean-Marc Blaise <nobody@nowhere.com> wrote:[color=green]
> > 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 ?[/color]
>
> No. Its capped at 128k - it may be less if extent size * page size is less
> than 128k, but no higher.
>
> jsoh
>[/color]