Leon's questions

我们正在使用PostgreSql-13作为核心服务器，遇到了性能瓶颈。

硬件包括2个CPU（AMD EPYC9754，每个128核256线程），128GB内存，硬件RAID0包括2 * 4T SSD（Samsung990Pro）。

我认为 PG 服务器未能充分发挥硬件的潜力。

在我在这里问这个问题之前，我已经做了以下事情：

1. 检查我的 SQL 查询（准确使用主键，没有序列扫描）；
2. 确认我的程序运行正确（我虽然用 Python 和 C++ 编写了相同的逻辑，但都运行得很慢）；
3. 配置我的PG服务器使用HugePage（我总共配置了80GB的HugePage内存，并允许PG服务器使用64GB作为shared_buffers）；
4. 增加每个 PG worker 的内存限制( work_mem，temp_buffers，...)；
5. 增加并行工作者的数量( max_worker_processes，max_parallel_workers_per_gather，max_parallel_workers，...)；
6. 打开force_parallel_mode；
7. 降低 io 成本和 cpu 成本 cfg( random_page_cost=1.01, cpu_tuple_cost=0.0025, cpu_index_tuple_cost=0.001,...)；
8. 最大default_statistics_target为1000；
9. 禁用复制；
10. 启动32或64个进程并行连接到服务器，并使用不同的主键查询同一张表的不同行。
11. 所有查询都是只读的，并且测试期间没有插入/更新/DDL 操作；

我的期望是：

1. PG服务器至少使用50%的CPU/内存资源；
2. 没有任何单个进程阻塞其他查询；
3. 总吞吐量基本上随着我分配给 PG 服务器的资源量线性增加（也许我很天真？）；

我的测试：

1. 表定义：

   CREATE TABLE IF NOT EXISTS ob_lots(
       t_stamp INT8 NOT NULL,
       trd_sec INT4 NOT NULL,
       o_level INT2 NOT NULL,
       i_olots INT4 NOT NULL,
       f_olots FLOAT4 NULL,
   CONSTRAINT prk_ob_lots PRIMARY KEY( t_stamp, o_level ) );
   

2. 查询：

   SELECT f_olots, t_stamp, trd_sec FROM ob_lots
   WHERE t_stamp BETWEEN $1 AND $2
   ORDER BY t_stamp DESC, o_level DESC
   LIMIT 4096;
   

3. 测试程序：启动我的程序的多个实例，使用上述查询 SQL 并行随机查询上述表格中的行。当客户端数量从 2 个增加到 4 个，或从 4 个增加到 8 个，或从 8 个增加到 16 个时，我们确实观察到总吞吐量每次都几乎翻倍。但从 16、32 或更高时，总吞吐量从未改变。

