More CPU???

[Jason Gyetko]

I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4 virtual)
and am wondering if there is a way to configure DB2 to use more processor.
Right now I'm running a delete query to delete around 4 million records. It
is only using 5% - 25% of the processor (mostly the lower end with some
spikes). This system, for the most part, is strictly used as a database
server so I'd like to be able to set it so that DB2 can use at least 50% of
the processor, but better yet up to 80% would be great. Does anyone know
how or if I can accomplish this? I'm assuming you can't dedicate loads over
multiple processors, it's probably handled by the system, but if there is a
way to do that, I'd greatly appreciate any info you could offer. Thanks in
advance.

[Ian]

Jason Gyetko wrote:

I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4 virtual)
and am wondering if there is a way to configure DB2 to use more processor.
Yes, try setting the undocumented registry variable DB2_GO_FAST=YES. If
that's not good enough, there is also DB2_GO_FASTER.

(Just kidding)
Right now I'm running a delete query to delete around 4 million records. It
is only using 5% - 25% of the processor (mostly the lower end with some
spikes). This system, for the most part, is strictly used as a database
server so I'd like to be able to set it so that DB2 can use at least 50% of
the processor, but better yet up to 80% would be great. Does anyone know
how or if I can accomplish this?

Chances are very good that this is a I/O system limitation, not a CPU
limitation. Your CPUs are likely waiting for IO to complete.

What kind of disks are you writing to, and how are they configured
(RAID5, RAID1, etc)? From a database perspective, you may be getting
poor performance with database logging, or poor performance for async
writes in the database.

[Shyam Peri]

By the performing delete on the respective table(s), are u trying to
acheive a truncate!!!

[Jason Gyetko]

I'm not real sure on what you mean by truncate. Is truncate the same as a
delete without the database logging? Can you truncate a table using
criteria or is it an all or nothing deal? I am deleting all records in a
table prior to a certain date. This particular table has 7 million records,
my delete statement will delete 4 million of them. It took 1:10 to complete
the delete. Prior to the delete I performed an export of the data that I
was to delete so I can import it into a seperate archive database.

"Shyam Peri" <pe*******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

By the performing delete on the respective table(s), are u trying to
acheive a truncate!!!

[Jason Gyetko]

Ha, ha... If it were only as easy as just making up a key for what you want
to accomplish.

I have 8, 80GB, 15K hard drives configured for RAID1.
OS on C:, Database on E:, and log files on G:

I have some database monitoring running on the buffers and they are all
minimal usage. I do notice that the "Select SQL Statements Execute" &
"Update/Insert/Delete SQL Statements Executed" monitors are peaked out. Do
you know what database parameter effects these? Are there any other
monitors I should watch?

"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

Jason Gyetko wrote:

I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4 virtual) and am wondering if there is a way to configure DB2 to use more processor.

Yes, try setting the undocumented registry variable DB2_GO_FAST=YES. If
that's not good enough, there is also DB2_GO_FASTER.

(Just kidding)

Right now I'm running a delete query to delete around 4 million records.

It is only using 5% - 25% of the processor (mostly the lower end with some
spikes). This system, for the most part, is strictly used as a database
server so I'd like to be able to set it so that DB2 can use at least 50% of the processor, but better yet up to 80% would be great. Does anyone know how or if I can accomplish this?

Chances are very good that this is a I/O system limitation, not a CPU
limitation. Your CPUs are likely waiting for IO to complete.

What kind of disks are you writing to, and how are they configured
(RAID5, RAID1, etc)? From a database perspective, you may be getting
poor performance with database logging, or poor performance for async
writes in the database.

[Jurgen Haan]

Jason Gyetko wrote:

I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4 virtual)
and am wondering if there is a way to configure DB2 to use more processor.
Right now I'm running a delete query to delete around 4 million records. It
is only using 5% - 25% of the processor (mostly the lower end with some
spikes). This system, for the most part, is strictly used as a database
server so I'd like to be able to set it so that DB2 can use at least 50% of
the processor, but better yet up to 80% would be great. Does anyone know
how or if I can accomplish this? I'm assuming you can't dedicate loads over
multiple processors, it's probably handled by the system, but if there is a
way to do that, I'd greatly appreciate any info you could offer. Thanks in
advance.

Apply basic performance tuning. Perhaps let control center enforce the
settings proposed by the configuration advisor.
Don't be shy using indexes, use plenty. (though deleting isn't affected)
Large bufferpools.

Okay, so far some db2 settings.
I do not know what system you are using, but if you're running something
like Linux (I do not know if other un*x-es have it) have a funny little
program called renice. This alters the niceness of a process (a priority
modifier) enableing you to give a process a higher priority.
Probably you shouldn't use this, because it might make things worse.
Another thing you could check is the swappiness of the kernel.
The swappiness indicates how eager a system may be to use swap. Setting
this lower will change the way the system uses paging.

