RE: DB2EUG: Running like the proverbial dog.

[Fortier, Christi (USPC.PCT.Hopewell)]

I'll provide the 2x4 :}.   Steve, there is alot of tuning work you can do,
but ultimately to get you running for testing, up your RAM to at least 200
MB. Run the following:

db2 update dbm cfg using numdb 2  (alot of memory calculations are based on
this...if you
                         are keeping sample database use 2, otherwise 1
after you drop Sample)
db2 update dbm cfg using maxagents 100 (reduce to at least 1/2 of
defaults..reducing the number of connections..impacts memory)
db2 update dbm cfg using num_poolagents 100 (keep in sync w maxagents
db2 update dbm cfg using num_initagents 0 
db2 update dbm cfg using sheapthres 2500 (reduces the overall sortheap)
Now play...
Create your DB dbname, then.....
db2 update db cfg for dbname using sortheap 128 (per connection memory)
db2 update db cfg for dbname using buffpage 128 (definitely tune up later)
db2 update db cfg for dbname using util_heap_sz 1000 (definitely tune up for
bkup, loads, reorgs, runstats etc.)

Later, when you have adequate RAM, and have a database created, check out
IBM's performance wizard in control center (right click on database folder).
It is not conclusive, but will give you some tuning ideas for your business
needs.   Rule of thumb for alot of DBMs's but especially DB2:
The more RAM, the more connections & faster your DB will perform.  CPU speed
has some impact, but RAM the most.  Keep your logs on a separate controller
from your temp & data.  Index tablespaces do not need to be separated on
different disks from data tablespaces like in other DBMSs.  If you can
afford the space raid 10 your logs so you don't incur the write hit of RAID
5.  If you use RAID 5 ensure there is some hardware tuning to minimize the
write hit (and cache it).  Move your NT temp away from your page file.

 As an example, for the Intel Develpoer Conference 400 concurrent
transactions 350TPS I used 1 GB per server in a EEE cluster with 1/2 TB of
data.  If you have a mixed transaction load, Referential Integrity and fat
tables (OLCP...60-40 insert ratio) you may need 2GB per server to achieve
100 TPS with multi-TBs of data.  It all depends on your business needs, your
schema,  the size of your Database, the concurrency, and your
physical/logical  TUNING!!!!

-----Original Message-----
From: Palgrave, Greg [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 16, 2001 10:05 PM
To: [EMAIL PROTECTED]
Subject: RE: DB2EUG: Running like the proverbial dog.


Hi Steve,

You *really* need more memory to run UDB properly, but if this is a "test"
server you can live with it if you go through the dbm config and reduce heap
sizes and various memory allocations etc.  Use task manager to check how
much memory various processes are using - if your memory use is high you may
be going to your swapfile, which is relatively slow. The GUI is generally a
little slow - particularly with the new Java code. Java is fun, but
slooooow.

If this is a real server (with 128Mb - nah?!) then kick it up to at least
512Mb. You also want a *physically* separate drive for DB2 logs, so that the
data and logs aren't sharing a disk array. If your NT guru tells you it's a
RAID disk so it doesn't matter, smack them with a 2x4 until they listen to
reason.

regards, 

Greg Palgrave
Database Administrator
Unisys WEST


-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 17 July 2001 08:12
To: [EMAIL PROTECTED]
Subject: DB2EUG: Running like the proverbial dog.


Can anyone point me in the right direction please?

I have UDB (7.1) installed pretty much out of the box on a Pentium III with
128Mg.

Anything I do seems to take an age, I have checked out the DBA certification
guide
and listened to a webcast on performance but they are aimed at application
performance with which I am already familiar (several years m/f DBA).  

As an example; if, in the control centre, I click on tables it takes 20-30
secs to
show a table list.  A visual explain of a very simple select take about 45
secs.

Do I just need heaps more memory or are there some basic tuning parameters
which I should attack first?  As I said earlier just some pointers to the
correct part of the doco would be good.

Or is this normal for little DB2 and I just have to get used to it?

TIA

Steve T


=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod

=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod



=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod