DB2物理布局 - 需要注释 [英] DB2 physical layout - comments needed

问题描述

DB2 500G数据库，Wintel，负载很重的OLTP（每天5M +交易;

所有交易都非常小，所有选择都受到控制

（无ad-hoc） ，99％的选择都非常小（没有表格扫描，扫描的索引扫描非常有限））。写性能

通常比读取性能更重要，读取性能

重度查询（带有表扫描的查询）并不重要。


问题：如何跨物理磁盘传播数据以实现最高性能？$ / b

建议布局：登录单独的镜像，系统开启单独的

镜像，tempspace在单独的镜像（？）上，所有其他磁盘

是''数据磁盘''组织为镜像 - 见下文。表是

分为小和大。所有小的都位于

一个表空间，该表空间具有相关的缓冲池等于表空间

大小（应该确保99.9999 ...％命中率）。大（历史）

有单独的数据和索引表空间，

单独的缓冲池。所有表空间（大和小）

分布在所有''数据磁盘'上，每个表空间具有

每个磁盘上相同大小的容器。重要提示：因为

加载是99.9％随机访问而不是顺序访问，

以减少磁盘级别的争用，建议使用

PREFETCHSIZE完全等于EXTENTSIZE（！）。由于这个

配置，每次读取操作预计会在不触及邻居的情况下影响一个磁盘。由于在所有磁盘上平均分配表空间
，每个磁盘上的负载将在统计上平衡。


任何评论（特别是解释似乎是错误的）

将不胜感激。

DB2 500G database, Wintel, heavily loaded OLTP (5M+ transactions a day;
all transactions are extremely small, all selects are controlled
(no ad-hoc), 99% of all selects are very small (no table
scans, index scans are very limited in size) ). Write performance
is generally more important than read performance, read performance
of heavy queries (the ones with table scans) is not important at all.

Question: how to spread data across physical disks to achieve
maximum performance?

Proposed layout: logs on separate mirror, system on separate
mirror, tempspace on separate mirror (?), all other disks
are ''data disks'' organized as mirrors - see below. Tables are
divided into ''small'' ones and ''big'' ones. All small ones sit in
one tablespace that has associated bufferpool equal to tablespace
size (that should ensure 99.9999...% hit ratio). Big (historical)
ones have separate tablespaces for data and indexes, with
separate bufferpools. All tablespaces (both big and small)
are spread over all ''data disks'' with each tablespace having
one container of equal size on each disk. Important: as
load is 99.9% random access opposed to sequential access,
to reduce contention at disk level it is proposed to have
PREFETCHSIZE exactly equal to EXTENTSIZE (!). As a result of this
configuration, each read operation is expected to affect exactly
one disk without touching neighbours; due to equal spreading
of tablespaces across all disks, load on every disk will
be statistically balanced.

Any comments (especially with explanations what seems to be wrong)
will be appreciated.

推荐答案

" Nobody" < IP ******* @ yahoo.com>在消息中写道

新闻：e5 ************************** @ posting.google.c om ...

"Nobody" <ip*******@yahoo.com> wrote in message
news:e5**************************@posting.google.c om...

DB2 500G数据库，Wintel，重负载OLTP（每天5M +交易;
所有交易都非常小，所有选择都受到控制
（无ad-hoc），99％所有选择都非常小（没有表格扫描，索引扫描的大小非常有限））。写性能一般比读取性能更重要，读取性能繁重的查询（带有表扫描的查询）并不重要。

问题：如何传播数据跨物理磁盘实现最大性能？

建议布局：登录单独的镜像，系统在单独的镜像，tempspace在单独的镜像（？），所有其他磁盘上/>是''数据磁盘''组织为镜像 - 见下文。表格分为小和大。所有小的都位于一个表空间中，该表空间具有与表空间大小相关的缓冲池（应该确保99.9999 ...％的命中率）。大（历史）
有单独的表空间用于数据和索引，具有单独的缓冲池。所有表空间（大小）都分布在所有数据磁盘上，每个表空间在每个磁盘上都有一个相同大小的容器。重要提示：因为
加载是99.9％随机访问而不是顺序访问，为了减少磁盘级别的争用，建议将PREFETCHSIZE完全等于EXTENTSIZE（！）。由于这种配置，每次读取操作都会在不触及邻居的情况下完全影响一个磁盘;由于所有磁盘上的表空间均等分布，每个磁盘上的负载将在统计上达到平衡。

任何评论（特别是解释似乎有什么错误）将不胜感激。

DB2 500G database, Wintel, heavily loaded OLTP (5M+ transactions a day;
all transactions are extremely small, all selects are controlled
(no ad-hoc), 99% of all selects are very small (no table
scans, index scans are very limited in size) ). Write performance
is generally more important than read performance, read performance
of heavy queries (the ones with table scans) is not important at all.

Question: how to spread data across physical disks to achieve
maximum performance?

