临时文件和临时表

运维指南 运维指南
1

  通过本文的学习，您将能够：

  • 了解什么是数据库服务器创建的临时对象

  • 设定临时对象相关的配置参数和环境变量

  • 了解使用多个临时数据空间(dbspace)的重要性

  • 了解创建临时表格时使用WITH NO LOG 子句的目的

  • 使用onstat 和 SMI 监控创建临时对象所用到的空间

1. 临时对象类型

  动态服务器使用两种临时对象存储临时数据：临时文件和临时表。

  在以下情况下会创建临时文件：

  • SELECT 语句中使用 ORDER BY 或 GROUP BY 子句，但没有合适的索引可以对结果进行排序。
  • SELECT 语句中使用 UNIQUE 或 DISTINCT 来消除重复值。
  • SELECT语句中使用排序归并联接两个表，而没有索引可以使用。
  • 哈希联接必须创建哈希表，但共享内存空间不足。
  • 建立索引时需要对键值进行排序。
  • 在热恢复期间，逻辑日志文件会先放到临时文件。

  在以下情况下会创建临时表：

  • 隐性的——SELECT 语句中使用 INTO TEMP 子句。
  • 显性的——使用 CREATE TEMP TABLE 语句。
  • 从应用进程传递二进制大对象到存储过程中，或者有全局二进制大对象全局变量被赋值。

2. 临时对象配置

对于临时表，以下优先顺序适用：

  • DBSPACETEMP 环境变量

  • DBSPACETEMP 配置参数

  • root dbspace或者创建数据库所在的dbspace

对于临时文件，以下优先顺序适用：

  • PSORT_DBTEMP 环境变量

  • DBSPACETEMP 环境变量

  • DBSPACETEMP 配置参数

  • /tmp

  服务器中的临时表可以自动生成至 dbspace中。 DBSPACETEMP 环境变量可以设置一个或多个dbspace。如果没有设置 DBSPACETEMP 环境变量，服务器会使用 DBSPACETEMP 配置参数。临时表会在这些 环境变量或配置参数指定的dbspace中分段生成。无论查询是否为 PDQ 查询，这种情况都会发生。

  如果没有指定 DBSPACETEMP，临时表将放置于root dbspace 或创建数据库所在的 dbspace 中。

  • SELECT …INTO TEMP 语句将临时表放在root dbspace 中。
  • CREATE TEMP TABLE 语句将临时表放在创建数据库所在的 dbspace 中。

  临时文件可以创建在 dbspace 或文件系统空间。如果PSORT_DBTEMP被设置成一个或者多个目录，无论是否为并行排序，将在所有列出的目录中以循环方式创建临时文件（第一个文件在一个目录中，第二个文件在下一个目录中）。

  如果没有设置 PSORT_DBTEMP 环境变量，临时文件将分段分布在 DBSPACETEMP 环境变量列出的 dbspace 中。这意味着无论查询是否为 PDQ 查询，所有这些 dbspace的空间都会被这单一临时文件所分配和利用。如果没有设置 DBSPACETEMP 环境变量，DBSPACETEMP 配置参数中列出的 dbspace 将用于存储临时文件。如果环境变量和配置参数都没有设置，则临时文件存储于 /tmp 目录中。

  指定的 dbspace 或文件系统目录必须有足够的磁盘空间存储临时表或临时文件。如果磁盘空间不足，操作将会失败。

3. DBSPACETEMP 和日志

$ export DBSPACETEMP=dbs1,dbs2

  • 与带有日志的数据库一起使用：

  • 临时表tp1 会创建在rootdbs：
    SELECT number FROM account INTO TEMP tp1

  • 临时表tp2 会在dbs1 和dbs2 中片段式分布。
    SELECT number FROM account INTO TEMP tp2 WITH NO LOG

  建议您在服务器实例的不同设备中创建多个临时 dbspace。由于临时 dbspace 不需要日志记录也无需备份，所以会对性能有所帮助。同时，当临时文件和表创建时，会以分段形式分布于可用的临时 dbspace 中，因此能够以并行方式访问。

  如果数据库是无日志模式而且DBSPACETEMP有设置， 则临时表会自动创建在 DBSPACETEMP 指定的空间中。在有日志模式的数据库中，默认临时表也将被记录日志，并且不会创建在 DBSPACETEMP 指定的空间中。为了能使用 DBSPACETEMP 特点，需要在 SELECT语句、INTO TEMP 语句或 CREATE TEMP TABLE 语句中加上with no log。

  上例中显示如何设置 DBSPACETEMP 环境变量。在客户端应用程序启动前，将环境变量 DBSPACETEMP设置一个或者多个dbspace。这些 dbspace 必须是您所连接的数据库服务器的一部分。如果配置参数 DBSPACETEMP被设置成为dbs1和dbs2，则不需要环境变量。