4. SQL解释：

   Gather  (cost=1000.28..1002.41 rows=1 width=18) (actual time=6.840..9.232 rows=0 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   Buffers: shared hit=8
   ->  Limit  (cost=0.28..2.31 rows=1 width=18) (actual time=0.033..0.033 rows=0 loops=1)
           Buffers: shared hit=8
           ->  Index Scan Backward using prk_ob_lots_sc5555 on ob_lots_sc5555  (cost=0.28..2.31 rows=1 width=18) (actual time=0.031..0.031 rows=0 loops=1)
               Index Cond: ((t_stamp >= 123) AND (t_stamp <= 456))
               Buffers: shared hit=8
   Planning:
   Buffers: shared hit=109
   Planning Time: 0.759 ms
   Execution Time: 9.274 ms
   

以下是我觉得奇怪的事情：

1. 有一个 PG 进程使用了​​几乎 100% 的单个 CPU 核心，其他进程的使用率都很少；
2. 测试期间没有io、没有交换，并且大量内存/CPU/io 资源处于空闲状态。似乎所有其他工作进程都在等待主进程（我猜是主进程占用了 100% CPU）PG 进程，从而导致瓶颈；
3. 测试数据库使用了大约 4GB 的磁盘空间，因此足够小以至于可以完全由 PG 保存在内存中。事实上，正如我们所观察到的，根本没有 io 操作；

它在做什么？为什么所有工作进程都在等待单个进程？

这是我的 PG conf 的主要部分（我只列出我更改的行）：

最大连接数 = 2048

共享缓冲区 = 64GB

huge_pages = 开启

临时缓冲区 = 256MB

最大准备事务数 = 256

工作内存 = 256MB

维护工作内存 = 16GB

autovacuum_work_mem = -1

dynamic_shared_memory_type = posix

有效 io 并发 = 1000

维护io并发性 = 1000

最大工作进程数 = 256

最大并行维护工作者数 = 256

每个聚集的最大并行工作者数 = 256

parallel_leader_participation = on

最大并行工作者数 = 256

fsync = off

同步提交 = off

全页写入 = 关闭

wal_compression = on

wal_buffers = -1

wal_writer_delay = 10000ms

wal_writer_flush_after = 1GB

提交延迟 = 100000

commit_siblings = 128

检查点超时 = 1d

最大wal大小=128GB

min_wal_size = 32GB

检查点完成目标 = 1.0

checkpoint_flush_after = 0

检查点警告 = 0

最大发送者数 = 0

seq_page_cost = 1.0

随机页面成本 = 1.01

cpu_tuple_cost = 0.0025

cpu_index_tuple_cost = 0.001

CPU 操作成本 = 0.00125

有效缓存大小 = 64GB

默认统计目标 = 1000

force_parallel_mode = on

自动清理 = 开启

top和的输出如下iotop：

顶部 - 16:38:16 启动 4:09，2 个用户，平均负载：14.16、9.14、3.97

任务：总计 1581 个，正在运行 2 个，正在休眠 1573 个，已停止 0 个，僵尸 6 个

%Cpu：3.5 us、4.3 sy、0.0 ni、92.1 id、0.0 wa、0.0 hi、0.1 si、0.0 st

GiB 内存：总计 125.6，可用 34.6，已使用 82.9，缓冲/缓存 9.1

GiB 交换：总计 1.0，可用 1.0，已使用 0.0。42.6 可用内存

进程号 USER PR NI VIRT RES SHR SWAP %CPU %MEM TIME+ COMMAND

31159 leon 20 0 4654.2米 105.1米 12.5米 0.0米 S 152.7 0.1 7:09.93 负载

3186 postgres 0 -20 66.7g 18.6m 16.2m 0.0m R 99.5 0.0 5:03.16 postgres #它是主服务器吗？

3192 postgres 0 -20 80.4m 6.8m 3.6m 0.0m S 8.2 0.0 0:24.97 postgres

32218 postgres 0 -20 66.7g 13.5米 9.9米 0.0米 S 5.8 0.0 0：12.90 postgres

31217 postgres 0 -20 66.7g 13.4m 9.9m 0.0m S 5.3 0.0 0:12.74 postgres

31234 postgres 0 -20 66.7g 13.5米 9.9米 0.0米 S 5.3 0.0 0：12.74 postgres

（很多很多的 postgres 进程...）

总磁盘读取量：0.00 B/s | 总磁盘写入量：0.00 B/s

当前磁盘读取：0.00 B/s | 当前磁盘写入：0.00 B/s

TID  PRIO  USER     DISK READ DISK WRITE>    COMMAND

  1 be/4 root        0.00 B/s    0.00 B/s init

  2 be/4 root        0.00 B/s    0.00 B/s [kthreadd]

  3 be/0 root        0.00 B/s    0.00 B/s [rcu_gp]

  4 be/0 root        0.00 B/s    0.00 B/s [rcu_par_gp]

  5 be/0 root        0.00 B/s    0.00 B/s [slub_flushwq]

多年来，$PGUSER当我使用 psql/pg_dump/pg_restore 等客户端工具时，我一直习惯使用环境变量。

最近我建立了一个新的 PG13 服务器，当我尝试在服务器终端上使用客户端工具时，环境变量$PGUSER没有生效。我收到如下消息：

leon@mamba:~$ echo $PGUSER

PostgreSQL 的

leon@mamba:~$ psql

psql：错误：通过套接字“/var/run/postgresql/.s.PGSQL.5432”连接到服务器失败：

严重：角色“leon”不存在

如果我在另一台物理计算机的终端上使用这些工具，则环境变量会正常生效。

当然，两台机器上都已经设置了var。

我的pg_hba.conf：

# 类型 数据库 用户 地址 方法

主机全部全部 127.0.0.1/32 信任

主机全部全部 192.168.xx/16 md5

#IPv6 本地连接：

托管所有所有::1/128 信任

我正在使用 PostgreSQL-13.0。

有没有办法获取数据库的创建时间或上次修改时间？

谢谢！

我正在Postgresql-11中创建一个表，我需要同时为其创建一个主键，以防止在创建PK之前插入重复的数据，所以我希望这两个操作在同一个事务中完成。

我知道这可以得到它：

CREATE TABLE IF NOT EXISTS my_tab (
    my_key INT8 NOT NULL,
    PRIMARY KEY ( my_key ) );

但我还想为此 PK 设置一个名称，而不是默认名称，以便使维护工作变得容易。

如果我这样做：

CREATE TABLE IF NOT EXISTS my_tab ( my_key INT8 NOT NULL );
ALTER TABLE my_tab ADD CONSTRAINT custom_pk_name_my_tab PRIMARY KEY ( my_key );

我担心这两个语句将在两个不同的事务中完成。即在创建 PK 之前是否有可能插入一些重复的数据？

更进一步，如果该表已经存在怎么办？如果 tabe 已经存在，第二条语句会失败吗？

上面的SQL是在我的C++程序中执行的，这是一个长时间的守护服务。我不希望它因 SQL 故障而在无人参与的情况下崩溃。

如何获得？谢谢！

我搜索了 PostgreSQL 和 Google 的文档，但没有找到该函数pg_get_tabledef, whilepg_get_indexdef存在。

没有吗pg_get_tabledef？也许实施起来太困难了？但是 pgAdmin4 可以为数据库中的每个对象提供非常漂亮/清晰/干净的 DDL 脚本。效果如何？

事实上，我需要比较两个数据库的许多表的定义（How to rapid Compare of multiple tables of两个 PostgreSQL 数据库的定义？）。

尽管有pg_dump --section=pre-data并且pg_dump --section=post-data可以转储所有定义，但比较结果仍然是一项艰巨的工作。我需要准确找出哪些表结构已更改。

所以我期待pg_get_tabledef，比如pg_get_indexdef，但是对于桌子。奇怪的是，虽然存在，但没有这样的功能pg_get_indexdef。

请给我一些提示吗？谢谢！

我在 Postgresql-11 数据库中有数百个具有相同结构的表，我需要对每个表执行相同的统计。

由于真正的统计逻辑非常复杂，为了简化我在这里的问题，假设我需要计算每个的 MAX/MIN/MEAN/STDDEV。

我不想为每个单独的表一个一个地编辑单独的 SQL，因为如果是这样，我将编写数百行非常相似的 SQL，唯一不同的是表名。

所以我在一个函数中使用动态 SQL 一次对一个表进行计算，如下所示：

DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
    IN  table_name VARCHAR,
    OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$

DECLARE sql_string VARCHAR :=
    format('
        SELECT ''%s'' table_name,     -- just for validating
            MAX(feature) max_feature,
            MIN(feature) min_feature,
            AVG(feature) avg_feature,
            STDDEV(feature) std_feature
        FROM "%s";',
        table_name, table_name );

BEGIN
  EXECUTE sql_string INTO result_;
END
$func$;

然后在查询中用数据库中的每个表名调用它，如下所示：

SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
  AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";

它有效，但结果类似于以下内容：

"table_name", "get_features_for"

“analy_001”，“（analy_001，-3,2,0,2.5）”

“analy_002”，“（analy_002，-3,2，-1,2）”

“analy_003”，“（analy_003，-3,2,0,2）”

你可以看到所有的结果列都被包装成一个字符串列，让我不能方便地使用它们。

我希望它们类似于：

表名、最大特征、最小特征、平均特征、标准特征

"analy_001", "analy_001", -3, 2, 0, 2.5

"analy_002", "analy_002", -3, 2, -1, 2

"analy_003", "analy_003", -3, 2, 0, 2

我应该怎么得到它？

顺便说一句，我正在使用 pgAdmin。

谢谢！！！

我有一张像下面这样的大桌子：

CREATE TABLE public.huge_table (
    sampl_day date NOT NULL,
    tick_time timestamp(6) with time zone NOT NULL,
    crit_feat integer NOT NULL,
    ---
    --- other fields
    ---
    CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time)
);
CREATE INDEX idx_huge_table_day_time
ON public.huge_table USING brin (sampl_day, tick_time);

