gertvdijk's questions

我正在尝试在 PostgreSQL 12-beta2 中以特定顺序将数据从一个表批量复制到另一个表。该表未使用序列，但包含复合唯一主键 ( user_id, object_id)。

为了确定下一批从哪里开始，我想从最后插入的行 ( WHERE user_id >= last_user_id AND object_id > last_object_id) 开始。

从这个开始：

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... 返回一个包含 1000 个元组的表。我想从中获取最后插入的元组。

我试图围绕它做一个 SELECT ，如下所示：

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...) 
    FROM source_table 
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

但这会返回语法错误：

ERROR:  syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
               ^

我也尝试RETURNING ... INTO variable 过这里描述的，但这也失败了：

ERROR:  syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
                                      ^

我是否需要为此创建一个函数（例如 plpgsql），或者我是否在普通 SQL 中遗漏了一些明显的东西让我这样做？那将是非常有利的。

我已经使用下表设置了一个 PostgreSQL FDW 服务器，由user_id四个以上的服务器分片：

CREATE TABLE my_big_table
(
    user_id bigint NOT NULL,
    serial bigint NOT NULL,         -- external, incrementing only
    some_object_id bigint NOT NULL,
    timestamp_ns bigint NOT NULL,
    object_type smallint NOT NULL,
    other_type smallint NOT NULL,
    data bytea
) PARTITION BY HASH (user_id) ;

CREATE SERVER shardA
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
 .
 .
 .
CREATE SERVER shardD
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');

create foreign table my_big_table_mod4_s0 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 0) server shardA
    OPTIONS (table_name 'my_big_table_mod4_s0');
 .
 .
 .
create foreign table my_big_table_mod4_s3 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 3) server shardD
    OPTIONS (table_name 'my_big_table_mod4_s3');

在后端服务器上，我设置了一个包含多个索引的表，其数据(user_id, serial)跨多个分区聚集。不过，我不认为这些细节与我的实际问题非常相关。

针对我的集群的常见查询采用以下模式：

SELECT * from my_big_table
WHERE
  user_id = 12345     -- only 1 user, always! --> single foreign server.
ORDER BY serial DESC  -- get 'newest' 90% of the time, 10% ASC
LIMIT 1000;           -- max limit 1000, sometimes less

对于 < 1000 条记录的用户：一切正常，没问题。

对于具有 > 100.000 条记录的用户，我看到了导致性能不佳的问题：explain显示 LIMIT 和排序发生在 FDW 上，而不是下推。为什么？

 Limit  (cost=927393.08..927395.58 rows=1000 width=32)
   Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
   ->  Sort  (cost=927393.08..931177.06 rows=1513592 width=32)
         Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
         Sort Key: my_big_table_mod4_s0.serial DESC
         ->  Foreign Scan on public.my_big_table_mod4_s0  (cost=5318.35..844404.46 rows=1513592 width=32)
               Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, length(my_big_table_mod4_s0.data)
               Remote SQL: SELECT serial, some_object_id, timestamp_ns, object_type, other_type, data FROM public.my_big_table_mod4_s0 WHERE ((user_id = 4560084))
 JIT:
   Functions: 3
   Options: Inlining true, Optimization true, Expressions true, Deforming true

综上所述：

• 已选择单个后端服务器：OK！（解决了这个）
• Remote SQL: SELECT [...]表示没有 ORDER BY，没有 LIMIT。问题。

在后端服务器上执行直接显示：

 Limit  (cost=1.74..821.42 rows=1000 width=32)
   Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, (length(my_big_table_mod4_s0_part123.data))
   ->  Append  (cost=1.74..1240669.45 rows=1513592 width=32)
         ->  Index Scan Backward using my_big_table_mod4_s0_part123_pkey on public.my_big_table_mod4_s0_part123  (cost=0.43..290535.67 rows=355620 width=32)
               Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, length(my_big_table_mod4_s0_part123.data)
               Index Cond: (my_big_table_mod4_s0_part123.user_id = 4560084)
         ->  Index Scan Backward using [... other partitions ...]

我尝试了什么：

• 由于 FDW 仍在开发中，我尝试使用更新的版本：FDW 和后端服务器都使用 11.4 和 12-beta2。没有观察到差异。
• 针对外部表运行 ANALYZE（在 FDW 实例上）。花费大量时间；看起来它正在扫描远程表的全表？查询计划没有区别。
• 更改fetch_size远程 SERVER 对象上的值。没有不同。
• use_remote_estimate=true在远程 SERVER 对象上设置。没有不同。
• fdw_tuple_cost=100在远程 SERVER 对象上设置。排序现在发生在远程服务器上，但 LIMIT 仍然没有被按下。

