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
13  1995 
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
"Simprini" <to********@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:3a******************************@localhost.ta lkaboutdatabases.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.
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! =-----
> > 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.
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! =-----
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" <to********@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:8d******************************@localhost.ta lkaboutdatabases.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.
On Thu, 13 May 2004 12:20:22 -0600, Mark A <ma@switchboard.net> wrote: "Simprini" <to********@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:8d******************************@localhost.ta lkaboutdatabases.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*****@mobileaudio.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
On Thu, 13 May 2004 05:18:29 GMT, Jonathan Leffler
<jl******@earthlink.net> wrote: Simprini wrote: I have a new box that [...] 2 8 disk RAID 5 arrays
http://www.baarf.com/
Also Google at groups.google.com in comp.databases.informix for Author
'Art Kagel' and 'RAID 5'.
YMMV.
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
<Un**********@Invalid.Domain> wrote: 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
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:
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?
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" <to********@nohighvoumeemaildeployment.thesellings ource.com> wrote in message news:<op**************@ds03.tss>...
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Malvin Su |
last post by:
We have a Fire880 box running Solaris2.8 and Oracle8.1.7.
And Use the DiskSuite to Create the RAID5 device md9, and mount it as
a file system.
Now When to create tablespace on the file system, the...
|
by: Andrew S. |
last post by:
I recently performed an SQL server 2000 installation. Other than
placing the program files on C: and data on D:, I saw no option to
install transaction logs in an alternate location.
What is...
|
by: babak |
last post by:
Hi everyone
I'm doing a project in Microsoft embedded Visual C++ 4.0 and I get the
following error when I try to compile:
LINK : fatal error LNK1195: target machine 'ARM' requires...
|
by: Prem K Mehrotra |
last post by:
I come from Oracle background. In Oracle, when one wants to do a point
in time
recovery, one can specify recover database until timestmap. Oracle's
database maps to a db2 subsystem, i.e., in...
|
by: Prem K Mehrotra |
last post by:
I come from Oracle background. I am trying to find a script (job)
which creates a db2 subsystem (create all system catalog tables etc),
create necessary "DBA" logins,so I can
create databases and...
|
by: Stanley Sinclair |
last post by:
(I have never been involved with storage hardware before this.)
Given several tablespaces with very different table size needs, eg,
one needs a 4k page, another a 32k page, and another deals with...
|
by: Ramachandran Subramanian |
last post by:
I am trying to clone a db2 subsystem DB2A to a new subsystem DB2B
both the lpars (A where DB2A runs and B where DB2B will run) share
the DASD.
I wanted to take an ADRDSSU dump of DB2A...
|
by: Jkaatz |
last post by:
I've recently upgraded from SQL 2000 to SQL 2005 and installed the latest service pack for SQL 2005.
The issue I'm having is that I created a simple package to move data from a flat file to a SQL...
|
by: peanutbuttercravings |
last post by:
I don't know much about db2 but I need to move a filesystem from a
striped logical volume to raid5? And are there any implications moving
the filesystems which hold db2 tables to sharks? Is there...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
