Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Select statements and buffer pool question

    Hi,

    I have table TAB with following attributes:


    Table: TAB

    COL
    ----
    1
    2
    3
    4
    5


    1. I restarted DB2 to empty all buffers, caches, etc
    2. I executed SQL: Select COL from TAB where COL > 2
    3. Then I executed SQL: Select COL from TAB where COL = 3


    Question 1: Does secound SQL gets the data from buffer pool or DB2 reads the data from disk?
    Question 2: How can I check if some SQL gets the data from disk or from memory?
    Question 3: Can somebody point me to the documentation source (book, web link, etc)?

    Thanks,
    Grofaty

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. Assuming the needed page(s) (and index pages if applicable) is already in the buffer pool from a previous query, and is not kicked out because another page needs to be brought in, then the second read will be from the buffer pool. If the needed page(s) did get kicked out, DB2 would have to read them back into the buffer pool first (and maybe kick some other pages out). All data access is from the buffer pool (the buffer pool is never bypassed). Obviously, if there is plenty of buffer pool space, no pages get kicked out.

    2. You can check the buffer pool hit ratio. Some of the monitor tools like the ones listed in the Useful DB2 Stuff can show these statistics. I used the db2mon.exe program and it works fairly well. You will need to "UPDATE MONITOR SWITCHES USING bufferpool ON" (see the Command Reference). You can also get snapshot information on this. Lastly, you can look at the disk drive light on you server if you happen to be near it (I admit to doing this occasionally).

    3. Check the "Administration Guide: Performance", Chapter 8 Operational Performance, Buffer Pools. This has a good discussion of how bufferpools work, and has diagrams also.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you very much Marcus_A. You helped me a lot.

    I have some more questions.

    Questnion 1: Is there any detailed document source to get info about bufferpool attributes retrived with GET SNAPSHOT FOR ALL BUFFERPOOLS:

    Code:
    Buffer pool data logical reads
    Buffer pool data physical reads
    Buffer pool data writes
    Buffer pool index logical reads
    Buffer pool index physical reads
    Total buffer pool read time (ms)
    Total buffer pool write time (ms)
    Asynchronous pool data page reads
    Asynchronous pool data page writes
    Buffer pool index writes
    Asynchronous pool index page reads
    Asynchronous pool index page writes
    Total elapsed asynchronous read time
    Total elapsed asynchronous write time
    Asynchronous read requests
    Direct reads
    Direct writes
    Direct read requests
    Direct write requests
    Direct reads elapsed time (ms)
    Direct write elapsed time (ms)
    Database files closed
    Data pages copied to extended storage
    Index pages copied to extended storage
    Data pages copied from extended storage
    Index pages copied from extended storage
    Question 2: Are there any recomendations for this buffer pool attribues? Witch parameter is most important to watch?

    Question 3: What should the "buffer pool hit ratio" parameter be? (using the db2mon.exe tool)

    Question 4: When should I increase the "Buffer pool size" (BUFFPAGE) parameter and for how many bytes? So what is indicator to take some action?

    Comments about bufferpools
    I have also found one excelent simple to use freeware Windows tool about accessing processes to disk. Tool name is Filemon. Defining Filter to "c:\db2" I can see process name to access my db2 database. So I now I can see if data are accessed directly from bufferpool or from disk.

    Grofaty

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Q1)
    Bufferpool Snapshot allows you to calcuate three things
    1) Hit Ratio
    2) Effectiveness of the prefetchers (Asynchronous Reads)
    3) How bufferpool data is written to disk (Writes)

    As a general idea,
    1) Lesser the physical reads, better the performance (so high hit ratio)
    2) More the prefetching, the better is the chance to find the page the application wants (so high asynchronous to total reads ratio)
    3) It is better for io cleaners to write data from bp to disk than at the time the application requires a page (so higher percentage of asynchronous writes)

    If you'd like further details, I can give later today

    Q2)
    I'll think all are important ... But, hit ratio is the most important ...
    With V8, block based buffer pools, there is an additional element to consider and make use of ...

    Q3)
    Hitratio is considered to be good if the value is more than 90% ... I'll say, for OLTP, it will be good to be higher than 97% ... DSS systems normally will have lower hit ratios. For DSS , you may want to consdier asynchronous read percentage

    Q4)
    BUFFPAGE is a db cfg parameter which defines the default number of pages if you don't explictly specify in your CREATE BUFFERPOOL Statement ... I always prefer to have the size defined in the CREATE Statement ... You will have to increase the dbheap each time you increase the bp size as dbheap holds information on the bp pages ..

    HTH

    Sathyaram





    Originally posted by grofaty
    Hi,

    Thank you very much Marcus_A. You helped me a lot.

    I have some more questions.

    Questnion 1: Is there any detailed document source to get info about bufferpool attributes retrived with GET SNAPSHOT FOR ALL BUFFERPOOLS:


    Question 2: Are there any recomendations for this buffer pool attribues? Witch parameter is most important to watch?

    Question 3: What should the "buffer pool hit ratio" parameter be? (using the db2mon.exe tool)

    Question 4: When should I increase the "Buffer pool size" (BUFFPAGE) parameter and for how many bytes? So what is indicator to take some action?

    Comments about bufferpools
    I have also found one excelent simple to use freeware Windows tool about accessing processes to disk. Tool name is Filemon. Defining Filter to "c:\db2" I can see process name to access my db2 database. So I now I can see if data are accessed directly from bufferpool or from disk.

    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I believe that in version 8, the DB2 manual recommends that BUFFPAGE no longer be used and that buffer pools be explicitly sized with SQL CREATE and ALTER statements.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you very much sathyaram_s. Can you please clarify to me the questions bellow...

    Question 1: Did you mean: "Asynchronous pool data page reads"?
    2) Effectiveness of the prefetchers (Asynchronous Reads)
    Question 2: Did you mean: "Buffer pool data writes"?
    3) How bufferpool data is written to disk (Writes)
    Question 3: Did you mean: high asynchronous = high "Asynchronous pool data page reads"? Did you mean the formula: "Asynchronous pool data page reads" / "Buffer pool data logical reads"?
    2) More the prefetching, the better is the chance to find the page the application wants (so high asynchronous to total reads ratio)
    Question 4: What did you mean with "percentage of asynchronous writes"?
    Did you mean "Asynchronous pool data page writes" / ("Asynchronous pool data page writes" + "Asynchronous pool data page reads")?
    3) It is better for io cleaners to write data from bp to disk than at the time the application requires a page (so higher percentage of asynchronous writes)
    Thanks a lot,
    Grofaty
    Last edited by grofaty; 01-21-04 at 06:02.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    nitingm,

    Lots of people are interested in this and other threads. If everyone who was interested in a thread posted a bogus message just to keep track of it (not sure I understand), this forum would become much less useful for everyone else. Your cooperation is appreciated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I have found web page about Optimizing Buffer Pools in DB2 UDB for UNIX, Windows, and OS/2 at The IDUG Solutions.

    Grofaty
    Last edited by grofaty; 01-21-04 at 06:07.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think that there is a lot of good information in that article, but I also think that most of the time it would be a big mistake to define 7 different buffer pools in a database. Memory is a constrained resource and the more buffer pools that are created, the smaller each one will be.

    I have found that the biggest mistake people make is to have either too few (1) or too many buffer pools. The worst mistake is to have too many, which is why some people just recommend using a single buffer pool, because some people get carried away when they start having more than 1.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you Marcus_A.

    I will still have one buffer pool... I am only interested of size of bufferpool and when it should be enlarged. So on above web page are some formulas how to determine the bufferpool hit ratio and when the bufferpool needs to be enlarged.

    Grofaty

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    1) Both asynchronous pool data and index reads

    2) Buffer pool data and index writes

    3) Asynchronous data and index page reads/Buffer pool physical data and index page reads

    4) Asynchronous data and index page writes/Buffer pool data and index page writes

    Sathyaram

    Originally posted by grofaty
    Hi,

    Thank you very much sathyaram_s. Can you please clarify to me the questions bellow...

    Question 1: Did you mean: "Asynchronous pool data page reads"?


    Question 2: Did you mean: "Buffer pool data writes"?


    Question 3: Did you mean: high asynchronous = high "Asynchronous pool data page reads"? Did you mean the formula: "Asynchronous pool data page reads" / "Buffer pool data logical reads"?


    Question 4: What did you mean with "percentage of asynchronous writes"?
    Did you mean "Asynchronous pool data page writes" / ("Asynchronous pool data page writes" + "Asynchronous pool data page reads")?


    Thanks a lot,
    Grofaty
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thank you very much sathyaram_s for your answers. One more question...

    Are there any recomended values to say what is good and what is bad for following three formulas:

    1. Bufferpool hit ratio = (1 - ("Buffer pool data physical reads" + "Buffer pool index physical reads") / ("Buffer pool data logical reads" + "Buffer pool index logical reads"))*100

    2. Effectiveness of prefetchers = ("Asynchronous data page reads" + "Asynchronous index page reads") / ("Buffer pool physical data page reads" + "Buffer pool physical index page reads")*100

    3. I/O cleaners effectiveness = ("Asynchronous data page writes" + "Asynchronous index page writes")/("Buffer pool data page writes" + "Buffer pool index page writes")*100


    You have already written someting about "Bufferpool hit ratio" witch should be more than 90% for OLTP systems. What about "Effectiveness of prefetchers" and "I/O cleaners effectiveness"? Can you point in general what percentage is good and what is bad?

    Thanks,
    Grofaty

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The following is from a db2 Performance Tool manual. Write Percentage has been indicated as 95% or more .. For read percentage, I think it is more on what sort of environment you are, ie , for OLTP, your Asynchronous read percentage may be ver small .. It is more about appr.

    awp, swp

    The asynchronous and synchronous write percentages, respectively. A high percentage for asynchronous is advantageous, while a high percentage for synchronous is not. NUM_IOCLEANERS and CHGDPGS_THRES should be tuned appropriately such that asynchronous write percentage is 95% or better. To do this, gradually increment NUM_IOCLEANERS until NUM_IOCLEANERS equals one less than the number of physical disks containing containers for DB2 tablespaces. Once this point for NUM_IOCLEANERS is reached, gradually begin lowering the changed pages threshold until the asynchronous write percentage goal is achieved.

    arp, srp

    The percentage of asynchronous (prefetch) reads and the percentage of synchronous reads, respectively. Use these metrics to understand how the data is being read. buffer pools that are read synchronously (randomly) should benefit from higher hit ratios, while tablespaces that are read asynchronously (prefetch) should be backed by buffer pools of sufficient size to support prefetched pages.



    appr

    The number of pages read per asynchronous read. It is a measure of the effectiveness of the asynchronous read engines (NUM_IOSERVERS). If the number of pages read per asynchronous read request is much smaller than the prefetch size for the underlying tablespace(s), the tablespace may be prefetch-impaired due to buffer pool contention.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Mar 2003
    Posts
    343
    Wjy are synchronous writes bad? And what exactly does it mean?

  15. #15
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    A bufferpool will have data which has been read or modified ... These modified pages reside on the bufferpool and is served from the bufferpool itself ... It wouldn't have been written to the tablespace ....

    Assume that all bufferpool pages are full, and say, there is a select query which needs some 1000 bufferpool pages ... At this point, using various algorithms db2 identifies the pages to be flushed out to disk to make room for the new pages ... When db2 does this operation, the application has to WAIT for free pages ... This is synchronous write ...

    As you can see, synchronous write has an impact on application performance ... To avoid synchronous writes, using the value of CHGDPGS_THRES (percentage of pages in the bufferpool that have changed pages) db2 uses IO Cleaners to flush out these pages to disk asynchronously ...

    IO cleaning also has an impact on the system, but it is lesser than the synchronous writes ... Hence, change page threshold has to be optimized ... There can be multiple IO Cleaners as defined by the NUM_IOCLEANERS config parm and the value can be tuned as defined in my previous post ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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