
平台论坛博客文库

› 论坛 › 数据库技术 › DB2 › 如何较准确的规划表空间大小

如何较准确的规划表空间大小 [复制链接]

HunterBlob

白手起家

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2004-09-16 15:33 |只看该作者 |倒序浏览

现在我要建表空间，里面要放数据。我想问的是有没有好的经验或方法并且较迅速的估计我的数据大概可以用多少表空间？

比如我有多张表，每张表有1000000条记录。是不是必须将每条记录的精确长度计算出来啊。另外每条记录所使用的字节数×表记录数得到的空间是不是一定是占用如此大小的表空间啊。。大家对此有何经验或方法？

文库|博客

huyuhui001

版主

论坛徽章:: 0

2楼 [报告]

发表于 2004-09-17 08:09 |只看该作者

如何较准确的规划表空间大小

DB2和oracle都有自带的预估表容量的工具。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

mymm

荣誉版主

论坛徽章:: 0

3楼 [报告]

发表于 2004-09-17 22:12 |只看该作者

如何较准确的规划表空间大小

估计表的空间需求
数据库对象的大小估计不可能做到很精确。磁盘碎片、空闲空间以及使用变长列所造成的额外开销都使大小估计变得十分困难，因为可能的列类型和列长度的范围实在是太广了。在最初估计数据库大小后，创建一个测试数据库，并用有代表性的数据填充它。
在“控制中心”中，可存取许多专门辅助您确定各种数据库对象的大小需求的实用程序：
可选择一个对象，然后使用“估计大小”实用程序。此实用程序可告诉您现存对象（例如，表）的当前大小。在更改该对象之后，该实用程序可以对该对象计算出新的估计值。该实用程序可以帮助您估计存储器需求（将未来的增长考虑在内）。它不仅仅是只能估计对象的大小。它还提供对象可能的大小范围：最小大小（基于当前值）和可能的最大大小。
可通过使用“显示相关的”对话来确定对象之间的关系。
可选择实例中的任何一个数据库对象，然后请求“生成 DLL”。此功能使用 db2look 实用程序来生成数据库的数据定义语句。有关此实用程序的详情，参考 Command Reference。
在这两种情况下，可使用“显示 SQL”或“显示命令”按钮。也可将生成的 SQL 语句或命令作为脚本文件保存，以便稍后使用。所有这些实用程序都有联机帮助可向您提供帮助。
在制订物理数据库需求计划时，应考虑这些实用程序。
估计数据库的大小时，必须考虑下列各项的影响：
系统目录表
用户表数据
长整数字段数据
大对象 (LOB) 数据
索引空间
不讨论与下列各项相关的空间需求：
本地数据库目录文件
系统数据库目录文件
操作系统所需的文件管理额外开销，包括：
文件块大小
目录控制空间
系统目录表
创建数据库时，会创建系统目录表。当将数据库对象和特权添加至该数据库时，这些系统表将增大。最初，这些系统表使用大约 3.5 MB 的磁盘空间。
为目录表分配的空间容量取决于表空间的类型和包含这些目录表的表空间的数据块大小。例如，若使用数据块大小为 32 的 DMS 表空间，则目录表空间最初将被分配 20MB 的空间。有关详情，参见设计和选择表空间。注意: 对于含多个分区的数据库，目录表只驻留在发出 CREATE DATABASE 命令的分区上。目录表的磁盘空间仅对于该分区才是必需的。
用户表数据
在缺省情况下，表数据存储在 4KB 页上。每一页（不管页大小如何）都包含 76 个字节的数据库管理程序额外开销。这留下了 4020 字节来存放用户数据（即行），虽然 4 KB 页上的行的长度不能超过 4005 字节。一行不会横跨多页。当使用 4KB 的页大小时，最多可有 500 列。
表数据页不包含用 LONG VARCHAR、LONG VARGRAPHIC、BLOB、CLOB 或 DBCLOB 数据类型定义的列的数据。但是，一个表数据页中的行的确包含这些列的描述符。（有关估计确实包含这些数据类型的表对象的空间所需的详情，参见长整数字段数据和大对象 (LOB) 数据。）
通常，以“首个合适”次序将行插入到表中。（使用空闲空间映象）搜索文件，查找大小足以存放该新行的第一个可用空间。当更新一行时，除非该页上所剩的空间不足以包含它，否则在原位置进行更新。若所剩空间不足以包含新行，则在原始行位置创建一个记录，以指向更新后的行在表文件中的新位置。
若调用 ALTER TABLE APPEND ON 语句，则将一直追加数据，且不保留关于数据页上任何空闲空间的信息。有关此语句的详情，参考 SQL Reference。
对于数据库中的每个用户表，可以通过如下计算公式来估计 4KB 页数：
ROUND DOWN(4020/(平均行大小 + 10)) = records_per_page
然后，将结果插入：
(number_of_records/records_per_page) * 1.1 = number_of_pages
其中，平均行大小是平均列大小的总和（有关每一列的大小的详情，参考 SQL Reference 中的 CREATE TABLE 语句。），而乘数 "1.1" 表示额外开销。注意: 此公式只是提供一个估计值。若记录长度因碎片和溢出记录而改变，则估计的准确性将降低。
也可以选择创建具有 8 KB、16 KB 或 32 KB 页大小的缓冲池或表空间。在特定大小的表空间中创建的所有表都将具有匹配的页大小。假设使用 32 KB 的页大小，则单个表或索引对象的最大大小可达 512 GB。当使用 8 KB、16 KB 或 32 KB 的页大小时，最多可有 1012 列。对于 4 KB 的页大小，最大列数为 500。最大行宽也随页大小的不同而不同：
当页大小是 4 KB 时，行长度最大可为 4005 字节。
当页大小是 8 KB 时，行长度最大可为 8101 字节。
当页大小是 16 KB 时，行长度最大可为 16 293 字节。
当页大小是 32 KB 时，行长度最大可为 32 677 字节。
拥有更大的页大小有助于减小任何索引中的级别数。若使用执行随机行读写的 OLTP（联机事务处理）应用程序，则小一点的页大小会更好，这样，因不期望的行而浪费的缓冲区空间更少。若使用一次存取大量连续行的 DSS（决策支持系统）应用程序，则大一点的页大小会更好，这样可以减少读取特定行数所需的 I/O 请求数。当行大小小于页大小除以 255 的值时，会发生异常。在这种情况下，每个页上都有浪费的空间。（记住，每页最多只能有 255 行。）为减少浪费的空间，小一点的页大小可能更合适。
不能将备份复原为另一种页大小。
不能调入超过 755 列的 IXF 数据文件。有关将数据调入表和 IXF 数据文件的详情，参考 Data Movement Utilities Guide and Reference。
已说明临时表只能在它们自已的“用户临时”表空间类型的表空间中创建。没有缺省用户临时表空间。临时表不能含有 LONG 数据。当应用程序与数据库断开连接时，该表被隐式卸下，估计它们的空间需求时应将这一点考虑在内。
长整数字段数据
长整数字段数据存储在一个单独的表对象中，它的结构与其他数据类型不同（参见用户表数据和大对象 (LOB) 数据）。
数据存储在 32 KB 区域中，该区域被分成大小为 512 字节的“2 的幂” 倍的段。（因此，这些段可以是 512 字节、1024 字节、2048 字节，以此类推，直至 32 700 字节。）
长整数字段数据类型（LONG VARCHAR 或 LONG VARGRAPHIC）以使空闲空间易于收回的方式存储。有关分配和空闲空间的信息存储在 4KB 分配页中，它在整个对象中不经常出现。
对象中未使用的空间量取决于长整数字段数据的大小以及此大小是否在该数据的所有出现之处都是不变的。对于大于 255 字节的数据项，这个未使用的空间最大可为该长整数字段数据大小的 50%。
若该字符数据的长度小于页大小，且它适合含有该数据其余部分的记录，则应该使用 CHAR、GRAPHIC、VARCHAR 或 VARGRAPHIC 数据类型，而不要使用 LONG VARCHAR 或 LONG VARGRAPHIC。
大对象 (LOB) 数据
大对象 (LOB) 数据存储在两个单独的表对象中，这两个对象的结构与其他数据类型不同（参见用户表数据和长整数字段数据）。
要估计 LOB 数据所需的空间，需要考虑用来存储使用这些数据类型定义的数据的两个表对象：
LOB 数据对象
数据存储在 64 MB 区域中，该区域被分成大小为 1024 字节的“2 的幂” 倍的段。（因此，这些段可以是 1024 字节、2048 字节、4096 字节，以此类推，直至 64MB。）
要减少 LOB 数据所用的磁盘空间量，可在 CREATE TABLE 和 ALTER TABLE 语句上的 LOB 选项子句上使用 COMPACT 参数。COMPACT 选项将所需的磁盘空间量减至最小，方法是将 LOB 数据分成更小的段。此过程不涉及数据压缩，只是使用最接近 1 KB 极限的最小空间量。使用 COMPACT 选项可能导致在追加 LOB 值时性能下降。
包含在 LOB 数据对象中的空闲空间量将受到更新和删除活动量，以及要插入的 LOB 值的大小的影响。
LOB 分配对象
有关分配和空闲空间的信息存储在与实际数据分离的 4 KB 分配页中。这些 4KB 页的数目取决于数据量，包括为大对象数据分配的未使用空间。额外开销的计算如下：每 64 GB 一个 4 KB 页加上每 8 MB 一个 4KB 页。
若该字符数据的长度小于页大小，且它适合含有该数据其余部分的记录，则应该使用 CHAR、GRAPHIC、VARCHAR 或 VARGRAPHIC 数据类型，而不要使用 BLOB、CLOB 或 DBCLOB。
索引空间
对于每个索引，可以按如下公式估计所需的空间：
（平均索引关键字大小 + 8） * 行数 * 2
其中：
“平均索引关键字大小”是索引关键字中每列的字节计数。有关如何计算不同数据类型的列的字节计数的详情，参考 SQL Reference 中的 CREATE TABLE 语句。（估计 VARCHAR 和 VARGRAPHIC 列的平均列大小时，使用当前数据大小的平均值加一个字节。不要使用最大说明大小。）
乘数 "2" 表示额外开销，如非叶子页和空闲空间。
注意: 对于允许 NULL 的每个列，添加一个额外的字节以表示该空值指示符。
创建索引时，临时空间是必需的。在创建索引期间所需的最大临时空间可以按如下公式估计：
（平均索引关键字大小 + 8） * 行数 * 3.2
其中，乘数 "3.2" 表示索引额外开销，以及索引创建期间进行排序所需的空间。注意: 对于非唯一索引，存储重复的关键字项只需四个字节。上面显示的估计是假定没有重复项。因此以上公式可能会过多地估计存储索引所需的空间。
可使用下面两个公式来估计叶子页的数目（第二个公式提供更准确的估计）。这些估计的准确度很大程度上取决于平均值反映实际数据的准确程度。注意: 对于 SMS，所需的最小空间为 12 KB。对于 DMS，最小值是一个数据块。
每个叶子页的平均关键字数的粗略估计是：
(.9 * (U - (M*2))) * (D + 1)
----------------------------
K + 6 + (4 * D)
其中：
U（一页上的可用空间）大约等于页大小减 100。对于 4096 的页大小， U 是 3996。
M = U / (8 + 最小关键字大小)
D = 每个关键字值的平均重复项数
K = 平均关键字大小
记住，最小关键字大小和平均关键字大小必须有一个额外字节，表示每个可空关键字部分；还必须有一个额外的字节，表示每个变长关键字部分的长度。
若存在包括列，则在计算最小关键字大小和平均关键字大小时应将它们考虑在内。
若在创建索引期间指定了非缺省值 10% 的一个空闲百分比，则可以使用任何 (100 - pctfree)/100 替换 .9。
每个叶子页的平均关键字数的更准确估计是：
L = 叶子页数 = X / (叶子页上的平均关键字数)
其中，X 是表中的总行数。
可按如下方法估算索引的原始大小：
(L + 2L/(叶子页上的平均关键字数)) * 页大小
对于 DMS 表空间，将一个表上所有索引的大小加在一起，然后四舍五入为该索引所在表空间的数据块大小的一个倍数。
应该为 INSERT/UPDATE 活动所引起的索引增长提供附加空间，这种增长可能导致分页。
使用下列计算方法来获得更精确的原始索引大小的估算值，以及该索引中级别数的估算值。（若索引定义中使用包括列，可能要引起特别注意。）每个非叶子页的平均关键字数大约是：
(.9 * (U - (M*2))) * (D + 1)
----------------------------
K + 12 + (8 * D)
其中：
U（一页上的可用空间）大约等于页大小减 100。对于 4096 的页大小， U 是 3996。
D 是在非叶子页上每个关键字值的重复值的平均数目（这将比在叶子页上的小很多，您可能想将该值设置为 0 以便简化计算）。
M = U / (8 + 非叶子页的最小关键字大小)
K = 非叶子页的平均关键字大小
只要没有包括列，非叶子页与叶子页的最小关键字大小和平均关键字大小将是相同的。包括列不存储在非叶子页上。
除非 (100 - pctfree)/100 大于 .9，否则不应使用它来替换 .9，因为在创建索引期间会在非叶子页上留下 10% 的空闲空间。
可用如下所示的方法估算非叶子页数：
if L >; 1 then {P++; Z++}
While (Y >; 1)
{
P = P + Y
Y = Y / N
Z++
}
其中：
P 是页数（最初为 0）。
L 是叶子页数。
N 是每个非叶子页的关键字数。
Y = L / N
Z 是索引树中的级别数（最初为 1）。
总页数是：
T = (L + P + 2) * 1.0002
附加的 0.02% 表示额外开销，包括空间映象页。
创建索引所需的空间容量估算为：
T * 页大小