Also check if the statement monitor is not activated. (tracing
statements slows down)

But as stated before, a big problem could be the I/O.
Running DB2 on a machine with 2 IDE drives put in a RAID 1 is bad.
Running DB2 on a machine with 16 SCSI disks put in a raid 0+1 is better
Running DB2 on a machine with a huge SAN array is even better
Running DB2 on a machine with a netApp storage array is best.
Using an enormous amount of memory is not too shabby as well. (As soon
as a database starts to swap, the performance collapses and the
processors are just in queue because of the I/O waits), so check your
free memory (and used swap).

-R-

[Mark Yudkin]

Since you say "80GB", and SCSI disks tend to come in different sizes, it
seems you aren't on SCSI, let alone fibre-attached SAN. Your system sounds
to be one with "a big head on a baby's body".

Your cheapest option is probably to upgrade to SCSI LVD 320, using a caching
controller with (fully recharged) battery-backed write cache and adequate
memory, replace the disks with suitable 15K RPM SCSI 320 LVD drives, and try
again.

You also may wish to check what sort of memory bus architecture your system
has, as that may also be a bottleneck. If so, you're probably best of buying
a "more powerful" server.

(We're running DB2 8.1.5 on a 4-way 3.2GHz hyperthreading Xeon HP server
with 4GB RAM, Hitachi disk subsystem over a fibe-attached IBM SAN).

"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:wX******************@newssvr31.news.prodigy.c om...

Ha, ha... If it were only as easy as just making up a key for what you
want
to accomplish.

I have 8, 80GB, 15K hard drives configured for RAID1.
OS on C:, Database on E:, and log files on G:

I have some database monitoring running on the buffers and they are all
minimal usage. I do notice that the "Select SQL Statements Execute" &
"Update/Insert/Delete SQL Statements Executed" monitors are peaked out.
Do
you know what database parameter effects these? Are there any other
monitors I should watch?

"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

Jason Gyetko wrote:

> I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4 virtual) > and am wondering if there is a way to configure DB2 to use more processor.

Yes, try setting the undocumented registry variable DB2_GO_FAST=YES. If
that's not good enough, there is also DB2_GO_FASTER.

(Just kidding)

> Right now I'm running a delete query to delete around 4 million
> records.

It > is only using 5% - 25% of the processor (mostly the lower end with some
> spikes). This system, for the most part, is strictly used as a
> database
> server so I'd like to be able to set it so that DB2 can use at least
> 50% of > the processor, but better yet up to 80% would be great. Does anyone know > how or if I can accomplish this?

Chances are very good that this is a I/O system limitation, not a CPU
limitation. Your CPUs are likely waiting for IO to complete.

What kind of disks are you writing to, and how are they configured
(RAID5, RAID1, etc)? From a database perspective, you may be getting
poor performance with database logging, or poor performance for async
writes in the database.

[Jason Gyetko]

Sorry, should have included this before.

(4) PERC LD PERCRAID SCSI Disks
2GB Memory

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:42**********************@news.sunrise.ch...

Since you say "80GB", and SCSI disks tend to come in different sizes, it
seems you aren't on SCSI, let alone fibre-attached SAN. Your system sounds
to be one with "a big head on a baby's body".

Your cheapest option is probably to upgrade to SCSI LVD 320, using a caching controller with (fully recharged) battery-backed write cache and adequate
memory, replace the disks with suitable 15K RPM SCSI 320 LVD drives, and try again.

You also may wish to check what sort of memory bus architecture your system has, as that may also be a bottleneck. If so, you're probably best of buying a "more powerful" server.

(We're running DB2 8.1.5 on a 4-way 3.2GHz hyperthreading Xeon HP server
with 4GB RAM, Hitachi disk subsystem over a fibe-attached IBM SAN).

"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:wX******************@newssvr31.news.prodigy.c om...

Ha, ha... If it were only as easy as just making up a key for what you
want
to accomplish.

I have 8, 80GB, 15K hard drives configured for RAID1.
OS on C:, Database on E:, and log files on G:

I have some database monitoring running on the buffers and they are all
minimal usage. I do notice that the "Select SQL Statements Execute" &
"Update/Insert/Delete SQL Statements Executed" monitors are peaked out.
Do
you know what database parameter effects these? Are there any other
monitors I should watch?

"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

Jason Gyetko wrote:
> I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4

virtual)

> and am wondering if there is a way to configure DB2 to use more

processor.

Yes, try setting the undocumented registry variable DB2_GO_FAST=YES. If that's not good enough, there is also DB2_GO_FASTER.

