DB2 Spatial Extender 性能调优（1）（1）

在几乎所有的数据库系统中，使运行在系统上的工作负载取得良好的性能是一项十分重要的任务。依靠 IBM DB2 Spatial Extender 的帮助，你可以在数据库中将空间数据和你希望存储的其他数据无缝的集成在一起。该扩展器提供了一组数据类型来表示空间信息。但是，DB2 Universal Database™（DB2 UDB）数据库引擎还不能识别空间数据的确切属性，并且也没有提供内建的专用数据类型。所以，在为获得最佳性能而调优空间数据库时，需要比通常调优其他 DB2 UDB 数据库时考虑更多的东西。本文介绍了在使用 DB2 Spatial Extender 时应该考虑的基本优化步骤，并对此作了具体解释。简介DB2 UDB for Linux®、UNIX® 和 Windows® 从 7.1 版开始就提供了 DB2 Spatial Extender，用于支持空间数据的存储、治理和修改（请参阅 参考资料 一节，下载 DB2 Spatial Extender）。可以用专用于空间信息及其属性的结构来扩展已有的数据类型。对于每个数据库系统，无论它支持的功能有多好，假如不能为构建在系统上的应用程序提供所要求的性能，就毫无价值。因此，性能调优是一项非常要害的任务。这不仅是数据库治理员的任务，也是应用程序开发人员的任务。DB2 UDB 已经提供了各种各样的工具来帮助调优和监视系统，例如“Configuration Advisor和“Design Advisor。它们都可以通过 Control Center 访问。DB2 Spatial Extender 附带了一个 Index Advisor，可以用它来优化包含空间数据的列上定义的索引。然而，索引调优只是性能调优的一部分，还可以通过调整其他一些参数来提高空间操作的性能，例如 inline length、聚集或表空间类型。在下面几节中，我们将解释其中每个参数，并展示它们相对于未经修改的系统的效果。我们在一台配有 1.2 GHz CPU 和 1 GB 物理内存的 IBM ThinkPad T30 上进行了评测。操作系统是 SUSE Linux 9.3，所有评测都基于带 FixPak 9 的 DB2 UDB Version 8.2。请注重，我们没有执行实际的基准测试，而是集中在单个更改上，这样做只是为了展示特定调优选项的效果。您不能简单地将结果中显示的数据与其他系统进行比较。 12345678910下一页 基本性能调优在任何性能调优过程中，第一步是为系统取得一个良好的基线。运行 DB2 Configuration Advisor 对该任务有所帮助（请参阅 参考资料 一节，获得对 DB2 Configuration Advisor 的介绍和关于调优数据库性能的信息）。可以从 Control Center 中通过右键单击一个数据库来启动 Configuration Advisor，如图 1 所示。图 1. 启动 Configuration Advisor您只需回答关于整个系统的一些简单问题，在任何情况下您应该都知道这些问题。下面是这些问题的一个简化列表，并提供了我们作出的选择，后者以 斜体 显示。从我们的选择中可以看出，我们从一开始就关注性能。您具有的场景可能要求不同的选择，例如对于锁和恢复方面的问题就是如此。您想将多少内存用于 DB2 实例？ 809 MB（80%）。您将执行更多的数据仓库操作，还是执行更多的事务性操作？ 都有。您的事务是长还是短？一分钟预期有多少事务？ 较短的事务；一分钟 60 个。对您来说，更快的恢复与更快的事务哪个优先？ 更快的事务。数据库已经包含了数据吗？ 还没有包含数据。本地和远程连接的平均数量是多少？ 平均 5 个本地连接，2 个远程连接。您想使用哪种隔离级别？ Cursor stability （更少的锁）。在回答这组问题之后，Configuration Advisor 将提供对建议修改项的总结。现在您可以立即应用建议的修改，或将一个任务保存在任务中心。您应该检查每一项修改，确定它们对系统是否有意义。假如想根据自己的需要进行调整，那么可以在任务中心创建一个包含配置修改的任务，然后按照自己的需要修改任务脚本。在系统上运行 Configuration Advisor 之后可以得到如图 2 所示的结果。最值得注重的是对缺省缓冲池大小的修改。增加缓冲池大小通常是提高系统整体性能的最重要的决定，因为它指定可以将多少数据缓存在内存中，从而减少对（较慢的）磁盘 I/O 的需要。 上一页1234567下一页 图 2. Configuration Advisor 的建议其他更改主要针对将可用内存划分出一些必要的缓存，例如包缓存和编目缓存，或者调整短事务的设置（日志记录和排序）。除了减少辅助日志文件的决定之外，所有更改都将被接受，不需任何人为干涉。我们将辅助日志文件的数量设为 10，以避免在空间导入操作期间可能发生的问题。取决于您的系统以及在那组问题中给出的答案，您得到的建议可能不同。性能比较首先在一个新安装的系统上，在应用建议的配置更改之前，我们运行一组空间操作。这些操作的执行时间通过操作系统命令 time 或 DB2 的 db2batch 工具来测量。最后，我们清除数据库，应用 DB2 Configuration Advisor 建议的配置，并重复相同的操作。为得到比较可靠的结果，我们多次执行导入操作和查询，对测量到的时间取平均值。这些空间操作如下所示：使用 Spatial Extender 命令行工具 db2se 从 DB2 Spatial Extender 附带的第一张 Data & Maps CD 中将 shapefile 文件 europe/roads.shp 导入一个名为 ROADS 的表中。执行一个空间操作，该操作使用一个简单的空间查询将这个表中所有的几何图形与一个固定的 linestring 进行比较。在空间列上创建一个网格索引。清单 1 展示了确切的步骤和它们产生的性能结果。可以在 下载 一节中找到 SQL 脚本 test_config_advisor.sql。清单 1. 未调优的数据库上的空间操作$ time db2se import_shape testdb -fileName /home/stolze/europe/roads-srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1-spatialColumn shape -typeName ST_LineString -idColumn id-commitScope 1500 -messagesFile /home/stolze/import.msgGSE0000I　The operation was completed successfully.real2m19.086suser0m0.050ssys 0m0.021s$ db2batch -d testdb -f test_config_advisor.sql -i complete -s on---------------------------------------------Statement number: 1SELECT idFROM　 roadsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　secondsExecute Time is:　 1.248　secondsFetch Time is: 0.000　secondsElapsed Time is:　 1.248　seconds---------------------------------------------Statement number: 2CREATE INDEX roads_grid_index ON roads(shape)　 EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)Elapsed Time is:　 25.503 seconds--------------------------------------------- 上一页12345678下一页 注重，测试系统使用的配置是次优的，因为导入的数据是从与数据库和数据库日志在同一个硬盘驱动器上的文件中读取的。因而，读操作与日志写和缓冲池中的数据页的写操作之间存在竞争。可能需要将各种特定于磁盘的任务分配到不同的文件系统上。当使用 DB2 Spatial Extender 导入工具导入 shapefile 文件时，建议不要尝试直接从 CD 装载数据，而是先将它复制到一个硬盘上。CD-ROM 驱动器不是很适合读 shapefile 文件的访问模式，因此整个操作的速度会急剧降慢。在应用了 Configuration Advisor 的建议之后，重复前面列出的步骤就产生了清单 2 中的结果。可以看到，仅仅是导入操作的性能就提高了 11%，查询的速度快了 28%，甚至创建索引所花的时间也只有之前的 90%。所以不应当忽视最基本的性能调优。清单 2. 调优后的数据库上的空间操作$ time db2se import_shape testdb -fileName /home/stolze/europe/roads-srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1-spatialColumn shape -typeName ST_LineString -idColumn id-commitScope 1500 -messagesFile /home/stolze/import.msgGSE0000I　The operation was completed successfully.real2m2.848suser0m0.051ssys 0m0.027s$ db2batch -d testdb -f test_config_advisor.sql -i complete -s on---------------------------------------------Statement number: 1SELECT idFROM　 roadsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　secondsExecute Time is:　 0.895　secondsFetch Time is: 0.000　secondsElapsed Time is:　 0.895　seconds---------------------------------------------Statement number: 2CREATE INDEX roads_grid_index ON roads(shape)　 EXTEND USING db2gse.spatial_index(0.27, 0.54, 1.6)Elapsed Time is:　 22.980 seconds--------------------------------------------- 上一页123456789下一页 DB2 内部对空间数据的处理空间数据可能变得非常复杂，需要很多空间来存储一个几何图形中各个点的信息。例如，表示整个美国的区域的几何图形由 60 个多边形组成，总共有 198569 个点来定义那些多边形。按照 Spatial Extender 内部格式，这个几何图形的完整定义要使用 0.9 MB 的磁盘空间（使用了压缩）。假如几何图形按照 ESRI 几何图形格式编码，那么它实际上需要 3.1 MB 的磁盘空间（请参阅 参考资料 一节，了解关于 ESRI 几何图形格式的更多信息）。所有信息封装在一个 ST_Geometry 值中，这意味着这个值在数据库中也需要大约 1 MB 的磁盘空间。另一个例子是只表示一个点的空间值。对于 X 和 Y 维，我们只有用于两个浮点值的 8 个字节。将一个点表示成 ST_Point 值会增加一些开销，但是我们谈论时仍然当作是几个字节。DB2 表中一个行中存储的所有值的总大小不能超过表空间的页宽。一个例外是大型对象（LOB），它最大可达 2 GB。DB2 支持的最大页宽是 32K。所以存储需要 1 MB 空间的几何图形需要类似于 LOB 的存储机制。但总是为点数据使用那种机制就过分了。为了解决千差万别的需求，DB2 实现了一种用于存储空间数据（或通常的结构数据）的混合方法。假如一个空间值超过了某个大小（即所谓的 inline length），那么这个值就被存储为 BLOB。否则，这个值就存储为 VARCHAR FOR BIT DATA 值。下一节 将具体讨论如何为空间列设置 inline length，以及通过更改设置可以获得的好处。之后，我们讨论 空间数据聚集，为 编写空间 SQL 查询、调优 空间网格索引 提供指南，最后我们解释假如经常要修改数据，则建议使用哪种 表空间类型。设置空间列的 inline length在 上一节 中，我们解释了 DB2 存储需求多变的空间数据的内部机制。确定几何图形是存储为 VARCHAR FOR BIT DATA 还是 BLOB 的决定因素就是所谓的 inline length，这个参数适用于任何表中的空间列。假如空间值的内部表示需要的字节数少于 inline length 设置中指定的值，那么它将以 内联（inline） 的方式存储为 VARCHAR FOR BIT DATA。否则，这个值将被 大对象化（lobify），并在该表的 LONG 表空间中存储为 LOB。 上一页12345678910下一页 应该记住，以内联方式存储数据比以大对象化方式存储数据要可取得多。原因是，内联的数据当作 VARCHAR FOR BIT DATA 对待。这个值与同一行中所有其他属性一起存储在一个数据页中。一旦数据存储在那样一个页上，那一页将通过缓冲池来访问，这样可以利用先进的缓存技术，从而尽量避免文件 I/O。而对于 LOB 则截然不同，它总是直接从磁盘读取。所以经验法则非常简单：将 inline length 设置得尽可能高，以便让尽可能多的空间值以内联方式存储。当然，实际情况并不像看上去的那么简单。高的 inline length 值告诉 DB2 空间值实际上可以在单独一行中占用很多字节。每一行的最大大小要受到针对表定义的页宽和属性（列）的限制。例如，假如有一个页宽为 4 KB（4096）的表空间，那么一行的最大大小不能超过 4005 字节（请参阅 参考资料 一节，了解关于 SQL 限制的更多信息）。假如这个表有一个不能为空的 INTEGER 列和一个可以为空的 VARCHAR(100) 列，再加上一个空间列，那么最多可以将 inline length 设置为 4005 - 6 - 4 - (1+2+100) - 1 = 3891，其中 6 个字节用于行的前缀，4 个字节是 INTEGER 列需要的空间，(1+2+100) 个字节是为 VARCHAR(100) 预留的，最后 1 个字节用于空间列的 NULL 指示符（请参阅 参考资料 一节，找到关于数据库对象和 CREATE TABLE 语句的一本书）。可以看到，其他列的长度和 inline length 实际上是相互竞争的。为了进一步增加 inline length，可以将表放在页宽为 8K、16K 甚至 32K 的表空间上。这样，对于之前的例子，就可以分别将 inline length 设为 7987、16179 或 32563 字节。inline length当在数据库中创建一个新的结构类型时，DB2 将根据类型定义中指定的属性计算那个数据类型的缺省 inline length。可以在系统编目视图 SYSCAT.DATATYPES 的 INLINE_LENGTH 列上找到一个结构类型的缺省 inline length。假如在 CREATE TABLE 或 ALTER TABLE ... ADD COLUMN ... 语句中定义表的列时没有显式地指定 inline length，那么将沿用缺省值。 上一页234567891011下一页 可以使用 ALTER TABLE ... ALTER COLUMN ... SET INLINE LENGTH ... 语句修改（增加）已有空间列的 inline length。除非通过 REORG TABLE 语句 加 LONGLOBDATA 选项重组存储在表中的数据，否则这种修改只影响 DB2 编目和随后的数据修改。假如值的大小小于新的 inline length，那么这个重组过程将把大对象化的空间值转换成内联值。选择适当的 inline length在将所有空间数据存储到 32K 的表空间上并且将 inline length 设置成尽可能大的值之前，应该首先分析您的数据实际上有多大以及其他参数可能对页宽产生的影响。假如只有 ST_Point 值，那么每个点将需要最多 245 字节的物理存储，如清单 3 所示。在这种情况下，甚至 减少 inline length 更有帮助，因为可以使用更小的页宽和/或在表中使用更多的列。但是要注重，ALTER TABLE 语句只答应增加 inline length。假如想使用更小的值，那么必须在创建表的时候指定。Spatial Extender 导入过程答应显式地为空间列指定 inline length。在结构类型中嵌套 LOB虽然 points 属性被定义为 BLOB，但是 DB2 并不会单独地存储它。相反，整个几何图形信息（包括 BLOB 数据）都存储在一起 —— 至于存储为内联值还是大对象化值，则取决于列的 inline length。结构类型的实现使所有属性值并置到一个二进制流中，任何添加的必要的元信息和产生的二进制流在物化（也就是存储到一个表中）的时候，或者存储为内联值，或者存储为大对象化值。这种方法使任何处理 LOB 的应用程序可以以内联方式存储短的 LOB 值，并利用 DB2 的缓冲池。清单 3 展示了如何计算每个几何图形在以内联方式存储时需要多少磁盘空间。我们假设所有几何图形都存储在一个名为 SPATIAL_DATA 的表的 GEOMETRY 列中。第一个查询使用 LENGTH 函数。该函数显示以内联方式存储的值的宽度。假如是大对象化的值，那么它显示引用实际值的定位符的宽度。所以只有知道所有空间值都是内联值时，才可以放心地使用该函数。因此，下面的查询根据空间数据类型的属性来计算数据的宽度。关于属性的信息可以从 DB2 编目视图 SYSCAT.DATATYPES 和 SYSCAT.ATTRIBUTES 获得。假如空间数据是使用结构类型实现的，那么在 SQL Reference 中关于 CREATE TYPE 语句的解释中提到的判定结构类型值宽度的法则同样适用（请参阅 参考资料 一节，了解关于 CREATE TYPE 语句的信息）。具体地说，ST_Geometry 类型定义 16 个属性，它的子类型都没有添加自己的属性。除了三个属性外，所有属性都是所谓的 短属性。其中两个非短属性 anno_text 和 ext 没有被使用，第三个非短属性 points 包含内部编码为 BLOB 的几何图形信息。除了实际的数据外，DB2 需要维护强制的 null 指示符（1 个字节）和长度信息（4 个字节）。因此，几何图形的大小可以通过公式 “32 + 16*10 + 5 + LENGTH(points) = 197 + LENGTH(points) 得出。 上一页3456789101112下一页 清单 3. 几何图形的空间需求-- maximum space requirement for spatial point dataCREATE TABLE test ( p db2gse.ST_Point INLINE LENGTH 3800 )@INSERT INTO test VALUES ( db2gse.ST_Point( 1234567890123456, 1234567890123456, 1234567890123456, 1234567890123456, 0) )@SELECT LENGTH(p) FROM test@1-----------245　1 record(s) selected.-- determining the size of all geometries in a tableSELECT 197 + LENGTH(geometry..points)FROM　 spatial_data@-- calculating #geometries that would be stored inline/lobified-- for a given inline lengthSELECT SUM(inline) AS inline, COUNT(*) - SUM(inline) AS lobifiedFROM　 ( SELECT CASE　 WHEN 197 + LENGTH(geometry..points) <= <inline_length>　 THEN 1　 ELSE 0END FROM　 spatial_data ) AS t(inline)@性能比较为了演示小的 inline length 与大的 inline length 的效果，我们首先将 shapefile 文件 europe/roads.shp 导入到一个 inline length 为 292 的表中。这是 DB2 答应的最小值。接着运行一个 SQL 脚本，该脚本确定有多少几何图形以内联方式存储，有多少几何图形必须以大对象化的方式存储。然后测量执行一个简单空间查询的时间，并显示在执行期间产生的语句快照的一个摘录，以揭示影响性能的最突出的因素。整个过程在 inline length 为 2000 的情况下再重复一遍，2000 这个值足以导致那个 shapefile 文件中的所有几何图形都以内联方式存储。下载 一节中包含了我们运行 db2batch 时使用的脚本 test_inline_length.sql。 上一页45678910111213下一页 清单 4. 不同 inline length 设置的效果$ time db2se import_shape testdb -fileName /home/stolze/europe/roads-srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1-spatialColumn shape -typeName ST_LineString -inlineLength 292-idColumn id -commitScope 1500GSE0000I　The operation was completed successfully.real3m15.604suser0m0.050ssys 0m0.026s$ db2batch -d testdb -f test_inline_length.sql -i complete -s on---------------------------------------------Statement number: 1SELECT SUM(inline) AS inline_storage,　 COUNT(*) - SUM(inline) AS lobified_storageFROM　 ( SELECT CASE　 WHEN 197 + LENGTH(shape..points) <=　 ( SELECT inline_length FROM　 syscat.columns WHERE　colname = 'SHAPE' ANDtabname = 'ROADS' )　 THEN 1　 ELSE 0END FROM　 roads ) AS t(inline)INLINE_STORAGE LOBIFIED_STORAGE　-------------- ---------------- 8959521384---------------------------------------------Statement number: 2SELECT idFROM　 roadsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　seconds　Execute Time is:　 0.854　seconds　Fetch Time is: 0.000　seconds　Elapsed Time is:　 0.855　secondsBuffer pool data logical reads　 = 16818Buffer pool index logical reads　= 19731Direct reads = 3088Direct read requests = 1544Direct read elapsed time (ms)= 18---------------------------------------------$ db2 "DROP TABLE roads"$ time db2se import_shape testdb -fileName /home/stolze/europe/roads-srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1-spatialColumn shape -typeName ST_LineString -inlineLength 2000-idColumn id -commitScope 1500GSE0000I　The operation was completed successfully.real1m57.212suser0m0.049ssys 0m0.027s$ db2batch -d testdb -f test_inline_length.sql -i complete -s on---------------------------------------------Statement number: 1SELECT SUM(inline) AS inline_storage,　 COUNT(*) - SUM(inline) AS lobified_storageFROM　 ( SELECT CASE　 WHEN 197 + LENGTH(shape..points) <=　 ( SELECT inline_length FROM　 syscat.columns WHERE　colname = 'SHAPE' ANDtabname = 'ROADS' )　 THEN 1　 ELSE 0END FROM　 roads ) AS t(inline)INLINE_STORAGE LOBIFIED_STORAGE　-------------- ----------------1109790　---------------------------------------------Statement number: 2SELECT idFROM　 roadsWHERE　db2gse.ST_Intersects(shape, db2gse.ST_LineString(　'linestring(10 50, 20 40)', 1003)) = 1Prepare Time is:　 0.000　secondsExecute Time is:　 0.792　secondsFetch Time is: 0.000　secondsElapsed Time is:　 0.792　secondsBuffer pool data logical reads　 = 17337Buffer pool index logical reads　= 19731Buffer pool index physical reads = 0Direct reads = 2Direct read requests = 1Direct read elapsed time (ms)= 0--------------------------------------------- 上一页567891011121314下一页 从结果中可以看出，对于重要操作，较大的 inline length 可以增加 40% 的速度，对样本数据的查询也快了 7%。这两种差异的底层原因是，当使用较小的 inline length 时，有超过 20000 个几何图形（大约 20% 的数据）以大对象化的方式存储。DB2 直接从磁盘读（写） LOB 数据。而在第二种场景中，大部分的直接读是不需要的，因为可以用存储在缓冲池中的内联数据来满足查询。注重，大对象化的数据所占的 20% 的比例实际上不算很坏。假如由于稍微复杂一点儿的几何图形导致更多的数据不能以内联方式存储，那么这里演示的差异还要大大增加。聚集空间数据根据某个属性聚集数据是一种常见的、也是非常有用的技术，这种技术可以物理地组织一个表的数据。通过对底层的观察可以发现，具有相似值的数据经常一起被访问。所以，可以将类似的数据存储在接近的位置，使得对那些数据的访问不必分散到表空间中很多不同的页上，而是分布在临近的几个页上。根据空间数据的空间属性或几何图形间的距离聚集空间数据是很自然的。空间查询是展示局部数据访问（换句话说，现实中临近的几何图形经常被一起访问）的最好例子之一。例如，假如您看一个城市的街道地图，那么很可能对那个城市的所有街道感爱好，而对地区另一边某个其他城市的街道不感爱好。所以在物理上将那个城市的一些行存储在相邻的位置的确很有意义。在 DB2 中建立数据聚集属性的方法是根据一个索引对表进行重组。然而，假如由于空间索引的复杂性质导致 DB2 REORG TABLE 命令不理解空间索引，事情就不会那么轻易了。关于这个问题有一个轻易的方法，即使用一个列，这个列的值是根据涉及的图形计算的。这个列上声明的数据类型必须保证 DB2 能在这个列上创建一个本地 B- 树索引。这里使用空间填充曲线来保存空间和拓扑属性（请参阅 参考资料 一节，找到 H. Sagan 撰写的书籍）。我们在几何图形上取一个点，即形心点， 计算那个点在空间填充曲线上的值，并将结果存储在一个附加的列中。最后，在附加列上创建一个索引，并根据那个索引对表进行重组。 上一页6789101112131415下一页