主持人 > 博客 > 管理 > 在 PostgreSQL 中为大容量数据节省一分钱

在 PostgreSQL 中为大容量数据节省一分钱

继续记录大数据流的话题 上一篇关于分区的文章，在此我们将探讨您可以使用的方法 减少存储的“物理”大小 PostgreSQL 中的内容及其对服务器性能的影响。

我们将讨论 TOAST 设置和数据对齐。 “平均而言”，这些方法不会节省太多资源，而且根本不需要修改应用程序代码。

在 PostgreSQL 中为大容量数据节省一分钱
然而，我们的经验在这方面非常富有成效，因为几乎所有监控的存储本质上都是 主要是仅附加 就记录的数据而言。 如果您想知道如何教导数据库写入磁盘 200MB /秒 一半 - 请在猫下。

大数据的小秘密

按职位简介 我们的服务，他们定期从巢穴飞向他 文本包.

由于 超大规模集成电路综合体我们监控的数据库是一个多组件产品，数据结构复杂，然后查询 以获得最佳性能 结果就像这样 算法逻辑复杂的“多卷”。 因此，我们收到的日志中请求的每个单独实例或生成的执行计划的数量“平均”相当大。

让我们看一下我们写入“原始”数据的表之一的结构 - 也就是说，这里是日志条目的原始文本：

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

一个典型的标志（当然，已经分区，所以这是一个分区模板），其中最重要的是文本。 有时相当庞大。

回想一下，PG 中一条记录的“物理”大小不能占用超过一页数据，但“逻辑”大小则完全不同。 要将体积值 (varchar/text/bytea) 写入字段，请使用 吐司技术:

PostgreSQL 使用固定的页面大小（通常为 8 KB），并且不允许元组跨越多个页面。 因此，不可能直接存储非常大的字段值。 为了克服这个限制，大字段值被压缩和/或分割到多个物理行。 用户不会注意到这种情况，并且对大多数服务器代码影响很小。 这种方法称为 TOAST...

事实上，对于每个具有“潜在大”字段的表，自动 创建了带有“切片”的配对表 2KB 段中的每个“大”记录：

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

也就是说，如果我们必须编写一个具有“大”值的字符串 data，然后就会发生真正的录音 不仅涉及主表及其PK，还涉及TOAST及其PK.

减少TOAST影响

但我们的大部分记录仍然没有那么大， 应该适合 8KB - 我怎样才能省钱呢？...

这就是该属性为我们提供帮助的地方 STORAGE 在表格列：

  • EXTENDED 允许压缩和单独存储。 这 标准选项 对于大多数 TOAST 兼容的数据类型。 它首先尝试执行压缩，然后如果行仍然太大，则将其存储在表外。
  • 主要 允许压缩但不允许单独存储。 （事实上​​，对于此类列，仍然会进行单独存储，只不过 作为最后的手段，当没有其他方法可以缩小字符串以使其适合页面时。）

事实上，这正是我们所需要的文本 - 尽量压缩，如果根本装不下就放到TOAST里。 这可以通过一个命令直接即时完成：

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

如何评估效果

由于数据流每天都在变化，我们无法比较绝对数字，而是相对数字 较小的份额 我们把它写在 TOAST 中——这样就更好了。 但这里存在一个危险——每条记录的“物理”量越大，索引就变得越“宽”，因为我们必须覆盖更多的数据页。

部分 更改前:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

部分 改变后:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

事实上，我们 写入 TOAST 的频率开始减少 2 倍，它不仅卸载了磁盘，还卸载了 CPU：

在 PostgreSQL 中为大容量数据节省一分钱
在 PostgreSQL 中为大容量数据节省一分钱
我要指出的是，我们在“读取”磁盘方面也变得更小，而不仅仅是“写入” - 因为当将记录插入到表中时，我们还必须“读取”每个索引的树的一部分以确定其未来在他们中的地位。

谁能在 PostgreSQL 11 上过得很好

更新到PG11后，我们决定继续“调优”TOAST，发现从这个版本开始该参数可以调优了 toast_tuple_target:

仅当要存储在表中的行值大于 TOAST_TUPLE_THRESHOLD 字节（通常为 2 KB）时，才会触发 TOAST 处理代码。 TOAST代码将压缩和/或将字段值移出表，直到行值变得小于TOAST_TUPLE_TARGET字节（变量值，通常也是2 KB）或者大小无法减小。

我们认为通常拥有的数据要么“非常短”，要么“非常长”，因此我们决定将自己限制在尽可能小的值：

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

让我们看看重新配置后新设置如何影响磁盘加载：

在 PostgreSQL 中为大容量数据节省一分钱
不错！ 平均的 磁盘队列已减少 大约 1.5 倍，磁盘“繁忙”率为 20%！ 但这也许会以某种方式影响CPU？

在 PostgreSQL 中为大容量数据节省一分钱
至少情况没有变得更糟。 不过，很难判断即使这样的容量是否仍然无法提高平均 CPU 负载 5%.

通过更改项的位置，总和...会发生变化！

如您所知，一分钱可以节省一卢布，就我们的存储量而言，大约是 10TB/月 即使是一点点优化也能带来不错的利润。 因此，我们关注数据的物理结构——到底如何 记录内的“堆叠”字段 每张桌子。

因为因为 数据对齐 这很简单 影响最终的体积:

许多架构提供机器字边界上的数据对齐。 例如，在 32 位 x86 系统上，整数（整数类型，4 字节）将在 4 字节字边界上对齐，双精度浮点数（双精度浮点数，8 字节）也是如此。 而在 64 位系统上，双精度值将与 8 字节字边界对齐。 这是不兼容的另一个原因。

由于对齐，表行的大小取决于字段的顺序。 通常这种影响不是很明显，但在某些情况下可能会导致尺寸显着增加。 例如，如果混合使用 char(1) 和整数字段，则它们之间通常会浪费 3 个字节。

让我们从合成模型开始：

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

在第一种情况下，几个额外的字节是从哪里来的？ 这很简单 - 2 字节smallint 在 4 字节边界上对齐 在下一个字段之前，当它是最后一个字段时，什么也没有，不需要对齐。

理论上，一切都很好，您可以根据需要重新排列字段。 让我们以其中一张表为例来检查实际数据，该表的每日部分占用 10-15GB。

初始结构：

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

更改列顺序后的部分 - 完全一致 相同的字段，只是顺序不同:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

该部分的总体积由“事实”的数量决定，并且仅取决于外部进程，因此让我们除以堆的大小（pg_relation_size）通过其中的记录数 - 也就是说，我们得到 实际存储记录的平均大小:

在 PostgreSQL 中为大容量数据节省一分钱
体积负6%， 伟大的！

但当然，一切并不那么美好——毕竟， 在索引中我们无法更改字段的顺序，因此“一般来说”（pg_total_relation_size）...

在 PostgreSQL 中为大容量数据节省一分钱
……也还在这里 节省 1.5%无需更改一行代码。 是的是的！

在 PostgreSQL 中为大容量数据节省一分钱

我注意到上述安排字段的选项并不是最优化的。 因为您不想出于美观原因“撕裂”某些字段 - 例如，几个字段 (pack, recno)，这是该表的 PK。

一般来说，确定字段的“最小”排列是一项相当简单的“蛮力”任务。 因此，您可以从您的数据中获得比我们更好的结果 - 试试吧！

来源： habr.com

添加评论