(Just kidding)

> Right now I'm running a delete query to delete around 4 million
> records.

It

> is only using 5% - 25% of the processor (mostly the lower end with some > spikes). This system, for the most part, is strictly used as a
> database
> server so I'd like to be able to set it so that DB2 can use at least
> 50%

of

> the processor, but better yet up to 80% would be great. Does anyone

know

> how or if I can accomplish this?

Chances are very good that this is a I/O system limitation, not a CPU
limitation. Your CPUs are likely waiting for IO to complete.

What kind of disks are you writing to, and how are they configured
(RAID5, RAID1, etc)? From a database perspective, you may be getting
poor performance with database logging, or poor performance for async
writes in the database.

[Mark Yudkin]

Then you have a DELL Poweredge server? Which model? Which PERC card is that
(PERC2/Si, PERC3/Di, PERC3/DC, PERC4/SC, etc)? How large is the battery
backed write cache (not all PERC models support batteries)? What's the
bandwidth (PERC2 in 100MB/s, PERC3 is 160MB/s, PERC4 is 320MB/s)? What about
the disks themselves? I'm still assuming you have Windows 2000 or 2003, is
this correct or are you on Linux? What's the motherboard chipset? I wish
you'd give full information up front.

In any case, I'm still guessing primarily I/O bottleneck, with a secondary
guess of inadequate memory architecture (would occur if you have a so-called
"entry-level" server). If you were to tell us what you had, we could narrow
this down,

Also, how large are the buffer pools, and how does system swapping look (2GB
is on the "low end" for a database server)? What does the performance
monitoring (system and DB2) show as the bottleneck?

I'm also still confused by your claim of 80GB drives. AFAI; DELL's SCSI
drives around that size are 73GB. Their 80GB drives are SATA, and these are
offered on DELL's entry level servers, but these don't connect to the PERC
controller (uses CERC or software-based RAID).
"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:Oc******************@newssvr31.news.prodigy.c om...

Sorry, should have included this before.

(4) PERC LD PERCRAID SCSI Disks
2GB Memory

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:42**********************@news.sunrise.ch...

Since you say "80GB", and SCSI disks tend to come in different sizes, it
seems you aren't on SCSI, let alone fibre-attached SAN. Your system
sounds
to be one with "a big head on a baby's body".

Your cheapest option is probably to upgrade to SCSI LVD 320, using a

caching

controller with (fully recharged) battery-backed write cache and adequate
memory, replace the disks with suitable 15K RPM SCSI 320 LVD drives, and

try

again.

You also may wish to check what sort of memory bus architecture your

system

has, as that may also be a bottleneck. If so, you're probably best of

buying

a "more powerful" server.

(We're running DB2 8.1.5 on a 4-way 3.2GHz hyperthreading Xeon HP server
with 4GB RAM, Hitachi disk subsystem over a fibe-attached IBM SAN).

"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:wX******************@newssvr31.news.prodigy.c om...

> Ha, ha... If it were only as easy as just making up a key for what you
> want
> to accomplish.
>
> I have 8, 80GB, 15K hard drives configured for RAID1.
> OS on C:, Database on E:, and log files on G:
>
> I have some database monitoring running on the buffers and they are all
> minimal usage. I do notice that the "Select SQL Statements Execute" &
> "Update/Insert/Delete SQL Statements Executed" monitors are peaked out.
> Do
> you know what database parameter effects these? Are there any other
> monitors I should watch?
>
> "Ian" <ia*****@mobileaudio.com> wrote in message
> news:42**********@newsfeed.slurp.net...
>> Jason Gyetko wrote:
>> > I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4
> virtual)
>> > and am wondering if there is a way to configure DB2 to use more
> processor.
>>
>> Yes, try setting the undocumented registry variable DB2_GO_FAST=YES. If >> that's not good enough, there is also DB2_GO_FASTER.
>>
>> (Just kidding)
>>
>> > Right now I'm running a delete query to delete around 4 million
>> > records.
> It
>> > is only using 5% - 25% of the processor (mostly the lower end with some >> > spikes). This system, for the most part, is strictly used as a
>> > database
>> > server so I'd like to be able to set it so that DB2 can use at least
>> > 50%
> of
>> > the processor, but better yet up to 80% would be great. Does anyone
> know
>> > how or if I can accomplish this?
>>
>> Chances are very good that this is a I/O system limitation, not a CPU
>> limitation. Your CPUs are likely waiting for IO to complete.
>>
>> What kind of disks are you writing to, and how are they configured
>> (RAID5, RAID1, etc)? From a database perspective, you may be getting
>> poor performance with database logging, or poor performance for async
>> writes in the database.
>>
>>
>>
>
>

