Logs and a RAID5 subsystem

[Simprini]

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

[Jan M. Nelken]

Simprini wrote:

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

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

[Mark A]

"Simprini" <to********@noh ighvolumeemaild eployment.these llingsource.com >
wrote in message
news:3a******** *************** *******@localho st.talkaboutdat abases.com...

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

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.

[Ian]

Mark A wrote:

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.
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.

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.

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! =-----

[Mark A]

> > 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.

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.

I don't think you read the original problem correctly.

[Jonathan Leffler]

Simprini wrote:

I have a new box that [...] 2 8 disk RAID 5 arrays

http://www.baarf.com/

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

YMMV.

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

[Ian]

Mark A wrote:

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.

I don't think you read the original problem correctly.

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! =-----

[Simprini]

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.

[Mark A]

"Simprini" <to********@noh ighvolumeemaild eployment.these llingsource.com >
wrote in message
news:8d******** *************** *******@localho st.talkaboutdat abases.com...

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.

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

[Todd Huish]

On Thu, 13 May 2004 12:20:22 -0600, Mark A <ma@switchboard .net> wrote:

"Simprini" <to********@noh ighvolumeemaild eployment.these llingsource.com >
wrote in message
news:8d******** *************** *******@localho st.talkaboutdat abases.com...

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.

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

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 <ia*****@mobile audio.com> wrote:
Mark A wrote:

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.

I don't think you read the original problem correctly.

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.

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