• 在网上寻找其他人看到这个，只显示这个相关的帖子：Missed LIMIT Clause pushdown in FDW API

  但是这个线程提到在 9.7 中修复这个问题等等，但我使用的是 11.4 和 12-beta2。还是我误解了这一点？

  并发布：外部表的奇怪成本估算显示了调整 FDW 的一个很好的例子，但不幸的是，它没有涵盖我的 LIMITs 问题。

• 快速浏览一下 PostgreSQL 源代码，我注意到这个语句，可能与 FDW 相关，也可能不相关（来源）。

  我们不能将包含 LIMIT/OFFSET 的子选择推送给工作人员，因为不能保证行顺序是完全确定的，并且应用 LIMIT/OFFSET 会导致顶层结果不一致。（在某些情况下，如果结果是有序的，我们可以放宽这个限制。但目前似乎不值得为此付出额外的努力。）

• 再看一下源代码，我发现了这个有希望的提交（d50d172e51）：

  这为 postgres_fdw 提供了处理 SELECT 命令的能力，以便它 1) 跳过 LockRows 步骤（如果有）（注意这是安全的，因为它执行早期锁定）和 2) 降低 LIMIT 和/或 OFFSET 限制（如果有) 到远端。这不处理 INSERT/UPDATE/DELETE 情况。

  它为我的案例添加了一个单元测试案例！

  -- and both ORDER BY and LIMIT can be shipped
  EXPLAIN (VERBOSE, COSTS OFF)
    SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
                                                                          QUERY PLAN                                                                         
  ------------------------------------------------------------------------------------------------------------------------------------------------------------
  Foreign Scan on public.ft1 t1
    Output: c1, c2, c3, c4, c5, c6, c7, c8
    Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2)) ORDER BY c2 ASC NULLS LAST LIMIT 1::bigint
  

  这应该是我已经运行的 12-beta2 的一部分...

^{我注意到我应该在这里按 DESC 顺序使用索引，但现在这不那么相关了。}

我已经在 MariaDB（10.4.5，当前为 RC）中使用 InnoDB 设置了一个表，使用按列进行分区，其值仅递增，并且始终在末尾插入新数据。对于这种情况，分区对于加快某些查询并在快速驱动器上保持新/活动分区以及在慢速旋转磁盘上保持旧/存档分区是有意义的。对于插入加速，它工作得很好！（类似于 TimescaleDb 方法，但没有时间也没有 PostgreSQL。）

在同一列上按范围选择也可以正常工作；它只会开始读取指定范围的分区（索引）。到目前为止一切都很酷。

现在，我还有一些查询在该列上没有子句，但按该列降序排列（即新数据优先），加上一个限制，通常只会命中一个或两个最新分区（快速，缓存指数）。但是，无论指定的顺序是什么，似乎 MySQL/MariaDB 都会从头到尾打开分区。真的有那么笨吗？此外，我真的找不到其他人有这个问题，这让我有点担心。（有时这意味着我错过了一些非常明显的东西。）

为了在这里更具体 - 为了测试，我有下表：

CREATE TABLE `mytable` (
  `user_id` bigint(20) unsigned NOT NULL,
  `my_id` bigint(20) unsigned NOT NULL,
  `data` varbinary(123) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`my_id`),
  UNIQUE KEY `my_id_idx` (`my_id`)  -- I was hoping this one could help me
) ENGINE=InnoDB ROW_FORMAT=COMPACT
 PARTITION BY RANGE (`my_id`)
(PARTITION `p0` VALUES LESS THAN (10000000) ENGINE = InnoDB,
 PARTITION `p10M` VALUES LESS THAN (20000000) ENGINE = InnoDB,
 PARTITION `p20M` VALUES LESS THAN (30000000) ENGINE = InnoDB,
 PARTITION `p30M` VALUES LESS THAN (40000000) ENGINE = InnoDB,
 [...]
) 

我运行如下查询：

SELECT 
    user_id,
    my_id,
    LENGTH(data) AS data_len
FROM
    mytable
    -- tried to optimize with index hints:
    -- USE INDEX FOR ORDER BY (MY_ID_IDX)
    -- USE INDEX FOR ORDER BY (PRIMARY)
    -- USE INDEX FOR ORDER BY (MY_IDX, PRIMARY)
WHERE
    user_id = 1234567
ORDER BY my_id DESC
LIMIT 10;

我发现它首先开始寻找所有数据，user_id = 1234567首先显示旋转磁盘上的大量 I/O 负载，然后最终进入快速存储以获取完整集，然后切断最后LIMIT 10一行......都在快速存储，所以我们浪费了几分钟的时间！唔。

