I have a new box that was purchased specificaly as our dedicated db2
server. The drive setup is
1 single ide device holding the filesystem
2 8 disk RAID 5 arrays one for data, one for logging
The problem I am having is that if the log directory is on the RAID,
database performance is heavily compromised. If I switch the log directory
to the single IDE drive perfomance is fine. I have read up on tablespaces
and extent/prefetch sizes and RAID systems and none of that seems to apply
here allthough I have been experimanting with those settings. The data in
the tablespaces doesn't appear to be the problem, it appears to be only
connected to logging. My test is understandably artificial I just devised
it when I was noticing performance problems with our more complex
applications. I script out a 10,000 row insert into a single table. I then
run it on my workstation, our production server logs on the RAID and
production logs on single the IDE.
Workstation 1400 inserts/s
Logs on RAID 135 inserts/s
Logs on IDE 1700 inserts/s
Interestingly enough if I save the commit until the entire 10,000 rows are
inserted, even with the logs on the RAID I get 1900 inserts/s.
Unfortunatly for our existing application autocommit is the norm and is
required. In the future as we become more comfortable with a transactional
database that will change.

Todd Huish

Simprini wrote:
[color=blue]
> I have a new box that was purchased specificaly as our dedicated db2
> server. The drive setup is
> 1 single ide device holding the filesystem
> 2 8 disk RAID 5 arrays one for data, one for logging
> The problem I am having is that if the log directory is on the RAID,
> database performance is heavily compromised. If I switch the log directory
> to the single IDE drive perfomance is fine.
> ...
> Todd Huish
>[/color]
Without detailed environment description it would be difficult to tune
the application for your criteria - and that would not be optimal for
other types of workload, but...

1. acquire (borrow) another 2 IDE drives - preferably of same capacity
and additional IDE controller;
2. connect additional IDE drives as PRIMARY on separate IDE channels
of the additional IDE controller;
3. using GUI interface or diskpart command line tool (real men use
command line) create a stripped volume across 2 dynamic volumes.
4. Instruct DB2 to use ase new stripped volume as location of your logs;
5. Rerun the benchmark.

