RE: [osdldbt-general] how to adjust std script to run dbt2 w/sapd b using 8 warehouses?

[Najarian, A. <Ano...@st...>]

A little update: I was finally able to build a 32-warehouse database  by
increasing the original script settings 8-fold:

param_adddevspace 1 DATA $HOME/$SID/DATA_001 F 655360
param_adddevspace 1 LOG  $HOME/$SID/LOG_001  F 327680

Scared to go to a larger warehouse count. :)  Will do a test run, for now,
A

-----Original Message-----
From: Najarian, Anoush 
Sent: Thursday, January 29, 2004 10:02 AM
To: 'Mary Edie Meredith'; Najarian, Anoush
Cc: 'ma...@os...'; osdldbt-general
Subject: RE: [osdldbt-general] how to adjust std script to run dbt2
w/sapd b using 8 warehouses?


Thank you very much for the calculations, they really do clear up various
points!

> For cached runs on our 4GB systems, we build a database of 100
> warehouses but set up the drivers to touch only 16 warehouses (8
> drivers, 2 warehouse each driver).  As I mentioned before, we are using
> raw (direct I/O) and therefore do not use page cache, so we will be able
> to cache more warehouse than you for the same memory.

I am planning to move to raw I/O once I know I can get a reasonable-size
database to build :).

> I vaguely remember trying to do cached runs on a database of only 16
> warehouses , but we had trouble running at full speed for a long time. 
> I attributed that to doing too many transactions concentrated over a
> relatively small database.  Problems went away when we went to 100
> warehouses and only touched 16.  The point is, you may experience this
> too trying to cache a 8 warehouse database using a total database size
> of only 8 warehouses.

How do I do that?  (Have 100 warehouses, but only touch 16?)

Many thanks,
A

-----Original Message-----
From: Mary Edie Meredith [mailto:mar...@os...]
Sent: Wednesday, January 28, 2004 5:36 PM
To: Najarian, Anoush
Cc: 'ma...@os...'; osdldbt-general
Subject: RE: [osdldbt-general] how to adjust std script to run dbt2
w/sapd b using 8 warehouses?


On Wed, 2004-01-28 at 11:38, Najarian, Anoush wrote:
> Mark, 
> 
> are you saying I should adjust the following lines (only) to double as I
go
> from 4 to 8 warehouses?
> 
> param_put MAXDATADEVSPACES 22
> ...
> param_adddevspace 1 DATA $HOME/$SID/DATA_001 F 81920
> param_adddevspace 1 LOG  $HOME/$SID/LOG_001  F 40960
> 
> My goal is to use the 3GB of memory (all SAPDB can use, right?) during the
> cached run.  For that, how many warehouses should I be looking to set up,
do
> you think?

SAPDB is limited to 3GB of virtual address space.  However, if you use
file systems, you will also have to consider the memory needed for page
cache for the database files.  This isn't counted in the virtual address
space but does take away from the available physical memory.  Our tests
use raw so that we do not have to make trade-offs between physical
memory for SAPDB memory versus the page cache.   

Another data point, our 100 warehouse database is about 11GB (table and
index space), that's about 113M per warehouse, so 8 warehouses will need
approximately 904MB or more.  That calculates to be 115712 8k pages.

An 8 warehouse database will then cache within a 904MB database buffer
cache.  That should leave plenty of space for the same amount of page
cache and the rest of the database and kernel structures on a 4GB
system.   The database buffer size is determined by the DATA_CACHE
parameter Mark mentioned earlier.  DATA_CACHE needs to be artificially
low for restores ( and loads if I remember correctly), but you want to
bump it up when you do the runs (see the run scripts).

For cached runs on our 4GB systems, we build a database of 100
warehouses but set up the drivers to touch only 16 warehouses (8
drivers, 2 warehouse each driver).  As I mentioned before, we are using
raw (direct I/O) and therefore do not use page cache, so we will be able
to cache more warehouse than you for the same memory.

I vaguely remember trying to do cached runs on a database of only 16
warehouses , but we had trouble running at full speed for a long time. 
I attributed that to doing too many transactions concentrated over a
relatively small database.  Problems went away when we went to 100
warehouses and only touched 16.  The point is, you may experience this
too trying to cache a 8 warehouse database using a total database size
of only 8 warehouses.

Hope this helps.

> A
> 
> 
> -----Original Message-----
> From: ma...@os... [mailto:ma...@os...]
> Sent: Wednesday, January 28, 2004 1:24 PM
> To: Ano...@st...
> Cc: osd...@li...
> Subject: Re: [osdldbt-general] how to adjust std script to run dbt2
> w/sapdb using 8 warehouses?
> 
> 
> Hi Anoush,
> 
> The number used for the param_adddevspace is the number of 8KB pages.
> Can you copy the entire create_db script you're using to set the
> database parameters.  I'm wondering if you're using the setting that
> restrict the total data devspace size, which may explain why the
> database stalls when you're initializng the database with bigger
> devspaces defined.
> 
> Scaling the database is pretty linear, so an 8 warehouse database should
> be about 2x the size of a 4 warehouse database.  If I follow how you
> increased your dev space sizes, you more than doubled them when you
> moved from 4 warehouses to 8.  
> 
> The knldiag log suggests you're still running out of space in
> your devspaces.  If my math is correct, you defined about 100MB of space
> for both the data and the log devspaces.  If you want to do some rough
> math, see how much space the datafiles (created by datagen) are taking
> up for 8 warehouses and create the devspace about 4x the size for safe
> measure.
> 
> Mark
>