该表存储了一个时间序列的许多样本（行），一个样本一行。大多数时候，“crit_feat”字段的值很低，例如 1 或 2。在某些异常时间点，它会变得很高，例如 100。

在单个查询中找出这些特殊行非常容易。我们称它们为“特殊时间点”。

现在我需要查看查询结果集中上述每个特殊时间点之前/之后5s内发生的行，以找出特殊事件之前/之后发生的事情。

如果我只需要找出一个这样的特殊时间点和它前后5s的行，相对容易一些。

我可以这样做：

WITH spec_time as (
    SELECT tick_time tp
    FROM huge_table
    ORDER BY crit_feat DESC LIMIT 1)

SELECT *
FROM huge_table
WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s'
                    AND (SELECT tp FROM spec_time) + INTERVAL '5s'
ORDER BY tick_time;

但是我需要对每个特殊点和UNION所有特殊点都这样做！

所有特殊点的数量至少在20k左右，应该选出的事件不仅是“ crit_feat”字段具有最大值的事件，而且还包括值大于指定阈值的事件，例如：整个数据集中“crit_feat”字段的中值。

我想我可以在存储过程/函数中使用临时表和游标来获取它，但我期待一种更简单的方法！

抱歉我的英语不好，我希望我已经正确表达了我想说的话。