Proposed layout: logs on separate mirror, system on separate
mirror, tempspace on separate mirror (?), all other disks
are ''data disks'' organized as mirrors - see below. Tables are
divided into ''small'' ones and ''big'' ones. All small ones sit in
one tablespace that has associated bufferpool equal to tablespace
size (that should ensure 99.9999...% hit ratio). Big (historical)
ones have separate tablespaces for data and indexes, with
separate bufferpools. All tablespaces (both big and small)
are spread over all ''data disks'' with each tablespace having
one container of equal size on each disk. Important: as
load is 99.9% random access opposed to sequential access,
to reduce contention at disk level it is proposed to have
PREFETCHSIZE exactly equal to EXTENTSIZE (!). As a result of this
configuration, each read operation is expected to affect exactly
one disk without touching neighbours; due to equal spreading
of tablespaces across all disks, load on every disk will
be statistically balanced.

Any comments (especially with explanations what seems to be wrong)
will be appreciated.

鉴于您的应用程序描述，我不希望发生很多（如果有的话）

预取活动。所以我不确定我会担心

使PREFETCHSIZE完全等于EXTENTSIZE。预取通常限制为表空间或索引空间扫描
。

给定小的缓冲池空间量。表格，你不需要

来担心磁盘上的数据放置。但我会尝试将

索引放在与表不同的磁盘上。

Given your application description, I would not expect much (if any)
prefetch activity to be happening. So I am not sure that I would worry about
making PREFETCHSIZE exactly equal to EXTENTSIZE. Prefetch is usually limited
to tablespace or index space scans.

Given the amount of buffer pool space for the "small" tables, you don''t need
to worry much about data placement on the disk. But I would try to put
indexes on different disks than tables.

" Mark A" < [e-mail]>在消息新闻中写道：< Gx ***************** @ news.uswest.net> ...

谢谢你的回答，马克。

"Mark A" <[e-mail]> wrote in message news:<Gx*****************@news.uswest.net>...
Thanks for the answer, Mark.

鉴于你的应用程序描述，我不希望发生很多（如果有的话）
预取活动。所以我不确定我是否会担心让PREFETCHSIZE完全等于EXTENTSIZE。预取通常限于表空间或索引空间扫描。
这正是这个决定背后的原因。
给定小的缓冲池空间量。表格，您不需要担心磁盘上的数据放置。但是我会尝试将
索引放在不同于表的磁盘上。

Given your application description, I would not expect much (if any)
prefetch activity to be happening. So I am not sure that I would worry about
making PREFETCHSIZE exactly equal to EXTENTSIZE. Prefetch is usually limited
to tablespace or index space scans. That was exactly the reasoning behind this decision.
Given the amount of buffer pool space for the "small" tables, you don''t need
to worry much about data placement on the disk. But I would try to put
indexes on different disks than tables.

这是我们需要做出的另一个设计决定：要么分配给
分配特定表/索引的磁盘，或者在所有磁盘上传播所有表/索引。我们决定支持

后者，因为它提供了更好的平衡。或者你是建议有两套磁盘 - 一个用于所有索引（包括

所有索引遍布此集合的所有磁盘），以及一个


表示数据（所有数据都分布在这套数据的所有磁盘上）？

如果是 - 为什么你这比我们的模型更好只用

两个索引/数据的一组磁盘？

That was another design decision we needed to make: either to
allocate disks for specific tables/indexes, or to spread all
the tables/indexes over all disks. We decided in favor of the
latter because it provides much better balancing. Or you are
proposing to have 2 sets of disks - one for all indexes (with
all indexes spread over all disks of this set), and one
for data (with all data spread over all disks of this set)?
If yes - why do you this it is better then our model with
only one set of disks for both indexes/data?

> >给定小的缓冲池空间量。表格，你不需要

> > Given the amount of buffer pool space for the "small" tables, you don''t
need

担心磁盘上的数据放置。但我会尝试将
索引放在与表不同的磁盘上。

to worry much about data placement on the disk. But I would try to put
indexes on different disks than tables.

这是我们需要做出的另一个设计决策：要么为特定的表/索引分配磁盘，要么将所有表/索引传播到所有磁盘上。我们决定支持
后者，因为它提供了更好的平衡。或者你建议有两组磁盘 - 一组用于所有索引（所有索引都分布在这一组的所有磁盘上），一组用于数据（所有数据都分布在一起）这套的所有磁盘）？
如果是的话 - 为什么你这比我们的模型更好，只有一组磁盘用于索引/数据？

That was another design decision we needed to make: either to
allocate disks for specific tables/indexes, or to spread all
the tables/indexes over all disks. We decided in favor of the
latter because it provides much better balancing. Or you are
proposing to have 2 sets of disks - one for all indexes (with
all indexes spread over all disks of this set), and one
for data (with all data spread over all disks of this set)?
If yes - why do you this it is better then our model with
only one set of disks for both indexes/data?

您不需要在另一组

设置的一组磁盘和表上拥有所有索引。但是，如果您可以确保对于给定的表，表数据与该表的索引位于一个单独的磁盘上，那就没问题了。但是，如果缓冲池与

数据一样大，那么
不会过度杀戮。

You don''t need to have all indexes on one set of disks and tables on another
set. But if you could ensure that for a given table, the table data was on a
separate disk from the indexes for that table, that would be fine. But I
would not overkill this in situations were the bufferpool is as large as the
data.

这篇关于DB2物理布局 - 需要注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！