日志和RAID5子系统 [英] Logs and a RAID5 subsystem
问题描述
我有一个专门购买的新盒子作为我们专用的db2
服务器。驱动器设置是
1单个ide设备持有文件系统
2 8个磁盘RAID 5阵列一个用于数据，一个用于记录
问题我有的是，如果日志目录在RAID上，那么
数据库性能会严重受损。如果我将日志目录
切换到单个IDE驱动器性能很好。我已经阅读了表格空间
和范围/预取大小和RAID系统，这些似乎都不适用
尽管我一直在尝试这些设置。表空间中的数据似乎不是问题，它似乎只是连接到日志记录的
。当我注意到我们更复杂的
应用程序的性能问题时，我的测试是可以理解的，我只是设计了
。我将10000行插入到一个表中。然后我在我的工作站上运行
，我们的生产服务器在IDE上登录RAID和
生产日志。
Workstation 1400 insert / s
登录RAID 135插入/ s
登录IDE 1700插入/ s
，即使RAID上的日志我得到1900次插入/秒。
不幸的是我们现有的应用程序autocommit是常态并且是
需要
。在未来，我们会更加适应将要更改的交易
数据库。
Todd Huish
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写道：
Simprini wrote:
我有一个特别购买的新盒子作为我们专用的db2
服务器。驱动器设置是一个装有文件系统的单一设备
2个磁盘RAID 5阵列一个用于数据，一个用于记录
我遇到的问题是如果日志目录在RAID，数据库性能严重受损。如果我将日志目录切换到单个IDE驱动器性能很好。
...
Todd Huish
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
如果没有详细的环境描述，它会难以调整
应用程序符合您的标准 - 这对于其他类型的工作量来说不是最佳的，但是......
1.获取（借用）另外2个IDE驱动器 - 最好具有相同容量
和额外的IDE控制器;
2.将其他IDE驱动器连接为PRIMARY IDE频道
额外的IDE控制器;
3.使用GUI界面或diskpart命令行工具（真人使用
命令行）创建2个动态卷上的剥离卷。
4.指示DB2使用新的剥离卷作为日志的位置;
5.重新运行基准测试。
如果您对性能感到满意 - 考虑购买IDE
控制器，它实现硬件剥离 - 因此消除了
Windows软件剥离开销。
现在专注于设置RAID 5参数以获得最佳的性能（DMS与SMS，条带化容器，容器数量，
扩展和预取大小调整为Raid阵列上的硬件条纹大小
等等。
如果使用SMS容器，请考虑使用db2empfa工具 - 依此类推......
Jan M. Nelken
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>
在留言中写道
news：3a ********* *********************@localhost.ta lkaboutdatabases.com ...
"Simprini" <to********@nohighvolumeemaildeployment.theselling source.com>
wrote in message
news:3a******************************@localhost.ta lkaboutdatabases.com...
我有一个特别购买的新盒子作为我们的专用的db2
服务器。驱动器设置是一个装有文件系统的单一设备
2个磁盘RAID 5阵列一个用于数据，一个用于记录
我遇到的问题是如果日志目录在RAID，数据库性能严重受损。如果我将日志目录切换到单个IDE驱动器性能很好。我已经阅读了表格空间和范围/预取大小和RAID系统，这些似乎都没有应用
尽管我一直在试验这些设置。表空间中的数据似乎不是问题，它似乎只与记录有关。当我注意到我们更复杂的应用程序的性能问题时，我的测试是可以理解的，我只是设计了它。我将10000行插入到一个表中。然后我在我的工作站上运行它，我们的生产服务器在IDE上登录RAID和
生产日志。
工作站1400插入/ s
登录RAID 135插入/ s
登录IDE 1700插入/ s
有趣的是，如果我保存提交直到插入整个10,000行，即使RAID上的日志我得到1900次插入/秒。<不幸的是，我们现有的应用程序自动提交是常态，并且是必需的。在未来，我们会对交易数据库变得更加舒适。•Todd Huish
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
请考虑以下事项：
1.通过logbufsz
数据库参数显着增加日志缓冲区的大小。
2。增加mincommit数据库参数。此参数允许您延迟将日志记录写入磁盘，直到执行了最小数量的提交
。此延迟可以帮助减少与写入日志记录相关的数据库管理器开销。因此，当您有多个针对数据库运行的应用程序时，这将提高性能
并且应用程序会在很短的时间内请求许多提交
frame。
3.增加日志文件的大小和数量。如果DB2需要时间来经常存档它们，那么拥有少量的
日志文件会导致问题。你可以试试这些参数。
4 ..我还会看看你的RAID配置，看看它是否可以
改进了。听起来你比IDE更受欢迎。一些
的差异应该是显而易见的，但不会像你看到的那么多。我怀疑
这部分与DB2有什么关系。您可能没有注意到由于DB2缓冲池而导致的数据写入数据，这些缓冲池为写入磁盘的表和索引页执行异步I / O.
。
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写道：
Mark A wrote:
2.增加mincommit"数据库参数。此参数允许您延迟将日志记录写入磁盘，直到执行了最少数量的提交。此延迟有助于减少与写入日志记录相关的数据库管理器开销。因此，当您有多个针对数据库运行的应用程序时，这将提高性能，并且应用程序会在很短的时间内请求许多提交。
小心MINCOMMIT。如果
的交易率不够高，这可以减少交易。即，COMMIT将_wait_直到
至少已发出MINCOMMIT提交语句或自发出COMMIT以来1秒已经过了
。除非您有高额交易
加载，这可能会对您造成伤害。
3.增加日志文件的大小和数量。如果DB2需要时间来经常归档它们，那么拥有少量的日志文件会导致问题。您可以试验这些参数。
4 ..我还会看一下您的RAID配置，看看它是否可以改进。听起来你比IDE更受欢迎。一些差异应该是显而易见的，但并不像你看到的那么多。我怀疑这部分与DB2有什么关系。您可能没有注意到由于DB2缓冲池而导致的数据写入，这些缓冲池为写入磁盘的表和索引页执行异步I / O.
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.
您可能还考虑将RAID-1用于日志。 RAID-5的写入性能肯定会达到，但如果你的
RAID控制器有一个电池备份的缓存，可以减轻这种影响。
----- =通过Newsfeeds.Com发布，未经审查的Usenet新闻= -----
http://www.newsfeeds.com - 世界排名第一的新闻组服务！
----- ==超过100,000个新闻组 - 19不同服务器！ = -----
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! =-----
这篇关于日志和RAID5子系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