谢谢！

我在 Postgresql-11 中有一个巨大的表，如下所示：

CREATE TABLE my_huge_table(
    tick_time timestamp(6) with time zone NOT NULL,
    brok_time timestamp(6) with time zone,
    trade_day date NOT NULL,

    --other fields ...
    ...

    CONSTRAINT my_huge_table_pkey PRIMARY KEY (tick_time)
);

CREATE INDEX idx_my_huge_table_td_time ON my_huge_table USING brin
    ( trade_day, abs(tick_time - brok_time) );

然后我进行查询并希望它利用索引idx_my_huge_table_td_time，如下所示：

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND ABS(tick_time - brok_time) < INTERVAL '10 s';

但是 PostgreSQL 拒绝执行它，并说：

错误：函数 abs(interval) 不存在

第 3 行：AND ABS(tick_time - brok_time) < INTERVAL '10 s'

           ^ 

提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

SQL 状态：42883 字符：525

看起来 funcabs()不能接受区间值作为参数。

然后，我改变了我的查询：

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND GREATEST(tick_time - brok_time, brok_time - tick_time) < INTERVAL '10 s';

这次可以执行了，但是没有利用到索引。

我的问题：

1.索引表达式应该怎么写？事实上，我希望它记录两个时间戳字段之间的距离（绝对间隔值）；