4. 并行排序

  在以下情况下，若有需要，数据库服务器以并行方式对数据进行排序:

  • ORDER BY

  如果一个 SELECT 语句中有一个 ORDER BY 子句，而又无法利用现有的索引， 那么排序包会按照 ORDER BY 子句指定的字段的顺序对查询结果进行排序。

  • GROUP BY

  和 ORDER BY 子句一样， SELECT 语句的结果按照 GROUP BY 子句指定的字段顺序进行排序。

  • 索引创建

  CREATE INDEX 语句中在键值插入索引之前会将键值进行排序。
  如果数据集比较大，会有些磁盘I/O发生。然而，如果数据集小，排序完全可以在内存中完成，这样小数据集的排序就会更快。

5. PSORT_NPROCS

  PSORT_NPROCS 环境变量设置了会话所用的排序线程数量。
1697600151507

  当需要排序时，排序线程负责从共享内存页面中读取数据，并将数据行放置在位于服务器共享内存虚拟部分中的排序缓冲区里。如果需要的排序空间很大，排序线程会将数据行写入磁盘上的临时文件。

  PSORT_NPROCS 环境变量设置会话在需要排序时使用的排序线程数。环境变量根据客户端应用程序的启动位置进行设置的。如果 PSORT_NPROCS 设置为 0, 代表使用系统默认值 (当前默认值为 2)。

PSORT_DBTEMP

  排序文件会被创建在 dbspaces 或文件系统空间。如果 PSORT_DBTEMP 被设置为一个或多个目录, 那么临时文件会被以循环的方式生成在列出的各个目录中（无论排序是否为并行排序）。

DBSPACETEMP

  如果未设置 PSORT_DBTEMP 环境变量，那么 DBSPACETEMP 环境变量中列出的 dbspaces 会用于以分段方式存储临时文件。

  如果未设置 DBSPACETEMP 环境变量，那么就会使用跟 DBSPACETEMP 配置参数所设置的 dbspaces 列表。

/tmp

  如果环境变量和配置参数都没设置，临时文件会被存储在 /tmp 目录中。

排序和PDQ

  如果 PSORT_NPROCS 的值大于1，不管 PDQPRIORITY 如何设置，排序都将按照并行方式执行。然而，PDQPRIORITY 的值大于0时，对并行排序会有显著的帮助，因为可以为排序分配更多的内存以及额外的扫描线程。

配置指南

  当运行 PDQ 查询时，建议将 PSORT_NPROCS 的初始值设置为2。然后观察 I/O 和 CPU 活动。如果 CPU 活动要比 I/O 活动慢，您可以增加 PSORT_NPROCS 的数值，最多可到10。然而设置超过2而性能得以提升的情形非常少。

3. 并行索引建立

1697600791599
1697600791599726×408 39.6 KB

  服务器使用一种叫做并行索引 创建的方式来建立索引。B+ 树通过两个或更多的线程以并行方式建立。每个线程创建一个子树，然后子树合并在一起变成最终的 B+ 树。

  为了运行一个并行索引构建，服务器会采样将近1000个行的数据来决定如何将若干键值拆分成单独的数据桶。下一步，服务器会扫描数据行，并用并行排序机制对键值进行排序。排好序的键会被放入适当的数据桶中，这些数据桶随后再被附加到索引子树。例如，如果键值是一个整数，采样机制可以决定 1 到 4000 的键值存放于 1 号数据桶中，4001 到 8000 的键存放于 2 号数据桶中。会有一个附加器(appender) 线程用1号数据桶里的键值创建一个子树。另一个附加器线程用2号数据桶里的键值创建另一个子树。因为键值是被分别独立排序好的，一旦创建了子树，就可以很容易再将子树合并成为最终的 B+ 树。

  子树创建进程通过排序而得以加速。因为所有的键都是排好序的，叶子节点无需访问整个树的结构就可以建立。

  CREATE INDEX 语句不是唯一会启动索引创建的语句。增加一个唯一键、主键，或引用约束，或启用这些约束都可导致索引的创建。这些也是并行的。服务器不会对少于 100 页的表进行并行索引创建。

