Hello again forummembers.
Clearly I'm new to db2 when I ask this but I have to anyway. I'm testing parameters in an isolated test environment

We are running x64 db2 9.5 fp7 on 2003 server x64.

So I have a database that's ~4300 Mb in size. All db is put into one buffer pool of 4800mb (1 200 000 x 4 Kb right?).

Still when I perform a select the first select is always a lot slower than the following ones. Shouldn't they be about equally fast since now the whole database fits in memory? It differ about 25%.

I can see in task manager that when i do the first select the I/O Reads increases, but not the following ones. The same applies if I double the buffer pool to 9600 Mb. Also reading from memory is only about 25% faster (varies a lot) than reading from SAN. (SAN is about 600 Mb/sec read in Hdtach) Does that mean we have a really fast SAN or have I done something wrong?

I'm doing the select on a table with about 1.1m rows. I'm evaluating gains from more memory and better hardware.

We are running four quad Opterons 8354. Would dual Intel x5680 perform better? 16 cores 8354 (or 6176) against 12 cores x5680?

Regards

of what?
- any select statement after restarting DB2?
- any select statement after ...?
- a specific select statement after ...?
- first select statement for a table after restarting DB2?
- ...
- ...
...

Sorry for being unclear.

I restart DB2, and wait for it to load by watching task manager until there is no more read activity, connects to the database, wait for any read activity to stop (if any) and run a simple select like this
"select test1, test2, test3 from schema1.table1" and gets about 1.1m rows result.

When I run it the first time it seems it wasn't reading from memory after all since I notice IO activity during the first run of this query. On the following runs there is no read IO activity at all.

I was under the impression that if you create a buffer pool that's larger than the database itself, it would automatically load the whole database into memory? With that in mind, I'm surprised it doesn't seem so. Or I have misunderstood it?

I've got this information from this thread on another site.
DB2 - forcing a table to cache all data in the buffer pool. - Database Forum

regards

No.
First "select all" statement after restarting DB2 would cache everything in the table.

Hello Tonkuma

Thanks for clearing this up. But I have to ask? Is there no other way to preload the buffers?
I'm thinking of the scenario when you run a onetime batch job that goes on for several hours. Perhaps I can do this on a small database. But we also have a +120 gb database. So to preload for instance a 32 gb bufferpool I have to perform a couple of "select *" before the batchjob?

Ps, is 25% shorter runtime for a query reasonable when reading from ram than from disk?

Regards

You also need to understand that the first connection to the database takes longer if no other apps are connected, since all the bufferpool memory must be allocated at that time. Not sure if this is a factor in your case. You can get around that by doing this to keep database activated and all memory allocated even if there are no connections:

db2 activate db <sample>

However, as already stated above, data itself is not cached in bufferpools until it is read the first time.

It is not going to be faster to preload the bufferpools and run the batch job compared to just running the batch job.

However, if your database is much larger than your bufferpools, you should consider defining your indexes to be in separate tablespaces from your tables. Then create a separate bufferpool just for the index tablespaces so that indexes always (or almost always stay in the bufferpool). You can put small, frequently accessed tables in the same bufferpool as the index bufferpool.

Also, I would put the catalog tablespace in its own completely different bufferpool separate from all the others with a size of about 4000 4K pages.