2.我应该如何编写可以使用上面索引的查询？

3.实际上GREATEST(tick_time - brok_time, brok_time - tick_time)不是一个好主意，因为它调用了两次计算。不是吗？

4.创建索引后，我注意到PostgreSQL上报的索引真正的DDL SQL是：

CREATE INDEX idx_my_huge_table_td_time ON public.my_huge_table USING brin
    (trade_day, abs(date_part('epoch'::text, tick_time - brok_time)));

表达式的值是否已转换为类型text？这显然不是我的期望！

最后几天，我针对大量大量写入事务调整了 PG11，并获得了惊人的结果。

我唯一担心的是，我关闭时是否有更大的丢失数据的风险synchronous_commit，并使用了一个大的wal_buffers值。

我正在使用 Postgresql-11，使用传统的 HDD 作为物理存储设备。

我需要优化写作性能。

我已将 -1 更改wal_buffers为 512MB，这意味着wal_buffers不再使用shared_buffers. 我对吗？

我想知道它是否可以提高写入性能以扩大shared_buffers。在我看来，它仅用于查询？

当前值为shared_buffers2GB，我的RAM是8GB。我想将它更改为 4GB。谢谢！

在调试程序时，我需要经常删除一个Postgresql的数据库，然后重新创建它。

为方便起见，我想在单个命令行中执行此操作，如下所示：

psql -c "DROP DATABASE my_db; CREATE DATABASE my_db;"

但我得到一个错误：

错误：DROP DATABASE 不能在事务块内运行

有什么办法可以做到吗？谢谢！

Postgresql-11

select tick_time, nano_secs,
    concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'),
            to_char(nano_secs, '000') )
from ticks
order by tick_time, nano_secs limit 100;

我想像上面那样连接 2 个字符串，但它总是在两个字符串之间添加一个空格字符。

如何防止它这样做？

谢谢！

我将使用 BRIN 索引来替换 Postgresql-11 中日期/时间戳列上的 B 树索引。我以前从未使用过它。

因为如果数据以列被索引的顺序物理存储，那么 BRIN 索引会更有效。

我怀疑我是否应该删除所有数据，然后按顺序重新插入它们。我的数据被用于静态分析，不会改变。

pg_dump我有一个由PostgreSQL v12 客户端附带的大转储文件，它是自定义格式而不是纯 sql 文本。服务器的版本也是 PostgreSQL v12。

现在我需要将文件重新加载到 PostgreSQL v11 服务器中，我收到一条错误消息：

unrecognized configuration parameter "default_table_access_method"

我猜这个参数是PG12引入的，所以PG11识别不出来，虽然我用的是12版本的pg_restore.

因为文件很大并且是二进制格式，我不能编辑它来手动删除该行。

我pg_restore test_file -f plain.sql用一个测试转储文件对其进行了测试，它看起来像工作，我可以从纯 SQL 文件中删除该行。但真正的转储文件大约是 30GB，从二进制扩展的纯 SQL 文件可能更大（工作数据库占用 300GB 磁盘空间）。

有没有办法，我可以干净地将它重新加载到 PG11 中，只有没有“SET default_table_access_method = heap;”这一行？

其实下面这些都可以忽略，因为都是默认值：

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';

我有一个使用 PostgreSQL v11 运行的大型数据库，并且磁盘空间非常有限，所以我不得不VACUUM (FULL)频繁地访问数据库（例如每天）。

但是吸尘是一项非常耗时的操作。此外，我经常遇到真空错误消息，例如

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

幸运的是，服务可以在一天中的特定时间点关闭，所以我可以在关闭数据库后转储数据库，立即恢复并重新启动服务。

此外，我发现“转储/恢复”比“清理”更快。

我的问题：“倾倒/恢复”所做的事情是否包括“吸满”所做的事情？

如果是这样，我可以这样做而不是吸尘。