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

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.

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

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

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.

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

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.

Hi,

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

Grofaty

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.

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

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

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

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.

Wjy are synchronous writes bad? And what exactly does it mean?

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