Hi,

Is there a simple command I can run to clear out the bufferpools? I'm testing out the speed of certain queries and want to make sure that it's tested with nothing pre-calculated in memory.. thanks!!

Seems like ipclean, stop, start of database will do it for me.. found this article :

How to empty the buffer pool?

Just restart the db. Don't use ipclean unless you're unable to force the applications off the db

today, it seems like the clearing of the bufferpool is simply not working.. if I execute a query, restart db2 and execute the same query.. it seems to give the result really fast the 2nd time around when it should take just as long as it did before the restart.

For some reason, it cleared it out better yesterday and I haven't changed any of the steps in clearing it out either... so not sure what is causing this.. !!

Did you restart your db before each run and only executed the query once? Use db2pd -buf to check the bufferpool statistics. They should be very similar for every run assuming you execute your query once and then restart the db. See my example. In the last db2bp output, hit ratio is higher because I ran the same query twice without restarting the db.

And there is only one tablespace/table assigned to this bp, system catalog is assigned to the default bp.

Let's be more clear about restarting. You need to stop (db2stop) and start (db2start) the instance (database manager), not restart the database (there is a RESTART DATABASE command that is different from stop/stop of instance).

You might be able to flush the bufferpools if you force all connections from the database, and then deactivate the database, then activate the database again (without having to stop/stop the instance).

This is contrary to the way DB2 normally works, since at least some data for every query (even if only the root index pages) should remain in memory. Any decent DBA should be able to configure BP's to keep most of the indexes in memory most of the time, in addition to keeping frequently used data pages in memory for small and medium size tables.

So whatever bufferpool states you are trying to setup are not representative of real world conditions, and your results will likely cause misleading conclusions regarding performance. If you want a theoretical number to compare performance of two queries (or the same query with two different indexes, etc), do an Explain and look at timerons.

For TPC-H benchmarks (TPC - Homepage) of data warehouse queries, the test specifies that each query be run 10 times and results are averaged. They do not require a flushing of bufferpools before each run.

I was the one connected to the db so it was enough to do db2 terminate to flush the bufferpools. I repeated the same thing:

- restarted instance and connected to db
- in the db2pd -buf output, I see some numbers even before I run the query:

DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
72 68 05.56% 0 0 00.00% 0 0 00.00% 0 0 00.00%


- in the db2pd -pages summary output, I see:

BPID TbspaceID ObjID Total Dirty Permanent Temporary Data Index LongField XMLData SMP LOB LOBA BMP
8 12 65534 33 0 33 0 0 0 0 0 33 0 0 0
8 12 65535 1 0 0 0 1 0 0 0 0 0 0 0


object 65534 and 65535 are some internal stuff (they belong to SMP and object table, I believe).


- executed query:

BPID TbspaceID ObjID Total Dirty Permanent Temporary Data Index LongField XMLData SMP LOB LOBA BMP
8 12 4 863 1 862 0 863 0 0 0 0 0 0 0
8 12 65534 33 0 33 0 0 0 0 0 33 0 0 0
8 12 65535 2 0 1 0 2 0 0 0 0 0 0 0


object id 4 is my table


- db2 terminate and connect to db


BPID TbspaceID ObjID Total Dirty Permanent Temporary Data Index LongField XMLData SMP LOB LOBA BMP
8 12 65534 33 0 33 0 0 0 0 0 33 0 0 0
8 12 65535 1 0 0 0 1 0 0 0 0 0 0 0



Terminating my connection flushed the bp.

Thanks, I restarted the db before each run.. when I run the db2pd command, I get back the top lines in your file but I don't get this --



Bufferpool Statistics for bufferpool 8 (when BUFFERPOOL monitor switch is ON):

DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
1851 935 49.49% 0 0 00.00% 0 0 00.00% 0 0 00.00%

DataWrts IdxWrts DirRds DirRdReqs DirRdTime DirWrts DirWrtReqs DirWrtTime
0 0 0 0 0 0 0 0

AsDatRds AsDatRdReq AsIdxRds AsIdxRdReq AsRdTime AsDatWrts AsIdxWrts AsWrtTime
861 27 0 0 82 0 0 0

TotRdTime TotWrtTime VectIORds VectIOReq BlockIORds BlockIOReq FilesClose NoVictAvl UnRdPFetch
162 0 861 27 0 0 0 0 0




I thought I had turned the monitoring on.. but I guess I messed up somewhere.


Btw, I'm a fan of panipuri too!!!

Marcus and db2girl, since we're talking about bufferpools, performance, etc I have a general question for you two --

If you were on a web application and wanted to see the monthly sum of reading values where the total number of records to sum up = 124,320 records ( total in table are in the billions), what's a reasonable amount of time you would expect it to come back with a result?

There is no accurate way to answer that since there are too many variables involved regarding your hardware setup (memory, CPU, etc) not to mention the other applications running.

But the response time primarily depends on whether the 124,320 rows can be retrieved efficiently via an index, or whether a table scan of several billion rows is necessary. But I would make sure you had the prefetch settings correct (and they are in synch with containers and extents), and also that you had parallel I/O set correctly in your db2set variables. If an index can be used, it should probably be in a separate tablespace and a separate bufferpool from the data tables.

I would do an explain on the statement to understand what the access plan is (index vs table scan, etc). But tuning one query in isolation of all other SQL statements in the application is treacherous.

Thanks Marcus! You're right.. there are many factors that would determine the processing time for such a query. In my experience, end users ( web app customers) don't really have much patience when it comes to data retrieval so we really want the report to come up in seconds.

Can you explain why you recommend the index should be in a separate tablespace / bufferpool from the table?

Thanks a lot for the tips!

Becasue for most databases, you want to have a higher bufferpool hit ratio for indexes compared to tables (this assumes that you cannot make bufferpools large enough to have all data and indexes in memory all the time).

So as an example, you may want something like the following (this may not be accurate for your particular database, but you should be able to understand the principles involved):

1. Bufferpool 1 (bufferpool is about 80-100% of the size of assigned tablespaces):

• Syscatspace (system catalog)
• Small tables
• Indexes on Small tables
• Indexes on Medium Tables

2. Bufferpool 2 (bufferpool is about 25-50% of the size of assigned tablespaces).

• Medium Size Tables
• Indexes on Large Tables

3. Bufferpool 3 (bufferpool is 15% or less of the size of assigned tablespaces)

• Large Tables
• System Temp Tablespaces

In the above scenario, the 3 bufferpools may be about equal in size (or maybe not), but because they have different desired bufferpool hit ratios, they have different amounts of data (from tablespaces) assigned to them.