索引创建和 PDQ

  不管 PDQPRIORITY 如何设置，索引创建都会以并行方式进行。但是，如果将 PDQPRIORITY 设置为大于 0，对于索引创建在性能上会有相当大的帮助。PDQPRIORITY 用来决定可以用于索引创建的排序部分的最大内存使用量，以及扫描线程的最大数量。

索引创建和 PSORT_NPROCS

  不管PDQPRIORITY如何设置，索引创建都会以并行方式进行。PSORT_NPROCS 的值决定每个 btappender 线程启动多少个排序线程。如果 PSORT_NPROCS 没有设置，就会使用默认值 2。

3.1 并行索引创建线程

1697609145549
1697609145549982×480 45.2 KB

  在并行索引创建过程中，可以看到几组不同的线程在执行创建索引的工作。

  • 扫描线程从共享内存页面读取数据行以提取键值。如果共享内存中没有需要的页面，扫描线程会向 AIO VP 发送请求从磁盘读取页面。扫描线程决定将键值放入哪个数据桶，并传给负责排序线程进行排序。

  • 排序线程将键值排序。

  • btappender 线程创建索引子树。

  • 最终，sqlexec 线程将子树连结在一启成为一棵 btree 。

  扫描和排序线程必须先完成各自的工作，之后btappender线程才能开始创建索引子树。

3.2 有多少个索引创建线程

  索引创建线程的数目取决于下列因素：

  • 扫描线程：存储表的 dbspace中的每 chunk 都会被自动分配一个扫描线程。

  • 排序线程：如果 PSORT_NPROCS 被设置为 x，排序线程的数量会被设置为x。如果 PSORT_NPROCS 未设置，排序线程的数默认为2。

  • Btappender 线程：自动设置为1 到 6 个线程。取决于服务器预期索引所需要的页面数，btappender 线程可设置为从 1 到 6 不等。下表说明了 被分配的页面和相应的 btappender数目之间的大致对照。

1697610582253

  例如，如果在一个 4 字节的字段上创建一个索引，小于100,000 键值用一个 btappender ， 1 百万个键值以下使用两个 btappender， 1 千万个键值以下使用 3 个 btappender， 5 千万个键值以下使用 4 个 btappender， 2.5 亿个键值以下使用 5 个 btappender，超过 2.5 亿个键使用 6 个 btappender。这例子是假设索引页被完全填充。

  这里的讨论是假设使用的是一个大型的多处理器系统。实际上，服务器不允许 btappender 线程的数目超过 CPU VP 加一的数量。

3.3 索引填充因子

  可以设置索引创建中每个索引页被填充的百分比，这个因子可以使用SQL 语句或onconfig 参数来设置。

  • 配置参数：
    FILLFACTOR 90

  • SQL 语句：

create index zip_code_idx on customer(zip_code) fillfactor 70;

  索引填充因子是索引创建中每个索引页被填充的百分比。该百分比可用 CREATE INDEX 语句来设定。如果 CREATE INDEX 语句没有明确指定该百分比，默认值就等于配置参数 FILLFACTOR 里指定的值。如果填充因子没有在SQL语句指定，也没有在onconfig中指定，则默认的填充因子为 90%。

  如果预期索引建立后不会有很多新数据插入表中，那么在创建索引时可以将 FILLFACTOR 设置得高一些。 如果预期索引建立后表中会插入大量数据，那么可以设置FILLFACTOR 为默认值或将其设置的低一些。但如果 FILLFACTOR 设置过低，会有降低缓存命中率的风险，而且会造成索引所需的磁盘空间增加。

  填充因子在索引生命周期内不会被应用。只有当索引创建时填充因子才被应用。