主页 / dba / 问题 / 251375
Accepted
dwenzel
Asked: 2019-10-18 06:38:00 +0800 CST

看不到进程进度，机器在尝试导入 5 亿条记录时变慢

  • 772

工作环境：PostgreSQL 12.0，Ubuntu 18.10，4GB内存，i5-3230M [email protected]×4，Os 64位

我正在尝试使用我的 PC 将包含超过 5 亿条记录的 .csv 文件导入 PostgreSQL 数据库表。（我们正在我的 PC 上进行测试，直到它可以工作，之后我们将在服务器上进行）

我的公司正在从 Firebird 迁移到 Postgres，所以这应该是一次性的任务。之后，我们预计每天插入约 20 万条记录。

使用（从原始 firebird 数据库生成的 SQL）创建表

     CREATE TABLE REGDATA
     (
        CODIGO integer NOT NULL,
        DTAREG timestamp NOT NULL,
        PERIOD integer NOT NULL,
        FLDCODIGO integer,
        REGVALUE double precision,
        CLICODIGO integer,
        SITCODIGO integer,
        CONSTRAINT PK_REGDATA PRIMARY KEY (CODIGO)
     );

.csv 是使用FBExport 1.9生成的

./fbexport -Sc -D /opt/firebird/bin/measures.fdb -H localhost -U user -P password -F /home/dani/Documents/raw_regdata.out -Q "SELECT * FROM REGDATA"

我将 raw_regdata.out 转换为 utf8 以使用 postgres 编码。去掉一些字符（ã、º等）

iconv -c -t utf8 /home/dani/Documents/raw_regdata.out > /home/dani/Documents/utf8_regdata.out

我已遵循Postgres关于如何导入大数据的指南，例如：

  • 删除所有索引；
  • 增加maintenance_work_mem (maintenance_work_mem = 512MB)
  • 增加 max_wal_size (max_wal_size = 4GB)
  • 设置 wal_level = 最小
  • 设置 max_wal_senders = 0

连接到 Postgres 集群后，我使用 COPY 导入数据

COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

问题来了，我看不到命令​​是否正在执行，一段时间后我的电脑变得非常慢，它只是冻结了。

第一次尝试：让它运行 2-3 小时，然后终止进程（终端上的 Ctrl+c），从日志中我看到它正在进行（第 131158327 行）：

2019-10-16 10:28:05.657 -03 [9258] postgres@measures ERROR:  canceling statement due to user request
2019-10-16 10:28:05.657 -03 [9258] postgres@measures CONTEXT:  COPY regdata, line 131158327: ""178865944","13.03.2015 12:10:00","600","22439","358.60000000000002","9","37""
2019-10-16 10:28:05.657 -03 [9258] postgres@measures STATEMENT:  COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

但是我尝试选择一些东西却什么也得不到，我认为由于 Copy 仅在整个过程完成后提交，它只是撤消所有内容，我最终得到一个空表

第二次尝试：让它运行 24 小时（即使我的 PC 完全锁定），然后再次终止该进程，希望在日志上看到更多进展，但令我惊讶的是，我没有看到任何类似的日志，只有相同的 3 个警告结束和再次：

2019-10-16 17:42:31.061 -03 [5646] LOG:  using stale statistics instead of current ones because stats collector is not responding
.
.
.
2019-10-17 06:10:31.423 -03 [2734] WARNING:  worker took too long to start; canceled
2019-10-17 06:57:19.150 -03 [5964] WARNING:  autovacuum worker started without a worker entry
2019-10-17 08:04:47.445 -03 [2327] LOG:  starting PostgreSQL 12.0 (Ubuntu 12.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
2019-10-17 08:04:47.622 -03 [2327] LOG:  listening on IPv4 address "127.0.0.1", port 5412
2019-10-17 08:04:48.048 -03 [2327] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5412"
2019-10-17 08:04:53.879 -03 [2546] LOG:  database system was interrupted; last known up at 2019-10-16 15:47:25 -03
2019-10-17 08:05:10.887 -03 [2546] LOG:  database system was not properly shut down; automatic recovery in progress
2019-10-17 08:05:11.534 -03 [2546] LOG:  redo starts at 14/B8254908
2019-10-17 08:05:11.847 -03 [2546] LOG:  invalid record length at 14/B8260848: wanted 24, got 0
2019-10-17 08:05:11.847 -03 [2546] LOG:  redo done at 14/B82607D0
2019-10-17 08:05:16.417 -03 [2327] LOG:  database system is ready to accept connections

有一种方法可以监控进度（它当前正在处理哪条线），并且在进程处于活动状态时出现死机是正常的吗？

编辑：查看原始火鸟数据库，我可以看到主键未排序： 在此处输入图像描述

postgresql import

2 个回答

  1. Best Answer

    没有内置的方法来监控进度。v12 为某些操作添加了进度监控，但不针对 COPY。

    我在这种情况下所做的就是用来strace -s 1024 -y -p <backend pid>跟踪加载过程。这将显示它从“/home/dani/Documents/utf8_regdata.out”文件中读取的数据（以及一堆其他内容）。然后，您可以从该流中获取最近的主键，然后fgrep -n ,<primarykey> /home/dani/Documents/utf8_regdata.out查看它所在的行号。这不是最优雅的过程，但比完全在黑暗中要好。

    如果你没有删除主键，并且加载时数据没有按主键顺序排序，那么一旦主键索引的大小超过某个阈值，就会出现严重的 IO 拥塞。这可以解释“陈旧的统计数据”和“工人启动时间太长”，并且系统也可能变得如此缓慢以至于它似乎已经死了（特别是如果您使用的是 GUI）。

    索引大小的性能崩溃阈值通常介于shared_buffer和之间greatest(shared_buffers,RAM - shared_buffers)，但具体位置取决于内核版本和设置。

    • 1

  2. 另一种方法是使用count_estimate函数来了解计算记录的进度SELECT count_estimate('SELECT * FROM REGDATA')

    • 0

相关问题