[Jason Gyetko]

Currently all the bufferpools are monitoring at 0%.

Sorry for bits & pieces, I'm posting on behalf of a client and did not have
all that info readily available. Here are the Specs:

Dell PowerEdge 4600 - BIOS A08
PERC 3/QC RAID Controller 128 cache
(2) 3GB Xeon Processors 512 cache
2GB RAM
(2) 18GB HD & (6) 73GB HD - RAID 1
Chipset - ????
Windows 2000 Server SP4
DB2 v8.1 SP5

Buffer Pool
------------
(IBMDEFAULTBP)=227380

Database Manager Configuration
---------------------------------
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0a00
(MAXTOTFILOP) = 16000
(CPUSPEED) = 3.306410e-007
(NUMDB) = 8
(DATALINKS) = NO
(FEDERATED) = NO
(TP_MON_NAME) =
(DFT_ACCOUNT_STR) =
(JDK_PATH) = C:\SQLLIB\java\jdk
(DIAGLEVEL) = 3
(NOTIFYLEVEL) = 3
(DIAGPATH) =
(DFT_MON_BUFPOOL) = OFF
(DFT_MON_LOCK) = OFF
(DFT_MON_SORT) = OFF
(DFT_MON_STMT) = OFF
(DFT_MON_TABLE) = OFF
(DFT_MON_TIMESTAMP) = ON
(DFT_MON_UOW) = OFF
(HEALTH_MON) = OFF
(SYSADM_GROUP) = DBADM
(SYSCTRL_GROUP) = DBCTRL
(SYSMAINT_GROUP) = DBMAINT
(SYSMON_GROUP) =
(AUTHENTICATION) = SERVER
(CATALOG_NOAUTH) = NO
(TRUST_ALLCLNTS) = YES
(TRUST_CLNTAUTH) = CLIENT
(FED_NOAUTH) = NO
(DFTDBPATH) = E:
(MON_HEAP_SZ) = 66
(JAVA_HEAP_SZ) = 512
(AUDIT_BUF_SZ) = 0
(INSTANCE_MEMORY) = AUTOMATIC
(BACKBUFSZ) = 1024
(RESTBUFSZ) = 1024
(AGENT_STACK_SZ) = 16
(MIN_PRIV_MEM) = 32
(PRIV_MEM_THRESH) = 32767
(SHEAPTHRES) = 30609
(DIR_CACHE) = YES
(ASLHEAPSZ) = 15
(RQRIOBLK) = 32767
(DOS_RQRIOBLK) = 4096
(QUERY_HEAP_SZ) = 1000
(DRDA_HEAP_SZ) = 128
(UTIL_IMPACT_LIM) = 100
(AGENTPRI) = SYSTEM
(MAXAGENTS) = 200
(NUM_POOLAGENTS) = 92
(NUM_INITAGENTS) = 0
(MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
(MAXCAGENTS) = MAX_COORDAGENTS
(MAX_CONNECTIONS) = MAX_COORDAGENTS
(KEEPFENCED) = YES
(FENCED_POOL) = MAX_COORDAGENTS
(NUM_INITFENCED) = 0
(INDEXREC) = ACCESS
(TM_DATABASE) = 1ST_CONN
(RESYNC_INTERVAL) = 180
(SPM_NAME) = AVBAR200
(SPM_LOG_FILE_SZ) = 256
(SPM_MAX_RESYNC) = 20
(SPM_LOG_PATH) =
(NNAME) =
(SVCENAME) = RBUDB
(DISCOVER) = SEARCH
(DISCOVER_INST) = ENABLE
(MAX_QUERYDEGREE) = 4
(INTRA_PARALLEL) = YES
(FCM_NUM_BUFFERS) = 1024
(FCM_NUM_RQB) = 512
(FCM_NUM_CONNECT) = (FCM_NUM_RQB * 0.75)
(FCM_NUM_ANCHORS) = (FCM_NUM_RQB * 0.75)

Database Configuration
------------------------
Database configuration release level= 0x0a00
Database release level = 0x0a00
Database territory = US
Database code page = 1252
Database code set = 1252
Database country/region code = 1
Multi-page file allocation enabled = NO
Log retain for recovery status = RECOVERY
User exit for logging status = YES
Default number of containers = 1
(DYN_QUERY_MGMT) = DISABLE
(DISCOVER_DB) = ENABLE
(DFT_QUERYOPT) = 5
(DFT_DEGREE) = 1
(DFT_SQLMATHWARN) = NO
(DFT_REFRESH_AGE) = 0
(NUM_FREQVALUES) = 10
(NUM_QUANTILES) = 20
(DL_EXPINT) = 60
(DL_WT_IEXPINT) = 60
(DL_NUM_COPIES) = 1
(DL_TIME_DROP) = 1
(DL_UPPER) = NO
(DL_TOKEN) = MAC0
(DBHEAP) = 2048
(DATABASE_MEMORY) = AUTOMATIC
(CATALOGCACHE_SZ) = 487
(LOGBUFSZ) = 76
(UTIL_HEAP_SZ) = 10000
(BUFFPAGE) = 1024
(ESTORE_SEG_SZ) = 16000
(NUM_ESTORE_SEGS) = 0
(LOCKLIST) = 1024
(APPGROUP_MEM_SZ) = 14494
(GROUPHEAP_RATIO) = 70
(APP_CTL_HEAP_SZ) = 160
(SHEAPTHRES_SHR) = (SHEAPTHRES)
(SORTHEAP) = 1024
(STMTHEAP) = 1024
(APPLHEAPSZ) = 512
(PCKCACHESZ) = 4096
(STAT_HEAP_SZ) = 4384
(DLCHKTIME) = 10000
(MAXLOCKS) = 60
(LOCKTIMEOUT) = -1
(CHNGPGS_THRESH) = 60
(NUM_IOCLEANERS) = 10
(NUM_IOSERVERS) = 4
(INDEXSORT) = YES
(SEQDETECT) = YES
(DFT_PREFETCH_SZ) = 32
(TRACKMOD) = OFF
(DFT_EXTENT_SZ) = 32
(MAXAPPLS) = 40
(AVG_APPLS) = 32
(MAXFILOP) = 64
(LOGFILSIZ) = 1024
(LOGPRIMARY) = 3
(LOGSECOND) = 125
(NEWLOGPATH) =
Path to log files = G:\DB2Logs\
(OVERFLOWLOGPATH) =
(MIRRORLOGPATH) =
First active log = S0000361.LOG
(BLK_LOG_DSK_FUL) = NO
(MAX_LOG) = 0
(NUM_LOG_SPAN) = 0
(MINCOMMIT) = 2
(SOFTMAX) = 120
(LOGRETAIN) = RECOVERY
(USEREXIT) = ON
(AUTORESTART) = ON
(INDEXREC) = SYSTEM (ACCESS)
(DFT_LOADREC_SES) = 1
(NUM_DB_BACKUPS) = 12
(REC_HIS_RETENTN) = 366
(TSM_MGMTCLASS) =
(TSM_NODENAME) =
(TSM_OWNER) =
(TSM_PASSWORD) =

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:42**********************@news.sunrise.ch...

Then you have a DELL Poweredge server? Which model? Which PERC card is that (PERC2/Si, PERC3/Di, PERC3/DC, PERC4/SC, etc)? How large is the battery
backed write cache (not all PERC models support batteries)? What's the
bandwidth (PERC2 in 100MB/s, PERC3 is 160MB/s, PERC4 is 320MB/s)? What about the disks themselves? I'm still assuming you have Windows 2000 or 2003, is
this correct or are you on Linux? What's the motherboard chipset? I wish
you'd give full information up front.

In any case, I'm still guessing primarily I/O bottleneck, with a secondary
guess of inadequate memory architecture (would occur if you have a so-called "entry-level" server). If you were to tell us what you had, we could narrow this down,

Also, how large are the buffer pools, and how does system swapping look (2GB is on the "low end" for a database server)? What does the performance
monitoring (system and DB2) show as the bottleneck?

I'm also still confused by your claim of 80GB drives. AFAI; DELL's SCSI
drives around that size are 73GB. Their 80GB drives are SATA, and these are offered on DELL's entry level servers, but these don't connect to the PERC
controller (uses CERC or software-based RAID).
"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:Oc******************@newssvr31.news.prodigy.c om...

Sorry, should have included this before.

(4) PERC LD PERCRAID SCSI Disks
2GB Memory

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:42**********************@news.sunrise.ch...

Since you say "80GB", and SCSI disks tend to come in different sizes, it seems you aren't on SCSI, let alone fibre-attached SAN. Your system
sounds
to be one with "a big head on a baby's body".

Your cheapest option is probably to upgrade to SCSI LVD 320, using a

caching

controller with (fully recharged) battery-backed write cache and adequate memory, replace the disks with suitable 15K RPM SCSI 320 LVD drives, and

try

again.

You also may wish to check what sort of memory bus architecture your

system

has, as that may also be a bottleneck. If so, you're probably best of

buying

a "more powerful" server.

(We're running DB2 8.1.5 on a 4-way 3.2GHz hyperthreading Xeon HP

server with 4GB RAM, Hitachi disk subsystem over a fibe-attached IBM SAN).

"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:wX******************@newssvr31.news.prodigy.c om...
> Ha, ha... If it were only as easy as just making up a key for what you > want
> to accomplish.
>
> I have 8, 80GB, 15K hard drives configured for RAID1.
> OS on C:, Database on E:, and log files on G:
>
> I have some database monitoring running on the buffers and they are all > minimal usage. I do notice that the "Select SQL Statements Execute" & > "Update/Insert/Delete SQL Statements Executed" monitors are peaked out. > Do
> you know what database parameter effects these? Are there any other
> monitors I should watch?
>
> "Ian" <ia*****@mobileaudio.com> wrote in message
> news:42**********@newsfeed.slurp.net...
>> Jason Gyetko wrote:
>> > I'm running DB2 v8.1 FP5 on a Server with 2 physical processors (4
> virtual)
>> > and am wondering if there is a way to configure DB2 to use more
> processor.
>>
>> Yes, try setting the undocumented registry variable DB2_GO_FAST=YES.