If you are satisfied with the performance - consider buying IDE
controller which implements stripping in hardware - thus eliminating
Windows software stripping overhead.
Now concentrate on setting your RAID 5 parameters for optimal
performance (DMS vs SMS, striping containers, number of containers,
extend and prefetch size tuned to hardware stripe size on Raid array
etc etc.
If using SMS containers, consider use of db2empfa tool - and so on...

Jan M. Nelken

"Simprini" <todd.huish@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:3aa1efc0452158a2a80d957377af1aaa@localhost.ta lkaboutdatabases.com...[color=blue]
> I have a new box that was purchased specificaly as our dedicated db2
> server. The drive setup is
> 1 single ide device holding the filesystem
> 2 8 disk RAID 5 arrays one for data, one for logging
> The problem I am having is that if the log directory is on the RAID,
> database performance is heavily compromised. If I switch the log directory
> to the single IDE drive perfomance is fine. I have read up on tablespaces
> and extent/prefetch sizes and RAID systems and none of that seems to apply
> here allthough I have been experimanting with those settings. The data in
> the tablespaces doesn't appear to be the problem, it appears to be only
> connected to logging. My test is understandably artificial I just devised
> it when I was noticing performance problems with our more complex
> applications. I script out a 10,000 row insert into a single table. I then
> run it on my workstation, our production server logs on the RAID and
> production logs on single the IDE.
> Workstation 1400 inserts/s
> Logs on RAID 135 inserts/s
> Logs on IDE 1700 inserts/s
> Interestingly enough if I save the commit until the entire 10,000 rows are
> inserted, even with the logs on the RAID I get 1900 inserts/s.
> Unfortunatly for our existing application autocommit is the norm and is
> required. In the future as we become more comfortable with a transactional
> database that will change.
>
> Todd Huish
>[/color]
Please consider the following:

1. Dramatically increase the size of your log buffer via the "logbufsz"
database parameter.

2. Increase the "mincommit" database parameter. This parameter allows you to
delay the writing of log records to disk until a minimum number of commits
have been performed. This delay can help reduce the database manager
overhead associated with writing log records. As a result, this will improve
performance when you have multiple applications running against a database
and many commits are requested by the applications within a very short time
frame.

3. Increase the size and number of your log files. Having a small number of
log files can cause problems if DB2 needs time to archive them often. You
can experiment with these parameters.

4.. I would also take a look at your RAID configuration to see if it can be
improved upon. Sounds like you are taking a very large hit vs. IDE. Some
difference should be apparent, but not as much as you are seeing. I doubt
this part has anything to do with DB2. You are probably not noticing the
data writes because of the DB2 bufferpools, which do asynchronous I/O for
the table and index pages written to disk.

Mark A wrote:
[color=blue]
> 2. Increase the "mincommit" database parameter. This parameter allows you to
> delay the writing of log records to disk until a minimum number of commits
> have been performed. This delay can help reduce the database manager
> overhead associated with writing log records. As a result, this will improve
> performance when you have multiple applications running against a database
> and many commits are requested by the applications within a very short time
> frame.[/color]

Be very careful with MINCOMMIT. This can _slow down_ transactions if the
transaction rate is not high enough. i.e., a COMMIT will _wait_ until
at least MINCOMMIT commit statements have been issued OR 1 second has
passed since the COMMIT was issued. Unless you have a high transaction
load this could hurt you.

[color=blue]
> 3. Increase the size and number of your log files. Having a small number of
> log files can cause problems if DB2 needs time to archive them often. You
> can experiment with these parameters.
>
> 4.. I would also take a look at your RAID configuration to see if it can be
> improved upon. Sounds like you are taking a very large hit vs. IDE. Some
> difference should be apparent, but not as much as you are seeing. I doubt
> this part has anything to do with DB2. You are probably not noticing the
> data writes because of the DB2 bufferpools, which do asynchronous I/O for
> the table and index pages written to disk.[/color]

You might also consider using RAID-1 for your logs. There is certainly a
write performance hit for RAID-5, although this can be mitigated if your
RAID controller has a battery-backed cache.



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

> > 2. Increase the "mincommit" database parameter. This parameter allows
you to[color=blue][color=green]
> > delay the writing of log records to disk until a minimum number of[/color][/color]
commits[color=blue][color=green]
> > have been performed. This delay can help reduce the database manager
> > overhead associated with writing log records. As a result, this will[/color][/color]
improve[color=blue][color=green]
> > performance when you have multiple applications running against a[/color][/color]
database[color=blue][color=green]
> > and many commits are requested by the applications within a very short[/color][/color]
time[color=blue][color=green]
> > frame.[/color]
>
> Be very careful with MINCOMMIT. This can _slow down_ transactions if the
> transaction rate is not high enough. i.e., a COMMIT will _wait_ until
> at least MINCOMMIT commit statements have been issued OR 1 second has
> passed since the COMMIT was issued. Unless you have a high transaction
> load this could hurt you.
>[/color]
I don't think you read the original problem correctly.

Simprini wrote:[color=blue]
> I have a new box that [...] 2 8 disk RAID 5 arrays[/color]

http://www.baarf.com/

Also Google at groups.google.com in comp.databases.informix for Author
'Art Kagel' and 'RAID 5'.

YMMV.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Mark A wrote:
[color=blue][color=green]
>>Be very careful with MINCOMMIT. This can _slow down_ transactions if the
>>transaction rate is not high enough. i.e., a COMMIT will _wait_ until
>>at least MINCOMMIT commit statements have been issued OR 1 second has
>>passed since the COMMIT was issued. Unless you have a high transaction
>>load this could hurt you.
>>[/color]
>
> I don't think you read the original problem correctly.[/color]

I do understand the original problem. I'm just not a fan of tweaking the
dials and knobs for a specific, "artificial" (original poster's word) test,
when it's easy to misunderstand the implications of the tweak.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

This is also not what I am trying to accomplish. My artificial test was
devised only after seeing a major speed hit in switching to the new
machine and testing my data conversion from mysql to db2. On my local
workstation I was converting 60 full records/s (each record is the result
of a many multi table joins) which I then munge and insert into the new
db2 structure. On the new machine I was down to 14 full records/s. This is
what tipped me off to the problem and when I changed everything I could I
just simplified the entire problem down to something I could use as a
quick "benchmark" as I changed options. From the other posts I am seeing
it is going to be far more complicated than my boss is going to want to
hear.

"Simprini" <todd.huish@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:8da9103ea83be57c271fba8ed4332a8d@localhost.ta lkaboutdatabases.com...[color=blue]
> This is also not what I am trying to accomplish. My artificial test was
> devised only after seeing a major speed hit in switching to the new
> machine and testing my data conversion from mysql to db2. On my local
> workstation I was converting 60 full records/s (each record is the result
> of a many multi table joins) which I then munge and insert into the new
> db2 structure. On the new machine I was down to 14 full records/s. This is
> what tipped me off to the problem and when I changed everything I could I
> just simplified the entire problem down to something I could use as a
> quick "benchmark" as I changed options. From the other posts I am seeing
> it is going to be far more complicated than my boss is going to want to
> hear.
>[/color]

What is not what you are trying to accomplish? Please include some of the
post you are responding to.

On Thu, 13 May 2004 12:20:22 -0600, Mark A <ma@switchboard.net> wrote:
[color=blue]
> "Simprini" <todd.huish@nohighvolumeemaildeployment.theselling source.com>
> wrote in message
> news:8da9103ea83be57c271fba8ed4332a8d@localhost.ta lkaboutdatabases.com...[color=green]
>> This is also not what I am trying to accomplish. My artificial test was
>> devised only after seeing a major speed hit in switching to the new
>> machine and testing my data conversion from mysql to db2. On my local
>> workstation I was converting 60 full records/s (each record is the
>> result
>> of a many multi table joins) which I then munge and insert into the new
>> db2 structure. On the new machine I was down to 14 full records/s. This
>> is
>> what tipped me off to the problem and when I changed everything I could
>> I
>> just simplified the entire problem down to something I could use as a
>> quick "benchmark" as I changed options. From the other posts I am seeing
>> it is going to be far more complicated than my boss is going to want to
>> hear.
>>[/color]
>
> What is not what you are trying to accomplish? Please include some of the
> post you are responding to.
>
>[/color]

I apologize for that. I posted through a website without realizing that
it was going to a newsgroup. I now have a real news client which should
make this much nicer. I was responding to

On Wed, 12 May 2004 23:25:05 -0700, Ian <ianbjor@mobileaudio.com> wrote:
[color=blue]
> Mark A wrote:
>[color=green][color=darkred]
>>> Be very careful with MINCOMMIT. This can _slow down_ transactions if
>>> the
>>> transaction rate is not high enough. i.e., a COMMIT will _wait_ until
>>> at least MINCOMMIT commit statements have been issued OR 1 second has
>>> passed since the COMMIT was issued. Unless you have a high transaction
>>> load this could hurt you.
>>>[/color]
>> I don't think you read the original problem correctly.[/color]
>
> I do understand the original problem. I'm just not a fan of tweaking the
> dials and knobs for a specific, "artificial" (original poster's word)
> test,
> when it's easy to misunderstand the implications of the tweak.[/color]

My issue is that even in a real world application I'm seeing 25% the
performance I was getting otherwise. Much of this has, no doubt, to do
with my inexperience with db2. The sinking suspicion in the pit of my
stomach is that we have a server that is not going to perform the way I
need
it to.

Todd Huish

On Thu, 13 May 2004 05:18:29 GMT, Jonathan Leffler
<jleffler@earthlink.net> wrote:
[color=blue]
> Simprini wrote:[color=green]
>> I have a new box that [...] 2 8 disk RAID 5 arrays[/color]
>
> http://www.baarf.com/
>
> Also Google at groups.google.com in comp.databases.informix for Author
> 'Art Kagel' and 'RAID 5'.
>
> YMMV.
>[/color]

Now that was an interesting read. Ostensibly I don't disagree however
convincing
my boss of this sort of thing is just not going to happen. The weirdest
thing
about this entire mess is that when I throw mysql onto this machine it
beats the
performance of our existing mysql db server by a large margin. This is why
honestly I never thought in a million years I could throw this kind of
hardware
at a database and not see improvements.

Todd Huish

On Wed, 12 May 2004 16:27:02 -0400, Jan M. Nelken
<Unknown.User@Invalid.Domain> wrote:
[color=blue]
> Simprini wrote:
>[color=green]
>> I have a new box that was purchased specificaly as our dedicated db2
>> server. The drive setup is
>> 1 single ide device holding the filesystem
>> 2 8 disk RAID 5 arrays one for data, one for logging
>> The problem I am having is that if the log directory is on the RAID,
>> database performance is heavily compromised. If I switch the log
>> directory
>> to the single IDE drive perfomance is fine. ...
>> Todd Huish
>>[/color]
> Without detailed environment description it would be difficult to tune
> the application for your criteria - and that would not be optimal for
> other types of workload, but...
>
> 1. acquire (borrow) another 2 IDE drives - preferably of same capacity
> and additional IDE controller;
> 2. connect additional IDE drives as PRIMARY on separate IDE channels
> of the additional IDE controller;
> 3. using GUI interface or diskpart command line tool (real men use
> command line) create a stripped volume across 2 dynamic volumes.
> 4. Instruct DB2 to use ase new stripped volume as location of your logs;
> 5. Rerun the benchmark.
>
> If you are satisfied with the performance - consider buying IDE
> controller which implements stripping in hardware - thus eliminating
> Windows software stripping overhead.
> Now concentrate on setting your RAID 5 parameters for optimal
> performance (DMS vs SMS, striping containers, number of containers,
> extend and prefetch size tuned to hardware stripe size on Raid array etc
> etc.
> If using SMS containers, consider use of db2empfa tool - and so on...
>
> Jan M. Nelken[/color]


In this kind of a setup, if I were to get 4 drives and do 2
2 drive stripes and then mirror them (RAID 1+0), all in hardware
would that be better than 2 single drives mirrored? What I am
getting at is, is this a RAID 5 issue or a RAID striping in
general issue?

--
Todd Huish

Todd Huish wrote:[color=blue]
>
> In this kind of a setup, if I were to get 4 drives and do 2
> 2 drive stripes and then mirror them (RAID 1+0), all in hardware
> would that be better than 2 single drives mirrored? What I am
> getting at is, is this a RAID 5 issue or a RAID striping in
> general issue?
>[/color]
There is a different I/O pattern for DB2 logging versus DB2 accessing
containers.

Raid 0 (striping) has advantages and disadvantages - performance wise.
Raid 5 has its own advantages and disadvantaages - different from Raid
0, Raid 1, Raid 1+0, Raid 0+1 etc. etc.

Your example concentrates on tuning for inserts into table. without
knowing RAID-5 characteristic and you DB2 containers setup and DB2
configuration I can only rely on your statetment that moving logs out
of Raid-5 improved I/O performance.

This may only mean that your Raid-5 is not configured correctly, DB2 is
not configured correcly, database you are using (tables/containers) is
not configured correctly, OS is not configured correctly, any
permuation and combination of the above - or given your I.O
configuartion and workload you are measuring - you have a saturated
(from I/O ppoint of view) system.
Remedy is to spread I/O across multiple controillers/devices in such a
way that I/O active subsystems (logging and tables you are inserting to
- for example) can be as parallel as possible.
Given that logs on separate IDE disk areimproving yur throuput I
indicated that wis relative low investment you can significantly
improve the throuput.
Raid 0 has performance advantage in sequential writes. If you were to
mirror those stripped disks - you would improve reliability as well.

Designing physical layout of DB2 objects (tablespaces and containers)
requires some knowledge of the underlying I/O subsystem for best results.

You need to post details of tablespace and containers layout, registry
settings, physical characteristic of Raid 5, table layout, DBM
configuration and DB configuration for starters ...

Jan M. Nelken

"Todd Huish" <todd.huish@nohighvoumeemaildeployment.thesellings ource.com> wrote in message news:<opr7yhp4ut5usdun@ds03.tss>...
[color=blue]
>
> Now that was an interesting read. Ostensibly I don't disagree however
> convincing
> my boss of this sort of thing is just not going to happen. The weirdest
> thing
> about this entire mess is that when I throw mysql onto this machine it
> beats the
> performance of our existing mysql db server by a large margin. This is why
> honestly I never thought in a million years I could throw this kind of
> hardware
> at a database and not see improvements.
>
> Todd Huish[/color]

Coming from a migration from mysql to DB2, I've experienced similar
sluggishness. One major problem is that there isn't much performance
tuning to be done with mySQL; it's just nice and fast (although very
limited in functionality). There's a lot of work you need to do with
DB2 to get it up to speed (although when that's done, it starts to
beat mySQL - depending on what you want it to do, of course...).
Worrying about raid config may be one of the last things to concern
yourself with - there's all the internal tuning to sort first