主页 / dba / 问题 / 220874
Accepted
Benjamin Soulas
Asked: 2018-10-25 01:32:39 +0800 CST

动态生成批量导入数据

  • 772

我需要执行一个测试，我必须在 PostgreSQL（和 TimescaleDB）数据库中模拟 20 年的历史数据。我的问题是我必须生成.sql文件并在目标数据库中摄取它们（使用psql-client ）。

我们做了一些计算，结果是我们的数据库将有2610亿行在我们的表中 20 年，所以每年包含13.05B数据。

对于每一行，我们都有一个时间戳（整数类型），我认为，为了提高效率，我在我的 .sql 文件事务中写了10.000 个元素。 为了使生成的.sql文件在磁​​盘空间中变小（我在 python 中生成这些文件），我将每个文件限制为20M 行。

所以我想我可以在 bash 文件中动态生成这些文件，当生成一个文件时，我运行一个 psql 命令将它摄取到数据库中，但问题是我不知道如何处理它： ingest 过程比 .sql 文件生成花费更多时间，所以在 bash 命令中，恐怕我的 bash 脚本会等待 ingest 过程，然后开始生成新的 .sql 文件并再次执行 ingest 过程。

总而言之，我尝试创建一个伪批处理摄取过程，其中每个已成功摄取的生成的 .sql 文件都将被删除，以避免占用太多磁盘空间。

如何避免在开始另一个 .sql 文件生成之前等待摄取过程然后开始摄取过程的事实？

这是出于开发目的，我想要摄取的这些数据接近于我们想要在生产模式下创建的数据。目前的目标是执行读取请求并在 PostgreSQL 和 TimescaleDB 中比较这些请求。

postgresql bulk-insert

4 个回答

  1. Best Answer

    实际的摄取率将取决于保存数据文件和日志文件的 RAID 后面的主轴（物理硬盘）数量。

    一个体面的 RAID 设置应该使您能够达到~1 M rows/sec或更高。

    您的算法是另一个主要瓶颈。

    方法一

    create file您可以通过让 Python 直接连接到数据库来跳过该步骤。

    我不知道是否有 Python 的 Timescale DB 驱动程序。

    对于速度：

    • 使用 BIND 变量
    • 缓存你的语句句柄
    • 如果驱动程序支持，请使用 BIND 数组。

    确保“每个 BIND 数组的行数”和“COMMIT 之间的组数”是程序中的变量。你会想用这些来找到the sweet spot你的系统。也就是说：您将需要运行benchmarks以找到适合您的设置的最佳值。

    本例insert_vendor_list中的将使用该BIND Array技术。

    方法二

    有时，您希望在数据生成之前对其进行检查。在那种情况下，您想要创建一个.csv文件，而不是一个.sql充满INSERT语句的文件。这些INSERT语句中的每一个都需要进行硬解析。

    我玩过的 RDBMS 数据库带有专门的应用程序，可以（或接近）最大速率（由您的 RAID 设置定义）摄取 CSV。

    速度性能注意事项

    根据您真正测试的内容，您应该在摄取数据之前禁用/删除目标表上的所有索引和约束。然后，之后创建它们。

    • 1

  2. 正如其他人所提到的，INSERT 语句会变慢。我将从查看管道 COPY 到 psql 开始。

    要使用示例，请使用 pg_dump 或 pgAdmin 以纯格式从表中转储数据。查看该文件，您会看到一行看起来像COPY <table_name> (<column_list>) FROM stdin;后面是制表符分隔格式的转储数据，最后一行是\..

    • 0

  3. COPY table FROM 'filename' WITH BINARY您可以通过使用而不是 CSV来大大加快您的摄取问题。这将要求您必须转储为二进制文件（使用COPY table TO 'filename' WITH BINARY）。

    或者，您也可以创建自己的程序来转储为这种格式，并以非常快的速度将数据流式传输到数据库中。这意味着数据库后端不必转换text为内部类型。

    • 0

  4. COPY 的瓶颈在于它是在 TimescaleDB 的事务中执行的，因此将是单线程的。

    对于批量导入以测试插入吞吐量，我们推荐我们的并行 COPY 工具： https ://github.com/timescale/timescaledb-parallel-copy

    您将它指向一个巨大的 CSV，它会在对数据库运行并行 COPY 命令之前按正确的时间顺序分割 CSV。

    （TimescaleDB 成员在这里）

    • 0

相关问题