If

>> that's not good enough, there is also DB2_GO_FASTER.
>>
>> (Just kidding)
>>
>> > Right now I'm running a delete query to delete around 4 million
>> > records.
> It
>> > is only using 5% - 25% of the processor (mostly the lower end with

some

>> > spikes). This system, for the most part, is strictly used as a
>> > database
>> > server so I'd like to be able to set it so that DB2 can use at least >> > 50%
> of
>> > the processor, but better yet up to 80% would be great. Does anyone > know
>> > how or if I can accomplish this?
>>
>> Chances are very good that this is a I/O system limitation, not a CPU >> limitation. Your CPUs are likely waiting for IO to complete.
>>
>> What kind of disks are you writing to, and how are they configured
>> (RAID5, RAID1, etc)? From a database perspective, you may be getting >> poor performance with database logging, or poor performance for async >> writes in the database.
>>
>>
>>
>
>

[Mark Yudkin]

The disk sizes are definitely more plausible, but your specs remain
incomplete (disk specs!). The PERC3/QC supports battery backup, but is only
160MB/s. Using the RAID setup utility, check that your battery is in good
condition and that you have allocated 64MB to the battery cache.

Run the tests with full performance monitoring on the I/O of each disk pair
using both the WIndows and DB2 tools to ensure you have properly balanced
I/O.

