postgresql - 如何将更新期间数据库争用的可能性降至最低

Question

我编写了一些 PostgreSQL 数据库客户端代码，用来自多个客户端的 IP 地址和主机名表更新中央数据库。有两个表:一个用于保存 IP 地址和主机名之间的映射，另一个用于保存尚未解析为主机名的 IP 地址队列。

这是 IP 地址到主机名的映射表:

CREATE TABLE g_hostmap(
    appliance_id     INTEGER,
    ip               INET,
    fqdn             TEXT,
    resolve_time     TIMESTAMP, 
    expire_time      TIMESTAMP,
    UNIQUE(appliance_id, ip))

这是工作队列表:

CREATE TABLE g_hostmap_work(
    ip               INET,
    input_table      TEXT)

每个数据库客户端都从一个工作队列表中拉取请求。每个请求都包含一个私有(private) IPv4 地址，需要为其请求主机名。

工作流程如下:每个客户端周期性地向中央数据库工作队列查询需要主机名的IP地址列表，对地址进行反向DNS查找，然后更新主机名表与(IP 地址，主机名)对，一次一个。我希望通过尝试同时解析相同的 IP 地址来最大程度地减少多个客户端重复工作的可能性。

我将每批更新限制为 10 行或工作队列行大小的 10%，以较大者为准。客户的时间安排有些独立。如何在更新过程中进一步减少对 DNS 名称服务器和主机名表的争用？我的客户担心会有很多重复工作。

这是对工作队列中项目计数的初始查询:

SELECT COUNT(*)
       FROM g_hostmap_work queued
       LEFT JOIN g_hostmap cached
            ON queued.ip = cached.ip
            AND now() < cached.expire_time

这是返回工作队列中项目子集的查询:

SELECT queued.ip, queued.input_table, cached.expire_time
       FROM g_hostmap_work queued
       LEFT JOIN g_hostmap cached
            ON queued.ip = cached.ip
            AND now() < cached.expire_time
       LIMIT 10

这是一个使用新的 IP 地址/主机名映射更新数据库的单个 INSERT 语句的示例:

INSERT INTO g_hostmap_20131230 VALUES
(NULL, '192.168.54.133', 'powwow.site', now(), now() + 900 * INTERVAL '1 SECOND')

Answer 1

我要提出一个听起来很奇怪的建议。在源表中添加一个auto-inc big int，并创建一组10个取模索引。这是一个简单的测试用例示例:

create table queue (id bigserial, input text);
create index q0 on queue (id) where id%10=0;
create index q1 on queue (id) where id%10=1;
create index q2 on queue (id) where id%10=2;
create index q3 on queue (id) where id%10=3;
create index q4 on queue (id) where id%10=4;
create index q5 on queue (id) where id%10=5;
create index q6 on queue (id) where id%10=6;
create index q7 on queue (id) where id%10=7;
create index q8 on queue (id) where id%10=8;
create index q9 on queue (id) where id%10=9;
insert into queue select generate_series(1,50000),'this';

我们在这里所做的是创建一组索引表的 1/10。接下来，我们将选择其中一个范围的一部分进行处理:

begin;
select * from queue where id%10=0 limit 100 for update;
id  | input 
------+-------
10 | this
20 | this
30 | this
-- do work here --
commit;

现在是有趣的部分。如果您有超过 10 个工作人员使用此设置，您只需通过数字循环他们，当上述选择更新运行时，任何超过 10 个工作人员将等待。但任何其他数字(1 到 9)仍然有效。

begin;
select * from queue where id%10=1 limit 100 for update;
 id  | input 
-----+-------
   1 | this
  11 | this
  21 | this
  31 | this
-- do work here
commit;

这样所有的工作都被分成了 10 个桶。想要更多水桶？更改 % 后的数字并增加要匹配的索引数。