我的数据太大，我们无法将所有索引都放入内存 - 我们依赖磁盘上“足够”的索引来缓存在存储层上。但是，即使所有索引都适合缓存，数据也必须来自磁盘，并且一些用户在这里有大量数据（> 10M 行），这样在内存中进行这种排序效率很低。 所以我希望找到一种方法让 MariaDB 查找最后 LIMIT 行，然后停止阅读。

作为一个人，你会首先开始查看最后一个分区，因为它ORDER BY my_id DESC和最新的分区包含它的最高值。但是，我如何告诉 MySQL/MariaDB 这样做呢？

explain partitions结果（对于上面列出的所有 USE INDEX 变体都是相同的）：

  select_type: SIMPLE
        table: mytable
   partitions: p0M,p10M,p20M,p30M, ... (~ hundred here)
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 9999999 (worst-case)
        Extra: Using where

事实上，与我的预期相反，如果使用 first-to-new 分区按升序进行查询，它的性能甚至不会更好。它仍然会请求所有分区的所有索引，然后发现它只需要一个...

我听说过一些关于 MySQL 未来版本中分区的全局索引的信息，但我怀疑它是否真的会对这里有帮助，因为它的大小很大......而且它已经通过我的分区布局得到了暗示案子。我发现的关于“分区修剪”的信息似乎与读取顺序无关；仅关于查询中的子句。

任何帮助表示赞赏。:-)

^{较新的分区将被动态创建，并且在特定分区上给出提示是不可行的。我的情况是“最新”分区很快，“旧”是“慢”，“最旧”是“超慢”——假设没有缓存在存储层上，因为太多了。此外，我在一台单独的机器上使用代理（SPIDER），它应该为客户端提供一个单一的查询界面，不需要知道后端的分区布局，所以我更喜欢一种方法来实现它'自动的'。}

我正在使用pt-table-checksum版本2.1.4来检查我的主从设置。如果我忘记在从机上设置正确的权限，它会写一个stderr这样的错误：

# pt-table-checksum -d employees
Cannot connect to h=node03.mydomain.local                                 <== error
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-04T15:33:33      0      0        9       1       0   0.014 employees.departments
[...]

但是，它的退出状态是0。

# echo $?
0

它会找到的数字差异始终为零，因为它没有什么可比较的。0是的，文档说如果没有报告差异或错误则退出状态。但这在大多数情况下没有意义。

有没有一种方法pt-table-checksum可以避免无声地失败并将其报告为一般失败？我应该提交错误还是可以配置？

^{它被证明会让更多用户感到困惑，例如在这个问题中：“需要 pt-table-checksum help”。}

我已经设置了MySQL复制，一主一从。现在我开始使用 Percona 的pt-table-checksum. 检测从站上的任何变化或漂移的绝佳工具。

据我从其文档中了解到，它在主服务器上运行，将校验和写入percona.checksums表，并在使用常规远程 MySQL 连接连接（自动检测）的从服务器上类似地运行。但是，percona.checksums表实际上也被复制了！如果从站上的数据不同，则来自主站的校验和可能会在从站上被覆盖而不会被检测到。

现在，我担心这可能会导致我在检测变化时观察到的假阴性。

是否pt-table-checksum采取了预防措施来防止percona.checksums桌面上的复制？

• 如果是这样，这是如何工作的？（据我所知，不能像这样更改复制过滤器）这可能有助于我查明我的观察的其他原因（见下文）。
• 如果不是，那么我如何确定该表的内容反映了该特定服务器的校验和？我应该放置一个复制过滤器吗？Percona 的工具包文档实际上不鼓励这样做。

实际观察

我在我的测试设置中看到了一个事件，其中以某种方式运行pt-table-checksum没有检测到故意对从站进行的更改。检查校验和表的内容显示了主人的校验和。在从站本身上运行该工具然后显示校验和应该是不同的！这让我相信存在竞争条件，这也是难以重现的原因。

另一个假设

假设 slave 在运行的时候发生了漂移，网络连接中断了一小会儿pt-table-checksum。它会在输出中显示它无法连接到从站stderr，但不会失败。不久之后，网络恢复，复制恢复，复制主表的内容percona.checksums。然后运行pt-table-checksum --replicate-check-only​​, 将提供漏报。我在这里吗？如果是这样，在这种情况下我该如何防止这种情况发生？

这是一个常规的 MySQL 服务器/客户端，使用 Percona 的工具包。

版本：

• pt表校验和2.1.4
• MySQL客户端：5.5.24
• MySQL 服务器：5.5.24-0ubuntu0.12.04.1-log (Ubuntu 12.04)