"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:0r****************@newssvr17.news.prodigy.com ...

Currently all the bufferpools are monitoring at 0%.

Sorry for bits & pieces, I'm posting on behalf of a client and did not
have
all that info readily available. Here are the Specs:

Dell PowerEdge 4600 - BIOS A08
PERC 3/QC RAID Controller 128 cache
(2) 3GB Xeon Processors 512 cache
2GB RAM
(2) 18GB HD & (6) 73GB HD - RAID 1
Chipset - ????
Windows 2000 Server SP4
DB2 v8.1 SP5

Buffer Pool
------------
(IBMDEFAULTBP)=227380

Database Manager Configuration
---------------------------------
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0a00
(MAXTOTFILOP) = 16000
(CPUSPEED) = 3.306410e-007
(NUMDB) = 8
(DATALINKS) = NO
(FEDERATED) = NO
(TP_MON_NAME) =
(DFT_ACCOUNT_STR) =
(JDK_PATH) = C:\SQLLIB\java\jdk
(DIAGLEVEL) = 3
(NOTIFYLEVEL) = 3
(DIAGPATH) =
(DFT_MON_BUFPOOL) = OFF
(DFT_MON_LOCK) = OFF
(DFT_MON_SORT) = OFF
(DFT_MON_STMT) = OFF
(DFT_MON_TABLE) = OFF
(DFT_MON_TIMESTAMP) = ON
(DFT_MON_UOW) = OFF
(HEALTH_MON) = OFF
(SYSADM_GROUP) = DBADM
(SYSCTRL_GROUP) = DBCTRL
(SYSMAINT_GROUP) = DBMAINT
(SYSMON_GROUP) =
(AUTHENTICATION) = SERVER
(CATALOG_NOAUTH) = NO
(TRUST_ALLCLNTS) = YES
(TRUST_CLNTAUTH) = CLIENT
(FED_NOAUTH) = NO
(DFTDBPATH) = E:
(MON_HEAP_SZ) = 66
(JAVA_HEAP_SZ) = 512
(AUDIT_BUF_SZ) = 0
(INSTANCE_MEMORY) = AUTOMATIC
(BACKBUFSZ) = 1024
(RESTBUFSZ) = 1024
(AGENT_STACK_SZ) = 16
(MIN_PRIV_MEM) = 32
(PRIV_MEM_THRESH) = 32767
(SHEAPTHRES) = 30609
(DIR_CACHE) = YES
(ASLHEAPSZ) = 15
(RQRIOBLK) = 32767
(DOS_RQRIOBLK) = 4096
(QUERY_HEAP_SZ) = 1000
(DRDA_HEAP_SZ) = 128
(UTIL_IMPACT_LIM) = 100
(AGENTPRI) = SYSTEM
(MAXAGENTS) = 200
(NUM_POOLAGENTS) = 92
(NUM_INITAGENTS) = 0
(MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
(MAXCAGENTS) = MAX_COORDAGENTS
(MAX_CONNECTIONS) = MAX_COORDAGENTS
(KEEPFENCED) = YES
(FENCED_POOL) = MAX_COORDAGENTS
(NUM_INITFENCED) = 0
(INDEXREC) = ACCESS
(TM_DATABASE) = 1ST_CONN
(RESYNC_INTERVAL) = 180
(SPM_NAME) = AVBAR200
(SPM_LOG_FILE_SZ) = 256
(SPM_MAX_RESYNC) = 20
(SPM_LOG_PATH) =
(NNAME) =
(SVCENAME) = RBUDB
(DISCOVER) = SEARCH
(DISCOVER_INST) = ENABLE
(MAX_QUERYDEGREE) = 4
(INTRA_PARALLEL) = YES
(FCM_NUM_BUFFERS) = 1024
(FCM_NUM_RQB) = 512
(FCM_NUM_CONNECT) = (FCM_NUM_RQB * 0.75)
(FCM_NUM_ANCHORS) = (FCM_NUM_RQB * 0.75)

Database Configuration
------------------------
Database configuration release level= 0x0a00
Database release level = 0x0a00
Database territory = US
Database code page = 1252
Database code set = 1252
Database country/region code = 1
Multi-page file allocation enabled = NO
Log retain for recovery status = RECOVERY
User exit for logging status = YES
Default number of containers = 1
(DYN_QUERY_MGMT) = DISABLE
(DISCOVER_DB) = ENABLE
(DFT_QUERYOPT) = 5
(DFT_DEGREE) = 1
(DFT_SQLMATHWARN) = NO
(DFT_REFRESH_AGE) = 0
(NUM_FREQVALUES) = 10
(NUM_QUANTILES) = 20
(DL_EXPINT) = 60
(DL_WT_IEXPINT) = 60
(DL_NUM_COPIES) = 1
(DL_TIME_DROP) = 1
(DL_UPPER) = NO
(DL_TOKEN) = MAC0
(DBHEAP) = 2048
(DATABASE_MEMORY) = AUTOMATIC
(CATALOGCACHE_SZ) = 487
(LOGBUFSZ) = 76
(UTIL_HEAP_SZ) = 10000
(BUFFPAGE) = 1024
(ESTORE_SEG_SZ) = 16000
(NUM_ESTORE_SEGS) = 0
(LOCKLIST) = 1024
(APPGROUP_MEM_SZ) = 14494
(GROUPHEAP_RATIO) = 70
(APP_CTL_HEAP_SZ) = 160
(SHEAPTHRES_SHR) = (SHEAPTHRES)
(SORTHEAP) = 1024
(STMTHEAP) = 1024
(APPLHEAPSZ) = 512
(PCKCACHESZ) = 4096
(STAT_HEAP_SZ) = 4384
(DLCHKTIME) = 10000
(MAXLOCKS) = 60
(LOCKTIMEOUT) = -1
(CHNGPGS_THRESH) = 60
(NUM_IOCLEANERS) = 10
(NUM_IOSERVERS) = 4
(INDEXSORT) = YES
(SEQDETECT) = YES
(DFT_PREFETCH_SZ) = 32
(TRACKMOD) = OFF
(DFT_EXTENT_SZ) = 32
(MAXAPPLS) = 40
(AVG_APPLS) = 32
(MAXFILOP) = 64
(LOGFILSIZ) = 1024
(LOGPRIMARY) = 3
(LOGSECOND) = 125
(NEWLOGPATH) =
Path to log files = G:\DB2Logs\
(OVERFLOWLOGPATH) =
(MIRRORLOGPATH) =
First active log = S0000361.LOG
(BLK_LOG_DSK_FUL) = NO
(MAX_LOG) = 0
(NUM_LOG_SPAN) = 0
(MINCOMMIT) = 2
(SOFTMAX) = 120
(LOGRETAIN) = RECOVERY
(USEREXIT) = ON
(AUTORESTART) = ON
(INDEXREC) = SYSTEM (ACCESS)
(DFT_LOADREC_SES) = 1
(NUM_DB_BACKUPS) = 12
(REC_HIS_RETENTN) = 366
(TSM_MGMTCLASS) =
(TSM_NODENAME) =
(TSM_OWNER) =
(TSM_PASSWORD) =

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:42**********************@news.sunrise.ch...

Then you have a DELL Poweredge server? Which model? Which PERC card is

that

(PERC2/Si, PERC3/Di, PERC3/DC, PERC4/SC, etc)? How large is the battery
backed write cache (not all PERC models support batteries)? What's the
bandwidth (PERC2 in 100MB/s, PERC3 is 160MB/s, PERC4 is 320MB/s)? What

about

the disks themselves? I'm still assuming you have Windows 2000 or 2003,
is
this correct or are you on Linux? What's the motherboard chipset? I wish
you'd give full information up front.

In any case, I'm still guessing primarily I/O bottleneck, with a
secondary
guess of inadequate memory architecture (would occur if you have a

so-called

"entry-level" server). If you were to tell us what you had, we could

narrow

this down,

Also, how large are the buffer pools, and how does system swapping look

(2GB

is on the "low end" for a database server)? What does the performance
monitoring (system and DB2) show as the bottleneck?

I'm also still confused by your claim of 80GB drives. AFAI; DELL's SCSI
drives around that size are 73GB. Their 80GB drives are SATA, and these

are

offered on DELL's entry level servers, but these don't connect to the
PERC
controller (uses CERC or software-based RAID).
"Jason Gyetko" <jg*****@epower-inc.com> wrote in message
news:Oc******************@newssvr31.news.prodigy.c om...

> Sorry, should have included this before.
>
> (4) PERC LD PERCRAID SCSI Disks
> 2GB Memory
>
> "Mark Yudkin" <my***********************@boing.org> wrote in message
> news:42**********************@news.sunrise.ch...
>> Since you say "80GB", and SCSI disks tend to come in different sizes, it >> seems you aren't on SCSI, let alone fibre-attached SAN. Your system
>> sounds
>> to be one with "a big head on a baby's body".
>>
>> Your cheapest option is probably to upgrade to SCSI LVD 320, using a
> caching
>> controller with (fully recharged) battery-backed write cache and adequate >> memory, replace the disks with suitable 15K RPM SCSI 320 LVD drives, and > try
>> again.
>>
>> You also may wish to check what sort of memory bus architecture your
> system
>> has, as that may also be a bottleneck. If so, you're probably best of
> buying
>> a "more powerful" server.
>>
>> (We're running DB2 8.1.5 on a 4-way 3.2GHz hyperthreading Xeon HP server >> with 4GB RAM, Hitachi disk subsystem over a fibe-attached IBM SAN).
>>
>> "Jason Gyetko" <jg*****@epower-inc.com> wrote in message
>> news:wX******************@newssvr31.news.prodigy.c om...
>> > Ha, ha... If it were only as easy as just making up a key for what you >> > want
>> > to accomplish.
>> >
>> > I have 8, 80GB, 15K hard drives configured for RAID1.
>> > OS on C:, Database on E:, and log files on G:
>> >
>> > I have some database monitoring running on the buffers and they are all >> > minimal usage. I do notice that the "Select SQL Statements Execute" & >> > "Update/Insert/Delete SQL Statements Executed" monitors are peaked out. >> > Do
>> > you know what database parameter effects these? Are there any other
>> > monitors I should watch?
>> >
>> > "Ian" <ia*****@mobileaudio.com> wrote in message
>> > news:42**********@newsfeed.slurp.net...
>> >> Jason Gyetko wrote:
>> >> > I'm running DB2 v8.1 FP5 on a Server with 2 physical processors
>> >> > (4
>> > virtual)
>> >> > and am wondering if there is a way to configure DB2 to use more
>> > processor.
>> >>
>> >> Yes, try setting the undocumented registry variable
>> >> DB2_GO_FAST=YES.
> If
>> >> that's not good enough, there is also DB2_GO_FASTER.
>> >>
>> >> (Just kidding)
>> >>
>> >> > Right now I'm running a delete query to delete around 4 million
>> >> > records.
>> > It
>> >> > is only using 5% - 25% of the processor (mostly the lower end
>> >> > with
> some
>> >> > spikes). This system, for the most part, is strictly used as a
>> >> > database
>> >> > server so I'd like to be able to set it so that DB2 can use at least >> >> > 50%
>> > of
>> >> > the processor, but better yet up to 80% would be great. Does anyone >> > know
>> >> > how or if I can accomplish this?
>> >>
>> >> Chances are very good that this is a I/O system limitation, not a CPU >> >> limitation. Your CPUs are likely waiting for IO to complete.
>> >>
>> >> What kind of disks are you writing to, and how are they configured
>> >> (RAID5, RAID1, etc)? From a database perspective, you may be getting >> >> poor performance with database logging, or poor performance for async >> >> writes in the database.
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>